[MySQL] GROUP BY en ORDER BY..

[Scr33x0r]

Ik ga het even versimpeld uitleggen:

Ben bezig met takensysteem, daarbij hebben we 3 tables:

- trajecten: traject_id, website, startdate, timespent, hours
- taken: taak_id, name, sort
- traject_taken: tt_id, traject_id, task_id, prio, status

In de trajecten tabel zie je dus startdate, timespent en hourspermonth. Kortom:
Klant 1 (traject_id) met website www.google.nl (website) is gestart met een traject op 01-11-2010 (startdate), hij heeft 1 uur per maand (hours) en daarvan is nog niks gebruikt (timespent).

Taken bevat een aantal standaard (SEO)-taken die wij afronden in een bepaalde volgorde (sort).

Traject Taken is een tabel met daarin de combinatie en of een taak eventuele prioriteit heeft (1-5).

Ik wil een overzicht met daarin:
tt_id, sort, website, name, timespent, timetospent, percentage ongebruikte tijd, prio

Dan kom ik op de volgende query:

SQL:

SELECT 
    taken.tt_id
    taken.sort,
    trajecten.website,
    taken.sort,
    taken.timespent
    (FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(trajecten.startdate)) / 2629743) + 1) * trajecten.hours * 3600 AS timetospent,
    100 - (trajecten.timespent / ((FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(trajecten.start)) / 2629743) + 1) * trajecten.hours * 3600) * 100) AS percentage,
    traject_taken.prio
FROM
    taken,
    trajecten,
    traject_taken
WHERE
   trajecten.traject_id = traject_taak.traject_id
AND
   traject_taak.taak_id  = taken.taak_id
AND
    traject_taak.status = 1
ORDER BY
   traject_taken.prio,
   percentage,
   taken.sort

Het probleem is nu, dat ik van elk traject alle taken krijg, terwijl dat ik per traject de taak met de laagste sort wil.

Krijg ik dat voor elkaar met een INNER JOIN? Zoja hoe? Want een GROUP BY trajecten.traject_id werkt niet.

Dus wie is hier de SQL heer en meester?

[NMe]

Dat kan inderdaad gewoon met een join (waarschijnlijk een left outer join, niet een inner join ). Ik durf gezien je vraagstelling met vrij grote zekerheid te zeggen dat je niet helemaal weet hoe een group by werkt. Zie daarvoor ook Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?

Hoe zag de group by-query die niet werkte eruit?

[Scr33x0r]

Een group by query:

SQL:

SELECT 
    taken.tt_id
    taken.sort,
    trajecten.website,
    taken.sort,
    taken.timespent
    (FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(trajecten.startdate)) / 2629743) + 1) * trajecten.hours * 3600 AS timetospent,
    100 - (trajecten.timespent / ((FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(trajecten.start)) / 2629743) + 1) * trajecten.hours * 3600) * 100) AS percentage,
    traject_taken.prio
FROM
    taken,
    trajecten,
    traject_taken
WHERE
   trajecten.traject_id = traject_taak.traject_id
AND
   traject_taak.taak_id  = taken.taak_id
GROUP BY
   trajecten.traject_id
ORDER BY
   traject_taken.prio,
   percentage,
   taken.sort

Heb je een voorbeeld van hoe ik die left join zou moeten doen?

[CH4OS]

Vreemd dat je niet bij taken aan een deadline hebt gedacht. Ook jouw deadline kan een reden zijn om iets juist een hoge prioriteit te geven!

[NMe]

Ik zou een voorbeeld kunnen geven maar ik kan ook nog eens wijzen naar de FAQ-link die ik hierboven ook al gaf. Ik zie dat je die niet doorgelezen hebt.

[Scr33x0r]

NMe schreef op dinsdag 28 december 2010 @ 20:14:
Ik zou een voorbeeld kunnen geven maar ik kan ook nog eens wijzen naar de FAQ-link die ik hierboven ook al gaf. Ik zie dat je die niet doorgelezen hebt.

Dus als ik de faq goed begrijp:

SQL:

SELECT 
    taken.tt_id
    MIN(taken.sort),
    trajecten.website,
    taken.sort,
    taken.timespent
    (FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(trajecten.startdate)) / 2629743) + 1) * trajecten.hours * 3600 AS timetospent,
    100 - (trajecten.timespent / ((FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(trajecten.start)) / 2629743) + 1) * trajecten.hours * 3600) * 100) AS percentage,
    traject_taken.prio
FROM
    taken,
    trajecten,
    traject_taken
