Requêtes SQL avancées pour l’analyse de données : CTE, fenêtres, pivot et optimisation

Ce tutoriel présente des techniques SQL avancées orientées analyse de données : CTE (Common Table Expressions), fonctions de fenêtre, pivot/dépivot, et optimisation. L’objectif est de produire des requêtes lisibles, correctes et performantes, avec des exemples concrets et des commandes exécutables.

Hypothèse : vous connaissez déjà SELECT, JOIN, GROUP BY, HAVING, ORDER BY et les agrégations de base.

1) Jeu de données d’exemple (schéma + données)

Les exemples utilisent un mini-modèle de vente. Vous pouvez l’adapter à votre SGBD (PostgreSQL, SQL Server, Oracle, MySQL 8+, SQLite). Certaines syntaxes (pivot) varient : elles seront indiquées.

1.1 Création des tables

CREATE TABLE clients (
  client_id      INTEGER PRIMARY KEY,
  nom            VARCHAR(100) NOT NULL,
  segment        VARCHAR(50)  NOT NULL,
  pays           VARCHAR(50)  NOT NULL
);

CREATE TABLE produits (
  produit_id     INTEGER PRIMARY KEY,
  libelle        VARCHAR(100) NOT NULL,
  categorie      VARCHAR(50)  NOT NULL,
  prix_catalogue NUMERIC(10,2) NOT NULL
);

CREATE TABLE commandes (
  commande_id    INTEGER PRIMARY KEY,
  client_id      INTEGER NOT NULL,
  date_commande  DATE NOT NULL,
  statut         VARCHAR(20) NOT NULL,
  CONSTRAINT fk_cmd_client FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

CREATE TABLE lignes_commande (
  commande_id    INTEGER NOT NULL,
  produit_id     INTEGER NOT NULL,
  quantite       INTEGER NOT NULL,
  prix_unitaire  NUMERIC(10,2) NOT NULL,
  remise        NUMERIC(5,2)  NOT NULL DEFAULT 0,  -- remise en %
  CONSTRAINT pk_ligne PRIMARY KEY (commande_id, produit_id),
  CONSTRAINT fk_ligne_cmd FOREIGN KEY (commande_id) REFERENCES commandes(commande_id),
  CONSTRAINT fk_ligne_prod FOREIGN KEY (produit_id) REFERENCES produits(produit_id)
);

1.2 Insertion de quelques données

INSERT INTO clients (client_id, nom, segment, pays) VALUES
(1, 'Aster', 'B2B', 'France'),
(2, 'Boreal', 'B2C', 'France'),
(3, 'Cobalt', 'B2B', 'Belgique'),
(4, 'Dune', 'B2C', 'Suisse');

INSERT INTO produits (produit_id, libelle, categorie, prix_catalogue) VALUES
(10, 'Clavier', 'Informatique', 40.00),
(11, 'Souris', 'Informatique', 25.00),
(12, 'Écran', 'Informatique', 180.00),
(20, 'Chaise', 'Mobilier', 90.00),
(21, 'Bureau', 'Mobilier', 220.00);

INSERT INTO commandes (commande_id, client_id, date_commande, statut) VALUES
(100, 1, DATE '2025-01-05', 'PAYEE'),
(101, 1, DATE '2025-02-10', 'PAYEE'),
(102, 2, DATE '2025-02-12', 'ANNULEE'),
(103, 3, DATE '2025-03-01', 'PAYEE'),
(104, 4, DATE '2025-03-15', 'PAYEE');

INSERT INTO lignes_commande (commande_id, produit_id, quantite, prix_unitaire, remise) VALUES
(100, 10, 2, 38.00, 0),
(100, 11, 1, 25.00, 10),
(101, 12, 1, 175.00, 0),
(101, 20, 4, 85.00, 5),
(102, 21, 1, 220.00, 0),
(103, 10, 5, 39.00, 0),
(103, 21, 2, 210.00, 0),
(104, 11, 2, 24.00, 0),
(104, 20, 1, 90.00, 0);

1.3 Mesure de base : chiffre d’affaires net

On définit souvent un montant net par ligne :

montant_brut = quantite * prix_unitaire
montant_net = montant_brut * (1 - remise/100)

SELECT
  lc.commande_id,
  lc.produit_id,
  lc.quantite,
  lc.prix_unitaire,
  lc.remise,
  (lc.quantite * lc.prix_unitaire) AS montant_brut,
  (lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0) AS montant_net
FROM lignes_commande lc;

2) CTE : structurer, réutiliser, clarifier

