[MySQL] Groeperen met voorkeur voor 'top'-positie

[HaTe]

Ik ben bezig met het verbeteren van de SQL query op de index pagina's (de dag-overzichten) op mijn website: *snip* Niet relevant: spam

Een uitzending kan verschillende typen video's bevatten, waaronder altijd 1 met classname 'uitzending' en voor de rest vaak 'eps_fragment'. Ook zijn er uitzendingen zonder een echte 'uitzending', dus die bevatten alleen andere classes.

Ik zou graag willen dat bij het groeperen van de lijst, de uitzending in het overzicht komt, mits deze er is. Nu krijg ik vaak een fragment in het overzicht.

Hier mijn huidige SQL query:

SQL:

SELECT 
    id,
    title,
    broadcastdatetime as date,
    thumbnail,
    episodekey,
    serieskey,
    samenvattinglang,
    serienaam,
    classname,
    UPPER(station) as station,
    broadcastdatetime,
    IF(classname = 'uitzending', 1, 0) as order2,
    COUNT(*) as count
FROM items2
WHERE 
    DATE(broadcastdatetime) = DATE(NOW()) - INTERVAL $daysback DAY
GROUP BY episodekey
ORDER BY broadcastdatetime DESC, order2 DESC

Ik had wat gespeeld met die order2, maar ik lees op internet dat dit niet uitmaakt, omdat het groeperen voor het sorteren gebeurd. Hoe moet het dan wel precies in dit geval? Ik kom er niet echt uit.

[ Voor 3% gewijzigd door RobIII op 10-11-2012 03:07 ]

[RobIII]

Het is inmiddels zo'n beetje de standaard link die we kunnen plaatsen if topic like '%mysql%' and like '%group by%': Hoe werkt dat GROUP BY nu eigenlijk?. Sterker: ik weet dat onze search nog te wensen over laat maar ik kan er haast niet bij dat je deze link niet zou zijn tegen gekomen.

Verder kun je die IF... prima in de order by plaatsen en zou ik zaken als upper lekker uitspenderen aan m'n presentatielaag; je database heeft andere verantwoordelijkheden dan zich druk te maken over hoe je een station gaat weergeven.

[ Voor 13% gewijzigd door RobIII op 10-11-2012 03:13 ]

[HaTe]

Bedankt voor die laatste tips. Dat wat in dat topic over GROUP BY staat wist ik al, ik wist alleen niet hoe ik naar die kolommen waar op wordt gesorteerd nog meer kolommen kon ophalen. Volgens mij is het me gelukt:

SQL:

SELECT 
    *, COUNT(*) as count
FROM 
    (SELECT
        id,
        title,
        thumbnail,
        episodekey,
        serieskey,
        samenvattinglang,
        serienaam,
        classname,
        broadcastdatetime       
    FROM items2
    ORDER BY IF(classname = 'uitzending', 1, 0) DESC) as t
WHERE 
    DATE(broadcastdatetime) = DATE(NOW()) - INTERVAL 1 DAY
GROUP BY episodekey
ORDER BY broadcastdatetime DESC

In PHPMyAdmin krijg ik in ieder geval iets heel plausibels er uit > Ja hoor, getest en in gebruik, werkt perfect.

Ik weet niet of dit nog beter kan? Tips zijn uiteraard welkom.

[ Voor 3% gewijzigd door HaTe op 10-11-2012 19:34 . Reden: order by moest group by zijn ]

[RobIII]

HaTe schreef op zaterdag 10 november 2012 @ 16:33:
Dat wat in dat topic over ORDER BY staat wist ik al,

Heb je m'n link überhaupt gelezen? Je GROUP BY klopt nog steeds niet (wie had 't over ORDER BY ) en elk resultaat dat correct lijkt is puur toeval. Alle velden die je selecteert die niet in een aggregate (sum, count, avg, ...) staan horen in je GROUP BY. Ook als je Select * ... gebruikt dus. Ik zie dan ook niet waarom je nu opeens een subquery gebruikt?

[ Voor 74% gewijzigd door RobIII op 10-11-2012 19:33 ]

[Gomez12]

HaTe schreef op zaterdag 10 november 2012 @ 16:33:
Ik weet niet of dit nog beter kan? Tips zijn uiteraard welkom.

Simpelweg goed begrijpen hoe group by werkt ipv maar blind een subquery bouwen is een hele goede tip al zeg ik het zelf...

