Configurare il window frame con ROWS e RANGE

Quando usi le funzioni window, ti chiedi: "Quante righe dentro la finestra partecipano al calcolo del valore per la riga attuale?" La risposta dipende dal window frame.

Window frame — è l'intervallo di righe che viene usato per calcolare il risultato della funzione window. Questo intervallo viene costruito a partire dalla riga attuale, più eventuali condizioni aggiuntive specificate tramite ROWS o RANGE.

Un esempio semplice: calcolando una somma cumulativa, puoi specificare:

• Considera solo la riga attuale.
• Considera la riga attuale e tutte le righe sopra.
• Considera la riga attuale e un numero fisso di righe sopra/sotto.

Proprio ROWS e RANGE controllano quali righe finiranno nel window frame.

Uso di ROWS

ROWS definisce il window frame a livello di posizione fisica delle righe. Questo significa che conta le righe dall'alto verso il basso nel loro ordine, indipendentemente dai valori in quelle righe.

Sintassi

funzione_window OVER (
    ORDER BY colonna
    ROWS BETWEEN inizio AND fine
)

Espressioni chiave:

• CURRENT ROW — riga attuale.
• numero PRECEDING — un certo numero di righe sopra l'attuale.
• numero FOLLOWING — un certo numero di righe sotto l'attuale.
• UNBOUNDED PRECEDING — dall'inizio della finestra.
• UNBOUNDED FOLLOWING — fino alla fine della finestra.

Esempio: somma cumulativa per la riga attuale e le 2 precedenti

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

Spiegazione:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW significa: prendi la riga attuale e le due righe sopra di essa.
• La somma cumulativa verrà calcolata solo per queste tre righe.

Risultato:

Esempio: analisi "finestra mobile" con numero fisso di righe

Obiettivo: calcolare la media degli stipendi per la riga attuale e le due successive.

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

Risultato:

Uso di RANGE

RANGE costruisce il window frame in base ai valori, non alla posizione delle righe. Questo significa che le righe vengono incluse nel frame se i loro valori nella colonna ORDER BY rientrano nell'intervallo specificato.

Sintassi

funzione_window OVER (
    ORDER BY colonna
    RANGE BETWEEN inizio AND fine
)

Esempio: somma cumulativa su intervallo di valori

Obiettivo: calcolare la somma cumulativa per le righe dove lo stipendio differisce da quello attuale non più di 2000.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

Spiegazione:

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING significa: prendi le righe dove il valore salary è nell'intervallo ±2000 rispetto alla riga attuale.

Risultato:

Confronto tra ROWS e RANGE

• ROWS lavora con le righe reali e il loro numero. Non dipende dai valori.
• RANGE lavora con l'intervallo logico di valori, specificato per la colonna in ORDER BY.

Per confronto, ecco un esempio. Supponiamo di avere una tabella sales con questi dati:

Confrontiamo le query:

ROWS:

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

Risultato:

Qui ogni riga viene aggiunta alla somma man mano che compare realmente.

RANGE:

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

Risultato:

Qui le righe 1 e 2 sono unite, perché il loro amount = 100. RANGE tiene conto dei valori ripetuti nella colonna amount.

Esempi di problemi reali

1. Calcolo dell'incremento del reddito

Obiettivo: calcolare la variazione del reddito rispetto alla riga precedente.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. Confronto della riga attuale con la media del gruppo

Obiettivo: per ogni reparto calcolare la differenza tra lo stipendio del dipendente e la media del reparto.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

Errori nell'uso di ROWS e RANGE

Ordinamento delle righe (ORDER BY) non specificato correttamente: Se non specifichi l'ordinamento, PostgreSQL darà errore perché non può determinare la riga attuale.

Mischiare approcci ROWS e RANGE nello stesso problema: Scegli l'approccio in base ai tuoi dati. ROWS va bene per problemi con numero fisso di righe, mentre RANGE — per intervalli di valori.

Saltare i valori ripetuti in RANGE: Ricorda che RANGE considera tutti i valori ripetuti, il che può cambiare molto il risultato.