0Pricing
SQL Academy · Leçon

Écrire des requêtes analytiques

Découpez, ventilez et agrégez les indicateurs.

Écrire des requêtes analytiques est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Que sont les requêtes analytiques ?

Les requêtes analytiques vont au-delà des simples recherches de lignes. Au lieu de demander quelle commande le client 42 a-t-il passée ?, elles demandent quel est le chiffre d’affaires total par région et par trimestre ? ou comment ce mois-ci se compare-t-il au mois dernier ?

Dans un entrepôt de données fondé sur un schéma en étoile, les requêtes analytiques réalisent un découpage (filtrer une dimension), une sélection multidimensionnelle (filtrer plusieurs dimensions) et une remontée (agréger à une granularité plus élevée) des faits afin de faire émerger des informations utiles pour l’entreprise.

Rappel sur le schéma en étoile

Un schéma en étoile possède une table de faits centrale (par ex. fact_sales) entourée de tables de dimensions (par ex. dim_date, dim_product, dim_store). Les requêtes analytiques effectuent une jointure entre la table de faits et les dimensions nécessaires à l’analyse en cours.

SELECT
    s.store_name,
    d.year,
    d.quarter,
    SUM(f.revenue)   AS total_revenue,
    SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store  s ON s.store_id  = f.store_id
JOIN dim_date   d ON d.date_id   = f.date_id
GROUP BY
    s.store_name,
    d.year,
    d.quarter
ORDER BY
    d.year,
    d.quarter,
    s.store_name;

Découpage : filtrer une dimension

Le découpage consiste à limiter l’ensemble de résultats à une seule valeur d’une dimension — par exemple, à examiner uniquement les données de l’année 2024. La clause WHERE est votre outil de découpage.

En découpant tôt, vous réduisez le nombre de lignes que la base de données doit agréger, ce qui permet de conserver des requêtes rapides sur les grandes tables de faits.

-- Slice: only year 2024
SELECT
    p.category,
    SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;

Sélection multidimensionnelle : filtrer plusieurs dimensions

La sélection multidimensionnelle consiste à appliquer simultanément des filtres sur au moins deux dimensions — par exemple, à examiner les ventes de produits électroniques dans la région Nord au premier trimestre. Chaque condition WHERE supplémentaire délimite un cube de données plus petit.

-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
    d.month,
    SUM(f.revenue)    AS revenue,
    SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE
    p.category  = 'Electronics'
    AND s.region = 'North'
    AND d.year   = 2024
    AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;

Remontée : agréger vers une granularité supérieure

La remontée consiste à passer d’une granularité détaillée (ventes quotidiennes par magasin) à une granularité plus élevée (ventes mensuelles par région). Vous y parvenez en supprimant les colonnes de niveau inférieur de GROUP BY et en effectuant une nouvelle agrégation.

Le modificateur ROLLUP permet de produire les sous-totaux et les totaux généraux dans une seule requête au lieu d’écrire plusieurs blocs UNION ALL.

-- Roll up from store/month to region/quarter with subtotals
SELECT
    s.region,
    d.quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date  d ON d.date_id  = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;

Comparaisons entre périodes avec LAG

L’un des modèles analytiques les plus courants consiste à comparer un indicateur à ce même indicateur sur une période précédente. La fonction de fenêtre LAG() permet d’insérer directement la valeur de la ligne précédente dans la ligne actuelle, sans jointure de la table avec elle-même.

Nous calculons ici la croissance du chiffre d’affaires d’un mois sur l’autre, en pourcentage.

WITH monthly AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
             / NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
    2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;

Totaux cumulés avec SUM OVER

Un total cumulé additionne la valeur de chaque ligne au total de toutes les lignes précédentes selon un ordre défini. C’est idéal pour suivre le chiffre d’affaires cumulé au cours d’une année ou surveiller la consommation progressive d’un budget.

La clause de cadre ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW rend la fenêtre explicite et sans ambiguïté.

