Requêtes SQL avancées pour l’analyse de données : techniques, optimisations et cas d’usage

Ce tutoriel propose une approche pratique et approfondie des requêtes SQL avancées appliquées à l’analyse de données : agrégations complexes, fonctions de fenêtrage, CTE récursives, gestion des données temporelles, optimisation des performances, et cas d’usage concrets. Les exemples utilisent une syntaxe compatible avec PostgreSQL (souvent transposable à d’autres SGBD, avec quelques ajustements).

1) Préparer un environnement d’exemples

Pour rendre les cas d’usage concrets, on va créer un petit schéma d’analyse : clients, commandes, lignes de commande, produits, événements web. Vous pouvez exécuter ces commandes dans PostgreSQL.

CREATE TABLE clients (
  client_id      BIGSERIAL PRIMARY KEY,
  email          TEXT UNIQUE NOT NULL,
  pays           TEXT NOT NULL,
  date_inscription DATE NOT NULL
);

CREATE TABLE produits (
  produit_id   BIGSERIAL PRIMARY KEY,
  categorie    TEXT NOT NULL,
  libelle      TEXT NOT NULL,
  prix_ht      NUMERIC(12,2) NOT NULL
);

CREATE TABLE commandes (
  commande_id   BIGSERIAL PRIMARY KEY,
  client_id     BIGINT NOT NULL REFERENCES clients(client_id),
  date_commande TIMESTAMP NOT NULL,
  statut        TEXT NOT NULL, -- ex: 'payee', 'annulee', 'remboursee'
  canal         TEXT NOT NULL  -- ex: 'web', 'mobile', 'boutique'
);

CREATE TABLE lignes_commande (
  commande_id BIGINT NOT NULL REFERENCES commandes(commande_id),
  produit_id  BIGINT NOT NULL REFERENCES produits(produit_id),
  quantite    INT NOT NULL CHECK (quantite > 0),
  prix_unitaire_ht NUMERIC(12,2) NOT NULL,
  remise_pct  NUMERIC(5,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (commande_id, produit_id)
);

CREATE TABLE evenements_web (
  event_id     BIGSERIAL PRIMARY KEY,
  client_id    BIGINT REFERENCES clients(client_id),
  session_id   TEXT NOT NULL,
  event_time   TIMESTAMP NOT NULL,
  event_type   TEXT NOT NULL,  -- ex: 'page_view', 'add_to_cart', 'purchase'
  page         TEXT
);

Index de base (indispensables pour l’analyse à l’échelle)

CREATE INDEX idx_commandes_date ON commandes(date_commande);
CREATE INDEX idx_commandes_client_date ON commandes(client_id, date_commande);
CREATE INDEX idx_lignes_produit ON lignes_commande(produit_id);
CREATE INDEX idx_events_time ON evenements_web(event_time);
CREATE INDEX idx_events_client_time ON evenements_web(client_id, event_time);

Pourquoi ces index ?

• Les analyses demandent souvent des filtrages temporels (WHERE date_commande >= ...) et des regroupements par client.
• Les jointures sur clés étrangères et les filtres sur event_time sont fréquents.
• Un index composite (client_id, date_commande) accélère les requêtes « par client dans le temps » (cohortes, rétention, LTV).

2) Agrégations avancées : GROUPING SETS, ROLLUP, CUBE

Les agrégations ne se limitent pas à GROUP BY. Pour produire plusieurs niveaux de synthèse en une seule requête, on utilise :

• GROUPING SETS : liste explicite de regroupements
• ROLLUP : hiérarchie (ex: jour → mois → total)
• CUBE : toutes les combinaisons (utile mais potentiellement coûteux)

Exemple : chiffre d’affaires par canal, par pays, et total

On calcule un CA HT par commande à partir des lignes (quantité, prix, remise).

WITH ca_par_commande AS (
  SELECT
    c.commande_id,
    c.client_id,
    c.canal,
    c.date_commande,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM commandes c
  JOIN lignes_commande l ON l.commande_id = c.commande_id
  WHERE c.statut = 'payee'
  GROUP BY c.commande_id, c.client_id, c.canal, c.date_commande
)
SELECT
  cl.pays,
  ca.canal,
  SUM(ca.ca_ht) AS ca_ht
