SQL avancé et modélisation

Section 12.3.3 : SQL avancé et modélisation

🎯 Objectif pédagogique

Maîtriser les techniques SQL avancées pour la modélisation de données : CTEs récursifs, window functions, et modélisation dimensionnelle pour l'analytics.

Window Functions : l'arme secrète du SQL

Les window functions permettent des calculs sur des groupes de lignes sans les agréger :

-- Classement des clients par CA
SELECT 
    client,
    ca_total,
    RANK() OVER (ORDER BY ca_total DESC) AS rang,
    PERCENT_RANK() OVER (ORDER BY ca_total DESC) AS percentile
FROM (
    SELECT client, SUM(montant) AS ca_total
    FROM commandes
    GROUP BY client
);

-- Moving Average (moyenne glissante 7 jours)
SELECT 
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS avg_7_jours
FROM daily_revenue;

-- Comparaison mois/mois
SELECT 
    mois,
    revenue,
    LAG(revenue) OVER (ORDER BY mois) AS revenue_mois_precedent,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY mois)) / 
          LAG(revenue) OVER (ORDER BY mois) * 100, 1) AS croissance_pct
FROM monthly_revenue;

Modélisation dimensionnelle (Star Schema)

Pour l'analytics, on organise les données en star schema :

• →Table de faits (fact_orders) : les événements mesurables (montant, quantité)
• →Tables de dimensions (dim_date, dim_customer, dim_product) : le contexte descriptif (qui, quoi, quand)