SELECT
    d.year,
    d.month,
    SUM(f.revenue)                                      AS monthly_revenue,
    SUM(SUM(f.revenue)) OVER (
        PARTITION BY d.year
        ORDER BY d.month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                   AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

Classer les dimensions avec DENSE_RANK

Le classement permet de trouver les meilleurs ou les moins bons résultats au sein d’un groupe. DENSE_RANK() attribue des rangs consécutifs sans trous en cas d’égalité, ce qui en fait le choix privilégié pour les classements des rapports de BI.

Encapsuler le résultat classé dans un CTE et filtrer sur le rang rend le modèle des N premiers clair et lisible.

WITH ranked_products AS (
    SELECT
        p.product_name,
        p.category,
        SUM(f.revenue) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.revenue) DESC
        ) AS rnk
    FROM fact_sales f
    JOIN dim_product p ON p.product_id = f.product_id
    JOIN dim_date   d ON d.date_id     = f.date_id
    WHERE d.year = 2024
    GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;

Pourcentage de contribution avec SUM fenêtré

Connaître le chiffre d’affaires absolu d’un produit est utile, mais savoir qu’il représente 38 % du chiffre d’affaires de sa catégorie est plus exploitable. Un SUM() fenêtré sur toute la partition fournit le dénominateur sans jointure avec une sous-requête.

SELECT
    p.category,
    p.product_name,
    SUM(f.revenue)                               AS product_revenue,
    SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
    ROUND(
        100.0 * SUM(f.revenue)
             / SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
    1)                                           AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id     = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;

Moyennes mobiles pour lisser les tendances

Les chiffres de vente quotidiens ou hebdomadaires sont fluctuants. Une moyenne mobile lisse les variations à court terme afin de faire apparaître la tendance sous-jacente. Ici, une moyenne mobile sur trois mois est calculée à l’aide d’un cadre de fenêtre glissante.

WITH monthly_rev AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
    2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;

CUBE pour toutes les combinaisons de dimensions

CUBE étend ROLLUP en calculant les sous-totaux pour toutes les combinaisons possibles des dimensions listées, et pas seulement le chemin de remontée hiérarchique. Cela produit le récapitulatif complet entre dimensions en un seul passage — utile pour les tableaux de bord multidimensionnels où les utilisateurs peuvent changer librement d’axe.

NULL dans une colonne de regroupement signifie toutes les valeurs de cette dimension — utilisez GROUPING() pour distinguer les NULL intentionnels dans les données des NULL issus de la remontée.

SELECT
    CASE WHEN GROUPING(s.region)   = 1 THEN 'ALL REGIONS'    ELSE s.region        END AS region,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category      END AS category,
    CASE WHEN GROUPING(d.quarter)  = 1 THEN 'ALL QUARTERS'   ELSE d.quarter::TEXT END AS quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;

Quelle opération limite les résultats à une seule valeur de dimension ?

Évaluez votre compréhension de la terminologie des requêtes analytiques utilisée dans les entrepôts de données.

Récapitulatif : écrire des requêtes analytiques

Dans cette leçon, vous avez étudié les principaux modèles permettant d’écrire des requêtes analytiques sur un schéma en étoile :

  • Découpage — filtrer une dimension avec WHERE pour se concentrer sur un segment précis.
  • Sélection multidimensionnelle — filtrer plusieurs dimensions simultanément pour délimiter un cube de données précis.
  • Remontée — agréger à une granularité plus élevée ; utiliser ROLLUP ou CUBE pour les sous-totaux à plusieurs niveaux.
  • LAG / LEAD — comparaisons entre périodes sans jointures de la table avec elle-même.
  • Totaux cumulés & moyennes mobiles — indicateurs cumulés et lissés grâce aux cadres de fenêtres.
  • DENSE_RANK — classements clairs des N premiers au sein des partitions.
  • Pourcentage de contribution — SUM fenêtré utilisé comme dénominateur pour calculer des parts.

Combiner ces modèles couvre la grande majorité des besoins en BI et en création de rapports que vous rencontrerez dans les entrepôts de données en production.

Questions Fréquemment Posées

La leçon « Écrire des requêtes analytiques » est-elle gratuite ?

Oui — le texte complet de « Écrire des requêtes analytiques » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Écrire des requêtes analytiques » ?

Découpez, ventilez et agrégez les indicateurs. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « Écrire des requêtes analytiques » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. OLTP ou OLAP
  2. Tables de faits et de dimensions
  3. Schémas en étoile et en flocon
  4. Écrire des requêtes analytiques
← Retour à SQL Academy