FROM ca_par_commande ca
JOIN clients cl ON cl.client_id = ca.client_id
GROUP BY GROUPING SETS (
  (cl.pays, ca.canal),
  (cl.pays),
  (ca.canal),
  ()
)
ORDER BY cl.pays NULLS LAST, ca.canal NULLS LAST;

Lecture du résultat :

• (pays, canal) : détail
• (pays) : total par pays
• (canal) : total par canal
• () : total général

Astuce : utilisez GROUPING() pour distinguer les lignes de total.

SELECT
  cl.pays,
  ca.canal,
  GROUPING(cl.pays)  AS is_total_pays,
  GROUPING(ca.canal) AS is_total_canal,
  SUM(ca.ca_ht) AS ca_ht
...

3) Fonctions de fenêtrage (window functions) : l’outil central de l’analyste

Les fonctions de fenêtrage permettent de calculer des métriques « par partition » sans perdre le détail ligne à ligne. Elles sont essentielles pour :

• classements (top N)
• cumul (running total)
• parts de contribution
• détection d’anomalies
• calculs temporels (rétention, churn, délais)

3.1) Classement des produits par CA dans chaque catégorie

WITH ventes_produits AS (
  SELECT
    p.categorie,
    p.produit_id,
    p.libelle,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM lignes_commande l
  JOIN commandes c ON c.commande_id = l.commande_id
  JOIN produits p ON p.produit_id = l.produit_id
  WHERE c.statut = 'payee'
  GROUP BY p.categorie, p.produit_id, p.libelle
)
SELECT *
FROM (
  SELECT
    categorie,
    produit_id,
    libelle,
    ca_ht,
    DENSE_RANK() OVER (PARTITION BY categorie ORDER BY ca_ht DESC) AS rang_cat
  FROM ventes_produits
) t
WHERE rang_cat <= 5
ORDER BY categorie, rang_cat, ca_ht DESC;

Pourquoi DENSE_RANK() ?

• En cas d’égalité de CA, plusieurs produits peuvent partager le même rang (sans « trou »).

3.2) Part de contribution (ratio au total) sans sous-requête supplémentaire

WITH ventes AS (
  SELECT
    p.categorie,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM lignes_commande l
  JOIN commandes c ON c.commande_id = l.commande_id
  JOIN produits p ON p.produit_id = l.produit_id
  WHERE c.statut = 'payee'
  GROUP BY p.categorie
)
SELECT
  categorie,
  ca_ht,
  ca_ht / SUM(ca_ht) OVER () AS part_du_total
FROM ventes
ORDER BY ca_ht DESC;

Ici, SUM(ca_ht) OVER () calcule le total sur l’ensemble des lignes du résultat ventes.

3.3) Cumul temporel (running total) par canal

WITH ca_jour AS (
  SELECT
    date_trunc('day', c.date_commande) AS jour,
    c.canal,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM commandes c
  JOIN lignes_commande l ON l.commande_id = c.commande_id
  WHERE c.statut = 'payee'
  GROUP BY 1, 2
)
SELECT
  jour,
  canal,
  ca_ht,
  SUM(ca_ht) OVER (
    PARTITION BY canal
    ORDER BY jour
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS ca_cumule
FROM ca_jour
ORDER BY canal, jour;

Point important : l’encadrement ROWS BETWEEN ... garantit un cumul ligne à ligne (et non par valeurs identiques). Sur une série temporelle, c’est généralement ce qu’on veut.

3.4) Valeur précédente/suivante : LAG, LEAD pour mesurer des variations

WITH ca_mois AS (
  SELECT
    date_trunc('month', c.date_commande) AS mois,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM commandes c
  JOIN lignes_commande l ON l.commande_id = c.commande_id
  WHERE c.statut = 'payee'
  GROUP BY 1
)
SELECT
  mois,
  ca_ht,
  LAG(ca_ht) OVER (ORDER BY mois) AS ca_mois_precedent,
  ca_ht - LAG(ca_ht) OVER (ORDER BY mois) AS delta_abs,
  (ca_ht / NULLIF(LAG(ca_ht) OVER (ORDER BY mois), 0) - 1) AS delta_pct
