Data 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

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;

FIRST_VALUE / LAST_VALUE — frame obligatoire

-- Sans "ROWS UNBOUNDED FOLLOWING", LAST_VALUE retourne la ligne courante, pas la dernière !
SELECT DISTINCT
    customer_id,
    FIRST_VALUE(product_name) OVER w AS first_purchase,
    LAST_VALUE(product_name)  OVER w AS last_purchase
FROM orders
WINDOW w AS (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

Dédoublonnage par partition (pattern courant)

-- Garder la ligne la plus récente par customer_id
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

CTEs

CTE non-récursive — clarifier les étapes

WITH
revenue_by_month AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
),
ranked AS (
    SELECT *, RANK() OVER (ORDER BY revenue DESC) AS rnk
    FROM revenue_by_month
)
SELECT * FROM ranked WHERE rnk <= 5;

CTE récursive — hiérarchie

WITH RECURSIVE org_tree AS (
    -- Ancre : nœud racine
    SELECT id, name, manager_id, 1 AS depth, CAST(name AS TEXT) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Récursion
    SELECT e.id, e.name, e.manager_id, ot.depth + 1, ot.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
    WHERE ot.depth < 10   -- garde-fou anti-cycle
)
SELECT * FROM org_tree ORDER BY path;

Pivot / Unpivot

CASE WHEN (portable sur tous les SGBD)

SELECT
    product_name,
    SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 1  THEN amount ELSE 0 END) AS jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 2  THEN amount ELSE 0 END) AS feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 3  THEN amount ELSE 0 END) AS mar
FROM sales
GROUP BY product_name;

PIVOT natif SQL Server

SELECT product_name, [1] AS jan, [2] AS feb, [3] AS mar
FROM (
    SELECT product_name, MONTH(sale_date) AS m, amount FROM sales
) src
PIVOT (SUM(amount) FOR m IN ([1],[2],[3])) pvt;

Unpivot — colonnes → lignes

SELECT product_name, month_name, revenue
FROM monthly_pivot
UNPIVOT (revenue FOR month_name IN (jan, feb, mar)) u;

Optimisation

Lire le plan d'exécution

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <votre requête>;

-- SQL Server
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- puis exécuter la requête, lire "logical reads"

Signaux d'alarme : Hash Join sur très grandes tables, Sort sur colonnes non indexées, Nested Loop avec Index Scan répété.

Index couvrants pour l'analytique

-- Couvre PARTITION BY + ORDER BY + colonnes SELECT
CREATE INDEX idx_sales_analytics
ON sales (category, sale_date)
INCLUDE (amount, product_id);

-- Index partiel pour réduire la taille
CREATE INDEX idx_active_orders
ON orders (customer_id, order_date)
WHERE status = 'active';

Matérialisation

-- PostgreSQL : vue matérialisée + refresh
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
    FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

-- SQL Server : indexed view (requiert WITH SCHEMABINDING)
CREATE VIEW dbo.vw_monthly_revenue WITH SCHEMABINDING AS
    SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month,
           SUM(amount) AS revenue, COUNT_BIG(*) AS cnt
    FROM dbo.orders GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1);

CREATE UNIQUE CLUSTERED INDEX ix_mv ON dbo.vw_monthly_revenue (month);

Garde-fous / Anti-patterns

Bonnes pratiques 2026

• Nommer les fenêtres avec WINDOW w AS (...) quand la même clause est réutilisée (lisibilité + plan partagé sur PostgreSQL 16+).
• Documenter chaque CTE avec un commentaire d'une ligne sur son rôle.
• Éviter les CTEs pour remplacer des index : une CTE n'est pas automatiquement matérialisée (SQL Server l'inline souvent) ; préférer une table temporaire si la CTE est jointurée plusieurs fois.
• Tester sur un sous-ensemble avant d'exécuter sur toute la table (WHERE order_date >= CURRENT_DATE - INTERVAL '7 days').
• Séparer les étapes : calcul → classement → filtrage, une CTE par étape.
• Sur SQL Server : utiliser ROWS plutôt que RANGE dans le frame — RANGE est plus lent car il gère les ex-aequo dynamiquement.

Communication Rules — MANDATORY

• Ultra-concise. No filler, no preamble, no pleasantries.
• Never say "happy to help", "sure!", "great question", "let me", or similar.
• Tool first, talk second. Act before explaining.
• Result first. Lead with outcome, not process.
• Stop when done. No summary, no recap, no trailing commentary.
• No politeness wrappers. Direct and blunt.
• Minimum words. If one word works, do not use ten.
• No unsolicited explanations.
• No emoji unless asked.