pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed a SQL to create sets of hobbies
3+ messages / 3 participants
[nested] [flat]
* Need a SQL to create sets of hobbies
@ 2006-09-20 05:18 CN <cnliou9@fastmail.fm>
0 siblings, 2 replies; 3+ messages in thread
From: CN @ 2006-09-20 05:18 UTC (permalink / raw)
To: pgsql-sql
Hi!
CREATE TABLE x1 (name TEXT, hobby TEXT, PRIMARY KEY(name,hobby));
INSERT INTO x1 VALUES('John','music');
INSERT INTO x1 VALUES('John','arts');
INSERT INTO x1 VALUES('Bob','arts');
INSERT INTO x1 VALUES('Bob','music');
INSERT INTO x1 VALUES('Rocky','copmputer');
INSERT INTO x1 VALUES('Steve','arts');
INSERT INTO x1 VALUES('Steve','football');
INSERT INTO x1 VALUES('Tom','computer');
INSERT INTO x1 VALUES('Tom','music');
select * from x1;
name | hobby
-------+----------
John | music
John | arts
Bob | arts
Bob | music
Rocky | computer
Steve | arts
Steve | football
Tom | computer
Tom | music
(9 rows)
John and Bob have the same hobbies - music and arts. So music and arts
are treated as one set of hobbies.
Rocky has an unique set of interest - computer.
Steve also likes arts just as John and Bob do, but he also has an
exclusive interest - football. Thus, his set of hobbies is unique -
arts, football.
One of Tom's hobbies, music, overlaps those of John, Bob, and Rocky; but
computer does not. Hence his hobbies, computer and music, forms a new
set of hobbies.
Now we have 4 sets of hobbies:
set 1: music, arts
set 2: computer
set 3: arts, football
set 4: computer, music
I am looking for an SQL that creates sets of hobbies in table x2 by
selecting from table x1:
CREATE TABLE x2 (sid INTEGER, hobby TEXT, PRIMARY KEY(sid,hobby));
and makes x2 contain rows:
sid | hobby
-------+----------
1 | music
1 | arts
2 | computer
3 | arts
3 | football
4 | computer
4 | music
where gid starts from 1.
Thank you in advance!
CN
--
http://www.fastmail.fm - A no graphics, no pop-ups email service
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need a SQL to create sets of hobbies
@ 2006-09-20 11:33 Harald Fuchs <hf0731x@protecting.net>
parent: CN <cnliou9@fastmail.fm>
1 sibling, 0 replies; 3+ messages in thread
From: Harald Fuchs @ 2006-09-20 11:33 UTC (permalink / raw)
To: pgsql-sql
In article <1158729519.6421.271361727@webmail.messagingengine.com>,
"CN" <cnliou9@fastmail.fm> writes:
> select * from x1;
> name | hobby
> -------+----------
> John | music
> John | arts
> Bob | arts
> Bob | music
> Rocky | computer
> Steve | arts
> Steve | football
> Tom | computer
> Tom | music
> (9 rows)
> Now we have 4 sets of hobbies:
> set 1: music, arts
> set 2: computer
> set 3: arts, football
> set 4: computer, music
> I am looking for an SQL that creates sets of hobbies in table x2 by
> selecting from table x1:
> CREATE TABLE x2 (sid INTEGER, hobby TEXT, PRIMARY KEY(sid,hobby));
> and makes x2 contain rows:
> sid | hobby
> -------+----------
> 1 | music
> 1 | arts
> 2 | computer
> 3 | arts
> 3 | football
> 4 | computer
> 4 | music
> where gid starts from 1.
You could use something like that:
CREATE TEMP TABLE tmp (
id SERIAL NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO tmp (name)
SELECT DISTINCT ON (ARRAY (
SELECT y1.hobby
FROM x1 y1
WHERE y1.name = y2.name
ORDER BY y1.name, y1.hobby
))
y2.name
FROM x1 y2;
INSERT INTO x2 (sid, hobby)
SELECT tmp.id, x1.hobby
FROM tmp
JOIN x1 ON x1.name = tmp.name;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need a SQL to create sets of hobbies
@ 2006-09-20 18:38 Aaron Bono <postgresql@aranya.com>
parent: CN <cnliou9@fastmail.fm>
1 sibling, 0 replies; 3+ messages in thread
From: Aaron Bono @ 2006-09-20 18:38 UTC (permalink / raw)
To: CN <cnliou9@fastmail.fm>; +Cc: pgsql-sql
On 9/20/06, CN <cnliou9@fastmail.fm> wrote:
>
> Hi!
>
> CREATE TABLE x1 (name TEXT, hobby TEXT, PRIMARY KEY(name,hobby));
>
> INSERT INTO x1 VALUES('John','music');
> INSERT INTO x1 VALUES('John','arts');
> INSERT INTO x1 VALUES('Bob','arts');
> INSERT INTO x1 VALUES('Bob','music');
> INSERT INTO x1 VALUES('Rocky','copmputer');
> INSERT INTO x1 VALUES('Steve','arts');
> INSERT INTO x1 VALUES('Steve','football');
> INSERT INTO x1 VALUES('Tom','computer');
> INSERT INTO x1 VALUES('Tom','music');
>
> select * from x1;
>
> name | hobby
> -------+----------
> John | music
> John | arts
> Bob | arts
> Bob | music
> Rocky | computer
> Steve | arts
> Steve | football
> Tom | computer
> Tom | music
> (9 rows)
>
> John and Bob have the same hobbies - music and arts. So music and arts
> are treated as one set of hobbies.
>
> Rocky has an unique set of interest - computer.
>
> Steve also likes arts just as John and Bob do, but he also has an
> exclusive interest - football. Thus, his set of hobbies is unique -
> arts, football.
>
> One of Tom's hobbies, music, overlaps those of John, Bob, and Rocky; but
> computer does not. Hence his hobbies, computer and music, forms a new
> set of hobbies.
>
> Now we have 4 sets of hobbies:
>
> set 1: music, arts
> set 2: computer
> set 3: arts, football
> set 4: computer, music
>
> I am looking for an SQL that creates sets of hobbies in table x2 by
> selecting from table x1:
>
> CREATE TABLE x2 (sid INTEGER, hobby TEXT, PRIMARY KEY(sid,hobby));
>
> and makes x2 contain rows:
>
> sid | hobby
> -------+----------
> 1 | music
> 1 | arts
> 2 | computer
> 3 | arts
> 3 | football
> 4 | computer
> 4 | music
>
> where gid starts from 1.
Your best design is to break this into 3 tables:
person (
person_id,
person_name
)
hobby (
hobby_id,
hobby_name
)
person_hobby (
person_id,
hobby_id
)
Then you can get the list of hobbies for each person like this:
SELECT
person.person_id,
array(
SELECT hobby_name
FROM person_hobby
INNER JOIN hobby ON (hobby.hobby_id = person_hobby.hobby_id)
WHERE person_hobby.person_id = person.person_id
ORDER BY hobby_name
) AS hobby_list
FROM person
Then do this to flatten it back out:
SELECT
-- Gives you a unique id though using a SERIAL on a table would be
better
min(mysub.person_id),
hobby.hobby_name
FROM (
SELECT
person.person_id,
array(
SELECT hobby_name
FROM person_hobby
INNER JOIN hobby ON (hobby.hobby_id = person_hobby.hobby_id)
WHERE person_hobby.person_id = person.person_id
ORDER BY hobby_name
) AS hobby_list
FROM person
) mysub
INNER JOIN person_hobby ON (person_hobby.person_id = mysub.person_id)
INNER JOIN hobby ON (hobby.hobby_id = person_hobby.hobby_id)
GROUP BY
mysub.hobby_list,
hobby.hobby_name
I did not try it so it may require a little tweaking to work. Also, I don't
know what the performance would be like.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-09-20 18:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-20 05:18 Need a SQL to create sets of hobbies CN <cnliou9@fastmail.fm>
2006-09-20 11:33 ` Harald Fuchs <hf0731x@protecting.net>
2006-09-20 18:38 ` Aaron Bono <postgresql@aranya.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox