Google+ Followers

vrijdag 5 december 2014

Everyday PostgreSQL: de top-N berichten uit een tabel per groep.

Elke zoveel tijd kom je een vraag tegen als "Ik heb blogposts/producten/foto's in meerdere categorieen en nu wil ik per categorie de nieuwste X laten zien."

De standaard oplossing is ingewikkeld doen met lussen of correlated subqueries, maar in PostgreSQL (en meerdere van de betere databases) heb je toegang tot windowing, en daarmee wordt het ineens een elegante oplossing:


DROP TABLE IF EXISTS tmp_artikelen;
CREATE TEMPORARY TABLE tmp_artikelen (id SERIAL, title VARCHAR(100), group_id INT);
INSERT INTO tmp_artikelen VALUES
(1, 'Hoe maak je een salade?', 1),
(2, 'Het water staat hoog.',2),
(3, 'Twee maten om te meten',1),
(4, 'Geniet maar drink met mate.', 1),
(5, 'Wizzel en pop gaan uit.', 1),
(6, 'Tomas de locomotief.', 2),
(7, 'Het weer is weer weerbarstig.', 3),
(8, 'Gnurkie wap wap!', 3);

WITH data_plus_rownumber AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY id DESC) AS r
FROM tmp_artikelen)
SELECT * FROM data_plus_rownumber WHERE r <4;
De CTE maakt een query die naast de data uit de tabel ook het rij-nummer genereert op basis van het group_id en de volgorde van de id's per group_id. De feitelijke query pikt daaruit alle records met een rownummer van minder dan vier, met andere woorden: alleen de eerste drie per group_id.

Het enige wat je hier nog bij moet doen is een beetje sorteren en klaar ben je.