WHERE
   trajecten.traject_id = traject_taak.traject_id
AND
   traject_taak.taak_id  = taken.taak_id
GROUP BY
    traject.traject_id
ORDER BY
   traject_taken.prio,
   percentage,
   taken.sort

Ik zal dit morgen op het wekr even testen, maar had al soortgelijk iets gedaan, maar dit moet ik dan combineren met een LEFT JOIN?

[RobIII]

Scr33x0r schreef op dinsdag 28 december 2010 @ 20:20:
[...]


Dus als ik de faq goed begrijp:

Je begrijpt 't niet goed

quote: http://gathering.tweakers...ssages/1105378///#groupby

zodra je een GROUP BY-clause gebruikt, moeten alle geselecteerde kolommen terugkomen in de GROUP BY list ofwel een aggregate functie hebben.

Ik zie maar 1 veld in je GROUP BY en toch meer dan 1 (non-aggregated) veld in je SELECT

Lees die hele FAQ eens aandachtig (en hell, voor mijn part met een kop koffie erbij) door:

MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

Verder vind ik impliciete joins in een FROM/WHERE clause ook lelijk, maar da's persoonlijk: Hoe werken joins?

[ Voor 80% gewijzigd door RobIII op 28-12-2010 21:00 ]

[NMe]

RobIII schreef op dinsdag 28 december 2010 @ 20:55:
[...]

Verder vind ik impliciete joins in een FROM/WHERE clause ook lelijk, maar da's persoonlijk: Hoe werken joins?

Da's niet alleen lelijk, het maakt ook een outer join onmogelijk.

[RobIII]

NMe schreef op dinsdag 28 december 2010 @ 22:12:
[...]

Da's niet alleen lelijk, het maakt ook een outer join onmogelijk.

Dat weet ik niet; ik gebruik altijd expliciete joins. Ik dacht dat zoiets wel kon:

SQL:

select X.*, Y.*
from X, Y
where X.id *= Y.id

( let op de *= of =* operator). Maar either way: implicit joins zijn bah

[ Voor 11% gewijzigd door RobIII op 28-12-2010 22:27 ]

[CyBeRSPiN]

NMe schreef op dinsdag 28 december 2010 @ 22:12:
[...]

Da's niet alleen lelijk, het maakt ook een outer join onmogelijk.

Of het in MySQL kan weet ik niet, met een Oracle DB werkt het met "where a.id = (+)b.a_id", vind het zelf ook zeer verwarrend/lelijk, maar de outer join is "pas" in 1999 in de officiele ANSI SQL standaard vastgelegd. Tot die tijd had elk zichzelf respecterende DBMS een eigen syntax hiervoor.

[DamadmOO]

RobIII schreef op dinsdag 28 december 2010 @ 22:23:
[...]

Dat weet ik niet; ik gebruik altijd expliciete joins. Ik dacht dat zoiets wel kon:

SQL:

select X.*, Y.*
from X, Y
where X.id *= Y.id

( let op de *= of =* operator). Maar either way: implicit joins zijn bah

Die join is inderdaad correct, maar gelukkig is die syntax deprecated sinds 2005 (in ieder geval in MSSQL 2005 compatibility mode).

[NMe]

RobIII schreef op dinsdag 28 december 2010 @ 22:23:
[...]

( let op de *= of =* operator). Maar either way: implicit joins zijn bah

MSSQL-only volgens mij. MySQL heeft voor zover ik weet geen impliciete outer join-mogelijkheden. Maar goed, laten we het ontopic houden.

[Cartman!]

Zou het niet veel handiger/sneller zijn je 'timespent' en je 'percentage' te denormaliseren? Van die 'magic numbers' in je query wordt t ook niet duidelijker.

[NMe]

Zolang het de query niet nodeloos vertraagt hoef je niet te denormaliseren. Als je de query zo onleesbaar vindt zou dat eerder een argument zijn om een stored procedure te gebruiken.

[ACM]

Toch jammer dat diverse mensen hier nu druk bezig zijn de topicstarter te wijzen op een fout gebruik van group by en verschillende manieren van joins schrijven... terwijl zijn probleem helemaal niet opgelost kan worden met een normale query met joins en group by.

