[MySQL] COUNT met een DISTINCT rij

[Maghiel]

Neem deze tabel(veldnamen zijn even fake maar maakt niet uit voor het idee):

code:

table
----------------
id         int
itemid   int
groupid int

Nu is het de bedoeling er een top 10 van itemids komt, dus een top 10 van hoevaak een bepaalde itemid voorkomt. MAAR, nu komt het struikelpunt, een groupid mag maar 1 keer in die top 10 voorkomen. Dus de meest voorkomende itemid in een groupid moet gepakt worden voor de top 10.

Deze query maakt een top10, met groupid nog niet uniek:

SQL:

SELECT `groupid`, `itemid`, COUNT(`itemid`) AS `count`
FROM `table`
GROUP BY `groupid`, `itemid`
ORDER BY `count` DESC LIMIT 0, 10

Dit was een ideetje maar werkt natuurlijk niet, en geeft precies hetzelfde resultaat:

SQL:

SELECT DISTINCT(`groupid`), `itemid`, COUNT(`itemid`) AS `count`
FROM `table` 
GROUP BY `groupid`, `itemid`
ORDER BY `count` DESC LIMIT 0, 10

Ik had verwacht hiermee een heel end te komen:

SQL:

SELECT `groupid`, `itemid`, count(2) AS `count`
FROM (   
    SELECT `groupid`, `itemid`
    FROM `table`
    GROUP BY `groupid`, `itemid`
    ) AS `subtable`
GROUP BY `groupid`, `itemid`
ORDER BY `aantal` DESC LIMIT 0,10

Maar dan is count altijd 1.

Ik heb nog een waslijst probeersels maar lijkt me een beetje overbodig hier erbij te posten nog.

Ik zit hier nu al tijden mee te worstelen, maar kom er niet uit. Kan iemand mij helpen de oplossing te vinden?

[Voutloos]

Maghiel schreef op woensdag 06 augustus 2008 @ 10:51:
Nu is het de bedoeling er een top 10 van itemids komt, dus een top 10 van hoevaak een bepaalde itemid voorkomt. MAAR, nu komt het struikelpunt, een groupid mag maar 1 keer in die top 10 voorkomen. Dus de meest voorkomende itemid in een groupid moet gepakt worden voor de top 10.

Top 10 van itemids? Maar je groepeert voornamelijk op groupid? Beschrijf eens beter wat je wil en/of maak een voorbeeld. Imo klinkt het nu als een stomme dataset of vraag naar random data (zie mysql group by gedrag mbt toestaan selecteren data welke niet in group by of aggregate functies staan).

[mr_derk]

kijk nog eens naar je GROUP BY. En om die derde query kan ik wel lachen

[ Voor 63% gewijzigd door mr_derk op 06-08-2008 11:11 ]

[Maghiel]

Voutloos schreef op woensdag 06 augustus 2008 @ 10:57:
[...]
Top 10 van itemids? Maar je groepeert voornamelijk op groupid? Beschrijf eens beter wat je wil en/of maak een voorbeeld. Imo klinkt het nu als een stomme dataset of vraag naar random data (zie mysql group by gedrag mbt toestaan selecteren data welke niet in group by of aggregate functies staan).

Dit is een voorbeeld van een top 10:

code:

groupid     itemid  count
12731   161594  154
12692   161511  109
6823    158584  43
6823    77766   34
12338   159133  34
9400    159452  25
6823    152119  24
12671   161307  22
12392   159480  21
12327   161260  19

Het probleem is dus dat bv 6823 er meerdere keren in voorkomt, en dat moet niet. Enkel het eerste resultaat van 6823 moet opgenomen worden, dus 6823,158584, 43.
Hoop dat het nu wat duidelijker is.

