[SQL] MIN() select record ipv value

[Aspje]

Zoals de meesten die dit lezen wellicht weten kun je met de geagregeerde function MIN() een minimale waarde uit een set selecteren. Nu wil ik echter het bijbehorende record ID waar deze minimale waarde komt OOK hebben, en dit schijnt MySQL dus niet automatisch mee te nemen. Wat ook vermeldt staat trouwens:

MIN([DISTINCT] expr)

Returns the minimum value of expr. MIN()

Nu even een simpel voorbeeld om te omschrijven wat ik wil... eerst even een simpele test table:

code:

CREATE TABLE test1 (id INT, name VARCHAR(10), test2id INT);
CREATE TABLE test2 (id INT, value INT);

INSERT INTO test1 VALUES (1,'aaa',1),(2,'bbb',1),(3,'bbb',2),(4,'bbb',3),(5,'aaa',2);
INSERT INTO test2 VALUES (1,10),(2,8),(3,6);

Stel de query:

code:

SELECT 
  test1.id, 
  test1.name, 
  min(test2.value) as minimal 
FROM 
  test1 
  INNER JOIN test2 ON test1.test2id = test2.id
GROUP BY
  test1.name

Deze geeft als resultaat:

code:

id | name | minimal
---+------+--------
 1 | aaa  | 8
 2 | bbb  | 6

De name en minimal kolom zijn logisch. Alleen ik wil dus dat hij het bijbehorende record wordt meegeselecteerd. In dit voorbeeld dus:

code:

id | name | minimal
---+------+--------
 5 | aaa  | 8
 4 | bbb  | 6

Aangezien record #4 uit test1 die minimale waarde heeft. Op geen enkele manier "tagged" MIN() het record waaruit hij de minimale waarde uitgehaald heeft.

De oplossing die ik zelf vond (maar met grote tabellen VEEL te traag gaat) is het result opnieuw te joinen op de MIN() waarden waardoor wel de goede records geselecteerd worden.

code:

SELECT
  test1.id, 
  test1.name, 
  tmp.minimal
FROM
  test1 
  INNER JOIN test2 ON test1.test2id=test2.id
  INNER JOIN
(SELECT 
  test1.name, 
  min(test2.value) as minimal 
FROM 
  test1 
  INNER JOIN test2 ON test1.test2id = test2.id
GROUP BY
  test1.name) as tmp ON (tmp.name = test1.name AND tmp.minimal = test2.value)
GROUP BY
  test1.name

Nu de vraag:

Is er een manier om dit voor elkaar te krijgen zonder te joinen op een subquery?

[NetForce1]

oplopend sorteren en dan LIMIT 1 eroverheen:

SQL:

SELECT 
  test1.id, 
  test1.name, 
  test2.value LIMIT 1
FROM 
  test1 
  INNER JOIN test2 ON test1.test2id = test2.id
GROUP BY
  test1.name
ORDER BY
  test2.value ASC

[KabouterSuper]

Volgens mij niet....maarre, ik geloof nooit dat je query het doet. Je selecteert een id en een name, en groupeert alleen op name.

[Aspje]

@netforce1:
Dank je voor de snelle reactie, maar die limit 1 in de veld definitie pakt hij bij mij niet...

@KabouterSuper:
De query runt prima. Het punt is alleen dat eigenlijk die geselecteerde IDs "undefined" zijn, en ik wil ze dus juist wel defined hebben (als in: degene die hoort bij de daadwerkelijke minimale waarde)

Maybe handig om te melden: Runt op MySQL

[Verwijderd]

edit: nevermind, verkeerd gelezen

[ Voor 85% gewijzigd door Verwijderd op 11-06-2008 17:27 ]

[Voutloos]

http://jan.kneschke.de/projects/mysql/groupwise-max

[Nick_S]

Volgens mij is het snelste met een subquery, zoals:

SQL:

SELECT
  test1.id, test1.name, test2.value
FROM 
  test1 
  INNER JOIN test2 ON test1.test2id = test2.id
WHERE
  test2.value = (SELECT min(test2.value) from test2 as t2 WHERE t2.id = test2.id);

Volgens mij moet er in de subquery nog een group by, maar het gaat om het idee

[P.O. Box]

zoek je niet "HAVING" ?

[Aspje]

@voutloos:

Thx voor die link, dat is idd precies wat ik moet hebben. Weet ik nu ook direct de term ervoor. Ik ga eens even verder klussen!

@nick

die staat idd ook in de link van voutloos, bedankt voor de reactie! Ga ermee aan de slag

