sql-advanced-analytics

SQL Avancé pour l'Analytique

Workflow

1. Identifier le besoin : classement, comparaison temporelle, agrégation cumulative, hiérarchie, pivot.
2. Choisir la construction :
   • Classement/comparaison dans une partition → window function
   • Hiérarchie ou récursion → CTE récursive
   • Rotation lignes/colonnes → PIVOT / CASE WHEN
   • Calcul réutilisé plusieurs fois → CTE non-récursive ou vue matérialisée
3. Écrire la requête : CTEs avant la requête principale, window functions dans le SELECT.
4. Valider le plan d'exécution : EXPLAIN ANALYZE (PG) / SET STATISTICS IO ON (SQL Server).
5. Optimiser : index couvrants, matérialisation, partitionnement.

---

Window Functions

Critères de choix

Besoin	Fonction
Rang sans ex-aequo	ROW_NUMBER()
Rang avec ex-aequo, sauts	RANK()
Rang avec ex-aequo, continu	DENSE_RANK()
Découper en N groupes égaux	NTILE(N)
Valeur N lignes avant/après	LAG(col, N) / LEAD(col, N)
Cumul depuis le début	SUM() OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)
Moyenne mobile	AVG() OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW)
Premier/dernier de la partition	FIRST_VALUE() / LAST_VALUE() + frame explicite

Classement dans une partition

SELECT
    product_name,
    category,
    total_sales,
    ROW_NUMBER()  OVER (PARTITION BY category ORDER BY total_sales DESC) AS rn,
    RANK()        OVER (PARTITION BY category ORDER BY total_sales DESC) AS rnk,
    DENSE_RANK()  OVER (PARTITION BY category ORDER BY total_sales DESC) AS dense_rnk,
    NTILE(4)      OVER (PARTITION BY category ORDER BY total_sales DESC) AS quartile
FROM products;

Comparaison temporelle et cumul

SELECT
    month,
    revenue,
    LAG(revenue, 1)  OVER (ORDER BY month)                                           AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month)                                  AS mom_delta,
    LEAD(revenue, 1) OVER (ORDER BY month)                                            AS next_month,
    AVG(revenue)     OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)  AS ma_3m,
    SUM(revenue)     OVER (ORDER BY month ROWS UNBOUNDED PRECEDING)                  AS cumul
FROM monthly_sales;