Les CTE (WITH ... AS (...)) servent à :

découper une requête complexe en étapes compréhensibles ;
éviter la duplication de sous-requêtes ;
améliorer la lisibilité et la maintenance ;
parfois aider l’optimiseur (mais pas toujours : cela dépend du SGBD).

2.1 CTE simple : base de faits “ventes”

On construit une table logique de faits de vente (en excluant les commandes annulées).

WITH ventes AS (
  SELECT
    c.commande_id,
    c.date_commande,
    c.client_id,
    cl.segment,
    cl.pays,
    p.categorie,
    lc.produit_id,
    lc.quantite,
    lc.prix_unitaire,
    lc.remise,
    (lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0) AS montant_net
  FROM commandes c
  JOIN clients cl ON cl.client_id = c.client_id
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  JOIN produits p ON p.produit_id = lc.produit_id
  WHERE c.statut = 'PAYEE'
)
SELECT
  categorie,
  COUNT(DISTINCT commande_id) AS nb_commandes,
  SUM(montant_net) AS ca_net
FROM ventes
GROUP BY categorie
ORDER BY ca_net DESC;

Pourquoi c’est utile :

ventes devient une source cohérente réutilisable pour plusieurs analyses.
Vous centralisez les règles métier (exclusion des annulées, calcul du net).

2.2 Plusieurs CTE : pipeline analytique

Exemple : calculer le CA mensuel par pays, puis classer les pays par CA.

WITH ventes AS (
  SELECT
    c.date_commande,
    cl.pays,
    (lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0) AS montant_net
  FROM commandes c
  JOIN clients cl ON cl.client_id = c.client_id
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  WHERE c.statut = 'PAYEE'
),
mensuel AS (
  SELECT
    DATE_TRUNC('month', date_commande) AS mois,
    pays,
    SUM(montant_net) AS ca_net
  FROM ventes
  GROUP BY 1, 2
),
classement AS (
  SELECT
    mois,
    pays,
    ca_net,
    DENSE_RANK() OVER (PARTITION BY mois ORDER BY ca_net DESC) AS rang_mois
  FROM mensuel
)
SELECT *
FROM classement
WHERE rang_mois <= 3
ORDER BY mois, rang_mois, pays;

DATE_TRUNC est typique de PostgreSQL. Sur d’autres SGBD, on utilisera une fonction équivalente (par exemple DATEFROMPARTS/DATETRUNC selon le moteur).

2.3 CTE récursif : hiérarchies et séries temporelles

Un CTE récursif sert à parcourir une hiérarchie (organigramme, catégories) ou à générer une série de dates.

2.3.1 Générer une série de mois (portable conceptuellement)

Exemple : produire tous les mois entre deux dates, puis joindre les ventes pour obtenir des mois à zéro.

WITH RECURSIVE mois AS (
  SELECT DATE '2025-01-01' AS debut_mois
  UNION ALL
  SELECT (debut_mois + INTERVAL '1 month')::date
  FROM mois
  WHERE debut_mois < DATE '2025-06-01'
),
ca_mensuel AS (
  SELECT
    DATE_TRUNC('month', c.date_commande)::date AS debut_mois,
    SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS ca_net
  FROM commandes c
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  WHERE c.statut = 'PAYEE'
  GROUP BY 1
)
SELECT
  m.debut_mois,
  COALESCE(cm.ca_net, 0) AS ca_net
FROM mois m
LEFT JOIN ca_mensuel cm ON cm.debut_mois = m.debut_mois
ORDER BY m.debut_mois;

Points d’attention :

La récursion doit avoir une condition d’arrêt sûre (WHERE debut_mois < ...).
Certains moteurs imposent une limite de récursion (configurable).

3) Fonctions de fenêtre : analyser sans “casser” les lignes

Les fonctions de fenêtre (... OVER (...)) permettent de calculer :

des rangs (ROW_NUMBER, RANK, DENSE_RANK) ;
des cumuls (SUM(...) OVER) ;
des moyennes mobiles ;
des comparaisons temporelles (LAG, LEAD) ;
des répartitions (NTILE) ; sans regrouper les lignes comme le ferait un GROUP BY.

3.1 Différence clé : agrégation vs fenêtre

