[Postgresql] Top N rows in GROUP BY

[Anoniem: 36940]

Voor een rating rapportage tool wil ik de Top 5 ratingscore per maand tonen van records die gerate zijn.
Met de functie date_trunc kan ik de maand van de rating timestamp en daarmee groeperen, maar ik kan er geen Top 5 resultaten per maand eruit krijgen. Ik zie iets over het hoofd, maar wat?

Ik zal een paar voorbeelden geven:

Tabel rating:

rating_create	content_id	score
2009-12-01 09:12:12	10	4
2009-12-05 10:12:12	10	4
2009-12-17 11:12:12	10	3
2009-12-10 12:12:12	10	3
2009-12-21 13:12:12	10	2
2009-12-15 14:12:12	10	5
2009-12-11 15:12:12	17	3
2009-12-20 16:12:12	17	5
2009-12-01 17:12:12	10	4
2009-12-05 18:12:12	10	4
2010-01-17 19:12:12	12	3
2010-01-10 20:12:12	12	3
2010-01-21 21:12:12	15	2
2010-01-15 22:12:12	15	5
2010-01-11 23:12:12	17	3
2010-01-20 24:12:12	17	5

Etc. etc...

SQL:

SELECT to_char(date_trunc('month',rating_create),'YYYY-MM-DD') AS trunc, content_id, AVG(score) AS score, COUNT(rating_id) AS votes 
FROM trunc 
GROUP BY dates, content_id 
ORDER BY dates, score DESC, votes DESC

Het resultaat is dan bijvoorbeeld:

trunc	content_id	score	votes
2009-01-01	23	4.9	122
2009-01-01	24	4.1	144
2009-01-01	25	4.1	133
2009-01-01	26	3.6	155
2009-01-01	27	3.4	166
2009-01-01	28	3.1	177
2009-01-01	29	3.0	188
2010-01-01	20	4.5	90
2010-01-01	21	4.1	100
2010-01-01	22	4.0	110
2010-01-01	23	3.9	120
2010-01-01	24	3.4	130
2010-01-01	25	3.0	140
2010-01-01	26	2.8	150
2010-01-01	27	2.7	160
2010-01-01	28	1.8	170

Ok, het resultaat per maand met unieke content_id's klopt, maar ik wil alleen de eerste 5 records van elke maand. Met LIMIT 5 krijg ik alleen de eerste 5 rows van het hele resultaat. Met HAVING COUNT(DISTINCT truncate) <= 5 gebeurt er niets met het resultaat Wat doe ik verkeerd?

Alvast bedankt!

[JaQ]

Als je echt lui bent, kan je een inline sub-query gebruiken?

[ACM]

Per group 5 records tonen is niet echt standaard sql-functionaliteit voor, althans niet als je voor 3 verschillende datums in totaal maximaal 15 records terug wilt zien, en dan voor elk van de drie 5.

Je kan wat klooien met subqueries of functions om zo de top5 terug te geven, in bijvoorbeeld een array. Maar in principe loopt dit snel uit op ofwel hele complexe queries, ofwel gewoon de keus om het dan maar per datum die je wilt weergeven los op te halen.
Als je records uniek identificeerbaar zijn met die sortering op rating, dan wordt het overigens makkelijker, dan kan je ze in de where-clause met een subquery eruit filteren door iets als WHERE id IN (SELECT id from ... where month = '...' order by rating limit 5).

[ACM]

't Kan trouwens wel in PostgreSQL als je versie 8.4 gebruikt. Je kan dan gebruik maken van de "windowing functions" en dan in dit geval met name de rank()-functie.

Zie hier de voorbeelden aan het eind van dit verhaal:
http://www.postgresql.org...tive/tutorial-window.html

[DamadmOO]

ACM schreef op donderdag 14 januari 2010 @ 17:27:
Per group 5 records tonen is niet echt standaard sql-functionaliteit voor, althans niet als je voor 3 verschillende datums in totaal maximaal 15 records terug wilt zien, en dan voor elk van de drie 5.

Semi offtopic: In MSSQL heb je daar sinds 2005 de volgende syntax voor

SQL:

 ROW_NUMBER() OVER(PARTITION BY Month ORDER BY Ranking DESC) AS RowNumber

en door dat dan in een CTE te wrappen kan je gewoon een query doen waar RowNumber <= 5

Verder zit het ook in Oracle en dus in PostgreSQL vanaf 8.4 e het zit in de standaard SQL;2003. Dus om te zeggen dat het niet echt standaard sql-functionaliteit is gaat me eigenlijk wel wat te ver.

[cariolive23]

DamadmOO schreef op donderdag 14 januari 2010 @ 21:19:
en door dat dan in een CTE te wrappen kan je gewoon een query doen waar RowNumber <= 5

Dat is volgens mij niet nodig, je kunt de query gewoon als subquery opnemen in een FROM en in de WHERE een vegelijking doen op het rownumber:

SELECT * FROM (hier jouw subquery met windowing) AS sub WHERE rownumber <= 5;

[ACM]

DamadmOO schreef op donderdag 14 januari 2010 @ 21:19:
Dus om te zeggen dat het niet echt standaard sql-functionaliteit is gaat me eigenlijk wel wat te ver.

Nouja, je moet eerst alle rows produceren en dan achteraf een (groot?) deel wegfilteren. Dus op zich is het niet echt standaard beschikbaar in sql, bij databases die dergelijke ranks/rownumbers kunnen genereren per partitie kan je het inderdaad wel gewoon bereiken.

[cariolive23]

ACM schreef op vrijdag 15 januari 2010 @ 10:32:
[...]

