[SQL] - Unique car_id, sort by price

[Verwijderd]

Mijn tabel ziet er ongeveer zo uit (erg gesimplificeerd):

code:

id  |     car_id     |      manuf_id      |    price
 1           12                  4               15
 2           12                  4               20
 3           13                  6               23 
 4           13                  6               13
 5           13                  6               12

Nu wil ik de car_id, manuf_id en price (en nog veel meer kolommen die hier niet vermeld zijn) krijgen van distinct car_id en de laagste prijs, d.w.z. de rijen 1 en 5. Ik heb gedacht aan een group_by; ik heb P&W FAQ - SQL hier even gekeken. Ik zou dan een group_by moeten doen op car_id en iets zodat de price minimaal is, maar doordat ik ook manuf_id wordt het moeilijk. Ik zat dus aan zoiets te denken:

code:

SELECT car_id, manuf_id, MIN(price) FROM table GROUP BY car_id

Zoals in de laatste regel staat van die uitleg, is dit geen correcte SQL.

Misschien moet dit met een subquery, maar mijn query is in php al 20 regels lang dus dat is ook moeilijk te implementeren.

Wie kan mij verder helpen?

[justmental]

Dit moet met een subquery.
En meer dan 20 regels query hoeft toch geen bezwaar te zijn?

[bigbeng]

kan een car_id voorkomen bij verschillende manuf_id's?
Zo niet, dan kun je braaf manuf_id ook opnemen in je group by. Zo wel, dan zijn subqueries de way to go.

[Verwijderd]

justmental schreef op dinsdag 01 november 2005 @ 11:27:
Dit moet met een subquery.
En meer dan 20 regels query hoeft toch geen bezwaar te zijn?

Die query heb ik niet zelf bedacht, ik moet hem uitbreiden met deze optie .

Hoe zou een dergelijke subquery er uit moeten zien? Iets in de trend van:

code:

SELECT car_id, manuf_id, price FROM table WHERE car_id 
NOT IN (SELECT car_id, MIN(price) FROM table GROUP BY car_id))

Volgens mij is dit niet goed, maar wel iets in de richting. Aangezien ik geen ervaring heb met subqueries zal dit wel niet kloppen .

bigbeng schreef op dinsdag 01 november 2005 @ 11:28:
kan een car_id voorkomen bij verschillende manuf_id's?
Zo niet, dan kun je braaf manuf_id ook opnemen in je group by. Zo wel, dan zijn subqueries de way to go.

Er komen nog veel meer kolommen bij die wel distinct zijn zeg maar, dus manuf_id opnemen in de query is not done .

Thanx voor de snelle replies trouwens!

/Edit: ik zie dat mijn query nooit kan volgens mij... Shoot me

[ Voor 31% gewijzigd door Verwijderd op 01-11-2005 11:46 ]

[bigbeng]

Verwijderd schreef op dinsdag 01 november 2005 @ 11:37:

[...]

Er komen nog veel meer kolommen bij die wel distinct zijn zeg maar, dus manuf_id opnemen in de query is not done .

[...]

Minor details, nog meer dingen die we moeten weten? Als er nog meer kolommen van belang zijn, dan is dat best belangrijk bij een query met aggregate functies erin.

Ik vrees dat je er niet aan gaat ontkomen aan subqueries. Nou ja, 20 regels of 25 regels, dat maakt toch ook niet zoveel uit.

[Dido]

bigbeng schreef op dinsdag 01 november 2005 @ 11:28:
kan een car_id voorkomen bij verschillende manuf_id's?
Zo niet, dan kun je braaf manuf_id ook opnemen in je group by.

Inderdaad, zoals je alle velden opneemt die niet in een aggregate function zitten.

Zo wel, dan zijn subqueries de way to go.

Dan zul je simpelweg de vraag moeten beantwoorden welke manuf_id je wilt laten zien als er meer zijn... het oude verhaal van "ik wil distinct maar hij doettutnie!", dus.:)

[Verwijderd]

bigbeng schreef op dinsdag 01 november 2005 @ 11:45:
[...]

Minor details, nog meer dingen die we moeten weten? Als er nog meer kolommen van belang zijn, dan is dat best belangrijk bij een query met aggregate functies erin.