FROM ca_mois
ORDER BY mois;

NULLIF(..., 0) évite une division par zéro.

4) CTE (WITH) : lisibilité, réutilisation, et matérialisation contrôlée

Les CTE rendent les requêtes complexes plus lisibles. Attention : selon le SGBD et la version, un CTE peut être matérialisé (donc potentiellement plus lent) ou inliné (optimisé comme une sous-requête).

4.1) Pipeline analytique : filtrer, enrichir, agréger

Objectif : CA par pays et par mois, uniquement sur commandes payées, en excluant des clients récents.

WITH clients_eligibles AS (
  SELECT client_id, pays
  FROM clients
  WHERE date_inscription <= CURRENT_DATE - INTERVAL '30 days'
),
commandes_payees AS (
  SELECT commande_id, client_id, date_commande
  FROM commandes
  WHERE statut = 'payee'
),
ca_par_commande AS (
  SELECT
    c.commande_id,
    c.client_id,
    date_trunc('month', c.date_commande) AS mois,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM commandes_payees c
  JOIN lignes_commande l ON l.commande_id = c.commande_id
  GROUP BY 1, 2, 3
)
SELECT
  ce.pays,
  ca.mois,
  SUM(ca.ca_ht) AS ca_ht
FROM ca_par_commande ca
JOIN clients_eligibles ce ON ce.client_id = ca.client_id
GROUP BY ce.pays, ca.mois
ORDER BY ce.pays, ca.mois;

Bonnes pratiques :

• Donnez des noms explicites aux CTE.
• Filtrez tôt (réduire le volume) avant les jointures coûteuses.
• Évitez de répéter des calculs (ex: formule de CA).

5) Cohortes et rétention : analyse comportementale en SQL

La rétention consiste à mesurer si un client revient après sa première action (achat, visite, etc.). On va faire une rétention mensuelle basée sur la première commande payée.

5.1) Définir le mois de cohorte (premier achat)

WITH premieres_commandes AS (
  SELECT
    client_id,
    MIN(date_trunc('month', date_commande)) AS mois_cohorte
  FROM commandes
  WHERE statut = 'payee'
  GROUP BY client_id
),
activite_mensuelle AS (
  SELECT
    client_id,
    date_trunc('month', date_commande) AS mois_activite
  FROM commandes
  WHERE statut = 'payee'
  GROUP BY client_id, date_trunc('month', date_commande)
),
cohortes AS (
  SELECT
    p.mois_cohorte,
    a.mois_activite,
    (EXTRACT(YEAR FROM a.mois_activite) - EXTRACT(YEAR FROM p.mois_cohorte)) * 12
    + (EXTRACT(MONTH FROM a.mois_activite) - EXTRACT(MONTH FROM p.mois_cohorte)) AS age_mois,
    a.client_id
  FROM premieres_commandes p
  JOIN activite_mensuelle a ON a.client_id = p.client_id
)
SELECT
  mois_cohorte,
  age_mois,
  COUNT(DISTINCT client_id) AS clients_actifs
FROM cohortes
GROUP BY mois_cohorte, age_mois
ORDER BY mois_cohorte, age_mois;

Interprétation :

• age_mois = 0 : mois du premier achat (taille de cohorte)
• age_mois = 1 : clients revenus le mois suivant, etc.

5.2) Taux de rétention (normalisation par la taille de cohorte)