GROUP BY werkt over kolommen, en verbreekt daardoor de relaties per regel die er normaal nog wel zijn. Daardoor kan je moeilijk garanderen dat je in elke regel per opgevraagde kolom daadwerkelijk de gegevens van de taak met de laagste nog beschikbare sort krijgt voor ieder traject. Dat kan eigenlijk alleen als je database daar een uitbreiding voor heeft in de SQL-variant (zoals PostgreSQL's DISCTINCT ON-feature), vziw heeft MySQL daar geen uitbreiding voor.

Hier is toch echt eoa subselect nodig om te kunnen joinen op specifieke taak die de laagste volgorde-nummer heeft. Zoiets:

SQL:

SELECT *
FROM trajecten tr
JOIN taken t ON t.tt_id = (SELECT ti.tt_id FROM taken ti 
                WHERE /* iets met de tijd */ and ti.traject_id = tr.id
                ORDER BY ti.sort LIMIT 1)

Uiteraard moet je daar dan ook nog je traject_taken-tabel in zien te verwerken en zijn er ook met dit soort wat complexere queries meerdere wegen naar Rome.
Ik weet ook niet helemaal zeker of MySQL ondertussen wel op die plek een subquery met order by en/of limit toelaat (de subquery mag er iig wel). Dat zou je dan eventueel weer met een sub-subquery in de WHERE-clause van de subquery kunnen oplossen die de MIN van alle nog beschikbare sort's opzoekt (where ti.sort = (select min(sort) from taken tii ...).

Ironisch genoeg heb je bij dit soort queries juist eigenlijk geen GROUP BY nodig

[ Voor 18% gewijzigd door ACM op 29-12-2010 08:20 ]

[cariolive23]

NMe schreef op woensdag 29 december 2010 @ 03:51:
Zolang het de query niet nodeloos vertraagt hoef je niet te denormaliseren. Als je de query zo onleesbaar vindt zou dat eerder een argument zijn om een stored procedure te gebruiken.

Ik mag aannemen aan dat je een VIEW bedoelt?

[Cartman!]

NMe schreef op woensdag 29 december 2010 @ 03:51:
Zolang het de query niet nodeloos vertraagt hoef je niet te denormaliseren. Als je de query zo onleesbaar vindt zou dat eerder een argument zijn om een stored procedure te gebruiken.

Dat zal wel smaak zijn dan, ik zou het onderhouds-technisch niet handig vinden om een hoop nikszeggende getallen in m'n queries te hebben staan.

[Scr33x0r]

ACM schreef op woensdag 29 december 2010 @ 08:04:
Toch jammer dat diverse mensen hier nu druk bezig zijn de topicstarter te wijzen op een fout gebruik van group by en verschillende manieren van joins schrijven... terwijl zijn probleem helemaal niet opgelost kan worden met een normale query met joins en group by.
....

Hmm, ik kom er toch niet uit zo..

Het probleem is dat ik steeds meerdere keren 1 traject terug zie komen..

Ik heb dus de volgende tabellen:

Trajecten

traject_id	website	startdate	timespent	hours
1	www.google.nl	2010-11-01	2300	1
2	www.yahoo.com	2010-11-01	1400	1
3	www.bing.com	2010-11-01	1700	1

Taken

taak_id	name	sort
1	Backup maken	1
2	Google Analytics code plaatsen	2
3	Titels uniek maken	3
4	Zoekmachine vriendelijke URL's maken	4
5	Custom Taak, deze is toegevoegd voor 1 klant	0

Traject_Taken

tt_id	traject_id	taak_id	prio	status
1	1	1	3	9
2	1	2	3	9
3	1	3	3	1
4	1	4	3	1
5	2	1	3	9
6	2	2	3	1
7	2	3	3	1
8	2	4	3	1
9	3	5	5	1

Wat we dus hierboven zien is het volgende:

Traject 1 (www.google.nl) heeft als eerst volgende openstaande taak (status = 1) taak_id 3 (zie tt_id =3)
Traject 2 (www.yahoo.com) heeft als eerstvolgende openstaande taak (status = 1) taak_id 2 (zie tt_id = 6)
Traject 3 (www.bing.com) heeft als eerstvolgende openstaande taak een taak met prio 5, dus die moet eerst (zie tt_id 9)

Wat ik dus eigenlijk wil zien in mijn tabel is:
Top 10 openstaande taken

Sort	Website	Name	Timespent	Timetospent	Percentage	Prio
0	www.bing.com	Custom Taak, deze is toegevoegd voor 1 klant	1700	7200	76.39%	5
2	www.yahoo.com	Google Analytics code plaatsen	1400	7200	80.56%	3
3	www.google.nl	Titels uniek maken	2300	7200	68.06%	3

Dus.. (tabellen maken kost best veel tijd ) wie kan me helpen?

[.oisyn]

Het is trouwens timetospend

[CH4OS]

Scr33x0r schreef op woensdag 29 december 2010 @ 11:24:
[...]


Hmm, ik kom er toch niet uit zo..

Het probleem is dat ik steeds meerdere keren 1 traject terug zie komen..

Ik heb dus de volgende tabellen:

Trajecten

traject_id	website	startdate	timespent	hours
1	www.google.nl	2010-11-01	2300	1
2	www.yahoo.com	2010-11-01	1400	1
3	www.bing.com	2010-11-01	1700	1

Taken

taak_id	name	sort
1	Backup maken	1
2	Google Analytics code plaatsen	2
3	Titels uniek maken	3
4	Zoekmachine vriendelijke URL's maken	4
5	Custom Taak, deze is toegevoegd voor 1 klant	0

Traject_Taken

tt_id	traject_id	taak_id	prio	status
1	1	1	3	9
2	1	2	3	9
3	1	3	3	1
4	1	4	3	1
5	2	1	3	9
6	2	2	3	1
7	2	3	3	1
8	2	4	3	1
9	3	5	5	1

Wat we dus hierboven zien is het volgende:

Traject 1 (www.google.nl) heeft als eerst volgende openstaande taak (status = 1) taak_id 3 (zie tt_id =3)
Traject 2 (www.yahoo.com) heeft als eerstvolgende openstaande taak (status = 1) taak_id 2 (zie tt_id = 6)
Traject 3 (www.bing.com) heeft als eerstvolgende openstaande taak een taak met prio 5, dus die moet eerst (zie tt_id 9)

Wat ik dus eigenlijk wil zien in mijn tabel is:
Top 10 openstaande taken

Sort	Website	Name	Timespent	Timetospent	Percentage	Prio
0	www.bing.com	Custom Taak, deze is toegevoegd voor 1 klant	1700	7200	76.39%	5
2	www.yahoo.com	Google Analytics code plaatsen	1400	7200	80.56%	3
3	www.google.nl	Titels uniek maken	2300	7200	68.06%	3

Dus.. (tabellen maken kost best veel tijd ) wie kan me helpen?

Misschien een stomme overbodige vraag, heb hem hier ook eerder gesteld, maar men ging alleen in op de group by, dat ik hem maar nog een keer plaats:

Hoe hou je rekening met je deadline, ook die is immers bepalend voor jouw prioriteit evenals de startdatum natuurlijk, maar daar vind ik niets van in jouw overzicht.

[Scr33x0r]

We werken niet met deadlines, alleen wanneer iets opgepakt wordt telt.. Elke klant heeft x-aantal uren per maand en daar blijft het bij...

[NMe]

cariolive23 schreef op woensdag 29 december 2010 @ 08:24:
[...]

Ik mag aannemen aan dat je een VIEW bedoelt?

Nee?

Cartman! schreef op woensdag 29 december 2010 @ 11:15:
[...]

Dat zal wel smaak zijn dan, ik zou het onderhouds-technisch niet handig vinden om een hoop nikszeggende getallen in m'n queries te hebben staan.

Die zouden dan niet in je query staan maar in je SP.

[Woy]

NMe schreef op woensdag 29 december 2010 @ 03:51:
Zolang het de query niet nodeloos vertraagt hoef je niet te denormaliseren. Als je de query zo onleesbaar vindt zou dat eerder een argument zijn om een stored procedure te gebruiken.

Zowel een view als een sp lossen het probleem van een complexe query niet op. Je stopt het alleen wat dieper weg. Dus ik zie niet in waarom een complexe query een reden zou zijn om het in een SP te doen. Je kunt de query ook gewoon op een andere plek neerzetten zodat het de rest van de code niet zoveel verstoord.

Scr33x0r schreef op woensdag 29 december 2010 @ 11:24:
[...]
Wat we dus hierboven zien is het volgende:

Traject 1 (www.google.nl) heeft als eerst volgende openstaande taak (status = 1) taak_id 3 (zie tt_id =3)
Traject 2 (www.yahoo.com) heeft als eerstvolgende openstaande taak (status = 1) taak_id 2 (zie tt_id = 6)
Traject 3 (www.bing.com) heeft als eerstvolgende openstaande taak een taak met prio 5, dus die moet eerst (zie tt_id 9)

Wat ik dus eigenlijk wil zien in mijn tabel is:
Top 10 openstaande taken

Sort	Website	Name	Timespent	Timetospent	Percentage	Prio
0	www.bing.com	Custom Taak, deze is toegevoegd voor 1 klant	1700	7200	76.39%	5
2	www.yahoo.com	Google Analytics code plaatsen	1400	7200	80.56%	3
3	www.google.nl	Titels uniek maken	2300	7200	68.06%	3

Dus.. (tabellen maken kost best veel tijd ) wie kan me helpen?

Volgens mij ben je op zoek naar een groupwise maximum / minimum

[ Voor 18% gewijzigd door Woy op 29-12-2010 13:21 ]

[Scr33x0r]

Hmm, nog even testen, maar volgens mij heb ik het:

SQL:

SELECT 
    taken.sort,
    taken.name,
    trajecten.website,
    trajecten.timespent,
    (FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(trajecten.startdate)) / 2629743) + 1) * trajecten.hours * 3600 AS timetospent,
    100 - (trajecten.timespent / ((FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(trajecten.startdate)) / 2629743) + 1) * trajecten.hours * 3600) * 100) AS percentage,
    traject_taken.status,
    traject_taken.tt_id,
    traject_taken.prio