Ik vrees dat je er niet aan gaat ontkomen aan subqueries. Nou ja, 20 regels of 25 regels, dat maakt toch ook niet zoveel uit.

Elke auto heeft specifieke eigenschappen, zoals kleur, grootte, etc. etc. Deze zijn verschillend voor elke auto, een group by zou daarom ook niet werken.

Dido schreef op dinsdag 01 november 2005 @ 11:46:
[...]

Inderdaad, zoals je alle velden opneemt die niet in een aggregate function zitten.

[...]

Dan zul je simpelweg de vraag moeten beantwoorden welke manuf_id je wilt laten zien als er meer zijn... het oude verhaal van "ik wil distinct maar hij doettutnie!", dus.:)

De combinatie car_id <-> manuf_id is altijd hetzelfde, hier zit het probleem ook niet. Ik had mss even moeten vermelden dat er dus extra kolommen zijn die wel verschillend zijn voor dezelfde auto's (zoals hierboven dus vermeld). Daarom moet het dus met een subquery, die ik heb aangedragen heb ik ergens gevonden via google. Echter, volgens mij klopt hij niet helemaal, maar aan subqueries had ik nog niet gedacht, vandaar.

[ Voor 42% gewijzigd door Verwijderd op 01-11-2005 11:50 ]

[Dido]

Verwijderd schreef op dinsdag 01 november 2005 @ 11:48:
Elke auto heeft specifieke eigenschappen, zoals kleur, grootte, etc. etc. Deze zijn verschillend voor elke auto, een group by zou daarom ook niet werken.

Wat bedoel je nu? Dat iedere auto_id een eigen kleur, etc heeft (een 1-1 relatie dus), of dat er verschillende per auto kunnen zijn?

In het eerste geval werkt het prima om die dingen in je group by op te nemen (in het tweede geval ook, maar je krijgt meer records per auto_id; wil je dat niet, dan moet je dus besluiten wat je wel wilt zien).

edit: als die zaken dus verschillend zijn voor dezelfde auto, dan krijg je op geen enkele manier 1 regel per auto terug zonder de vraag te beantwoorden welke je dan wilt.

Auto_12 Blauw
Auto_12 Rood

Welke van de twee wil je? SQL kan, mag en zal niet random kiezen.

[ Voor 19% gewijzigd door Dido op 01-11-2005 11:53 ]

[Verwijderd]

Dido schreef op dinsdag 01 november 2005 @ 11:51:
[...]

Wat bedoel je nu? Dat iedere auto_id een eigen kleur, etc heeft (een 1-1 relatie dus), of dat er verschillende per auto kunnen zijn?

In het eerste geval werkt het prima om die dingen in je group by op te nemen (in het tweede geval ook, maar je krijgt meer records per auto_id; wil je dat niet, dan moet je dus besluiten wat je wel wilt zien).

edit: als die zaken dus verschillend zijn voor dezelfde auto, dan krijg je op geen enkele manier 1 regel per auto terug zonder de vraag te beantwoorden welke je dan wilt.

Auto_12 Blauw
Auto_12 Rood

Welke van de twee wil je? SQL kan, mag en zal niet random kiezen.

[/code]
In je edit heb je gelijk. Ik had dit dus even moeten vermelden in mijn TS, excuses. Zo ziet de tabel er ongeveer uit:

code:

id  |     car_id     |      manuf_id      |    price   |  color  |  nr_of_seats
 1           12                  4               15         blue         3
 2           12                  4               20         red          5 
 3           13                  6               23         green        3
 4           13                  6               13         gray         5
 5           13                  6               12         black        2

Ik moet dan de volledige rijen krijgen en in dit geval dus nog steeds 1 en 5.

Dit moet dan toch wel werken:

code:

SELECT car_id, manuf_id, price, color, nr_of_seats FROM table WHERE car_id IN 
       (SELECT car_id, MIN(price) FROM table GROUP BY car_id))

Nu zal hij groupen op car_id en de goedkoopste auto eruit halen. Vervolgens wordt in de 'hoofdquery' de goede car_id geselecteerd. Of zie ik hier iets over het hoofd?

[Dido]

Je ziet over het hoofd dat regel twee ook voldoet aan "where car_id in blaat"

Je krijgt nu alle regels met een car_id waarvoor een min(price) gevonden wordt. Dat zijn ze allemaal, schat ik.

