Vensterfuncties: ranglijsten en berekeningen per groep

Een vensterfunctie is een functie die werkt op een "groep rijen" (het venster) maar — in tegenstelling tot GROUP BY — vermindert het aantal rijen: aan elke rij van het venster wordt een waarde berekend die over het venster wordt berekend eindresultaat.

Het is het juiste hulpmiddel voor ranglijsten, progressieve nummering en percentages over een groepstotaal, en vele andere handelingen die lastig zijn of onmogelijk met GROUP BY.

De syntaxis: function() OVER (…)

ROW_NUMBER() OVER (PARTITION BY colonna ORDER BY colonna2)

Componenten:

• PARTITION BY — definieert de "groepen" (zoals GROUP BY, maar de rijen gescheiden blijven);
• ORDER BY binnen OVER(…) — definieert de volgorde binnen de partitie.

OVER () (lege haakjes) betekent "de hele tabel als één enkel venster". Handig voor berekeningen zoals "percentage van totaal".

Rangschikkingsfuncties

ROW_NUMBER() OVER (PARTITION BY p ORDER BY o)
RANK()       OVER (PARTITION BY p ORDER BY o)
DENSE_RANK() OVER (PARTITION BY p ORDER BY o)

Verschillen (met waarden 100, 100, 90, 80):

Voorbeeld: productrangschikking per categorie

SELECT name,
       category_id,
       price,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
FROM   products
ORDER BY category_id, rank;

Voor elke category_id start de RANK opnieuw vanaf 1 en bestelt door af te dalen prijs: u krijgt de rangschikking van producten binnen elke categorie, in één schot.

Progressieve nummering

-- Progressive order number for each customer:
SELECT id,
       customer_id,
       ordered_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS row_num
FROM   orders
ORDER BY customer_id, row_num;

De eerste bestelling van elke klant heeft row_num = 1, de tweede 2, enz.

Probeer het

Oefening beoordelen