# SQL per Data Analyst: Funzioni Finestra, CTE e Query Avanzate > Guida completa alle funzioni finestra SQL, CTE e pattern di query avanzate per l'analisi dei dati. ROW_NUMBER, RANK, LAG, LEAD, CTE ricorsive e tecniche gaps-and-islands. - Published: 2026-04-09 - Updated: 2026-04-09 - Author: SharpSkill - Tags: sql, data-analytics, window-functions, cte, interview - Reading time: 10 min --- Le funzioni finestra SQL, le CTE (Common Table Expressions) e i pattern di query avanzate rappresentano il nucleo fondamentale dell'SQL analitico. Che si tratti di prepararsi a un colloquio come data analyst o di affrontare report complessi, queste tecniche trasformano subquery verbose e difficili da leggere in SQL pulito, performante e manutenibile. > **Riferimento Rapido** > > Le funzioni finestra eseguono calcoli su un insieme di righe correlate alla riga corrente, senza comprimerle in un'unica riga di output come fa GROUP BY. Combinate con le CTE, rendono le query analitiche complesse leggibili e facili da mantenere. ## Funzioni Finestra SQL e la Clausola OVER Una funzione finestra applica un calcolo su una "finestra" definita di righe. La clausola OVER controlla quali righe rientrano nella finestra e come vengono ordinate. A differenza delle funzioni aggregate con GROUP BY, le funzioni finestra mantengono ogni singola riga nel set di risultati. La sintassi generale segue uno schema coerente su tutti i principali database: PostgreSQL, MySQL 8+, BigQuery, SQL Server e Snowflake. ```sql -- window_function_syntax.sql SELECT employee_id, department, salary, RANK() OVER ( PARTITION BY department ORDER BY salary DESC ) AS dept_salary_rank, SUM(salary) OVER ( PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_salary_total FROM employees; ``` PARTITION BY suddivide i dati in gruppi (in modo simile a GROUP BY, ma senza comprimere le righe). ORDER BY definisce la sequenza all'interno di ciascuna partizione. La clausola frame opzionale (ROWS BETWEEN ...) restringe la finestra a un intervallo specifico di righe. ## ROW_NUMBER, RANK e DENSE_RANK a Confronto Queste tre funzioni di classificazione appaiono simili, ma si comportano in modo diverso quando esistono valori duplicati. Scegliere quella sbagliata rappresenta una fonte frequente di bug nelle query analitiche. ```sql -- ranking_comparison.sql SELECT product_name, category, revenue, ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num, RANK() OVER (ORDER BY revenue DESC) AS rank_val, DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val FROM products; ``` | product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 | ROW_NUMBER assegna sempre un intero sequenziale univoco: anche in caso di parita, una riga riceve arbitrariamente il numero inferiore. RANK assegna lo stesso numero ai valori pari ma salta i numeri successivi (1, 1, 3). DENSE_RANK gestisce anch'essa le parita, ma non salta mai i numeri (1, 1, 2). Per le query top-N in cui i duplicati devono condividere la stessa posizione, DENSE_RANK rappresenta generalmente la scelta corretta. ## LAG e LEAD per l'Analisi Periodo su Periodo LAG e LEAD accedono ai dati delle righe precedenti o successive senza ricorrere a self-join. Queste funzioni sono essenziali per calcolare la crescita periodo su periodo, individuare tendenze e identificare variazioni significative nei dati. ```sql -- period_over_period.sql SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue, ROUND( (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0 / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0), 2 ) AS mom_growth_pct, LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue FROM monthly_sales ORDER BY month; ``` Il secondo argomento di LAG/LEAD specifica l'offset (il valore predefinito e 1). Un terzo argomento opzionale fornisce un valore di default quando non esiste una riga a quell'offset, risultando utile per evitare NULL nella prima o nell'ultima riga. NULLIF nel calcolo della crescita previene gli errori di divisione per zero. ## NTILE e Segmentazione in Percentili NTILE distribuisce le righe in un numero specificato di gruppi approssimativamente uguali. I data analyst la utilizzano per l'analisi dei quartili, lo scoring per decili e la segmentazione della clientela. ```sql -- customer_segmentation.sql SELECT customer_id, total_spend, NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile, NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile FROM ( SELECT customer_id, SUM(order_total) AS total_spend FROM orders WHERE order_date >= '2025-01-01' GROUP BY customer_id ) customer_totals; ``` Il quartile 1 contiene i clienti con la spesa piu elevata, il quartile 4 quelli con la spesa piu bassa. Questo pattern si mappa direttamente sulla segmentazione RFM (Recency, Frequency, Monetary), ampiamente utilizzata nell'analisi di marketing. ## Common Table Expressions: Sostituire le Subquery Nidificate Le CTE (clausole WITH) scompongono query complesse in passaggi denominati e leggibili. Ogni CTE funge da set di risultati temporaneo con un nome che esiste solo per la durata della query. Oltre alla leggibilita, le CTE facilitano il debug: ogni passaggio puo essere testato in modo indipendente. ```sql -- cte_sales_analysis.sql WITH monthly_revenue AS ( SELECT DATE_TRUNC('month', order_date) AS month, product_category, SUM(amount) AS revenue, COUNT(DISTINCT customer_id) AS unique_customers FROM orders WHERE order_date >= '2025-01-01' GROUP BY DATE_TRUNC('month', order_date), product_category ), ranked_categories AS ( SELECT month, product_category, revenue, unique_customers, RANK() OVER ( PARTITION BY month ORDER BY revenue DESC ) AS category_rank FROM monthly_revenue ) SELECT month, product_category, revenue, unique_customers, category_rank FROM ranked_categories WHERE category_rank <= 3 ORDER BY month, category_rank; ``` ## CTE Ricorsive per Dati Gerarchici Le CTE ricorsive risolvono problemi che coinvolgono dati gerarchici o strutture a grafo: organigrammi aziendali, alberi di categorie, distinte base e query di ricerca percorsi. ```sql -- recursive_org_chart.sql WITH RECURSIVE org_hierarchy AS ( SELECT employee_id, employee_name, manager_id, 1 AS depth, employee_name AS management_chain FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, oh.depth + 1, oh.management_chain || ' > ' || e.employee_name FROM employees e INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id ) SELECT employee_id, employee_name, depth, management_chain FROM org_hierarchy ORDER BY management_chain; ``` > **Prestazioni delle CTE Ricorsive** > > Le CTE ricorsive possono risultare lente su dataset di grandi dimensioni. Occorre sempre includere un limite di profondita (WHERE depth < 10) e assicurarsi che la colonna di join (manager_id) sia indicizzata. Per gerarchie molto profonde, conviene valutare pattern alternativi come il materialized path o il nested set. ## Pattern Analitici Avanzati: Gaps, Islands e Totali Progressivi Il pattern gaps-and-islands identifica sequenze consecutive nei dati: periodi di abbonamento attivo, giorni di login consecutivi o cicli di produzione ininterrotti. Si tratta di una tecnica fondamentale per l'analisi delle serie temporali. ```sql -- consecutive_login_streaks.sql WITH login_days AS ( SELECT DISTINCT user_id, DATE(login_timestamp) AS login_date FROM user_sessions ), streaks AS ( SELECT user_id, login_date, login_date - (ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ))::int AS streak_group FROM login_days ) SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS streak_length FROM streaks GROUP BY user_id, streak_group HAVING COUNT(*) >= 3 ORDER BY streak_length DESC; ``` L'idea alla base di questo pattern e elegante nella sua semplicita: sottraendo ROW_NUMBER dalla data, le date consecutive producono lo stesso valore di gruppo. Qualsiasi interruzione nella sequenza genera un nuovo gruppo, permettendo di identificare e misurare ciascuna serie consecutiva. ## Combinare Funzioni Finestra con CASE per Analisi Condizionali Le funzioni finestra possono essere combinate con espressioni CASE per creare metriche analitiche sofisticate: medie mobili, totali cumulativi per trimestre e rilevamento automatico dei valori anomali. ```sql -- conditional_analytics.sql SELECT order_date, product_category, amount, AVG(amount) OVER ( PARTITION BY product_category ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS moving_avg_7d, SUM(amount) OVER ( PARTITION BY product_category, DATE_TRUNC('quarter', order_date) ORDER BY order_date ) AS qtd_cumulative, CASE WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category) THEN 'OUTLIER' ELSE 'NORMAL' END AS outlier_flag FROM daily_sales ORDER BY product_category, order_date; ``` Questa query combina tre tecniche in una singola istruzione: la media mobile a 7 giorni per individuare tendenze, il totale cumulativo quarter-to-date per monitorare gli obiettivi e un flag per i valori anomali basato sul confronto con la media della categoria. Questo tipo di analisi multidimensionale rappresenta un requisito comune nei colloqui per posizioni di data analyst. > **Ordine di Esecuzione delle Funzioni Finestra** > > Le funzioni finestra vengono eseguite dopo WHERE, GROUP BY e HAVING, ma prima di ORDER BY e LIMIT. Questo significa che non possono essere utilizzate direttamente nelle clausole WHERE. Per filtrare in base al risultato di una funzione finestra, occorre racchiudere la query in una CTE o in una subquery. ## Ottimizzazione delle Prestazioni per Query Analitiche Quando piu funzioni finestra condividono la stessa definizione di partizione e ordinamento, la clausola WINDOW permette di definirla una sola volta e riutilizzarla. Oltre a ridurre la duplicazione del codice, questo approccio puo aiutare l'ottimizzatore del database a riconoscere partizioni identiche e migliorare il piano di esecuzione. ```sql -- named_window.sql SELECT department, employee_id, salary, RANK() OVER dept_window AS dept_rank, SUM(salary) OVER dept_window AS dept_total, AVG(salary) OVER dept_window AS dept_avg FROM employees WINDOW dept_window AS ( PARTITION BY department ORDER BY salary DESC ); ``` Alcuni accorgimenti aggiuntivi per le prestazioni: filtrare i dati nelle clausole WHERE prima che le funzioni finestra vengano valutate, creare indici sulle colonne utilizzate in PARTITION BY e ORDER BY, e considerare la materializzazione dei risultati intermedi quando la stessa finestra viene utilizzata in piu query successive. ## Conclusione - Le funzioni finestra (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) operano su insiemi di righe senza comprimerle, a differenza degli aggregati con GROUP BY - Le CTE scompongono query complesse in passaggi denominati e testabili, sostituendo le subquery profondamente nidificate - Le CTE ricorsive gestiscono dati gerarchici come organigrammi e alberi di categorie, ma richiedono limiti di profondita e colonne di join indicizzate - La tecnica gaps-and-islands (ROW_NUMBER + aritmetica sulle date) identifica sequenze consecutive nei dati di serie temporali - Le finestre denominate (clausola WINDOW) riducono la duplicazione del codice e possono migliorare l'ottimizzazione del piano di query - Filtrare i dati nelle clausole WHERE prima della valutazione delle funzioni finestra per mantenere le prestazioni su tabelle di grandi dimensioni - Questi pattern compaiono frequentemente nei [colloqui di data analytics](/technologies/data-analytics) e si applicano direttamente al reporting quotidiano, alla segmentazione e all'analisi delle tendenze --- Source: SharpSkill (https://sharpskill.dev), tech interview preparation for your real stack. HTML version of this page: https://sharpskill.dev/it/blog/data-analytics/sql-window-functions-ctes-advanced-queries