FROM
    trajecten,
    traject_taken
JOIN
    taken
ON
    taken.taak_id = 
    (
        SELECT
            t.taak_id
        FROM
            taken t,
            traject_taken tt
        WHERE
            tt.taak_id  = t.taak_id
        AND
            tt.traject_id = trajecten.traject_id
        AND
            tt.status = 0
        ORDER BY
            tt.prio DESC,
            t.sort
        LIMIT
            1
    )
WHERE
   trajecten.traject_id = traject_taken.traject_id
AND
   traject_taken.taak_id  = taken.taak_id
AND
    traject_taken.status = 0
ORDER BY
   traject_taken.prio DESC,
   percentage DESC,
   taken.sort

[ Voor 1% gewijzigd door Scr33x0r op 29-12-2010 13:43 . Reden: Even nog een sort toevoegen voor prio in JOIN where clause.. ]

[ACM]

Scr33x0r schreef op woensdag 29 december 2010 @ 13:39:
Hmm, nog even testen, maar volgens mij heb ik het:

SQL:

-- ...
FROM
    trajecten,
    traject_taken
JOIN
    taken
-- ...

Het ziet er in ieder geval ongeveer uit zoals ik bedoelde, wellicht is het nog wel handig voor jezelf om bovenstaande im- en expliciete inner joins allemaal expliciet te maken (dus trajecten join traject_taken ... and ... and ... join taken ...). Dat houdt de query doorgaans net wat leesbaarder