[ Voor 42% gewijzigd door Dido op 01-11-2005 12:11 ]

[bigbeng]

Welk dbms hebben we het hier over? Er zijn er een aantal die dit accepteren:

code:

SELECT car_id, manuf_id, price, color, nr_of_seats FROM table t1 WHERE price IN 
       (SELECT MIN(price) FROM table t2 where t2.car_id = t1.car_id))

[sig69]

Ik begin een beetje mijn twijfels te krijgen over het datamodel. Wat is een car_id? Volgens mij geen unieke auto, aangezien car_id 13 al nr_of_seats 2, 3 en 5 heeft. Als car_id een auto type voorstelt, zouden mijns insziens auto's met een een verschillend aantal stoelen een uniek car_id moeten hebben.

Niet dat je hier verder iets mee opschiet, maar wilde het gewoon even noemen.

[Annie]

sig69 schreef op dinsdag 01 november 2005 @ 12:30:
Ik begin een beetje mijn twijfels te krijgen over het datamodel. Wat is een car_id? Volgens mij geen unieke auto, aangezien car_id 13 al nr_of_seats 2, 3 en 5 heeft. Als car_id een auto type voorstelt, zouden mijns insziens auto's met een een verschillend aantal stoelen een uniek car_id moeten hebben.

Niet dat je hier verder iets mee opschiet, maar wilde het gewoon even noemen.

Dus jij gaat voor elke mogelijke optie op een auto type een unieke car_id maken? Lijkt me niet echt handig. Het 'uniek maken' van een auto kan natuurlijk ook door het type te combineren met een (of meerdere) optie(s).

[Verwijderd]

sig69 schreef op dinsdag 01 november 2005 @ 12:30:
Ik begin een beetje mijn twijfels te krijgen over het datamodel. Wat is een car_id? Volgens mij geen unieke auto, aangezien car_id 13 al nr_of_seats 2, 3 en 5 heeft. Als car_id een auto type voorstelt, zouden mijns insziens auto's met een een verschillend aantal stoelen een uniek car_id moeten hebben.

Niet dat je hier verder iets mee opschiet, maar wilde het gewoon even noemen.

Car_id is bijv. een corsa en je hebt verschillende uitvoeringen van een corsa. Car_id is weer gekoppeld aan een andere tabel, waarin corsa staat. Oke, betere omschrijving is car_type_id.

bigbeng schreef op dinsdag 01 november 2005 @ 12:15:
Welk dbms hebben we het hier over? Er zijn er een aantal die dit accepteren:

code:

SELECT car_id, manuf_id, price, color, nr_of_seats FROM table t1 WHERE price IN 
       (SELECT MIN(price) FROM table t2 where t2.car_id = t1.car_id))

PHP icm MySQL.

Dido schreef op dinsdag 01 november 2005 @ 12:10:
Je ziet over het hoofd dat regel twee ook voldoet aan "where car_id in blaat"

Je krijgt nu alle regels met een car_id waarvoor een min(price) gevonden wordt. Dat zijn ze allemaal, schat ik.

Hmms, dit gaat inderdaad niet goed denk ik. Ik zal die andere query eens proberen, ik vermoed dat MySQL dat wel slikt?.

Excuses, ik probeerde het iets te simpel weer te geven. De prijs is geen kolom in de tabel, maar wordt berekend in de query.

D.w.z.

code:

SELECT price_car + price_add + price_customer AS total_price, * FROM table"

Kan ik in de subquery total_price weer gebruiken? Lijkt me niet he... Echt lastig

[ Voor 56% gewijzigd door Verwijderd op 01-11-2005 13:05 ]

[bigbeng]

Je kunt total_price toch opnieuw uitrekenen in de subquery? Dat zou dezelfde waarde moeten opleveren als in de hoofdquery.

[Verwijderd]

bigbeng schreef op dinsdag 01 november 2005 @ 13:21:
Je kunt total_price toch opnieuw uitrekenen in de subquery? Dat zou dezelfde waarde moeten opleveren als in de hoofdquery.

Dat kan ja, maar die query is al ong. 20 regels lang... Ik vermoed ook dat dat de performance niet ten goede komt .

[momania]

Verwijderd schreef op dinsdag 01 november 2005 @ 12:50:
[...]