GROUP BY réduit le nombre de lignes (une ligne par groupe).
Une fenêtre conserve les lignes et ajoute des colonnes calculées sur un “voisinage”.

Exemple : afficher chaque commande et le total client (sans perdre le détail commande).

SELECT
  c.commande_id,
  c.client_id,
  c.date_commande,
  SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS total_commande,
  SUM(SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)))
    OVER (PARTITION BY c.client_id) AS total_client
FROM commandes c
JOIN lignes_commande lc ON lc.commande_id = c.commande_id
WHERE c.statut = 'PAYEE'
GROUP BY c.commande_id, c.client_id, c.date_commande
ORDER BY c.client_id, c.date_commande;

Ici, on fait :

un GROUP BY pour totaliser par commande ;
une fenêtre par client sur ces totaux de commande.

3.2 Classements : top N par catégorie

Trouver les 2 produits les plus vendeurs par catégorie (en CA net).

WITH ca_produit AS (
  SELECT
    p.categorie,
    p.produit_id,
    p.libelle,
    SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS ca_net
  FROM commandes c
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  JOIN produits p ON p.produit_id = lc.produit_id
  WHERE c.statut = 'PAYEE'
  GROUP BY p.categorie, p.produit_id, p.libelle
),
rangs AS (
  SELECT
    *,
    DENSE_RANK() OVER (PARTITION BY categorie ORDER BY ca_net DESC) AS rang
  FROM ca_produit
)
SELECT categorie, produit_id, libelle, ca_net, rang
FROM rangs
WHERE rang <= 2
ORDER BY categorie, rang, produit_id;

Pourquoi DENSE_RANK ?

RANK saute des rangs en cas d’égalité (1,1,3…).
DENSE_RANK ne saute pas (1,1,2…).
ROW_NUMBER impose un ordre strict (utile si vous voulez exactement N lignes, même en cas d’égalité, mais il faut alors définir un critère de tie-break).

3.3 Cumuls : running total et part du total

CA cumulé par mois (tous pays confondus) :

WITH mensuel AS (
  SELECT
    DATE_TRUNC('month', c.date_commande)::date AS mois,
    SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS ca_net
  FROM commandes c
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  WHERE c.statut = 'PAYEE'
  GROUP BY 1
)
SELECT
  mois,
  ca_net,
  SUM(ca_net) OVER (ORDER BY mois) AS ca_cumule
FROM mensuel
ORDER BY mois;

Part de chaque pays dans le total :

WITH par_pays AS (
  SELECT
    cl.pays,
    SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS ca_net
  FROM commandes c
  JOIN clients cl ON cl.client_id = c.client_id
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  WHERE c.statut = 'PAYEE'
  GROUP BY cl.pays
)
SELECT
  pays,
  ca_net,
  ca_net / SUM(ca_net) OVER () AS part_du_total
FROM par_pays
ORDER BY ca_net DESC;

OVER () signifie “sur toutes les lignes du résultat”.

3.4 Comparaisons temporelles : LAG / LEAD

Évolution mensuelle : différence et taux de croissance.

WITH mensuel AS (
  SELECT
    DATE_TRUNC('month', c.date_commande)::date AS mois,
    SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS ca_net
  FROM commandes c
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  WHERE c.statut = 'PAYEE'
  GROUP BY 1
)
SELECT
  mois,
  ca_net,
  LAG(ca_net) OVER (ORDER BY mois) AS ca_mois_precedent,
  ca_net - LAG(ca_net) OVER (ORDER BY mois) AS delta,
  (ca_net / NULLIF(LAG(ca_net) OVER (ORDER BY mois), 0)) - 1 AS taux_croissance
FROM mensuel
ORDER BY mois;

Bonnes pratiques :

Utiliser NULLIF(..., 0) pour éviter la division par zéro.
Comprendre que LAG renvoie NULL sur la première ligne (pas de précédent).

3.5 Fenêtres “encadrées” : moyenne mobile

Moyenne mobile sur 3 mois (mois courant + 2 précédents) :