[Scr33x0r]

ACM schreef op woensdag 29 december 2010 @ 14:54:
[...]

Het ziet er in ieder geval ongeveer uit zoals ik bedoelde, wellicht is het nog wel handig voor jezelf om bovenstaande im- en expliciete inner joins allemaal expliciet te maken (dus trajecten join traject_taken ... and ... and ... join taken ...). Dat houdt de query doorgaans net wat leesbaarder

Dat snap ik niet

[Woy]

Scr33x0r schreef op woensdag 29 december 2010 @ 15:10:
[...]

Dat snap ik niet

Je selecteerd nu uit 2 tabellen in de FROM clause, en joint die nu in je WHERE clause. Het is duidelijker als je daar gewoon een JOIN clause voor gebruikt.

[NMe]

Woy schreef op woensdag 29 december 2010 @ 13:18:
[...]

Zowel een view als een sp lossen het probleem van een complexe query niet op. Je stopt het alleen wat dieper weg. Dus ik zie niet in waarom een complexe query een reden zou zijn om het in een SP te doen. Je kunt de query ook gewoon op een andere plek neerzetten zodat het de rest van de code niet zoveel verstoord.

Het ging er niet om om de query minder complex te maken maar om de magic numbers eruit te halen en op een centrale plaats te hebben.

[Woy]

NMe schreef op woensdag 29 december 2010 @ 15:16:
[...]

Het ging er niet om om de query minder complex te maken maar om de magic numbers eruit te halen en op een centrale plaats te hebben.

Maar ook daarvoor hoef je geen view of sp te maken, dus ik snap je punt niet helemaal.

[Cartman!]

NMe schreef op woensdag 29 december 2010 @ 13:16:
[...]
Die zouden dan niet in je query staan maar in je SP.

Dat snap ik maar dan heb je dus ergens los van je code nog meer code waarbij (meestal!) geen duidelijk documentatie zit wat die getallen zijn en wat ze doen. Als je dat tijdens het inserten meteen uitrekent dan heb je de duratie allang afgevangen, lijkt me veel simpeler en sneller. Je percentage wordt ook ineens simpeler. Snap je m'n punt?