Sintassi OVER() e le sue caratteristiche chiave

OVER() è un'istruzione che dice a SQL su quale set di righe applicare una window function. Si può dire che è il modo in cui si definisce la "finestra" di dati su cui applicare la funzione. Immagina di avere una stanza piena di persone e vuoi contare quante persone ci sono su ogni metro quadrato di pavimento. OVER() ti indica su quale parte della stanza concentrare i tuoi sforzi. In altre parole, specifica su quale set di righe lavorerà la funzione.

L'operatore OVER() si usa solo con le window function per fare operazioni sulle righe di una o più tabelle, senza raggruppare i dati.

Sintassi:

window_function() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS/RANGE ...]
)

Dove:

• PARTITION BY — divide il set di dati in gruppi logici
• ORDER BY — imposta l'ordine delle righe dentro ogni gruppo
• ROWS/RANGE — specifica la dimensione della "finestra" (tipo, riga corrente + 1 successiva)

Esempio: OVER() senza parametri

Quando OVER() viene usato senza parametri aggiuntivi, significa che la funzione davanti a lui lavorerà su tutto il set di dati.

SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER () AS row_num -- ROW_NUMBER() verrà applicata a tutte le righe del risultato
FROM employees;

Cosa succede?

1. ROW_NUMBER() assegna un numero unico a ogni riga.
2. Dato che in OVER() non ci sono parametri, tutte le righe della tabella employees vengono trattate come un unico blocco.

Risultato:

Uso di PARTITION BY per definire i gruppi

Ok, ora immagina che ci interessa numerare i dipendenti non in tutta l'azienda, ma dentro ogni reparto. Qui entra in gioco PARTITION BY.

PARTITION BY dentro OVER() divide i dati in gruppi (o "sezioni"). Per ogni gruppo la funzione calcola il valore separatamente. Cioè, se ROW_NUMBER() fosse un cameriere, ricomincerebbe a numerare per ogni "tavolo" (sezione).

Esempio: usiamo PARTITION BY

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id) AS row_num
FROM employees;

Cosa succede?

1. I dati della tabella employees sono divisi in gruppi per valore di department_id.
2. In ogni gruppo alle righe viene assegnato un numero progressivo tramite ROW_NUMBER().

Risultato:

Uso di ORDER BY per impostare l'ordine

Adesso aggiungiamo un po' di struttura. Immagina che non ci basta numerare le righe, ma vogliamo farlo in un certo ordine, tipo partendo dallo stipendio più alto. Questo si fa con ORDER BY.

ORDER BY definisce l'ordine in cui le righe saranno processate dalla window function.

Esempio: usiamo ORDER BY dentro OVER()

SELECT
    department_id,
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

Cosa succede?

1. I dati sono divisi in gruppi (PARTITION BY department_id).
2. Dentro ogni gruppo le righe sono ordinate per stipendio decrescente (ORDER BY salary DESC).
3. Ad ogni riga viene assegnato un rank in base all'ordinamento.

Risultato:

Combinare le window function

SQL ti permette di usare più window function in una query, e ognuna può lavorare con le sue regole. È come se nella stessa stanza ci fosse musica e si contassero le persone — ogni processo è indipendente!

Esempio: più window function

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

Cosa succede?

1. ROW_NUMBER() numererà le righe in ogni gruppo in ordine di stipendio decrescente.
2. AVG() calcola la media degli stipendi in ogni gruppo.

Risultato:

Esempi dalla vita reale

Le window function con OVER() si usano in un sacco di casi reali. Ecco qualche esempio:

• Analisi delle vendite: ranking dei prodotti per numero di vendite in ogni categoria.
• Classifiche: posizione degli studenti in ogni gruppo in base alla media voti.
• Serie temporali: somma cumulativa delle vendite nel tempo.

Esempio di analisi delle vendite:

SELECT
    category_id,
    product_id,
    product_name,
    SUM(sales) OVER (PARTITION BY category_id ORDER BY sales DESC) AS cumulative_sales
FROM products;

Errori comuni con le window function

1. Mancanza di PARTITION BY

Se non usi PARTITION BY, la window function si applica a tutta la tabella. Questo può dare risultati strani, soprattutto se ti aspettavi una divisione in gruppi.

---

2. Tipi di dato sbagliati in ORDER BY

ORDER BY dentro una window function è sensibile ai tipi di dato. Se ordini per un campo data salvato come testo (VARCHAR), l'ordine potrebbe essere alfabetico e non cronologico.

3. Uso sbagliato di ROWS BETWEEN

Di default le window function lavorano con frame definiti da ROWS BETWEEN. Se non specifichi il frame, può essere usato il comportamento di RANGE, che si comporta diversamente e può restituire più righe di quanto ti aspetti.

4. Gestione sbagliata dei NULL

Le window function possono trattare i NULL in modo diverso. Per esempio, RANK() e DENSE_RANK() considerano NULL come un valore e gli danno un rank separato.

5. Uso di funzioni di aggregazione window invece delle normali

A volte si usano funzioni di aggregazione window (SUM() OVER(...)) dove basterebbero gli aggregati normali con GROUP BY, complicando la query e rallentando l'esecuzione.