WITH base AS (
  -- Reprend la requête précédente jusqu’à cohortes
  WITH premieres_commandes AS (
    SELECT client_id, MIN(date_trunc('month', date_commande)) AS mois_cohorte
    FROM commandes
    WHERE statut = 'payee'
    GROUP BY client_id
  ),
  activite_mensuelle AS (
    SELECT client_id, date_trunc('month', date_commande) AS mois_activite
    FROM commandes
    WHERE statut = 'payee'
    GROUP BY client_id, date_trunc('month', date_commande)
  ),
  cohortes AS (
    SELECT
      p.mois_cohorte,
      a.mois_activite,
      (EXTRACT(YEAR FROM a.mois_activite) - EXTRACT(YEAR FROM p.mois_cohorte)) * 12
      + (EXTRACT(MONTH FROM a.mois_activite) - EXTRACT(MONTH FROM p.mois_cohorte)) AS age_mois,
      a.client_id
    FROM premieres_commandes p
    JOIN activite_mensuelle a ON a.client_id = p.client_id
  )
  SELECT mois_cohorte, age_mois, COUNT(DISTINCT client_id) AS clients_actifs
  FROM cohortes
  GROUP BY mois_cohorte, age_mois
)
SELECT
  mois_cohorte,
  age_mois,
  clients_actifs,
  clients_actifs::NUMERIC
  / NULLIF(MAX(CASE WHEN age_mois = 0 THEN clients_actifs END) OVER (PARTITION BY mois_cohorte), 0)
  AS taux_retention
FROM base
ORDER BY mois_cohorte, age_mois;

Ici, on récupère la taille de cohorte via une fenêtre MAX(...) OVER (PARTITION BY mois_cohorte).

6) Segmentation RFM (Récence, Fréquence, Montant)

Le scoring RFM est un classique de l’analyse CRM :

• Récence : depuis combien de temps le client a acheté
• Fréquence : nombre de commandes
• Montant : CA cumulé

6.1) Calcul des métriques RFM

WITH ca_commande AS (
  SELECT
    c.commande_id,
    c.client_id,
    c.date_commande,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM commandes c
  JOIN lignes_commande l ON l.commande_id = c.commande_id
  WHERE c.statut = 'payee'
  GROUP BY c.commande_id, c.client_id, c.date_commande
),
rfm AS (
  SELECT
    client_id,
    MAX(date_commande) AS derniere_commande,
    COUNT(*) AS nb_commandes,
    SUM(ca_ht) AS ca_total
  FROM ca_commande
  GROUP BY client_id
)
SELECT
  r.client_id,
  (CURRENT_DATE - r.derniere_commande::DATE) AS recence_jours,
  r.nb_commandes,
  r.ca_total
FROM rfm r
ORDER BY r.ca_total DESC;

6.2) Scoring par quantiles avec NTILE

On attribue des scores de 1 à 5.

WITH ca_commande AS (
  SELECT
    c.commande_id,
    c.client_id,
    c.date_commande,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM commandes c
  JOIN lignes_commande l ON l.commande_id = c.commande_id
  WHERE c.statut = 'payee'
  GROUP BY c.commande_id, c.client_id, c.date_commande
),
rfm AS (
  SELECT
    client_id,
    MAX(date_commande) AS derniere_commande,
    COUNT(*) AS nb_commandes,
    SUM(ca_ht) AS ca_total
  FROM ca_commande
  GROUP BY client_id
),
scores AS (
  SELECT
    client_id,
    (CURRENT_DATE - derniere_commande::DATE) AS recence_jours,
    nb_commandes,
    ca_total,
    NTILE(5) OVER (ORDER BY (CURRENT_DATE - derniere_commande::DATE) DESC) AS score_r,
    NTILE(5) OVER (ORDER BY nb_commandes ASC) AS score_f,
    NTILE(5) OVER (ORDER BY ca_total ASC) AS score_m
  FROM rfm
)
SELECT
  client_id,
  recence_jours,
  nb_commandes,
  ca_total,
  score_r, score_f, score_m,
  (score_r + score_f + score_m) AS score_total
FROM scores
ORDER BY score_total DESC, ca_total DESC;

Attention au sens du tri :

• Pour la récence, plus c’est récent, mieux c’est. Ici on trie recence_jours DESC pour que les plus anciens aient un score plus élevé, ce qui est souvent l’inverse de ce qu’on veut. Selon votre convention, inversez :
  • soit ORDER BY recence_jours ASC (les plus récents dans les tuiles basses),
  • soit conservez et transformez score_r en 6 - score_r.

Exemple avec transformation :

