Window functions: classifiche e calcoli per gruppo

Una window function è una funzione che opera su un "gruppo di righe" (la window, finestra) ma — a differenza di GROUP BY — non riduce il numero di righe: a ogni riga del risultato finale viene attaccato un valore calcolato sulla finestra.

È lo strumento giusto per classifiche, numerazioni progressive, percentuali sul totale di un gruppo, e tante altre operazioni che con GROUP BY sono goffe o impossibili.

La sintassi: `funzione() OVER (…)`

SQL

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

Componenti:

PARTITION BY — definisce i "gruppi" (come GROUP BY, ma le righe restano separate);
ORDER BY dentro OVER(…) — definisce l'ordine all'interno della partizione.

OVER () (parentesi vuote) significa "tutta la tabella come unica finestra". Utile per calcoli come "percentuale sul totale".

Le funzioni di ranking

SQL

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)

Differenze (con valori 100, 100, 90, 80):

Funzione	Posizioni
`ROW_NUMBER`	1, 2, 3, 4 (univoco sempre)
`RANK`	1, 1, 3, 4 (a parità stessa posizione, lascia "buchi")
`DENSE_RANK`	1, 1, 2, 3 (a parità stessa posizione, senza buchi)

Esempio: classifica prodotti per categoria

SQL

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

Per ogni category_id la RANK riparte da 1 e ordina per prezzo decrescente: ottieni la classifica dei prodotti dentro ogni categoria, in un colpo solo.

Numerazione progressiva

SQL

-- Numero d'ordine progressivo per ogni cliente:
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;

Il primo ordine di ogni cliente avrà row_num = 1, il secondo 2, ecc.

Una window function viene calcolata dopo WHERE e GROUP BY. Non puoi quindi filtrare con WHERE rank = 1: avvolgi la query in una CTE e filtra fuori.

SQL

WITH ranked AS (
  SELECT name, price, RANK() OVER (ORDER BY price DESC) AS r
  FROM   products
)
SELECT * FROM ranked WHERE r <= 3;

Prova tu

Esercizio#sql.m7.l2.e1

Tentativi: 0Caricamento…

Per ogni prodotto mostra name, category_id, price e rank (la classifica per prezzo decrescente DENTRO la categoria; usa RANK). Ordina per category_id crescente e rank crescente.

Caricamento editor…

Mostra suggerimento

PARTITION BY category_id raggruppa, ORDER BY price DESC ordina dentro al gruppo.

Soluzione disponibile dopo 3 tentativi

Esercizio di ripasso

Esercizio#sql.m7.l2.e2