|
|
 | SQL-forespørgsel (PostgreSQL)
Fra : Jonas Koch Bentzen |
Dato : 14-02-02 14:29 |
|
Jeg sidder her men en SQL-forespørgsel, der volder lidt problemer: Jeg
har en række billeder, der hver har ét eller flere motiver. Relationerne
mellem motiv og billede er gemt i en tabel ved navn picturesAndMotives,
der ser sådan her ud:
picture | motive
2345-001 | 4
2345-001 | 6
2467-233 | 3
2467-233 | 13
2467-233 | 4
Som vi kan se, har billede 2345-001 motiverne 4 og 6, mens billede
2467-233 har motiv 3, 13 og 4. Lad os nu sige, at brugeren gerne vil
finde de billeder, der *både* har motiv 4 og 6 (det er kun billede
2345-001, der opfylder det krav). Hvordan gør man det? At finde de
billeder, der har ét af motiverne 4 og 6 er let nok, men at finde de
billeder, der har begge motiver, er knapt så let. Brugeren skal i øvrigt
også kunne vælge 1, 3 eller flere motiver og finde de billeder, der har
præcis de motiver, han har valgt.
Jeg har prøvet med nedenstående SQL-forespørgsel. Den virker sådan set,
men kun, hvis brugeren har valgt to motiver. Han har valgt tre, så skal
man sammenlægge tre tabeller, har han valgt fire, bliver det fire
tabeller osv., og det går ikke.
imagebank=> SELECT t1.picture FROM picturesandmotives AS t1,
picturesandmotives AS t2 WHERE t1.picture = t2.picture AND t1.motive = 4
AND t2.motive = 6;
picture | motive | motive
----------+--------+--------
0038-009 | 4 | 6
1141-028 | 4 | 6
2066-052 | 4 | 6
4783-022 | 4 | 6
(4 rows)
--
Jonas Koch Bentzen
http://understroem.dk/
| |
 Nis Jorgensen (14-02-2002)
| Kommentar
Fra : Nis Jorgensen |
Dato : 14-02-02 16:48 |
|
On Thu, 14 Feb 2002 14:29:09 +0100, Jonas Koch Bentzen
<ingen.emailadresse@eksempel.dk> wrote:
>Jeg sidder her men en SQL-forespørgsel, der volder lidt problemer: Jeg
>har en række billeder, der hver har ét eller flere motiver. Relationerne
>mellem motiv og billede er gemt i en tabel ved navn picturesAndMotives,
>der ser sådan her ud:
>
>picture | motive
>2345-001 | 4
>2345-001 | 6
>2467-233 | 3
>2467-233 | 13
>2467-233 | 4
>
>Som vi kan se, har billede 2345-001 motiverne 4 og 6, mens billede
>2467-233 har motiv 3, 13 og 4. Lad os nu sige, at brugeren gerne vil
>finde de billeder, der *både* har motiv 4 og 6 (det er kun billede
>2345-001, der opfylder det krav). Hvordan gør man det? At finde de
>billeder, der har ét af motiverne 4 og 6 er let nok, men at finde de
>billeder, der har begge motiver, er knapt så let. Brugeren skal i øvrigt
>også kunne vælge 1, 3 eller flere motiver og finde de billeder, der har
>præcis de motiver, han har valgt.
>
>Jeg har prøvet med nedenstående SQL-forespørgsel. Den virker sådan set,
>men kun, hvis brugeren har valgt to motiver. Han har valgt tre, så skal
>man sammenlægge tre tabeller, har han valgt fire, bliver det fire
>tabeller osv., og det går ikke.
>
>imagebank=> SELECT t1.picture FROM picturesandmotives AS t1,
>picturesandmotives AS t2 WHERE t1.picture = t2.picture AND t1.motive = 4
>AND t2.motive = 6;
> picture | motive | motive
>----------+--------+--------
> 0038-009 | 4 | 6
> 1141-028 | 4 | 6
> 2066-052 | 4 | 6
> 4783-022 | 4 | 6
>(4 rows)
Hvis vi antager at (picture, motive) er unik, kan vi bruge:
SELECT picture
FROM picturesandmotives
WHERE motive In (4,6,13)
GROUP BY picture
HAVING Count(motive) = 3;
--
Nis Jorgensen
Amsterdam
Please include only relevant quotes, and reply below the quoted text. Thanks
| |
Jonas Koch Bentzen (14-02-2002)
| Kommentar
Fra : Jonas Koch Bentzen |
Dato : 14-02-02 17:32 |
|
Nis Jorgensen skrev:
>
> Hvis vi antager at (picture, motive) er unik, kan vi bruge:
>
> SELECT picture
> FROM picturesandmotives
> WHERE motive In (4,6,13)
> GROUP BY picture
> HAVING Count(motive) = 3;
Virker fint - tak.
--
Jonas Koch Bentzen
http://understroem.dk/
| |
Svenne Krap (14-02-2002)
| Kommentar
Fra : Svenne Krap |
Dato : 14-02-02 18:02 |
|
On Thu, 14 Feb 2002 16:47:40 +0100, Nis Jorgensen <nis@dkik.dk> wrote:
>Hvis vi antager at (picture, motive) er unik, kan vi bruge:
>
>SELECT picture
>FROM picturesandmotives
>WHERE motive In (4,6,13)
>GROUP BY picture
>HAVING Count(motive) = 3;
En mere korrekt query, der bruger relationel algebra ville være :
select picture from picturesandmotives where movie = 4 intersect
select picture from picturesandmotives where movie = 6 intersect
select picture from picturesandmotives where movie = 13
Om den kører stærkere ved jeg så ikke. Men den burde give planneren en
mulighed for at bruge index (i stedet for full-table-scan) hvis der er
tilstrækkeligt få rækker med samme motive.
Svenne
--
Job-offerings with more than a googolplex* USD a year are instantly accepted.
* = http://www.fpx.de/fp/Fun/Googolplex/
| |
 Jonas Koch Bentzen (14-02-2002)
