Ora sei pronto per tuffarti nel mondo degli esempi pratici e vedere come tutto questo funziona su problemi reali!
Esempio: calcolo del rank delle vendite per regione
Immagina di avere una tabella sales che contiene i dati delle vendite in diverse regioni. Dobbiamo determinare il rank delle vendite per ogni regione.
| id | region | sales_amount |
|---|---|---|
| 1 | North | 5000 |
| 2 | North | 3000 |
| 3 | North | 7000 |
| 4 | South | 2000 |
| 5 | South | 4000 |
| 6 | East | 8000 |
| 7 | East | 6000 |
Obiettivo: trovare il rank (RANK) delle vendite per ogni regione
SELECT
region,
sales_amount,
RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM
sales;
Risultato:
| region | sales_amount | sales_rank |
|---|---|---|
| North | 7000 | 1 |
| North | 5000 | 2 |
| North | 3000 | 3 |
| South | 4000 | 1 |
| South | 2000 | 2 |
| East | 8000 | 1 |
| East | 6000 | 2 |
Nota che abbiamo usato PARTITION BY region per calcolare i rank separatamente per ogni regione. Se non avessimo usato PARTITION BY, il rank sarebbe stato calcolato globalmente su tutta la tabella.
Esempio: calcolo della somma cumulativa del reddito
Adesso lavoriamo sulla tabella transactions per calcolare la somma cumulativa del reddito per ogni cliente.
| id | customer_id | purchase_date | amount |
|---|---|---|---|
| 1 | 101 | 2023-01-01 | 100 |
| 2 | 101 | 2023-01-03 | 50 |
| 3 | 102 | 2023-01-02 | 200 |
| 4 | 101 | 2023-01-05 | 150 |
| 5 | 102 | 2023-01-04 | 100 |
Obiettivo: calcolare la somma cumulativa per ogni cliente
SELECT
customer_id,
purchase_date,
amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS cumulative_sum
FROM
transactions;
Risultato:
| customer_id | purchase_date | amount | cumulative_sum |
|---|---|---|---|
| 101 | 2023-01-01 | 100 | 100 |
| 101 | 2023-01-03 | 50 | 150 |
| 101 | 2023-01-05 | 150 | 300 |
| 102 | 2023-01-02 | 200 | 200 |
| 102 | 2023-01-04 | 100 | 300 |
Qui il punto chiave è l'uso di ORDER BY purchase_date dentro OVER(), così la somma cumulativa viene calcolata in ordine cronologico.
Esempio: suddivisione dei dati in quantili
Immagina di avere una tabella students con i nomi e i risultati dei test. Vogliamo dividere gli studenti in 4 gruppi in base ai loro risultati.
| id | name | test_score |
|---|---|---|
| 1 | Alice | 85 |
| 2 | Bob | 95 |
| 3 | Charlie | 75 |
| 4 | Diana | 88 |
| 5 | Edward | 65 |
| 6 | Fiona | 70 |
Obiettivo: dividere gli studenti in 4 gruppi usando NTILE()
SELECT
name,
test_score,
NTILE(4) OVER (ORDER BY test_score DESC) AS quartile
FROM
students;
Il risultato della query sarà questo:
| name | test_score | quartile |
|---|---|---|
| Bob | 95 | 1 |
| Diana | 88 | 1 |
| Alice | 85 | 2 |
| Charlie | 75 | 3 |
| Fiona | 70 | 3 |
| Edward | 65 | 4 |
NTILE(4) divide i dati in 4 gruppi. Gli studenti con i punteggi più alti finiscono nel primo gruppo, quelli con i punteggi più bassi nell'ultimo.
Esempio: analisi dei dati temporali
Nella tabella site_visits ci sono i dati sul numero di visite al sito per giorno. Dobbiamo calcolare la differenza nel numero di visite tra i giorni per ogni sito.
| site_id | visit_date | visits |
|---|---|---|
| 1 | 2023-01-01 | 100 |
| 1 | 2023-01-02 | 120 |
| 1 | 2023-01-03 | 110 |
| 2 | 2023-01-01 | 50 |
| 2 | 2023-01-02 | 60 |
| 2 | 2023-01-03 | 70 |
Il nostro obiettivo è calcolare la differenza delle visite tra i giorni
SELECT
site_id,
visit_date,
visits,
visits - LAG(visits) OVER (PARTITION BY site_id ORDER BY visit_date) AS visit_diff
FROM
site_visits;
Risultato:
| site_id | visit_date | visits | visit_diff |
|---|---|---|---|
| 1 | 2023-01-01 | 100 | NULL |
| 1 | 2023-01-02 | 120 | 20 |
| 1 | 2023-01-03 | 110 | -10 |
| 2 | 2023-01-01 | 50 | NULL |
| 2 | 2023-01-02 | 60 | 10 |
| 2 | 2023-01-03 | 70 | 10 |
La funzione LAG() ti permette di prendere il valore dalla riga precedente. Se non ci sono dati per la riga precedente, il risultato sarà NULL. Ne saprai di più nelle prossime lezioni :P
GO TO FULL VERSION