Funzioni window principali per l’analisi

Prima di tutto, immagina di lavorare con una tabella di migliaia di righe di vendite. Il tuo compito: capire chi è il venditore numero uno in ogni categoria, chi è il secondo, e così via. Oppure, magari vuoi semplicemente numerare tutte le righe della query per tracciare l’ordine. Tutto questo si fa easy con le funzioni window.

Le funzioni window sono funzioni SQL che lavorano su un sottoinsieme di righe (chiamiamolo "finestra") di un dataset. A differenza delle funzioni aggregate, che comprimono le righe in una sola (tipo SUM() o AVG()), le funzioni window lasciano le righe come sono, aggiungendo i valori calcolati accanto.

La differenza rispetto alle funzioni aggregate

Le funzioni aggregate "schiacciano" i dati, raggruppando le righe:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Risultato: solo poche righe, una per ogni dipartimento.

Confronta con una funzione window — qui le righe restano tutte, ma si aggiunge una nuova colonna, tipo ROW_NUMBER():

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

Qui ottieni tutte le stesse righe, ma con una colonna extra rank_within_department, dove a ogni dipendente viene assegnato un numero all’interno del suo dipartimento.

Funzioni window principali

Sintassi di OVER()

La parte più importante di ogni funzione window è la parola magica OVER(). Serve a definire con quale "finestra" di dati lavorerà la funzione. Dentro OVER() puoi specificare la divisione in gruppi (PARTITION BY) e/o l’ordine di ordinamento (ORDER BY).

Sintassi generale:

<funzione_window>() OVER (
   [PARTITION BY <gruppo>] 
   [ORDER BY <ordine>]
)

Componenti:

• PARTITION BY: Divide le righe in gruppi. Tipo, "dividi i dati per dipartimento".
• ORDER BY: Indica l’ordine di elaborazione delle righe. Tipo, "ordina i dipendenti per stipendio dal più alto al più basso".

Funzione ROW_NUMBER()

La funzione ROW_NUMBER() numerizza le righe, partendo da 1, dentro la "finestra" specificata. A volte è utile per creare semplicemente un numero di riga in una tabella temporanea o per capire la posizione di una riga.

Esempio. Tabella sales (vendite):

Query:

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Risultato:

Come funziona:

1. I dati vengono divisi in gruppi per product_category.
2. Ogni gruppo viene ordinato per revenue (in ordine decrescente).
3. Le righe dentro ogni gruppo ricevono un numero progressivo.

Funzione RANK()

La funzione RANK() serve per classificare le righe. Diversamente da ROW_NUMBER(), tiene conto dei valori uguali e salta i numeri (rank) se ci sono dei pari merito.

Esempio:

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Risultato:

Funzione DENSE_RANK()

DENSE_RANK() è simile a RANK(), ma con una differenza: non salta i numeri di rank se ci sono valori uguali.

Esempio. Aggiungiamo una vendita con lo stesso revenue:

Query:

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Risultato:

Esempi d’uso: numerazione delle righe

Obiettivo: numerare tutti gli ordini nella tabella orders, ordinati per data.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Risultato: ottieni la lista degli ordini con la numerazione nell’ordine in cui sono stati fatti.

Esempi d’uso: top-3 venditori in ogni categoria

Obiettivo: trovare i tre migliori venditori in ogni categoria di prodotto.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

Esempi d’uso: trovare valori uguali

Obiettivo: capire se ci sono venditori con lo stesso revenue in ogni categoria.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Ora puoi vedere i rank dove i valori "si incollano" per i pari merito.