WITH mensuel AS (
  SELECT
    DATE_TRUNC('month', c.date_commande)::date AS mois,
    SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS ca_net
  FROM commandes c
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  WHERE c.statut = 'PAYEE'
  GROUP BY 1
)
SELECT
  mois,
  ca_net,
  AVG(ca_net) OVER (
    ORDER BY mois
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moyenne_mobile_3
FROM mensuel
ORDER BY mois;

ROWS vs RANGE :

ROWS compte des lignes physiques (2 lignes précédentes).
RANGE définit un intervalle de valeurs (utile avec des dates/nombres, mais peut regrouper plusieurs lignes ayant la même valeur d’ordre).

4) Pivot et dépivot : transformer des lignes en colonnes (et inversement)

Le pivot est fréquent en reporting : obtenir une colonne par mois, par catégorie, etc. La mise en œuvre dépend du SGBD.

4.1 Pivot “portable” via agrégations conditionnelles

C’est la méthode la plus universelle : SUM(CASE WHEN ... THEN ... END).

Exemple : CA net par pays, pivoté par catégorie.

WITH ventes AS (
  SELECT
    cl.pays,
    p.categorie,
    (lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0) AS montant_net
  FROM commandes c
  JOIN clients cl ON cl.client_id = c.client_id
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  JOIN produits p ON p.produit_id = lc.produit_id
  WHERE c.statut = 'PAYEE'
)
SELECT
  pays,
  SUM(CASE WHEN categorie = 'Informatique' THEN montant_net ELSE 0 END) AS ca_informatique,
  SUM(CASE WHEN categorie = 'Mobilier'      THEN montant_net ELSE 0 END) AS ca_mobilier,
  SUM(montant_net) AS ca_total
FROM ventes
GROUP BY pays
ORDER BY ca_total DESC;

Avantages :

Fonctionne presque partout.
Contrôle explicite des colonnes.

Limites :

Les valeurs pivotées doivent être connues à l’avance (ici Informatique, Mobilier).
Pour des catégories dynamiques, il faut du SQL dynamique (procédure, application, ou fonctionnalité spécifique).

4.2 Pivot natif (SQL Server)

SQL Server propose PIVOT. Exemple : CA mensuel pivoté en colonnes (janvier à mars).

WITH mensuel AS (
  SELECT
    cl.pays,
    FORMAT(c.date_commande, 'yyyy-MM') AS mois,
    (lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0) AS montant_net
  FROM commandes c
  JOIN clients cl ON cl.client_id = c.client_id
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  WHERE c.statut = 'PAYEE'
)
SELECT
  pays,
  ISNULL([2025-01], 0) AS [2025-01],
  ISNULL([2025-02], 0) AS [2025-02],
  ISNULL([2025-03], 0) AS [2025-03]
FROM mensuel
PIVOT (
  SUM(montant_net) FOR mois IN ([2025-01], [2025-02], [2025-03])
) p
ORDER BY pays;

4.3 Dépivot : revenir à un format “long”

Le dépivot sert à revenir à une table normalisée (utile pour graphiques, export, calculs).

4.3.1 Dépivot “portable” via `UNION ALL`

Supposons que vous ayez un résultat pivoté (par exemple une table de reporting). Vous pouvez dépivoter :

SELECT pays, 'Informatique' AS categorie, ca_informatique AS ca_net
FROM reporting_pays
UNION ALL
SELECT pays, 'Mobilier' AS categorie, ca_mobilier AS ca_net
FROM reporting_pays;

4.3.2 Dépivot natif (SQL Server)

SELECT pays, categorie, ca_net
FROM reporting_pays
UNPIVOT (
  ca_net FOR categorie IN (ca_informatique, ca_mobilier)
) u;

5) Techniques d’optimisation : rendre les requêtes rapides et fiables

Optimiser, ce n’est pas “écrire du SQL plus court”. C’est :

réduire le volume de données manipulées ;
permettre à l’optimiseur d’utiliser des index ;
éviter des opérations coûteuses (tri, hash, boucles) inutiles ;
garantir des résultats corrects (éviter les doublons accidentels).

5.1 Filtrer tôt, sélectionner peu

Mauvaise habitude : joindre tout puis filtrer tard. Bonne habitude : filtrer dès que possible, et ne sélectionner que les colonnes utiles.

-- Exemple : limiter aux commandes payées et à une période avant de joindre trop large
WITH commandes_filtrees AS (
  SELECT commande_id, client_id, date_commande
  FROM commandes
  WHERE statut = 'PAYEE'
    AND date_commande >= DATE '2025-01-01'
    AND date_commande <  DATE '2025-04-01'
)
SELECT
  cf.date_commande,
  cl.pays,
  SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS ca_net