Nouja, je moet eerst alle rows produceren en dan achteraf een (groot?) deel wegfilteren. Dus op zich is het niet echt standaard beschikbaar in sql, bij databases die dergelijke ranks/rownumbers kunnen genereren per partitie kan je het inderdaad wel gewoon bereiken.

<mierenneukmodus>
Het is dus wél standaard beschikbaar in SQL, zie de SQL Standaarden. Dat niet ieder merk database dit heeft geïmplementeerd (in iedere versie), dat is het probleem van dit merk database en niet van de taal SQL.

PostgreSQL, SQL Server en Oracle kennen het wel, doe er je voordeel mee.
</mierenneukmodus>

[Anoniem: 36940]

Allemaal bedankt voor de reacties!

Ik had eerder de window functie gezien, maar m'n hosting heeft PG 8.2, dus dat is geen optie. Ik heb ook een rank optie geprobeerd met een 'temporary sequence' zoals http://www.barik.net/archive/2006/04/30/162447/ maar dat werkt ook niet.

De enigste manier voor mij lijkt om deze query los op te halen per 'month'. Als PG 8.4 (of hoger) draait, dan kan ik de query met window ombouwen.

[DamadmOO]

cariolive23 schreef op vrijdag 15 januari 2010 @ 09:53:
[...]

Dat is volgens mij niet nodig, je kunt de query gewoon als subquery opnemen in een FROM en in de WHERE een vegelijking doen op het rownumber:

SELECT * FROM (hier jouw subquery met windowing) AS sub WHERE rownumber <= 5;

Je kan in de where clause geen alias opgeven. En de ROW_NUMBER() OVER clause kan ook niet in de where. Wat je wel kan doen is overal op groeperen en het dan in je having af te vangen. Maar dan is het overzichtelijker om een CTE of derived table te gebruiken,

[cariolive23]

@DamadmOO: Je ziet iets over het hoofd, de kolommen die je in de subquery aanmaakt, kun je prima in de outerquery aanroepen in de WHERE. Het is dus niet nodig (ook niet mogelijk) om de functie ROW_NUMBER() aan te roepen, dat doe je in de subquery, de alias die je daarin aanmaakt, gebruik je weer in de outerquery. En dat werkt als een zonnetje!

[DamadmOO]

SQL:

SELECT INV.ProductID
, ROW_NUMBER() OVER (PARTITION BY INV.GroupID ORDER BY INVP.OutPrice DESC ) AS [RowNumber]
FROM dbo.Inventory AS INV
JOIN dbo.InventoryPrice AS INVP ON INV.ProductID = INVP.ProductID
WHERE RowNumber <= 5

Geeft me toch echt een foutmelding:

code:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'RowNumber'.

[cariolive23]

Dat klopt, je hebt er dan ook geen subquery van gemaakt:

cariolive23 schreef op vrijdag 15 januari 2010 @ 09:53:
Dat is volgens mij niet nodig, je kunt de query gewoon als subquery opnemen in een FROM en in de WHERE een vegelijking doen op het rownumber:

SELECT * FROM (hier jouw subquery met windowing) AS sub WHERE rownumber <= 5;

SQL:

SELECT
  *
FROM
  (SELECT 
    INV.ProductID
  , ROW_NUMBER() OVER (PARTITION BY INV.GroupID ORDER BY INVP.OutPrice DESC ) AS RowNumber
  FROM 
    dbo.Inventory AS INV 
      JOIN dbo.InventoryPrice AS INVP ON INV.ProductID = INVP.ProductID
  ) AS sub
WHERE
  RowNumber <= 5;

[ Voor 28% gewijzigd door cariolive23 op 15-01-2010 15:23 . Reden: Subquery van gemaakt ]

[DamadmOO]

cariolive23 schreef op vrijdag 15 januari 2010 @ 15:17:
Dat klopt, je hebt er dan ook geen subquery van gemaakt:

[...]

SQL:

SELECT
  *
FROM
  (SELECT 
    INV.ProductID
  , ROW_NUMBER() OVER (PARTITION BY INV.GroupID ORDER BY INVP.OutPrice DESC ) AS RowNumber
  FROM 
    dbo.Inventory AS INV 
      JOIN dbo.InventoryPrice AS INVP ON INV.ProductID = INVP.ProductID
  ) AS sub
WHERE
  RowNumber <= 5;

Dat is geen subquery... En lees eens mijn quote waarop je reageerde.

DamadmOO schreef op vrijdag 15 januari 2010 @ 14:30:
[...]

Je kan in de where clause geen alias opgeven. En de ROW_NUMBER() OVER clause kan ook niet in de where. Wat je wel kan doen is overal op groeperen en het dan in je having af te vangen. Maar dan is het overzichtelijker om een CTE of derived table te gebruiken,

We hebben dus langs elkaar heen zitten praten

[ACM]

DamadmOO schreef op vrijdag 15 januari 2010 @ 18:56:
Dat is geen subquery... En lees eens mijn quote waarop je reageerde.

Als dat geen subquery is ben ik benieuwd wat het volgens jou wel is. Volgens mijn begrip van de term is het namelijk in principe elke query die binnen een andere query gebruikt wordt. En daarbij maakt het mij niet uit of het om een query in de SELECT-list, FROM-clause, of WHERE-clause gaat.
't Kan natuurlijk zijn dat je heel moeilijk allemaal losse definities voor de verschillende plekken wilt hanteren, maar gezien het reeds gegeven voorbeeld had je toch al moeten zien dat hij zijn eigen latere voorbeeld bedoelde

[cariolive23]

Derived table, tja, dat is inderdaad wel een naampje die ze daar voor gebruiken en die ik even over het hoofd had gezien. Voor mij is het niet meer dan een subquery, het zal mij een zorg zijn waar de subquery staat, een sub is een sub en dat werkt prima!