Car_id is bijv. een corsa en je hebt verschillende uitvoeringen van een corsa. Car_id is weer gekoppeld aan een andere tabel, waarin corsa staat. Oke, betere omschrijving is car_type_id.

Wat doet de manuf_id dan ook in die tabel? Zijn er Corsa's van verschillende fabrikanten soms

Lijkt me dat je je eerst nog eens goed bezig moet houden met je normalisering

[RobIII]

Verwijderd schreef op dinsdag 01 november 2005 @ 13:26:
[...]

Dat kan ja, maar die query is al ong. 20 regels lang... Ik vermoed ook dat dat de performance niet ten goede komt .

Het aantal regels van een query zegt doorgaans erg weinig over de performance ervan hoor...

[Dido]

RobIII schreef op dinsdag 01 november 2005 @ 13:28:
Het aantal regels van een query zegt doorgaans erg weinig over de performance ervan hoor...

Klopt, maar als je een berekening van 20 regels niet alleen op je resultaatset uitvoert, maar ook nog eens in een subquery, dan vermoed ik dat die performance heel snel in elkaar zakt...

[Verwijderd]

RobIII schreef op dinsdag 01 november 2005 @ 13:28:
[...]

Het aantal regels van een query zegt doorgaans erg weinig over de performance ervan hoor...

Het is gezien de slow_queries melding de langzaamste query. En het is niet echt netjes vind ik... Maar ik denk wel dat het zo moet ja

[ Voor 9% gewijzigd door Verwijderd op 01-11-2005 13:34 ]

[bigbeng]

Dido schreef op dinsdag 01 november 2005 @ 13:33:
[...]

Klopt, maar als je een berekening van 20 regels niet alleen op je resultaatset uitvoert, maar ook nog eens in een subquery, dan vermoed ik dat die performance heel snel in elkaar zakt...

Het hangt ook een beetje af van de verhouding tussen de resultaatset en de totale dataset. Als de resultaatset exclusief de subquery een regel of 100 beslaat, dan wordt bij een fatsoenlijk DBMS de subquery niet vaker dan 100 keer uitgevoerd, omdat de optimizer daar iets aan doet.

Waarschijnlijk kun je al wat performance winst halen door op alle gebruikte foreign keys indexen aan te leggen, zowel op de child als op de parent tabel. Als je InnoDB als engine gebruikt wordt dit al van je verwacht en als je MyISAM als engine gebruikt, dan worden foreign keys sowieso niet door de database ondersteund, maar dan kun je nog steeds wel de indexen aanleggen.

Tenslotte kun je eventueel met temptables eerst de query zonder subquery uitvoeren en dan op de temptable dmv een subquery het resultaat beperken. Daarmee voorkom je eventuele overbodige tablescans. Ik weet niet of het de performance ten goede komt, maar ja wie niet waagt, wie niet wint

Om iets minder in het duister te tasten: hoeveel records bevat je resultaat ongeveer?

[ Voor 7% gewijzigd door bigbeng op 01-11-2005 14:00 ]

[Dido]

bigbeng schreef op dinsdag 01 november 2005 @ 13:59:
Het hangt ook een beetje af van de verhouding tussen de resultaatset en de totale dataset. Als de resultaatset exclusief de subquery een regel of 100 beslaat, dan wordt bij een fatsoenlijk DBMS de subquery niet vaker dan 100 keer uitgevoerd, omdat de optimizer daar iets aan doet.