FROM commandes_filtrees cf
JOIN clients cl ON cl.client_id = cf.client_id
JOIN lignes_commande lc ON lc.commande_id = cf.commande_id
GROUP BY cf.date_commande, cl.pays;

5.2 Index : viser les bons prédicats et les bonnes jointures

Dans ce modèle, des index typiques :

CREATE INDEX idx_commandes_statut_date ON commandes (statut, date_commande);
CREATE INDEX idx_commandes_client      ON commandes (client_id);

CREATE INDEX idx_lignes_commande_cmd   ON lignes_commande (commande_id);
CREATE INDEX idx_lignes_commande_prod  ON lignes_commande (produit_id);

CREATE INDEX idx_clients_pays          ON clients (pays);
CREATE INDEX idx_produits_categorie    ON produits (categorie);

Principes :

Indexer les colonnes utilisées dans WHERE et JOIN.
Un index composite doit suivre l’ordre d’usage : si vous filtrez souvent par statut puis par date, (statut, date_commande) est logique.
Trop d’index ralentissent les écritures (insert/update) : il faut arbitrer.

5.3 Éviter les fonctions sur colonnes filtrées (non sargable)

Un prédicat “sargable” permet l’utilisation d’index.

À éviter :

SELECT *
FROM commandes
WHERE EXTRACT(YEAR FROM date_commande) = 2025;

Préférer :

SELECT *
FROM commandes
WHERE date_commande >= DATE '2025-01-01'
  AND date_commande <  DATE '2026-01-01';

Même logique pour UPPER(col) = 'X' : mieux vaut une collation adaptée, un index fonctionnel (si disponible), ou stocker une forme normalisée.

5.4 Attention aux doublons lors des jointures

Une jointure peut multiplier les lignes si la cardinalité n’est pas celle attendue. Symptôme : des sommes trop grandes.

Astuce : valider les cardinalités et utiliser COUNT(*) avant d’agréger.

-- Vérifier qu’une commande n’est pas dupliquée par une jointure inattendue
SELECT
  c.commande_id,
  COUNT(*) AS nb_lignes_apres_jointure
FROM commandes c
JOIN lignes_commande lc ON lc.commande_id = c.commande_id
WHERE c.statut = 'PAYEE'
GROUP BY c.commande_id
ORDER BY nb_lignes_apres_jointure DESC;

5.5 `EXISTS` vs `IN` vs `JOIN`

Pour tester l’existence d’une relation, EXISTS est souvent plus robuste.

Exemple : clients ayant au moins une commande payée.

SELECT cl.*
FROM clients cl
WHERE EXISTS (
  SELECT 1
  FROM commandes c
  WHERE c.client_id = cl.client_id
    AND c.statut = 'PAYEE'
);

Pourquoi c’est bien :

EXISTS s’arrête dès qu’il trouve une ligne.
Évite parfois des doublons qu’un JOIN pourrait introduire si on sélectionne des colonnes côté “many”.

5.6 Matérialisation : quand un CTE n’est pas “gratuit”

Selon le SGBD, un CTE peut être :

inliné (comme une sous-requête, recalculé/optimisé globalement),
ou matérialisé (calculé puis stocké temporairement), ce qui peut aider ou nuire.

Approche pratique :

Si une sous-partie est réutilisée plusieurs fois, la matérialisation peut être bénéfique.
Si elle est énorme et utilisée une seule fois, elle peut coûter cher.

Dans certains moteurs, on peut influencer cela (selon versions et options). Dans tous les cas, la méthode fiable est de mesurer.

5.7 Lire un plan d’exécution

La commande dépend du moteur. Exemples :

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  cl.pays,
  SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS ca_net
FROM commandes c
JOIN clients cl ON cl.client_id = c.client_id
JOIN lignes_commande lc ON lc.commande_id = c.commande_id
WHERE c.statut = 'PAYEE'
GROUP BY cl.pays;

SQL Server

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT
  cl.pays,
  SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS ca_net
FROM commandes c
JOIN clients cl ON cl.client_id = c.client_id
JOIN lignes_commande lc ON lc.commande_id = c.commande_id
WHERE c.statut = 'PAYEE'
GROUP BY cl.pays;

Ce qu’on cherche :

scans complets inutiles (table scan) ;
jointures coûteuses (hash join) sur des volumes importants ;
tris (sort) non nécessaires ;
estimations très éloignées des valeurs réelles (statistiques à mettre à jour).

