Funzioni finestra in SQLite: OVER, PARTITION BY e frame

Una funzione finestra aggiunge una colonna senza comprimere le righe

GROUP BY riduce molte righe a una. Una funzione finestra fa qualcosa di diverso: calcola un valore su un insieme di righe collegate ma tiene ogni riga di input nell'output. Ottieni il dettaglio riga per riga e l'aggregato, uno accanto all'altro.

La forma è sempre la stessa: una funzione, poi OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

La colonna total_all mostra il totale complessivo di tutte le righe, ripetuto su ogni riga. Le righe originali restano intatte. Confrontalo con SELECT SUM(amount) FROM sales: stesso numero, ma torna una sola riga. Le funzioni finestra ti danno entrambe le viste insieme.

PARTITION BY: aggregare dentro i gruppi

Un OVER () vuoto aggrega su tutta la tabella. Aggiungi PARTITION BY per aggregare dentro dei gruppi, un po' come GROUP BY, ma di nuovo senza comprimere le righe.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Ogni riga riceve il totale della propria regione e la propria quota di quel totale. Con un semplice GROUP BY perderesti il dettaglio per dipendente. È questo il grande vantaggio delle funzioni finestra: dettaglio e aggregato in una sola query.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

La famiglia del ranking numera le righe secondo un ORDER BY dentro OVER. Le tre varianti si distinguono per come gestiscono i pareggi.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

Come leggere l'output:

• ROW_NUMBER() è sempre unico: i pareggi vengono risolti in modo arbitrario. Usalo quando ti serve un numero stabile e distinto per ogni riga.
• RANK() dà la stessa posizione alle righe a pari merito, poi salta i numeri successivi. Due giocatori a pari merito in posizione 1 sono seguiti dalla posizione 3.
• DENSE_RANK() gestisce anch'esso i pareggi, ma non salta. La posizione successiva è 2.

Per i "primi N per gruppo", combina il ranking con PARTITION BY e filtra in una query esterna: WHERE non può fare riferimento direttamente alle funzioni finestra:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

I due che hanno guadagnato di più per ogni regione.

LAG e LEAD: guardare le righe vicine

LAG(col) restituisce il valore di col dalla riga precedente nella finestra. LEAD(col) guarda in avanti. Entrambe sono perfette per le domande sui cambiamenti nel tempo.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

Lo yesterday della prima riga è NULL: prima non c'è niente. Puoi fornire un valore predefinito: LAG(celsius, 1, celsius) OVER (ORDER BY day) userebbe il valore di oggi quando non esiste una riga precedente.

LEAD è l'immagine speculare. Combinale con PARTITION BY per ottenere sequenze per gruppo, per esempio confrontare le vendite di questo mese con quelle del mese precedente dentro ogni regione.

Totali progressivi con i frame della finestra

Aggiungi ORDER BY dentro OVER e le funzioni di aggregazione come SUM, AVG, COUNT iniziano a calcolare in modo cumulativo:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Due cose da notare:

• SUM(amount) OVER (ORDER BY day) è un totale progressivo. Il frame predefinito quando scrivi ORDER BY senza un frame esplicito è "dall'inizio della finestra fino alla riga corrente".
• La seconda colonna usa un frame esplicito: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. È una finestra scorrevole di 3 righe: una media mobile.

Il modello mentale per i frame: ogni funzione finestra viene valutata su un frame di righe, definito rispetto alla riga corrente. I frame più comuni:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: totale progressivo (il predefinito implicito).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW: finestra che segue la riga corrente.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: l'intera partizione.

ROWS conta le righe fisiche. Esiste anche RANGE, che raggruppa per valore: comodo quando ci sono pareggi nella colonna dell'ORDER BY e vuoi trattarli come un unico passo.

FIRST_VALUE, LAST_VALUE, NTILE

Qualche altra funzione finestra che vale la pena conoscere:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE e LAST_VALUE restituiscono il primo o l'ultimo valore dentro il frame. Con LAST_VALUE fai attenzione al frame: quello predefinito finisce a CURRENT ROW, quindi di solito ti serve ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING per ottenere davvero l'ultimo valore della partizione.
• NTILE(n) divide le righe in n gruppi di dimensioni circa uguali: utile per quartili, percentili e suddivisioni in stile A/B.

Dare un nome a una finestra con WINDOW

Quando più colonne condividono la stessa clausola OVER (...), la ripetizione diventa noiosa. SQLite ti permette di dare un nome a una finestra una volta e riutilizzarla:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

Stessa query, meno rumore. La clausola WINDOW va dopo WHERE/GROUP BY/HAVING e prima di ORDER BY.

Funzioni finestra vs GROUP BY

Entrambe riguardano l'aggregazione, ma rispondono a domande diverse:

• GROUP BY riduce. Una riga per gruppo. Usalo quando vuoi solo il riepilogo.
• Le funzioni finestra conservano. Ogni riga di input sopravvive, con colonne calcolate in più accanto.

Se ti capita di fare un GROUP BY e poi riunire gli aggregati alla tabella originale con un join, è un segnale forte che una funzione finestra farebbe il lavoro in una sola query.

Un paio di insidie

• WHERE non può fare riferimento alle funzioni finestra. I filtri vengono applicati prima che le finestre siano calcolate. Racchiudi la query in una subquery o in una CTE e filtra al livello esterno.
• I frame impliciti fanno brutti scherzi. SUM(x) OVER (ORDER BY y) è un totale progressivo perché il frame predefinito è RANGE UNBOUNDED PRECEDING. Se volevi la somma dell'intera partizione, scrivi OVER (PARTITION BY ...) senza un ORDER BY, oppure specifica il frame in modo esplicito.
• LAST_VALUE sorprende tutti la prima volta. Con il frame predefinito che finisce alla riga corrente, restituisce il valore corrente, non l'ultimo della partizione. Cambia il frame.
• Le funzioni finestra richiedono SQLite 3.25+ (uscito nel 2018). Qualsiasi installazione ragionevolmente moderna le ha, ma alcuni ambienti embedded restano indietro.

Prossimo passo: colonne generate

Le funzioni finestra sono un calcolo al momento della query. La prossima pagina parla del calcolo al momento della memorizzazione: le colonne generate, in cui il valore della colonna è definito da un'espressione e si aggiorna automaticamente quando cambiano i dati sottostanti.