[HaTe]

RobIII schreef op zaterdag 10 november 2012 @ 19:26:
[...]
Heb je m'n link überhaupt gelezen? Je GROUP BY klopt nog steeds niet (wie had 't over ORDER BY )

Zeker dat ik die heb gelezen, ik bedoelde uiteraard GROUP BY, niet ORDER BY.

en elk resultaat dat correct lijkt is puur toeval. Alle velden die je selecteert die niet in een aggregate (sum, count, avg, ...) staan horen in je GROUP BY. Ook als je Select * ... gebruikt dus. Ik zie dan ook niet waarom je nu opeens een subquery gebruikt?

Dat had ik er niet van begrepen. Als je die zin nou in de FAQ zet is het een stuk duidelijker. Ik zie alleen niet in hoe ik die Aggregate functies in mijn geval kan toepassen of hoe ik opeens wel het juiste resultaat krijg als ik alle kolommen achter GROUP BY zet.

Je komt trouwens niet vriendelijk over.

[Voutloos]

Eerlijk gezegd staat die zin juist letterlijk in de foutmelding waar de halve 'Wat doet GROUP BY?' sectie over gaat. Incl. uitleg waarom het fout is, en extra hint dat mysql je met standaard instellingen niet waarschuwt.

[RobIII]

HaTe schreef op zaterdag 10 november 2012 @ 19:42:
Dat had ik er niet van begrepen.

Er staat letterlijk:

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

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.

De alinea's ervoor leiden tot die zin. Het héle stuk gaat erover...

HaTe schreef op zaterdag 10 november 2012 @ 19:42:
Je komt trouwens niet vriendelijk over.

Want proberen je te helpen en iets duidelijk te maken is niet vriendelijk? Je op de juiste relevante informatie wijzen is niet vriendelijk? Tot de conclusie komen dat je niet de moeite neemt (of lijkt te nemen dan) om iets met mijn informatie te doen maakt me mogelijk wat kortaf ja. Ik kan 't wel in een snoeziepoezie hello kitty verhaaltje verpakken als je wil... maar denk je dat dat helpt?

[ Voor 73% gewijzigd door RobIII op 10-11-2012 20:05 ]

[HaTe]

Afijn. Maar hoe met het nou wel volgens jullie? Want:

Ik zie alleen niet in hoe ik die Aggregate functies in mijn geval kan toepassen of hoe ik opeens wel het juiste resultaat krijg als ik alle kolommen achter GROUP BY zet.

[RobIII]

HaTe schreef op zaterdag 10 november 2012 @ 20:05:
Afijn. Maar hoe met het nou wel volgens jullie?

Nogmaals: Alles wat niet in aggregates staan hoort in de group by. Doe eens gek en probeer het eens?

Niet om weer onvriendelijk te klinken, maar als je de oplossing kant-en-klaar voorgekauwd op een zilveren presenteerblaadje aangereikt wil krijgen ben je hier aan 't verkeerde adres

Give a man a fish and feed him for a day. Teach a man how to fish and feed him for a lifetime.

[ Voor 40% gewijzigd door RobIII op 10-11-2012 20:07 ]

[HaTe]

Komt totaal niet uit wat ik wil hebben met dit:

SQL:

SELECT 
    id,
    title,
    thumbnail,
    episodekey,
    serieskey,
    serienaam,
    classname,
    station,
    broadcastdatetime,
    COUNT(*) as count
FROM items2
WHERE 
    DATE(broadcastdatetime) = DATE(NOW()) - INTERVAL 1 DAY
GROUP BY episodekey,    id,
    title,
    thumbnail,
    serieskey,
    serienaam,
    classname,
    station,
    broadcastdatetime
ORDER BY broadcastdatetime DESC, IF(classname = 'uitzending', 1, 0) DESC

Ik krijg gewoon de hele lijst terug, niet gegroepeerd.

[Gomez12]

HaTe schreef op zaterdag 10 november 2012 @ 20:15:
Ik krijg gewoon de hele lijst terug, niet gegroepeerd.

Nogmaals : Lees de gegeven link en google eens op group by.

Je krijgt het wel gegroepeerd terug, enkel zeg jij momenteel dat jij het ook gegroepeerd wilt hebben op classname en dus krijg je meerdere classnames terug (ik vermoed tenminste dat je dat bedoelt met "de hele lijst" )