5.8 Pré-agréger au bon niveau

Si vous avez besoin de métriques par commande, calculez d’abord par commande, puis joignez. Cela réduit le volume.

WITH total_commande AS (
  SELECT
    lc.commande_id,
    SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS total_net
  FROM lignes_commande lc
  GROUP BY lc.commande_id
)
SELECT
  cl.pays,
  SUM(tc.total_net) AS ca_net
FROM commandes c
JOIN total_commande tc ON tc.commande_id = c.commande_id
JOIN clients cl ON cl.client_id = c.client_id
WHERE c.statut = 'PAYEE'
GROUP BY cl.pays;

Intérêt :

On agrège lignes_commande une seule fois.
La jointure suivante se fait sur moins de lignes.

6) Cas d’usage complet : tableau d’analyse “client” (RFM simplifié)

On veut un tableau par client avec :

Récence : jours depuis la dernière commande payée
Fréquence : nombre de commandes payées
Monétaire : CA net total
- un rang par segment sur le CA

6.1 Requête complète avec CTE + fenêtres

WITH commandes_payees AS (
  SELECT commande_id, client_id, date_commande
  FROM commandes
  WHERE statut = 'PAYEE'
),
total_par_commande AS (
  SELECT
    lc.commande_id,
    SUM((lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0)) AS total_net
  FROM lignes_commande lc
  GROUP BY lc.commande_id
),
faits AS (
  SELECT
    cp.client_id,
    cp.commande_id,
    cp.date_commande,
    tpc.total_net
  FROM commandes_payees cp
  JOIN total_par_commande tpc ON tpc.commande_id = cp.commande_id
),
rfm AS (
  SELECT
    f.client_id,
    MAX(f.date_commande) AS derniere_commande,
    COUNT(DISTINCT f.commande_id) AS frequence,
    SUM(f.total_net) AS montant
  FROM faits f
  GROUP BY f.client_id
),
enrichi AS (
  SELECT
    cl.client_id,
    cl.nom,
    cl.segment,
    cl.pays,
    r.derniere_commande,
    r.frequence,
    r.montant,
    (CURRENT_DATE - r.derniere_commande) AS recence_jours
  FROM clients cl
  LEFT JOIN rfm r ON r.client_id = cl.client_id
)
SELECT
  *,
  DENSE_RANK() OVER (PARTITION BY segment ORDER BY montant DESC NULLS LAST) AS rang_ca_segment
FROM enrichi
ORDER BY segment, rang_ca_segment, client_id;

Explications importantes :

total_par_commande pré-agrège au niveau commande (efficace et propre).
rfm agrège au niveau client.
LEFT JOIN sur clients permet de conserver les clients sans commande (valeurs NULL).
La fenêtre DENSE_RANK classe les clients à l’intérieur de chaque segment.

7) Pièges fréquents et bonnes pratiques

7.1 `NULL` et agrégations

SUM ignore NULL, mais si tout est NULL, le résultat est NULL.
Utilisez COALESCE si vous voulez 0.

SELECT COALESCE(SUM(montant_net), 0) AS ca_net
FROM (
  SELECT (lc.quantite * lc.prix_unitaire) * (1 - lc.remise / 100.0) AS montant_net
  FROM commandes c
  JOIN lignes_commande lc ON lc.commande_id = c.commande_id
  WHERE c.statut = 'PAYEE'
) x;

7.2 `COUNT(*)` vs `COUNT(colonne)`

COUNT(*) compte les lignes.
COUNT(colonne) compte les lignes où colonne n’est pas NULL.

7.3 Fenêtres et ordre

Sans ORDER BY dans OVER, certaines fonctions restent valides (ex. SUM(...) OVER (PARTITION BY ...)), mais d’autres perdent leur sens (ex. cumul). Soyez explicite.

7.4 Définir clairement le grain

Avant d’écrire une requête, posez :

Quel est le grain (ligne = commande ? ligne de commande ? client-mois ?)
Quelles dimensions sont autorisées dans le SELECT sans casser le grain ?
Où doit se faire l’agrégation ?

Cette discipline évite 80% des erreurs d’analyse (doublons, sommes gonflées, ratios faux).

8) Résumé des patterns à réutiliser

