Errori tipici nell'uso delle funzioni window

È arrivato il momento di parlare delle difficoltà che possono saltare fuori quando si lavora con le funzioni window. Come spesso succede nella programmazione (e nella vita), è meglio imparare dagli errori degli altri. Vediamo insieme gli errori più comuni che fanno i principianti (e a volte pure i dev più esperti) e impariamo a evitarli.

Errore n°1: Uso sbagliato di PARTITION BY

Uno degli errori più frequenti è dimenticarsi o impostare male il parametro PARTITION BY, soprattutto se vuoi dividere i dati in gruppi. Senza di lui, PostgreSQL considera tutte le righe come un unico grande gruppo, e i risultati potrebbero essere molto diversi da quello che ti aspetti.

Supponiamo di avere una tabella sales che contiene dati sulle vendite:

Vuoi trovare la somma cumulativa (SUM()) delle vendite per mese per ogni regione. Puoi scrivere una query così:

SELECT
    region,
    month,
    SUM(total) OVER (ORDER BY month) AS running_total
FROM 
    sales;

Risultato:

A prima vista sembra tutto ok. Ma il risultato chiaramente non è quello che ci si aspetta, perché la somma cumulativa viene calcolata su tutte le righe, non per regione. Il problema è che ci siamo dimenticati di aggiungere PARTITION BY region.

Codice corretto:

SELECT 
    region,
    month,
    SUM(total) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM 
    sales;

Risultato:

Ora tutto funziona come si deve: i dati sono raggruppati per regione e la somma cumulativa viene calcolata separatamente per ogni regione.

Errore n°2: Ordine sbagliato in ORDER BY

ORDER BY dentro OVER() controlla l'ordine delle righe nella finestra. Se l'ordine è sbagliato, i risultati saranno strani.

Vuoi trovare le somme cumulative delle vendite ordinate in ordine decrescente di mese. Puoi scrivere una query così:

SELECT
    month,
    total,
    SUM(total) OVER (ORDER BY month DESC) AS running_total
FROM 
    sales;

Risultato:

A prima vista sembra tutto giusto, ma occhio: le righe sono raggruppate per mese, ma la somma non viene calcolata correttamente a causa dell'ordine decrescente. Ecco perché i risultati sono confusi.

Correzione: riscrivi la query usando l'ordine giusto in ORDER BY:

SELECT
    month,
    total,
    SUM(total) OVER (ORDER BY month ASC) AS running_total
FROM 
    sales;

Errore n°3: Uso delle funzioni window senza indici

Le funzioni window spesso lavorano con grandi quantità di dati, e la mancanza di indici sulle colonne chiave può portare a performance disastrose.

Esempio: abbiamo una tabella large_sales con milioni di righe e vogliamo calcolare i rank delle vendite:

SELECT
    id,
    total,
    RANK() OVER (ORDER BY total DESC) AS rank
FROM 
    large_sales;

Su pochi dati la query va veloce, ma su grandi volumi può metterci una vita.

Correzione: aggiungi un indice sulla colonna usata in ORDER BY:

CREATE INDEX idx_total ON large_sales(total DESC);

Ora la query andrà molto più veloce.

Errore n°4: Non capire la finestra definita da ROWS o RANGE

Quando usi ROWS e RANGE è importante capire come calcolano la finestra delle righe. Capire male queste parole chiave può portare a risultati inaspettati.

Esempio: vuoi calcolare la media mobile delle vendite per il mese corrente e i due precedenti:

SELECT
    month,
    AVG(total) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

Se invece di ROWS usi RANGE:

SELECT
    month,
    AVG(total) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

Il risultato sarà diverso, perché RANGE lavora sui range di valori, non su un numero preciso di righe.

Errore n°5: Uso eccessivo delle funzioni window

Usare più funzioni window nella stessa query può causare calcoli duplicati e rallentare tutto.

Esempio:

SELECT 
    id,
    total,
    SUM(total) OVER (PARTITION BY region) AS region_total,
    SUM(total) OVER (PARTITION BY region) / COUNT(total) OVER (PARTITION BY region) AS region_avg
FROM 
    sales;

Qui SUM(total) e COUNT(total) vengono calcolati più volte per ogni riga.

Correzione: accorcia la query usando subquery o CTE:

WITH cte_region_totals AS (
    SELECT 
        region,
        SUM(total) AS region_total,
        COUNT(total) AS region_count
    FROM 
        sales
    GROUP BY 
        region
)
SELECT 
    s.id,
    s.total,
    t.region_total,
    t.region_total / t.region_count AS region_avg
FROM 
    sales s
JOIN 
    cte_region_totals t ON s.region = t.region;

Consigli per evitare errori

Controlla PARTITION BY e ORDER BY: assicurati sempre che la finestra sia impostata correttamente.

Indicizza i dati: soprattutto se usi ordinamenti (ORDER BY) o filtri.

Usa i CTE per calcoli ripetuti: così eviti di ripetere gli stessi passaggi.

Guarda il piano di esecuzione: usa EXPLAIN e EXPLAIN ANALYZE per capire come PostgreSQL esegue la query.

Testa sui dati reali: assicurati che i risultati siano quelli che ti aspetti e che il problema sia risolto davvero.