SQL Analytics — Cheat Sheet

Tag: sql, analytics, data warehouse, window functions, cte, aggregations

Livello: intermedio

TL;DR: Sintesi sulle tecniche avanzate di interrogazione dati, focalizzata su Window Functions, CTE e raggruppamenti complessi per analisi di business.

Concetti chiave

Le Window Functions permettono calcoli su un set di righe correlate alla riga corrente senza collassare il risultato.

La clausola OVER() definisce la finestra su cui applicare la funzione analitica.

PARTITION BY suddivide il result set in partizioni isolate per il calcolo della funzione.

ORDER BY all'interno di OVER() definisce l'ordine logico di valutazione delle righe.

Le Common Table Expressions (CTE) tramite WITH rendono le query complesse leggibili e modulari.

Le funzioni ROW_NUMBER(), RANK() e DENSE_RANK() assegnano un numero sequenziale o un rango.

Le funzioni LAG() e LEAD() accedono a dati di righe precedenti o successive senza self-join.

ROLLUP e CUBE estendono GROUP BY generando subtotali e totali generali.

Domande tipiche e risposte pronte

Q: Qual è la differenza tra RANK() e DENSE_RANK()?R: Entrambi assegnano il rango. In caso di parità RANK() salta i valori successivi (1, 1, 3), mentre DENSE_RANK() non lascia buchi (1, 1, 2).

Q: A cosa serve la funzione LAG()?R: La uso per confrontare il valore della riga corrente con quello di una riga precedente, molto utile per calcolare variazioni percentuali.

Q: Perché preferire una CTE a una subquery?R: Preferisco le CTE perché migliorano nettamente la leggibilità del codice, permettono di riutilizzare la logica più volte e ne facilitano il debug.

Q: Come calcoli una media mobile in SQL?R: Utilizzo una window function combinata con la clausola ROWS BETWEEN, definendo un frame temporale limitato ai periodi precedenti alla riga corrente.

Insidie e errori comuni

Dimenticare che le Window Functions vengono valutate dopo WHERE, GROUP BY e HAVING.

Usare ORDER BY in finestra senza frame esplicito: il default RANGE può dare risultati inattesi sui valori duplicati.

Confondere ROW_NUMBER() con RANK() nei report dove i record in pareggio devono avere lo stesso posizionamento.

Abusare di CTE troppo nidificate, che impattano il query planner e degradano drasticamente le performance.

Applicare window functions pesanti sull'intero dataset prima di filtrare i record, causando colli di bottiglia.

Snippet e comandiSQL

```-- Variazione percentuale rispetto al mese precedente

WITH MonthlySales AS (

SELECT

DATE_TRUNC('month', order_date) AS sales_month,

SUM(amount) AS total_sales

FROM orders

GROUP BY 1

)

SELECT

sales_month,

total_sales,

LAG(total_sales) OVER (ORDER BY sales_month) AS prev_sales,

(total_sales - LAG(total_sales) OVER (ORDER BY sales_month)) /

LAG(total_sales) OVER (ORDER BY sales_month) * 100 AS percent_change

FROM MonthlySales;

-- Rango senza salti partizionato

SELECT

employee_id,

department_id,

salary,

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank

FROM employees;

-- Media mobile a 3 periodi

SELECT

date,

daily_revenue,

AVG(daily_revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3d

FROM daily_metrics;

```

Da ripassare

[ ] Sintassi avanzata del Window Frame (ROWS vs RANGE)

[ ] Ottimizzazione con GROUPING SETS rispetto a CUBE

[ ] CTE ricorsive per l'interrogazione di strutture gerarchiche