CTE : transformer une requête en pipeline lisible (ventes → mensuel → classement).
Fenêtres :
- DENSE_RANK() pour top N par groupe,
- SUM(...) OVER (ORDER BY ...) pour cumuls,
- LAG/LEAD pour comparaisons temporelles,
- fenêtres encadrées (ROWS BETWEEN) pour moyennes mobiles.
Pivot :
- méthode portable : SUM(CASE WHEN ...),
- pivot natif si disponible pour des rapports standardisés.
Optimisation :
- filtrer tôt, éviter les fonctions sur colonnes filtrées,
- indexer selon WHERE et JOIN,
- pré-agréger au bon niveau,
- lire les plans d’exécution et mesurer.

9) Exercices pratiques (avec pistes)

Calculer le CA net par segment et par mois, puis afficher le cumul par segment.
Piste : CTE mensuel + fenêtre SUM(...) OVER (PARTITION BY segment ORDER BY mois).
Trouver, pour chaque pays, le produit dont le CA net est maximal.
Piste : agrégation par pays-produit, puis ROW_NUMBER() OVER (PARTITION BY pays ORDER BY ca_net DESC).
Produire un tableau pivoté des CA mensuels (colonnes = mois) pour l’année 2025.
Piste : agrégations conditionnelles ou pivot natif ; attention aux mois manquants.
Mesurer l’impact d’un index sur commandes(statut, date_commande) via un plan d’exécution.
Piste : comparer avant/après, observer scans vs index scans.

Si vous me précisez votre SGBD (PostgreSQL, SQL Server, Oracle, MySQL, SQLite) et votre besoin (reporting mensuel, cohortes, entonnoir, churn, etc.), je peux adapter les exemples avec la syntaxe exacte (dates, pivot natif, fonctions disponibles) et proposer une version optimisée pour votre moteur.

Requêtes SQL avancées pour l’analyse de données : CTE, fenêtres, pivot et optimisation

Requêtes SQL avancées pour l’analyse de données : CTE, fenêtres, pivot et optimisation

1) Jeu de données d’exemple (schéma + données)

1.1 Création des tables

1.2 Insertion de quelques données

1.3 Mesure de base : chiffre d’affaires net

2) CTE : structurer, réutiliser, clarifier

2.1 CTE simple : base de faits “ventes”

2.2 Plusieurs CTE : pipeline analytique

2.3 CTE récursif : hiérarchies et séries temporelles

2.3.1 Générer une série de mois (portable conceptuellement)

3) Fonctions de fenêtre : analyser sans “casser” les lignes

3.1 Différence clé : agrégation vs fenêtre

3.2 Classements : top N par catégorie

3.3 Cumuls : running total et part du total

3.4 Comparaisons temporelles : LAG / LEAD

3.5 Fenêtres “encadrées” : moyenne mobile

4) Pivot et dépivot : transformer des lignes en colonnes (et inversement)

4.1 Pivot “portable” via agrégations conditionnelles

4.2 Pivot natif (SQL Server)

4.3 Dépivot : revenir à un format “long”

4.3.1 Dépivot “portable” via UNION ALL

4.3.2 Dépivot natif (SQL Server)

5) Techniques d’optimisation : rendre les requêtes rapides et fiables

5.1 Filtrer tôt, sélectionner peu

5.2 Index : viser les bons prédicats et les bonnes jointures

5.3 Éviter les fonctions sur colonnes filtrées (non sargable)

5.4 Attention aux doublons lors des jointures

5.5 EXISTS vs IN vs JOIN

5.6 Matérialisation : quand un CTE n’est pas “gratuit”

5.7 Lire un plan d’exécution

PostgreSQL

SQL Server

5.8 Pré-agréger au bon niveau

6) Cas d’usage complet : tableau d’analyse “client” (RFM simplifié)

6.1 Requête complète avec CTE + fenêtres

7) Pièges fréquents et bonnes pratiques

7.1 NULL et agrégations

7.2 COUNT(*) vs COUNT(colonne)

7.3 Fenêtres et ordre

7.4 Définir clairement le grain

8) Résumé des patterns à réutiliser

9) Exercices pratiques (avec pistes)

Tutoriels associés

4.3.1 Dépivot “portable” via `UNION ALL`

5.5 `EXISTS` vs `IN` vs `JOIN`

7.1 `NULL` et agrégations

7.2 `COUNT(*)` vs `COUNT(colonne)`