[ Voor 26% gewijzigd door Aspje op 11-06-2008 16:37 ]

[Voutloos]

Een subquery met min of max staat ook in mijn link (beste oplossing boven "Hmm, this was too simple ?"), met als voordeel dat een derived table meerdere ordes van grootte sneller kan zijn. Anyway, mijn link + weten hoe group by werkt (inc. mysql random data featurebug) == compleet antwoord.

Het is een veel voorkomende vraag, dus deze link post ik ook met enige regelmaat.

[KabouterSuper]

Aspje schreef op woensdag 11 juni 2008 @ 16:20:

@KabouterSuper:
De query runt prima. Het punt is alleen dat eigenlijk die geselecteerde IDs "undefined" zijn, en ik wil ze dus juist wel defined hebben (als in: degene die hoort bij de daadwerkelijke minimale waarde)

Dan formuleer ik het anders: de query zou niet moeten draaien. Zo te zien doet MySQL impliciet MIN(test1.id), maar eigenlijk zou er een flinke foutmelding terug moeten komen (zoals Oracle netjes doet).

[Voutloos]

Dat is dus de genoemde random data "feature": het niet zeuren om selecteren van kolommen welke niet in de group bij staan of resultaat zijn van aggregate function.

[Aspje]

Ik heb even wat snelle tests gedaan (de testdata is licht vergelijkbaar met het de totale data), en op dit moment lijkt een join op het snelste, maar alsnog moet ik dit under load testen. De echte tabellen hebben 31 miljoen records ("test1") en 1 miljoen records ("test2") daarnaast is de test1 tabel nog uitgebreid met nog een aantal foreign ids.

Goed, so far problem solved. Thx voor de snelle antwoorden

[pedorus]

Eventueel is er nog deze hack:

SQL:

SELECT * FROM 
  (SELECT test1.id, test1.name, test2.value 
   FROM test1 INNER JOIN test2 ON test1.test2id = test2.id 
   ORDER BY test2.value ASC) t
GROUP BY name

In theorie is zo'n order by in een subquery onzin, in de praktijk werkt het oa met de huidige MySQL...