SELECT
  *,
  (6 - score_r) AS score_r_corrige
FROM scores;

7) Analyse d’entonnoir (funnel) avec événements web

Un funnel mesure le passage d’étapes : page_view → add_to_cart → purchase. On peut le faire par session.

7.1) Marquer la présence d’étapes par session

WITH sessions AS (
  SELECT
    session_id,
    MIN(event_time) AS debut_session,
    MAX(event_time) AS fin_session,
    BOOL_OR(event_type = 'page_view') AS a_vue_page,
    BOOL_OR(event_type = 'add_to_cart') AS a_ajoute_panier,
    BOOL_OR(event_type = 'purchase') AS a_achete
  FROM evenements_web
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY session_id
)
SELECT
  COUNT(*) AS sessions_total,
  SUM(CASE WHEN a_vue_page THEN 1 ELSE 0 END) AS sessions_page_view,
  SUM(CASE WHEN a_vue_page AND a_ajoute_panier THEN 1 ELSE 0 END) AS sessions_panier,
  SUM(CASE WHEN a_vue_page AND a_ajoute_panier AND a_achete THEN 1 ELSE 0 END) AS sessions_achat
FROM sessions;

Pourquoi BOOL_OR ?

• C’est une agrégation booléenne pratique (PostgreSQL) : vrai si au moins un événement correspond.

7.2) Délai entre étapes avec MIN(...) FILTER (WHERE ...)

WITH etapes AS (
  SELECT
    session_id,
    MIN(event_time) FILTER (WHERE event_type = 'page_view') AS t0,
    MIN(event_time) FILTER (WHERE event_type = 'add_to_cart') AS t1,
    MIN(event_time) FILTER (WHERE event_type = 'purchase') AS t2
  FROM evenements_web
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY session_id
)
SELECT
  session_id,
  t0, t1, t2,
  (t1 - t0) AS delai_page_vers_panier,
  (t2 - t1) AS delai_panier_vers_achat
FROM etapes
WHERE t0 IS NOT NULL;

Remarque : vous pouvez ajouter AND t1 >= t0 si vous voulez imposer l’ordre.

8) Requêtes hiérarchiques : CTE récursives

Les CTE récursives sont utiles pour :

• hiérarchies (catégories, organigrammes)
• graphes simples (parcours)
• séries temporelles (génération de calendrier)

8.1) Générer un calendrier de dates (utile pour combler les trous)

WITH RECURSIVE calendrier AS (
  SELECT CURRENT_DATE - INTERVAL '30 days' AS jour
  UNION ALL
  SELECT jour + INTERVAL '1 day'
  FROM calendrier
  WHERE jour + INTERVAL '1 day' <= CURRENT_DATE
)
SELECT jour::DATE
FROM calendrier
ORDER BY jour;

8.2) CA quotidien avec jours sans ventes (jointure sur calendrier)

WITH RECURSIVE calendrier AS (
  SELECT (CURRENT_DATE - INTERVAL '30 days')::DATE AS jour
  UNION ALL
  SELECT (jour + 1)
  FROM calendrier
  WHERE jour + 1 <= CURRENT_DATE
),
ca_jour AS (
  SELECT
    date_trunc('day', c.date_commande)::DATE AS jour,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM commandes c
  JOIN lignes_commande l ON l.commande_id = c.commande_id
  WHERE c.statut = 'payee'
    AND c.date_commande >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY 1
)
SELECT
  cal.jour,
  COALESCE(ca.ca_ht, 0) AS ca_ht
FROM calendrier cal
LEFT JOIN ca_jour ca ON ca.jour = cal.jour
ORDER BY cal.jour;

9) Gestion avancée du temps : fuseaux, arrondis, périodes glissantes

9.1) date_trunc et périodes

• date_trunc('hour', ts) : arrondi à l’heure
• date_trunc('week', ts) : début de semaine (selon configuration)
• date_trunc('month', ts) : début de mois

Exemple : CA sur les 7 derniers jours glissants (rolling window) par jour.