| Kommentar
Fra : Jonas Koch Bentzen |
Dato : 14-02-02 18:11 |
|
Svenne Krap skrev:
>
> select picture from picturesandmotives where movie = 4 intersect
unknown column "movie" : )
Spøg til side - tak for forslaget, det ser ud til at virke.
--
Jonas Koch Bentzen
http://understroem.dk/
| |
Nis Jorgensen (14-02-2002)
| Kommentar
Fra : Nis Jorgensen |
Dato : 14-02-02 18:30 |
|
On Thu, 14 Feb 2002 18:01:36 +0100, Svenne Krap <usenet@krap.dk>
wrote:
>On Thu, 14 Feb 2002 16:47:40 +0100, Nis Jorgensen <nis@dkik.dk> wrote:
>
>
>>Hvis vi antager at (picture, motive) er unik, kan vi bruge:
>>
>>SELECT picture
>>FROM picturesandmotives
>>WHERE motive In (4,6,13)
>>GROUP BY picture
>>HAVING Count(motive) = 3;
>
>En mere korrekt query, der bruger relationel algebra ville være :
>
>select picture from picturesandmotives where movie = 4 intersect
>select picture from picturesandmotives where movie = 6 intersect
>select picture from picturesandmotives where movie = 13
Mere korrekt? Jeg vil hoejst gaa med til "mere ren". Jeg er bange for
at jeg er begyndt at stirre mig blind paa nogle af de mere "beskidte"
konstruktioner i SQL - sikkert en miljoeskade fra at arbejde for meget
med Access.
--
Nis Jorgensen
Amsterdam
Please include only relevant quotes, and reply below the quoted text. Thanks
| |
Svenne Krap (14-02-2002)
| Kommentar
Fra : Svenne Krap |
Dato : 14-02-02 18:34 |
|
On Thu, 14 Feb 2002 18:29:33 +0100, Nis Jorgensen <nis@dkik.dk> wrote:
>Mere korrekt?
Mere korrekt fordi du aldrig kan få den til at gøre andet end det du
oprindeligt havde planlagt .. fx. selv om motive (sorry for the typo)
ikke er unikt mere vil den stadig kun returnere det korrekte.
Og som sagt, execution-planneren får pludseligt lov til at vise sin
værd i stedet for hjernedødt at pløje et full-table-scan igennem.
Når tabellen bliver tilpas stor vil man helst undgå full-table-scan
(man kan jo for sjov bare fjerne alle indices på sin tabel :)
Svenne
--
Job-offerings with more than a googolplex* USD a year are instantly accepted.
* = http://www.fpx.de/fp/Fun/Googolplex/
| |
Svenne Krap (14-02-2002)
| Kommentar
Fra : Svenne Krap |
Dato : 14-02-02 18:58 |
|
On Thu, 14 Feb 2002 18:01:36 +0100, Svenne Krap <usenet@krap.dk>
wrote:
>On Thu, 14 Feb 2002 16:47:40 +0100, Nis Jorgensen <nis@dkik.dk> wrote:
>
>
>>Hvis vi antager at (picture, motive) er unik, kan vi bruge:
>>
>>SELECT picture
>>FROM picturesandmotives
>>WHERE motive In (4,6,13)
>>GROUP BY picture
>>HAVING Count(motive) = 3;
en tredje mulighed, et tripple self-join:
select picture from picturesandmotives p0, picturesandmotives p1,
picturesandmotives p2 where p0.picture=p1.picture and p0.motive=4 and
p1.motive=6 and p0.picture=p2.picture and p2.motive=13
Perfomer marginalt bedre ved store resultatsæt og marginalt dårligere
ved små resultatsæt.
--
Job-offerings with more than a googolplex* USD a year are instantly accepted.
* = http://www.fpx.de/fp/Fun/Googolplex/
| |
Svenne Krap (14-02-2002)
| Kommentar
Fra : Svenne Krap |
Dato : 14-02-02 18:03 |
|
On Thu, 14 Feb 2002 14:29:09 +0100, Jonas Koch Bentzen
<ingen.emailadresse@eksempel.dk> wrote:
>Jeg sidder her men en SQL-forespørgsel, der volder lidt problemer: Jeg
>har en række billeder, der hver har ét eller flere motiver. Relationerne
>mellem motiv og billede er gemt i en tabel ved navn picturesAndMotives,
>der ser sådan her ud:
>
>picture | motive
>2345-001 | 4
>2345-001 | 6
>2467-233 | 3
>2467-233 | 13
>2467-233 | 4
Jeg går ud fra, at det er en mange-til-mange relationstabel ?
Så er det god kotume at kalde den
rel_picture_motive
Så kan folk straks gennemskue, hvad den gør :)
Svenne
--
Job-offerings with more than a googolplex* USD a year are instantly accepted.
* = http://www.fpx.de/fp/Fun/Googolplex/
| |
Kristian Damm Jensen (15-02-2002)
| Kommentar
Fra : Kristian Damm Jensen |
Dato : 15-02-02 08:37 |
|
Svenne Krap wrote:
>
> On Thu, 14 Feb 2002 14:29:09 +0100, Jonas Koch Bentzen
> <ingen.emailadresse@eksempel.dk> wrote:
>
> >Jeg sidder her men en SQL-forespørgsel, der volder lidt problemer: Jeg
> >har en række billeder, der hver har ét eller flere motiver. Relationerne
> >mellem motiv og billede er gemt i en tabel ved navn picturesAndMotives,
> >der ser sådan her ud:
> >
> >picture | motive
> >2345-001 | 4
> >2345-001 | 6
> >2467-233 | 3
> >2467-233 | 13
> >2467-233 | 4
>
> Jeg går ud fra, at det er en mange-til-mange relationstabel ?
>
> Så er det god kotume at kalde den
>
> rel_picture_motive
>
> Så kan folk straks gennemskue, hvad den gør :)
Det kommer bestemt an på, hvor i verden du er. Jeg er aldrig stødt på
det som konvention før.
Personligt ville jeg foretrække et mere descriptivt navn som
PictureContainsMotive.
--
Kristian Damm Jensen | Feed the hungry at www.thehungersite.com
kristian-damm.jensen@cgey.dk | Two wrongs doesn't make a right,
ICQ# 146728724 | but three lefts do.
| |
|
|