[ Voor 0% gewijzigd door pedorus op 11-06-2008 21:29 . Reden: een->zo'n, onderstaande post klopt ]

[SKiLLa]

Moet dat niet zijn "een ORDER BY in een subquery zonder TOP/LIMIT is ... ".

[Voutloos]

pedorus schreef op woensdag 11 juni 2008 @ 21:11:
Eventueel is er nog deze hack:

In theorie is zo'n order by in een subquery onzin, in de praktijk werkt het oa met de huidige MySQL...

Dit schrijven is net zoveel werk als de echte oplossing. Query zoals jij hem neerzet is nog niet compleet en als je op bewust een halfbakken manier de waarden probeert te kiezen voor een slechte group by ben je een complete (*&%$@$, want je weet blijkbaar van het probleem maar plakt toch een pleister welke maar korte tijd kan blijven zitten.

Kom maar met hacks als er geen mooie oplossingen gepost zijn.

[Aspje]

Misschien even leuk om de resultaten te posten op voutloos zijn link. De resultaten zijn op de echte data, niet op de aangegeven test data.

Er wordt neergezet "10 different ways", maar het komt neer op 5 typen waarbij er een aantal een andere schrijfwijze zijn. (Correct me if i'm wrong).

Manier 1: Temp table maken
Kan op 2 manieren:
1. Een echte CREATE TEMPORARY TABLE
2. via een "subquery" in de FROM.

Ik heb gekozen voor de direct subquery in FROM aangezien dit 1 query is en niet direct 3. (Wat weer performance verlies kan opleveren ivm PHP).

Test resultaat: De inside query runde in ongeveer 400 ms, de outside krikte het totaal op tot een gemiddelde van 6,5 seconden.

Manier 2: Sub query in WHERE
Kan op 3 manier worden geschreven.
1. vanuit subquery naar outside query refereren en de MIN gelijk stellen aan huidige record.
2. hele rij opzoeken (beide waarden) in de subquery
3. MAX elimineren en een <= ALL gebruiken.

Heb manier 1 en 3 gepakt.

Test resultaat: Ik kreeg deze niet sneller als 30 seconden gemiddeld. (De inside query is binnen no-time klaar, maar aangezien de outside elke keer deze inside afvuurt vlot het nog niet).

Manier 3: Pure joins
Komt neer op het joinen van 2 dezeflde tables.
Kan ook op 3 manieren geschreven worden.
1. outer join (met check op NULL)
2. inner join met group by en count in having
3. inner join met group by en max/min in having

Test resultaat: Aangezien mijn te minimaliseren waarde in een andere tabel staat als in deze voorbeelden gaf dit het meeste werk om te schrijven. De queries heb ik nog niet werkend gekregen, aangezien ik bij voorbaat al vrees dat het te traag wordt. (30M records joinen met nog eens 30M records.... )... dit joinen heb ik los geprobeerd, maar duurde al 10 sec.

Manier 4:
Zelfde soort met subquery, waarbij de aggregatie functie in de outside query zit (en dus performance kan winnen), maar niet supported is by MySQL.

Test resultaat: Geen

Manier 5: concat methode
Maxen op een string waarde. Snelheidswinst doordat er geen subqueries gebruikt worden etc etc. Verlies op "conversie" van typen.

In mijn ogen een minder nette oplossing, dus niet eens getest.

Mijn vote gaat nu uit naar de temp table manier. Ik ga vandaag nog verder met testen voor:

1. "materialized view" oplossing (geimplementeerd mbv triggers in MySQL).
2. In-code oplossing; zelf de records van de "inner query" aflopen.

[pedorus]

Aspje schreef op donderdag 12 juni 2008 @ 12:31:
Manier 3: Pure joins
Komt neer op het joinen van 2 dezeflde tables.
Kan ook op 3 manieren geschreven worden.
1. outer join (met check op NULL)
2. inner join met group by en count in having
3. inner join met group by en max/min in having

Test resultaat: Aangezien mijn te minimaliseren waarde in een andere tabel staat als in deze voorbeelden gaf dit het meeste werk om te schrijven. De queries heb ik nog niet werkend gekregen, aangezien ik bij voorbaat al vrees dat het te traag wordt. (30M records joinen met nog eens 30M records.... )... dit joinen heb ik los geprobeerd, maar duurde al 10 sec.

In de MySQL manual staat ook de left join, vast niet zonder reden. Gebruik anders eerst een CREATE VIEW zoals:

SQL:

CREATE VIEW testj AS 
   SELECT test1.id,test1.name,test2.value 
   FROM test1 INNER JOIN test2 ON test1.test2id = test2.id order by name,value;

Dan geeft

SQL:

select * from testj group by name;

gelijk het juiste antwoord, maar dat is natuurlijk 'niet netjes' en breekt misschien in mysql 10+, dus doe je een left join:

SQL:

SELECT t1.id,t1.name,t1.value
FROM testj t1 LEFT JOIN testj t2 ON t1.name=t2.name AND t1.value>t2.value 
WHERE t2.value IS NULL;

[Aspje]

pedorus schreef op donderdag 12 juni 2008 @ 14:42:
[...]

In de MySQL manual staat ook de left join, vast niet zonder reden. Gebruik anders eerst een CREATE VIEW zoals:

Left join = outer join.

Eerst views maken kan, maar feit blijft dat 30M records outer of inner joinen gewoon lang duurt. Of dit nu eerst in een view zit of niet...

Ik heb nu een oplossing waarbij ik zelf de groupwise-min uitvoer in code. (Eenvoudige oplossing).

code:

SELECT 
  test1.id,
  test1.name, 
  min(test2.value) as minimal 
FROM 
  test1 
  INNER JOIN test2 ON test1.test2id = test2.id
ORDER BY
  minimal ASC

vervolgens een simpele loop met associative array:

code:

$ids = array();
foreach($records as $record) {
    if (!array_key_exists($record['name'], $ids)) {
        $ids[$record['name']] = $record['id'];
    }
}

Dit geeft altijd maar 1 name + ID combo, waarbij het altijd degene met minimale waarde voor die naam betreft. Voordeel van deze oplossing is dat de eerste query rond de 300ms kost, het aflopen van de records is ook zo klaar (max 20ms). Dit scheelt zowiezo een hoop tijd tenopzichte van de pure SQL oplossingen, zodat ik deze dan maar heb gekozen.

offtopic:
Misschien eens tijd dat er een SQL GROUPWISE operator/aggreratie functie wordt gemaakt?

[Voutloos]

Aspje schreef op vrijdag 13 juni 2008 @ 14:53:

offtopic:
Misschien eens tijd dat er een SQL GROUPWISE operator/aggreratie functie wordt gemaakt?

GROUPORDER BY
Bestaat nog niet, maar idee is er al langer, en dit is sowieso een nuttige blog om eens in de zoveel tijd reclame voor te maken.