Over group by, al veel informatie over opgezocht (bv http://www.pollenation.net/journal/matt/92), en ik dacht dat ik het hierboven goed deed.

[Noork]

Heb je de distinct query wel op group_id gedaan? Ik zie dat nergens terug.

Je zou het ook in PHP (of wat je ook gebruikt) kunnen oplossen. Maak b.v. een top 20 lijst aan, Loop hier doorheen zodat je alle dubbele group_id's uitsluit en tot de 10 komt.

[mr_derk]

Maghiel schreef op woensdag 06 augustus 2008 @ 11:13:
[...]


Dit is een voorbeeld van een top 10:

code:

groupid     itemid  count
12731   161594  154
12692   161511  109
6823    158584  43
6823    77766   34
12338   159133  34
9400    159452  25
6823    152119  24
12671   161307  22
12392   159480  21
12327   161260  19

Het probleem is dus dat bv 6823 er meerdere keren in voorkomt, en dat moet niet. Enkel het eerste resultaat van 6823 moet opgenomen worden, dus 6823,158584, 43.
Hoop dat het nu wat duidelijker is.

Over group by, al veel informatie over opgezocht (bv http://www.pollenation.net/journal/matt/92), en ik dacht dat ik het hierboven goed deed.

je doet group by op 2 kollommen, dat is er 1 teveel

[Voutloos]

Maghiel schreef op woensdag 06 augustus 2008 @ 11:13:
Het probleem is dus dat bv 6823 er meerdere keren in voorkomt, en dat moet niet.

Blijkbaar wil je dan enkel op groupid groeperen?

Enkel het eerste resultaat van 6823 moet opgenomen worden, dus 6823,158584, 43.

En als je itemid niet in je group by hebt moet je dus dmv een aggregate function op een deterministische manier een itemid pakken als je 1 itemid wil selecteren. Bijvoorbeeld met MIN().

Noork schreef op woensdag 06 augustus 2008 @ 11:19:
Heb je de distinct query wel op group_id gedaan? Ik zie dat nergens terug.

Is ook een beetje raar als je op meerdere zaken groepeert.

Je zou het ook in PHP (of wat je ook gebruikt) kunnen oplossen. Maak b.v. een top 20 lijst aan, Loop hier doorheen zodat je alle dubbele group_id's uitsluit en tot de 10 komt.

Nee Nee en nog eens Nee . DIt is duidelijk een DB taakje en die query is gewoon appeltje-eitje als je helder hebt wat je wil.

Er klopt ook geen hol van je suggeste, die 20 is natte vinger werk en gaat geheid een keer pear-shaped. Bovendien heb je helemaal niets aan je count kolom zo.

[ Voor 7% gewijzigd door Voutloos op 06-08-2008 11:26 ]

[Noork]

Voutloos schreef op woensdag 06 augustus 2008 @ 11:25:
Is ook een beetje raar als je op meerdere zaken groepeert.
[...]

distinct != group by

Nee Nee en nog eens Nee . DIt is duidelijk een DB taakje en die query is gewoon appeltje-eitje als je helder hebt wat je wil.

Er klopt ook geen hol van je suggeste, die 20 is natte vinger werk en gaat geheid een keer pear-shaped. Bovendien heb je helemaal niets aan je count kolom zo.

Ik beweer ook nergens dat dit een 'mooie' oplossing is, maar werken doet het wel. Als je tenminste wel de limit goed instelt, zodat het geen fouten oplevert. Het ligt er maar helemaal aan waarvoor dit nodig is. Als het snel moet gebeuren, is het best een goede Q&D oplossing, i.p.v. 3 dagen lang Googelen naar de juiste query. Verder weet ik ook wel dat het beter is om direct in de DB op te lossen.

[Maghiel]

Noork schreef op woensdag 06 augustus 2008 @ 11:19:
Heb je de distinct query wel op group_id gedaan? Ik zie dat nergens terug.

Je zou het ook in PHP (of wat je ook gebruikt) kunnen oplossen. Maak b.v. een top 20 lijst aan, Loop hier doorheen zodat je alle dubbele group_id's uitsluit en tot de 10 komt.

Sorry, in het voorbeeld hierboven had ik de veldnamen vergeten aan te passen.

In PHP oplossen is GEEN oplossing. Sowieso vind ik dat zo lelijk dat ik het niet van m'n hart kan krijgen, EN wat nou als ik, bijvoorbeeld voor een subpagina, resultaten 11 t/m 20 wil hebben? Dat gaat niet werken dan.

mr_derk schreef op woensdag 06 augustus 2008 @ 11:23:
[...]


je doet group by op 2 kollommen, dat is er 1 teveel

Ok, maar enkel op groupid krijg ik precies hetzelfde resultaat.

Voutloos schreef op woensdag 06 augustus 2008 @ 11:25:
[...]
[...]
En als je itemid niet in je group by hebt moet je dus dmv een aggregate function op een deterministische manier een itemid pakken als je 1 itemid wil selecteren. Bijvoorbeeld met MIN().

Daar heb ik aan zitten denken, maar ik kan niet goed voor me krijgen hoe dat aan te pakken.
Zou je misschien een voorbeeldje hebben? Dan ga ik die in mijn probeersels erin proberen te bakken.

[Voutloos]

Noork schreef op woensdag 06 augustus 2008 @ 11:33:
i.p.v. 3 dagen lang Googelen naar de juiste query

Ten eerst zoek je dan wel ongelooflijk slecht en ten tweede krijg je group by beter (of uberhaupt) onder de knie, waar je later ook nog wat aan hebt.

Maghiel schreef op woensdag 06 augustus 2008 @ 11:46:
Daar heb ik aan zitten denken, maar ik kan niet goed voor me krijgen hoe dat aan te pakken.
Zou je misschien een voorbeeldje hebben? Dan ga ik die in mijn probeersels erin proberen te bakken.

Erm, ik geef toch alle ingredienten voor die query? Selecteer groupid, MIN(itemid) en COUNT(*) en groepeer op groupid? Misschien wil je iets anders met items, maar verzeker je er maar eerst van dat je deze basic group by query begrijpt.

[ Voor 46% gewijzigd door Voutloos op 06-08-2008 11:55 ]

[Noork]

Sowieso denk ik dat je de query uitvoert op de verkeerde tabel. Is dit niet een soort koppeltabel tussen de items en de groups? (in feite zit je dan met veel op veel) Lijkt me dat je beter de query kan beginnen vanuit groups of items? Iets klopt er gewoonweg niet. Denk nog eens goed na wat je exact wilt. Geef desnoods wat meer prijs van je datamodel en omschrijf nog eens duidelijk wat je wilt.

[reddog33hummer]

Dus als ik het goed begrijp krijg je bij deze tabel:

id	itemid	groupid
1	1	1
2	2	1
4	1	1
5	1	1
6	3	2
7	3	2
8	4	2
9	5	1

De uitkomst:

1	1	3
2	3	2

Dan wil je dus,
De top 10 van de lijst die de maximale aantallen van een itemid hebben per groep.

Vertaling:
The top 10 of a list with the maximum amount per goupid and the itemid of that maximum count.

Vertaling

SQL:

--The Top 10
select top 10 aantallen.groupid, aantallen.itemid, aantallen.aantal 
-- of a list with the maximum amount per goupid 
(
  select aantallen.groupid, max(aantal) as aantal
  from ( 
      select groupid, itemid, count(*) as aantal
      from dbo.Table_1
      group by groupid, itemid
    ) as aantallen
   group by aantallen.groupid
) as maximum
-- and the itemid of that maximum count
inner join
( 
  select groupid, itemid, count(*) as aantal
  from dbo.Table_1
  group by groupid, itemid
) as aantallen on maximum.aantal = aantallen.aantal and --wat hoort hier nog... gezien de tekst
-- hoord nog bij de top 10
order by aantallen.aantal DESC

[ Voor 31% gewijzigd door reddog33hummer op 06-08-2008 12:34 ]

[Maghiel]

Voutloos schreef op woensdag 06 augustus 2008 @ 11:53:
[...]
Ten eerst zoek je dan wel ongelooflijk slecht en ten tweede krijg je group by beter (of uberhaupt) onder de knie, waar je later ook nog wat aan hebt.

[...]
Erm, ik geef toch alle ingredienten voor die query? Selecteer groupid, MIN(itemid) en COUNT(*) en groepeer op groupid? Misschien wil je iets anders met items, maar verzeker je er maar eerst van dat je deze basic group by query begrijpt.

Heel veel dank, nu kom ik ergens Alleen zit ik me nu af te vragen of de resultaten wel kloppen:

SQL:

SELECT `artistid`, MIN(`trackid`), COUNT(*) AS `count`
FROM `main_statistics_listened`
GROUP BY `artistid`
ORDER BY `count` DESC LIMIT 0,10

Resultaat:

code:

artistid    MIN(`trackid`)  count Descending
12731   161594  164
6823    152119  126
12338   159133  116
12692   161511  109
9400    122145  52
4105    38215   47
51      159090  45
6991    123487  41
585     148745  41
12705   161467  41

SQL:

SELECT `artistid`, `trackid` , COUNT( `trackid` ) AS `count`
FROM `main_statistics_listened`
GROUP BY `artistid` 
ORDER BY `count` DESC
LIMIT 0 , 10

Resultaat:

code:

artistid    trackid     count Descending
12731   161594  164
6823    158584  126
12338   159408  116
12692   161511  109
9400    122145  52
4105    44182   47
51      277     45
6991    147943  41
585     158038  41
12705   161495  41

Heb inderdaad lopen klooien met die group by, want bij die tweede klopt het nu ook gewoon.
Als ik het goed begrijpt, pakt hij bij de query met de MIN(), de laagste trackid bij een bepaalde artistid? Dat is niet helemaal bedoeling. Ik snap dan alleen niet hoe hij bij de tweede query op lagere trackids uitkomt.
Is het eigenlijk niet gewoon de tweede query die mijn gewenste resultaat geeft?
Zie hieronder voor het echte datamodel.

Noork schreef op woensdag 06 augustus 2008 @ 11:53:
Sowieso denk ik dat je de query uitvoert op de verkeerde tabel. Is dit niet een soort koppeltabel tussen de items en de groups? (in feite zit je dan met veel op veel) Lijkt me dat je beter de query kan beginnen vanuit groups of items? Iets klopt er gewoonweg niet. Denk nog eens goed na wat je exact wilt. Geef desnoods wat meer prijs van je datamodel en omschrijf nog eens duidelijk wat je wilt.

Het is inderdaad een soort koppeltabel.
Dit is hem:

code:

CREATE TABLE `main_statistics_listened` (
  `id` int(11) NOT NULL auto_increment,
  `remote_ip` varchar(16) default NULL,
  `sourceprefix` int(11) NOT NULL default '0',
  `trackid` varchar(50) default NULL,
  `artistid` varchar(255) NOT NULL,
  `labelusername` varchar(255) default NULL,
  `genrelinkid` int(11) NOT NULL,
  `siteshopid` int(11) default NULL,
  `timestamp` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `remote_ip` (`remote_ip`),
  KEY `trackid` (`trackid`),
  KEY `siteshopid` (`siteshopid`),
  KEY `sourceprefix` (`sourceprefix`),
  KEY `genrelinkid` (`genrelinkid`),
  KEY `artistid` (`artistid`),
  KEY `labelusername` (`labelusername`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Het betreft hier dus muziek(tracks). Ik kan echter niet vanaf andere tabellen beginnen, dit zit namelijk zo: een deel van de muziek staat bij ons, een deel van de muziek komt van een webservice af.

Het is dus de bedoeling de meest populaire tracks te pakken, maar een artiest mag maar 1 keer in de lijst voorkomen.

[Noork]

De MIN statement lijkt me inderdaad niet correct.

Ik zit even hardop te denken, dus als ik gekke dingen zeg, neem me het niet kwalijk. Maar is het niet onlogisch om de artiest_id op te slaan wanneer je alleen registreert hoe vaak een track is beluisterd? Middels het track_id kun je de artiest weer opvragen lijkt me.

[Maghiel]

Noork schreef op woensdag 06 augustus 2008 @ 13:04:
De MIN statement lijkt me inderdaad niet correct.

Ik zit even hardop te denken, dus als ik gekke dingen zeg, neem me het niet kwalijk. Maar is het niet onlogisch om de artiest_id op te slaan wanneer je alleen registreert hoe vaak een track is beluisterd? Middels het track_id kun je de artiest weer opvragen lijkt me.

Hardop denken is juist goed, leren we allemaal wat van

De reden dat ik artistid opsla is: aangezien een groot deel van de muziek dus van een webservice afkomt, zou ik bij elke trackid van die muziek een call naar die webservice moeten doen om de artistid op te halen. Dit maakt de boel ontzettend trager. (Ja ik cache dingen, maar dat doet er even niet toe )

En het leek mij makkelijker om een top zoveel op te stellen waar een artistid slechts 1 keer in voor mag komen, als ik die artistid gellijk in de tabel opsla.

[Noork]

Heb je nog gekeken naar de post van reddog33hummer? Ziet er complex uit, maar is het proberen waard.

Ik kon het niet laten, en heb zelf ook nog zitten knoeien. Ik heb het hier getest, en opgelost met behulp van een view Dus als dat een mogelijkheid is, zou je het ook zo op kunnen lossen.

Maak een view aan op basis van je werkende query (zonder unieke artiest dus). Zoiets dus

code:

SELECT
*, 
Count(item_id) AS teller
FROM
test2
GROUP BY
item_id
ORDER BY
teller DESC

Voer daarna een query uit op je view, met een group by op group_id/artiest

code:

SELECT
*
FROM
view
GROUP BY
group_id
ORDER BY
teller DESC
LIMIT 10

Lekker omslachtig, maar het werkt

[Voutloos]

WTF (etc. etc.)

Ik heb er al 2 keer naar gehint, maar welke logische waarde verwacht je nu in hemelsnaam uiteindelijk in de teller kolom. Voor random getallen waar je niets mee kan kan je ook gewoon rand() gebruiken, geen db voor nodig.

En reddog33hummer doet gewoon een omslachtige poging voor een groupwise maximum, welke oa leuk uitgelegd wordt op: http://jan.kneschke.de/projects/mysql/groupwise-max

[Noork]

Voutloos schreef op woensdag 06 augustus 2008 @ 13:40:
WTF (etc. etc.)

Ik heb er al 2 keer naar gehint, maar welke logische waarde verwacht je nu in hemelsnaam uiteindelijk in de teller kolom. Voor random getallen waar je niets mee kan kan je ook gewoon rand() gebruiken, geen db voor nodig.

Fijn dat je ook lekker constructief meedenkt!

Volgens mij wil de TS het aantal keer dat een nummer is gespeelt weergeven. Hier een top 10 lijst van maken, maar een artiest slechts 1x in de lijst terug laten komen. Het is dus geen zuivere top 10 in dit geval. Lijkt me dat de logische waarde van de teller-kolom dus wel duidelijk is.

[Maghiel]

Noork schreef op woensdag 06 augustus 2008 @ 13:35:
Heb je nog gekeken naar de post van reddog33hummer? Ziet er complex uit, maar is het proberen waard.

Ik kon het niet laten, en heb zelf ook nog zitten knoeien. Ik heb het hier getest, en opgelost met behulp van een view Dus als dat een mogelijkheid is, zou je het ook zo op kunnen lossen.

Maak een view aan op basis van je werkende query (zonder unieke artiest dus). Zoiets dus

code:

SELECT
*, 
Count(item_id) AS teller
FROM
test2
GROUP BY
item_id,
group_id
ORDER BY
teller DESC

Voer daarna een query uit op je view, met een group by op group_id/artiest

code:

SELECT
*
FROM
view
GROUP BY
group_id
ORDER BY
teller DESC
LIMIT 10

Lekker omslachtig, maar het werkt

Ga ik straks even naar kijken, er is nu even iets tussendoor gekomen. Maar het lijkt me inderdaad ook een beetje omslachtig. Moet toch wel zonder view kunnen?

Voutloos schreef op woensdag 06 augustus 2008 @ 13:40:
WTF (etc. etc.)

Ik heb er al 2 keer naar gehint, maar welke logische waarde verwacht je nu in hemelsnaam uiteindelijk in de teller kolom. Voor random getallen waar je niets mee kan kan je ook gewoon rand() gebruiken, geen db voor nodig.

En reddog33hummer doet gewoon een omslachtige poging voor een groupwise maximum, welke oa leuk uitgelegd wordt op: http://jan.kneschke.de/projects/mysql/groupwise-max

Noork schreef op woensdag 06 augustus 2008 @ 13:55:
[...]

Fijn dat je ook lekker constructief meedenkt!

Volgens mij wil de TS het aantal keer dat een nummer is gespeelt weergeven. Hier een top 10 lijst van maken, maar een artiest slechts 1x in de lijst terug laten komen. Het is dus geen zuivere top 10 in dit geval. Lijkt me dat de logische waarde van de teller-kolom dus wel duidelijk is.

Inderdaad, leek me zo onderhad ook wel duidelijk.

En we zitten niet allemaal op hetzelfde niveau he Voutloos.

[Voutloos]

Noork schreef op woensdag 06 augustus 2008 @ 13:55:
Lijkt me dat de logische waarde van de teller-kolom dus wel duidelijk is.

Nee, dat is die teller niet, want order by gebeurd ná group by. Je selecteert dus gewoonweg random data.

Maghiel schreef op woensdag 06 augustus 2008 @ 13:59:
En we zitten niet allemaal op hetzelfde niveau he Voutloos.

Ik post hier ook niet om voor mysql-god uitgemaakt te worden. Maar de oorzaak van het genoemde random data probleem haalde ik al aan in de allereerste reactie in dit topic, waarna een paar uur later iedereen queries moet spuien welke mysql (enkel met de niet stricte default instellingen) als enige db toevallig pikt.

[Maghiel]

Voutloos schreef op woensdag 06 augustus 2008 @ 14:25:
[...]
Nee, dat is die teller niet, want order by gebeurd ná group by. Je selecteert dus gewoonweg random data.


[...]
Ik post hier ook niet om voor mysql-god uitgemaakt te worden. Maar de oorzaak van het genoemde random data probleem haalde ik al aan in de allereerste reactie in dit topic, waarna een paar uur later iedereen queries moet spuien welke mysql (enkel met de niet stricte default instellingen) als enige db toevallig pikt.

Maar als je ziet dat wij het niet zo goed snappen, en erop door blijven gaan, en jij weet wat we verkeerd doen, kun je dan niet een iets hulpzamer antwoord geven dan telkens te zeggen dat we random data aan het ophalen zijn?

Ik snap dat je hints wil geven zodat ik er zelf mee stoei, en me de goede richting op wilt sturen, en ik weet wel waar ik ergens heen moet lopen, alleen is dat pad nogal mistig voor me.

[pedorus]

Het probleem met die oplossing van oa Noork is dat het alleen werkt omdat MySQL op dit moment zo is geimplementeerd. Bij volgende versies kan dat veranderen, en krijg je een willekeurige rij terug. Theoretisch gezien heeft een ORDER BY zonder TOP/LIMIT in een view of subquery geen enkel effect. MSSQL gooit zoiets bijvoorbeeld weg bij het optimizen.

Voor de juiste oplossing: zie die link over groupwise max of zie de manual.

[Voutloos]

pedorus schreef op woensdag 06 augustus 2008 @ 16:00:
Bij volgende versies kan dat veranderen, en krijg je een willekeurige rij terug.

Nogmaals, met deze query geeft mysql al vele jaren random data terug, anders was de query simpelweg niet uit te voeren.
Er is wel een sql_mode geintroduceerd om correcte group by queries af te dwingen (ONLY_FULL_GROUP_BY) . Het is aan te raden om eens al je queries te draaien met deze setting aan. Dit ivm mogelijke bugs en het breken van je applicatie zodra de default setting verandert (== verbetert )

[pedorus]

Ik was idd niet duidelijk genoeg. Het wordt ook sterk afgeraden in de huidige versies:

Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

Enkel 'indeterminate' valt in de praktijk nogal mee. In 100% van de door mij bekeken gevallen komt de data 'toevallig' uit de eerste rij die bij die groep hoort...

[Verwijderd]

pedorus schreef op woensdag 06 augustus 2008 @ 16:58:
Enkel 'indeterminate' valt in de praktijk nogal mee. In 100% van de door mij bekeken gevallen komt de data 'toevallig' uit de eerste rij die bij die groep hoort...

Totdat MySQL besluit een andere index te gebruiken omdat je bv. de ORDER BY aanpast...

Maar 't blijft gewoon bad practise om in je query niet-geaggregeerde velden te selecten die niet in je GROUP BY voorkomen. 't Voldoet doodgewoon niet aan de SQL standaard, en op 't moment dat je overstapt op bv. PostgreSQL, FireBird, Oracle, DB2, MSSQL etc. word je (terecht) keihard afgestrafd.
Zelfs wanneer je zeker weet dat dat niet-geaggregeerde veld binnen die GROUP BY steeds hetzelfde is, aggregeer 'm dan met bv. MIN().

[Maghiel]

(Ik was even op korte vakantie)

Bedankt voor de informatie en hulp allemaal! Ik ga de groupwise max uitzoeken.
Als ik eruit gekomen ben(of net niet) zal ik het hier weer posten!

[Maghiel]

Ik ben ermee aan de slag gegaan, maar kom er nog niet helemaal uit. Vooral omdat ik dus een MAX op een COUNT moet doen.

Dit is wat ik tot nu toe heb:

SQL:

SELECT stats.sourceprefix, stats.artistid, stats.trackid, stats2.mostlistened
    FROM main_statistics_listened AS stats,
        (SELECT artistid, MAX(timeslistened) AS mostlistened
            FROM (SELECT artistid, COUNT(trackid) AS timeslistened
                    FROM main_statistics_listened
                    GROUP BY artistid) AS stats3
            WHERE stats3.artistid = artistid
            GROUP BY artistid) AS stats2
    WHERE stats2.artistid = stats.artistid
    ORDER BY mostlistened DESC          

Nu krijg ik alleen maar dezelfde artistid, verschillende trackids, en dezelfde mostlistened.
Het getal dat mostlistened geeft is overigens wel het aantal keer dat de meest beluisterde track is beluisterd.

[winkbrace]

Volgens mij doet dit de truuk:

code:

select groupid
,      itemid
,      count
from
(
    select a.*
    ,      max(itemid) over (partition by groupid) max_itemid
    from
    (
        select groupid
        ,      itemid
        ,      count(*) count
        from   table
        group by groupid
        ,        itemid
    ) a
)
where itemid = max_itemid

[ Voor 26% gewijzigd door winkbrace op 14-08-2008 17:17 ]

[pedorus]

Maar wat nu als er 2 items zijn in 1 groep met dezelfde count? Daar gaat de top 10 met unieke groepen!

Die oplossing van Noork had dat probleem niet..

(Kijk naar left join/not exists voor een mogelijke oplossing. Neem bij gelijke counts bijvoorbeeld het item met het hoogste itemid.)

[winkbrace]

Hmm.. In Oracle zou ik daarvoor gewoon de row_number() over (partition by groupid order by count) gebruiken.
Maar voor MySQL is zoiets niet eenvoudig.

Ik zou inderdaad voor jouw oplossing kiezen. Zowel max(itemid) als max(count) nemen.
(Overigens staat in mijn query een fout. Ik kies daar max(itemid) ipv max(count) )

En de oplossing van Noork kan gemakkelijk zonder een view te maken door dat stukje sql als een inner view te gebruiken. Dat kan al vanaf MySQL 4 en views worden pas vanaf MySQL 5 ondersteund.

[Maghiel]

BazzPsychoNut schreef op donderdag 14 augustus 2008 @ 17:12:
Volgens mij doet dit de truuk:

code:

select groupid
,      itemid
,      count
from
(
    select a.*
    ,      max(itemid) over (partition by groupid) max_itemid
    from
    (
        select groupid
        ,      itemid
        ,      count(*) count
        from   table
        group by groupid
        ,        itemid
    ) a
)
where itemid = max_itemid

pedorus schreef op donderdag 14 augustus 2008 @ 17:38:
Maar wat nu als er 2 items zijn in 1 groep met dezelfde count? Daar gaat de top 10 met unieke groepen!

Die oplossing van Noork had dat probleem niet..

(Kijk naar left join/not exists voor een mogelijke oplossing. Neem bij gelijke counts bijvoorbeeld het item met het hoogste itemid.)

BazzPsychoNut schreef op donderdag 14 augustus 2008 @ 23:15:
Hmm.. In Oracle zou ik daarvoor gewoon de row_number() over (partition by groupid order by count) gebruiken.
Maar voor MySQL is zoiets niet eenvoudig.

Ik zou inderdaad voor jouw oplossing kiezen. Zowel max(itemid) als max(count) nemen.
(Overigens staat in mijn query een fout. Ik kies daar max(itemid) ipv max(count) )

En de oplossing van Noork kan gemakkelijk zonder een view te maken door dat stukje sql als een inner view te gebruiken. Dat kan al vanaf MySQL 4 en views worden pas vanaf MySQL 5 ondersteund.

Dank jullie voor de tips! Wanneer de werktijd weer begint ga ik ermee stoeien, en laat ik weten wat het geworden is

(Er wordt overigens MySQL 5 gedraait waar ik dit voor moet maken)

[Maghiel]

Ik kom er nog steeeeeeeeds niet uit.
Oplossing hierboven met MAX() OVER PARTITION BY werkt niet,
dat kan niet in MySQL toch?
Ik heb hem geporbeerd te combineren met wat ik al had.

De oplossing van Noork geeft toch random data terug?

Ik heb nu dit:

SQL:

SELECT 
    stats.artistid, stats.trackid, mostlistened
    FROM main_statistics_listened AS stats,
        (
            SELECT 
                artistid, trackid, MAX(timeslistened) AS mostlistened, stats3.timeslistened, max_trackid
            FROM 
            (
                SELECT artistid, trackid, trackid AS max_trackid, COUNT(*) AS timeslistened
                FROM main_statistics_listened
                GROUP BY artistid, trackid
            ) AS stats3
            GROUP BY artistid, trackid
        ) AS stats2
    WHERE stats.trackid = max_trackid
    ORDER BY mostlistened DESC
    LIMIT 0, 10

Nu krijg ik 10 keer hetzelfde resultaat. Dat is overigens wel echt de meest beluisterde track!

[ Voor 12% gewijzigd door Maghiel op 02-09-2008 16:51 ]

[Voutloos]

Lees dan nog eens de gegeven link, of ga na wat elk van die subqueries doet.

En max_trackid is helemaal niet het maximum trackid?

[ Voor 26% gewijzigd door Voutloos op 02-09-2008 17:02 ]

[pedorus]

Misschien is er hier een goed voorbeeld nodig van hoe het moet. Uit de manual kun je het volgende voorbeeld kopiëren:

SQL:

CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

Enkel dat gaat 'mis' als er nog een dealer is die voor een artikel dezelfde prijs rekent, bijv. dealer E:

SQL:

INSERT INTO shop VALUES (4,'E',19.95);

Dat kun je oplossen door ook naar dealers te kijken in de left join:

SQL:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND ((s1.price < s2.price) OR 
    ((s1.price = s2.price) AND (s1.dealer < s2.dealer)))
WHERE s2.article IS NULL;

(met voorkeur voor latere dealers in het alfabet)

In jouw geval gaat het niet over artikelen, dealers en prijzen, maar met 1 view valt dat zo op te lossen:

SQL:

CREATE VIEW shop AS
SELECT artistid article, trackid dealer, COUNT(*) price
FROM main_statistics_listened
GROUP BY artistid, trackid;

(niet voor productie )

[Maghiel]

pedorus schreef op dinsdag 02 september 2008 @ 18:28:
Misschien is er hier een goed voorbeeld nodig van hoe het moet. Uit de manual kun je het volgende voorbeeld kopiëren:

SQL:

CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

Enkel dat gaat 'mis' als er nog een dealer is die voor een artikel dezelfde prijs rekent, bijv. dealer E:

SQL:

INSERT INTO shop VALUES (4,'E',19.95);

Dat kun je oplossen door ook naar dealers te kijken in de left join:

SQL:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND ((s1.price < s2.price) OR 
    ((s1.price = s2.price) AND (s1.dealer < s2.dealer)))
WHERE s2.article IS NULL;

(met voorkeur voor latere dealers in het alfabet)

In jouw geval gaat het niet over artikelen, dealers en prijzen, maar met 1 view valt dat zo op te lossen:

SQL:

CREATE VIEW shop AS
SELECT artistid article, trackid dealer, COUNT(*) price
FROM main_statistics_listened
GROUP BY artistid, trackid;

(niet voor productie )

Hmm maar price zou dan dus overeenkomen met een COUNT(*) voor mij. Maar hoe krijg ik die COUNT in de join?

Dit gaat natuurlijk niet werken:

SQL:

SELECT s1.artistid, s1.trackid, COUNT(s1.trackid)
    FROM main_statistics_listened s1
    LEFT JOIN main_statistics_listened s2 ON s1.artistid = s2.artistid 
                                            AND ((COUNT(s1.trackid) < COUNT(s2.trackid)) 
                                                    OR (COUNT(s1.trackid) = COUNT(s2.trackid) AND (s1.trackid < s2.trackid))
                                                 )
                                                
    WHERE s2.artistid IS NULL;          
    GROUP BY s1.artistid, s2.trackid
    ORDER BY COUNT(s1.trackid) DESC LIMIT 0, 10

[pedorus]

Maghiel schreef op woensdag 03 september 2008 @ 12:42:
Hmm maar price zou dan dus overeenkomen met een COUNT(*) voor mij. Maar hoe krijg ik die COUNT in de join?

Het lijkt me het handigst om gewoon een VIEW aan te maken.

Als je het perse in 1 SQL statement wil moet je (bijna) dezelfde query 2 keer (als subquery in de from) opschrijven, want volgens mij ondersteunt MySQL het WITH keyword nog niet.