WITH ca_jour AS (
  SELECT
    date_trunc('day', c.date_commande)::DATE AS jour,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM commandes c
  JOIN lignes_commande l ON l.commande_id = c.commande_id
  WHERE c.statut = 'payee'
  GROUP BY 1
)
SELECT
  jour,
  ca_ht,
  SUM(ca_ht) OVER (
    ORDER BY jour
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ca_7j_glissants
FROM ca_jour
ORDER BY jour;

Différence ROWS vs RANGE :

• ROWS : nombre de lignes (adapté si vous avez une ligne par jour).
• RANGE : intervalle de valeurs (plus délicat avec des dates, dépend du SGBD).

10) Optimisation : principes, diagnostics, et réécritures

L’optimisation SQL est souvent un mélange de :

• réduction de volume (filtrer tôt)
• bons index
• éviter les fonctions sur colonnes indexées dans les filtres
• jointures correctes
• agrégations maîtrisées
• compréhension du plan d’exécution

10.1) Lire un plan avec EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  c.client_id,
  COUNT(*) AS nb
FROM commandes c
WHERE c.date_commande >= CURRENT_DATE - INTERVAL '90 days'
  AND c.statut = 'payee'
GROUP BY c.client_id;

À observer :

• Seq Scan (scan complet) vs Index Scan
• coût estimé vs temps réel
• Buffers : lectures mémoire/disque
• HashAggregate vs GroupAggregate
• cardinalités (nombre de lignes) à chaque étape

10.2) Éviter de « casser » un index avec une fonction

Mauvais (souvent) :

SELECT *
FROM commandes
WHERE date_trunc('day', date_commande) = CURRENT_DATE;

Meilleur :

SELECT *
FROM commandes
WHERE date_commande >= CURRENT_DATE
  AND date_commande < CURRENT_DATE + INTERVAL '1 day';

Ici, la colonne date_commande reste « utilisable » par l’index.

10.3) Index partiels pour les statuts fréquents

Si 90% des analyses portent sur statut = 'payee', un index partiel peut être très efficace.

CREATE INDEX idx_commandes_payees_date
ON commandes(date_commande)
WHERE statut = 'payee';

10.4) Pré-agrégation : vues matérialisées

Pour des tableaux de bord lourds, pré-calculer peut être plus rentable que d’optimiser à l’extrême une requête ad hoc.

CREATE MATERIALIZED VIEW mv_ca_jour AS
SELECT
  date_trunc('day', c.date_commande)::DATE AS jour,
  c.canal,
  SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
FROM commandes c
JOIN lignes_commande l ON l.commande_id = c.commande_id
WHERE c.statut = 'payee'
GROUP BY 1, 2;

CREATE INDEX idx_mv_ca_jour ON mv_ca_jour(jour, canal);

Rafraîchissement :

REFRESH MATERIALIZED VIEW mv_ca_jour;

Variante : REFRESH MATERIALIZED VIEW CONCURRENTLY mv_ca_jour; (nécessite un index unique et a des contraintes).

10.5) Réécriture : JOIN vs sous-requête corrélée

Souvent, une sous-requête corrélée est plus lente qu’un JOIN + agrégation.

Sous-requête corrélée (peut être coûteuse) :

SELECT
  cl.client_id,
  (SELECT COUNT(*)
   FROM commandes c
   WHERE c.client_id = cl.client_id
     AND c.statut = 'payee') AS nb_commandes
FROM clients cl;

Réécriture :

SELECT
  cl.client_id,
  COALESCE(x.nb_commandes, 0) AS nb_commandes
FROM clients cl
LEFT JOIN (
  SELECT client_id, COUNT(*) AS nb_commandes
  FROM commandes
  WHERE statut = 'payee'
  GROUP BY client_id
) x ON x.client_id = cl.client_id;

11) Qualité des données : dédoublonnage, contrôles, et règles

11.1) Détecter des doublons (emails)

SELECT email, COUNT(*) AS nb
FROM clients
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY nb DESC, email;

11.2) Garder une ligne par clé avec ROW_NUMBER()

Exemple : si vous aviez des doublons de clients et vouliez garder le plus ancien.