Hint : Het zal waarschijnlijk een 2-traps raket worden als ik het zo volg :
- Je wilt eerst enkel je unieke episodekeys terugkrijgen in je inner query volgens de eisen / specs die jij hebt
- Je outer query kan dan alle info erbij zoeken van de episodekeys.

Dus niet random maar alle velden selecteren en dan dat weer in een subquery gieten die * velden selecteert, maar selectief opvragen wat je nodig hebt.

[pedorus]

RobIII schreef op zaterdag 10 november 2012 @ 19:26:
is puur toeval.

Maar toch eigenlijk ook niet gezien de huidige source code van mysql. Wel is het totaal ongedefinieerd gedrag.

Maar dit is gewoon een groupwise maximum-probleem, waar je met mysql niet vrolijk van wordt. Je krijgt dan als correcte(re) oplossing iets als (uitgegaan van de oplossing zoals in http://dev.mysql.com/doc/...mum-column-group-row.html ):

SQL:

SELECT
        items2.id,
        items2.title,
        items2.thumbnail,
        items2.episodekey,
        items2.serieskey,
        items2.samenvattinglang,
        items2.serienaam,
        items2.classname,
        items2.broadcastdatetime,
        COUNT(*)        
    FROM items2 left join items2 i2 on i2.episodekey = items2.episodekey and
        DATE(i2.broadcastdatetime) = DATE(NOW()) - INTERVAL 1 DAY and
        ((i2.classname = 'uitzending' and i2.id > items2.id) or
        (items2.classname != 'uitzending' and 
            (i2.classname = 'uitzending' or i2.id > items2.id)))
    inner join items2 i3 on i3.episodekey = items2.episodekey and 
        DATE(i3.broadcastdatetime) = DATE(NOW()) - INTERVAL 1 DAY
WHERE 
    i2.episodekey is null and
    DATE(items2.broadcastdatetime) = DATE(NOW()) - INTERVAL 1 DAY
GROUP BY items2.id
ORDER BY items2.broadcastdatetime DESC

Die 3 keer DATE(items2.broadcastdatetime) = DATE(NOW()) - INTERVAL 1 DAY kan verbeterd worden met een VIEW, mysql ondersteund helaas tal van functies zoals WITH niet of andere handige oplossingen voor groupwise-maximumproblemen.

Tabelnamen met een nummer erin zoals items2 lijken mij wat brak. Sommigen zullen beweren dat je de group by eigenlijk moet uitbreiden met de rest van de zaken in de select, maar ik hou me even buiten dat issue

[Gomez12]

pedorus schreef op zondag 11 november 2012 @ 00:54:
[...]

Maar toch eigenlijk ook niet gezien de huidige source code van mysql. Wel is het totaal ongedefinieerd gedrag.

En het probleem hierbij is dus dat het nergens gegarandeerd wordt.

Zou mysql zeggen : Zo handelen wij het af, dan zou het een quirk zijn, maar zou het wel bruikbaar zijn.
Momenteel is het echter nergens zo gedefinieerd en bij de volgende release hoeft het opeens niet meer zo te werken.

In documentatie vastgelegde quirks valt in sommige gevallen nog wel mee te werken, maar enkel in source code vastgelegde quirks zijn simpelweg niet te gebruiken.

[Woy]

HaTe schreef op zaterdag 10 november 2012 @ 20:05:
Afijn. Maar hoe met het nou wel volgens jullie? Want:
[...]

Dan moet je je eerst goed bedenken waar precies op gegroepeerd moet worden. Als je een veld namelijk niet in de Group By clause hebt staan betekend dat automatisch dat er niet een enkele scalar value uit komt, en dus moet het RDBMS een keuze maken welke waarde hij moet kiezen. Dat kiezen/berekenen doet hij d.m.v. aggregate functies ( Die zetten een set waardes om naar een enkele scalar value ).

Als je die keuze niet geeft hoort een RDBMS gewoon een fout te geven. Binnen mysql wordt dat niet gedaan en is het resultaat net als dat je een "RANDOM" aggregate functie zou gebruiken. Als dat een nuttige feature was geweest hadden ze er IMHO beter voor kunnen kiezen om daadwerkelijk een RANDOM als aggregate aan te bieden, want dan maak je het ten minste expliciet.

[ Voor 25% gewijzigd door Woy op 11-11-2012 14:58 ]