Je subquery wel, inderdaad. Maar dat zegt niet zoveel als die subquery die berekening van twintig regels een paar honderd keer uitvoerd. (Er zijn bijvoorbeeld gemiddeld 100 auto's van type X).
Dan wordt de berekening (niet de subquery) 10.000 keer ipv 100 keer uitgevoerd.

[Verwijderd]

Om iets minder in het duister te tasten: hoeveel records bevat je resultaat ongeveer?

Mwuah, rond de 100? Ik heb even overlegd met de hoofdprogrammeur en die ziet een subquery ook niet zitten. We gaan even onderzoeken of we het nog wel willen dan...

[Creepy]

hoe langzaam is die query dan? En weet je zeker dat dat door de subquery komt? Heb je ook al gecheckt of je de juiste indexen hebt staan, want daar ga je zeer waarschijnlijk de boel mee versnellen. Want als je met 100 auto's al performance problemen hebt dan is er echt iets goed mis.

[RobIII]

Verwijderd schreef op dinsdag 01 november 2005 @ 15:38:
[...]

Mwuah, rond de 100? Ik heb even overlegd met de hoofdprogrammeur en die ziet een subquery ook niet zitten. We gaan even onderzoeken of we het nog wel willen dan...

Hmpf... Als je met 100 records al in de problemen komt met je performance dan is er of iets goed mis, of je hebt een belachelijk slecht genormaliseerde DB of je maakt geen gebruik van indexes e.d...

En heeft die "hoofdprogrammeur" ook een goede reden gegeven waarom 'ie geen subquery's wil? Of is het iets onbekends en dus "eng" bij voorbaat?

edit:
Damn you Creepy

[ Voor 27% gewijzigd door RobIII op 01-11-2005 15:47 ]

[Dido]

Als dat resultaat uit een tabel met 10.000 entries komt kijken we naar 10.000 keer een berekening van 20 regels (dus ik schat veel velden) met die subquery (100 records in resultset, ieder vergeleken met 100 keer de uitkomst van de berekening op een andere record met idem car_id). Niet zo heel gek dat dat niet echt snel gaat, toch (100 keer langzamer dan zonder de subquery)?

Het zou nog steeds niet direct een probleem mogen zijn, denk ik, maar als de berekening ingewikkelder wordt dan a+b+c+d (misschien wel uit verschillende tabellen?) dan is een factor 100 performanceverlies nooit leuk.

[Verwijderd]

De query duurt nu ongeveer 300ms en komt uit een tabel met ongeveer 130k records. Op elk record wordt een totaalprijs berekend, etc. etc. Nu is de performance nog niet echt een issue nee, maar met een subquery zou dit wel uit de hand lopen.

En heeft die "hoofdprogrammeur" ook een goede reden gegeven waarom 'ie geen subquery's wil? Of is het iets onbekends en dus "eng" bij voorbaat?

De prijs wordt op een zeer ingewikkelde manier berekend (veel userinput), waardoor het erg lastig zal gaan worden (veel verschillende scenario's te onderscheiden).

En weet je zeker dat dat door de subquery komt?

Aangezien die query erg lastig zal gaan worden, heb ik het nog niet geimplementeerd.

/Edit: Hoe het natuurlijk wel kan is de huidige query uitvoeren met duplicate car_id's en vervolgens door de array loopen en bijhouden welke car_id's al zijn geweest. Als die car_id al is geweest -> verwijderen. Dit is niet echt een nette oplossing, maar ik vermoed dat dit sneller is dan een subquery...

[ Voor 81% gewijzigd door Verwijderd op 01-11-2005 17:41 ]

[Verwijderd]

Wat vinden jullie van mijn oplossing in de edit??

[RobIII]

Verwijderd schreef op woensdag 02 november 2005 @ 16:09:
Wat vinden jullie van mijn oplossing in de edit??

Eerlijk?
Ranzig. Een DB doet dit geheid sneller dan zelf door je resultset heen wandelen...

[ Voor 48% gewijzigd door RobIII op 02-11-2005 18:11 ]

[bigbeng]

En je kunt dus ook een subquery vermijden door een temptable te gebruiken, zodat alle prijzen al berekend zijn. Mijn leraar databases zei altijd dat je temptables ten alle tijde moest vermijden, behalve:
1) je echt geen ander alternatief hebt, of
2) performance redenen

2) is van toepassing, tenminste misschien. Meestal zijn temptables trager, omdat je ze moet creeeren, vullen en uitvragen. Maar goed, dat kun je dus proberen. En niet vergeten de temptable weer op te ruimen als je er klaar mee bent.

[Verwijderd]

@bigbeng: Dit wordt inderdaad al gebruikt. Er is nog een combi waarbij ook gegrouped moet worden op een andere combinatie, hiervoor wordt een temptable gebruikt.

@RobIII: Dat idee had ik ook al inderdaad...

Ik zal er nog eens goed naar kijken, wellicht is een temptable toch de oplossing. De oplossing om na de query de duplicates er uit te halen is gewoon in strijd met alles .

[ Voor 42% gewijzigd door Verwijderd op 03-11-2005 01:05 ]