WITH d AS (
  SELECT
    client_id,
    email,
    date_inscription,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY date_inscription ASC, client_id ASC) AS rn
  FROM clients
)
SELECT *
FROM d
WHERE rn = 1;

Pour supprimer les doublons (à faire avec prudence) :

WITH d AS (
  SELECT
    client_id,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY date_inscription ASC, client_id ASC) AS rn
  FROM clients
)
DELETE FROM clients
WHERE client_id IN (SELECT client_id FROM d WHERE rn > 1);

12) Cas d’usage complet : tableau de bord « performance commerciale »

Objectif : produire, sur les 90 derniers jours, par pays et par canal :

• CA total
• nombre de commandes
• panier moyen
• top 3 produits par CA (par pays)

12.1) CA et panier moyen par pays/canal

WITH ca_commande AS (
  SELECT
    c.commande_id,
    c.client_id,
    c.canal,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM commandes c
  JOIN lignes_commande l ON l.commande_id = c.commande_id
  WHERE c.statut = 'payee'
    AND c.date_commande >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY c.commande_id, c.client_id, c.canal
)
SELECT
  cl.pays,
  cc.canal,
  SUM(cc.ca_ht) AS ca_ht,
  COUNT(*) AS nb_commandes,
  AVG(cc.ca_ht) AS panier_moyen_ht
FROM ca_commande cc
JOIN clients cl ON cl.client_id = cc.client_id
GROUP BY cl.pays, cc.canal
ORDER BY ca_ht DESC;

12.2) Top 3 produits par CA dans chaque pays

WITH ventes AS (
  SELECT
    cl.pays,
    p.produit_id,
    p.libelle,
    SUM(l.quantite * l.prix_unitaire_ht * (1 - l.remise_pct/100.0)) AS ca_ht
  FROM commandes c
  JOIN lignes_commande l ON l.commande_id = c.commande_id
  JOIN produits p ON p.produit_id = l.produit_id
  JOIN clients cl ON cl.client_id = c.client_id
  WHERE c.statut = 'payee'
    AND c.date_commande >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY cl.pays, p.produit_id, p.libelle
),
r AS (
  SELECT
    pays,
    produit_id,
    libelle,
    ca_ht,
    DENSE_RANK() OVER (PARTITION BY pays ORDER BY ca_ht DESC) AS rang
  FROM ventes
)
SELECT
  pays,
  produit_id,
  libelle,
  ca_ht
FROM r
WHERE rang <= 3
ORDER BY pays, ca_ht DESC;

13) Checklist de techniques avancées à maîtriser

• Fenêtrage : ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD, agrégats OVER (PARTITION BY ...)
• Agrégations multi-niveaux : GROUPING SETS, ROLLUP, CUBE
• CTE : structuration, réutilisation, attention à la matérialisation
• Récursif : calendriers, hiérarchies
• Temps : date_trunc, fenêtres glissantes, filtres sargables (compatibles index)
• Optimisation : EXPLAIN ANALYZE, index composites/partiels, vues matérialisées, réécritures de requêtes

14) Pour aller plus loin (pistes pratiques)

1. Ajouter une table de taux de change et convertir le CA multi-devises via jointure temporelle.
2. Mettre en place une table de dimensions (produits, clients, temps) et structurer un schéma en étoile.
3. Tester des stratégies d’indexation :
   • (statut, date_commande) vs index partiel
   • index couvrants (incluant des colonnes) selon le SGBD
4. Comparer une approche « tout à la volée » vs pré-agrégations (vues matérialisées) sur un tableau de bord réel.
5. Définir des métriques robustes : CA net (remboursements), commandes récurrentes, taux de conversion, LTV.

Conclusion

Les requêtes SQL avancées pour l’analyse de données reposent sur quelques piliers : fenêtrage, agrégations multi-niveaux, CTE bien structurées, gestion rigoureuse du temps, et optimisation guidée par les plans d’exécution. En combinant ces techniques, vous pouvez produire des analyses fiables, rapides et maintenables, même sur des volumes importants, tout en gardant un SQL lisible et industrialisable.