Préparer le jeu de données ventes Dakar

Ce que vous saurez faire à la fin

1. Maîtriser les Common Table Expressions (CTE) pour découper des requêtes complexes en blocs lisibles et réutilisables.
2. Utiliser les window functions (ROW_NUMBER, RANK, LAG, LEAD, SUM OVER) pour calculer classements, cumuls et variations sans GROUP BY.
3. Combiner INNER, LEFT, RIGHT, FULL et CROSS JOIN pour relier 4 à 6 tables sans dupliquer de lignes.
4. Écrire des CTE récursives pour explorer une hiérarchie d’employés, une arborescence de catégories ou un graphe de parrainage.
5. Optimiser des requêtes lentes de 12 secondes vers 200 millisecondes en lisant le plan d’exécution et en posant les bons index.

Durée : 4h30. Pré-requis : PostgreSQL 14+ ou MySQL 8+ installé en local, DBeaver ou pgAdmin, jeu de données ventes_dakar.sql (fourni en téléchargement gratuit), bases SQL niveau intermédiaire (SELECT, GROUP BY, sous-requêtes), 1 PME sénégalaise comme cas pratique. Coût : 0 FCFA, tout est open source.

Étape 1 — Préparer le jeu de données ventes_dakar

Pour pratiquer dans un contexte réaliste, nous travaillons sur une base d’une supérette de Dakar avec 4 tables : clients (1 200 lignes), produits (350 lignes), ventes (45 000 lignes sur 2024-2025), boutiques (12 boutiques entre Plateau, Almadies, Pikine, Thiès). Téléchargez ventes_dakar.sql depuis le dépôt de formation, puis chargez-le.

createdb ventes_dakar
psql -d ventes_dakar -f ventes_dakar.sql
psql -d ventes_dakar -c "SELECT COUNT(*) FROM ventes;"
# Attendu : 45 218

Les montants sont en FCFA, les dates couvrent du 1er janvier 2024 au 31 décembre 2025. Cette base sert de fil rouge pour tous les exercices.

Étape 2 — Première CTE pour clarifier une requête

Une CTE remplace une sous-requête imbriquée par un bloc nommé en tête de requête. Lisibilité multipliée par 3, et le moteur l’optimise comme une vue temporaire. Voici comment isoler les ventes 2025 puis les agréger par boutique.

WITH ventes_2025 AS (
    SELECT boutique_id, montant_fcfa, date_vente
    FROM ventes
    WHERE date_vente >= '2025-01-01'
      AND date_vente <  '2026-01-01'
)
SELECT b.nom_boutique,
       COUNT(*)             AS nb_ventes,
       SUM(montant_fcfa)    AS ca_total_fcfa,
       AVG(montant_fcfa)::INT AS panier_moyen_fcfa
FROM ventes_2025 v
JOIN boutiques  b ON b.id = v.boutique_id
GROUP BY b.nom_boutique
ORDER BY ca_total_fcfa DESC;

La CTE ventes_2025 est lue une seule fois, puis réutilisée. Sur PostgreSQL 14+, elle est inline par défaut, donc aussi rapide qu’une sous-requête mais infiniment plus lisible.

Étape 3 — Chaîner plusieurs CTE

Vous pouvez empiler les CTE séparées par des virgules. Chaque CTE peut référencer la précédente. Exemple : isoler les top 20% clients (clients VIP), puis calculer leur fréquence d’achat moyenne.

WITH ca_par_client AS (
    SELECT client_id, SUM(montant_fcfa) AS ca_client
    FROM ventes
    GROUP BY client_id
),
seuil_vip AS (
    SELECT PERCENTILE_CONT(0.80) WITHIN GROUP (ORDER BY ca_client) AS seuil
    FROM ca_par_client
),
clients_vip AS (
    SELECT c.client_id, c.ca_client
    FROM ca_par_client c, seuil_vip s
    WHERE c.ca_client >= s.seuil
)
SELECT COUNT(*)                              AS nb_clients_vip,
       SUM(ca_client)                         AS ca_vip_fcfa,
       ROUND(AVG(ca_client))::INT             AS panier_vip_moyen
FROM clients_vip;

Le code se lit de haut en bas comme un raisonnement d’analyste : d’abord le CA par client, ensuite le seuil 80e percentile, ensuite la sélection. Aucune sous-requête imbriquée.

Étape 4 — Première window function : ROW_NUMBER

Une window function applique un calcul sur un groupe de lignes sans agréger. ROW_NUMBER attribue un numéro unique par partition. Cas typique : pour chaque boutique, identifier la vente la plus récente.

SELECT boutique_id, client_id, montant_fcfa, date_vente, num_ligne
FROM (
    SELECT v.*,
           ROW_NUMBER() OVER (
               PARTITION BY boutique_id
               ORDER BY date_vente DESC
           ) AS num_ligne
    FROM ventes v
) t
WHERE num_ligne = 1;

OVER (PARTITION BY … ORDER BY …) découpe les lignes en groupes par boutique, puis numérote chaque groupe par date décroissante. La ligne num_ligne = 1 correspond à la dernière vente de chaque boutique.

Étape 5 — RANK et DENSE_RANK pour des classements

ROW_NUMBER donne un numéro unique. RANK saute des positions en cas d’égalité. DENSE_RANK ne saute pas. Classement des produits les plus vendus en novembre 2025.

SELECT p.nom_produit,
       SUM(v.quantite) AS qte_vendue,
       RANK()       OVER (ORDER BY SUM(v.quantite) DESC) AS rang_rank,
       DENSE_RANK() OVER (ORDER BY SUM(v.quantite) DESC) AS rang_dense
FROM ventes v
JOIN produits p ON p.id = v.produit_id
WHERE v.date_vente BETWEEN '2025-11-01' AND '2025-11-30'
GROUP BY p.nom_produit
ORDER BY qte_vendue DESC
LIMIT 10;

Si deux produits ex-aequo au rang 3, RANK passe à 5 ensuite, DENSE_RANK reste à 4. Pour primer les meilleurs vendeurs avec un bonus, DENSE_RANK est plus juste.

Étape 6 — LAG et LEAD pour comparer des lignes voisines

LAG accède à la ligne précédente, LEAD à la suivante. Indispensable pour calculer une variation jour sur jour ou un délai entre deux achats du même client.

SELECT date_vente,
       SUM(montant_fcfa) AS ca_jour,
       LAG(SUM(montant_fcfa)) OVER (ORDER BY date_vente) AS ca_veille,
       SUM(montant_fcfa) - LAG(SUM(montant_fcfa)) OVER (ORDER BY date_vente)
           AS variation_fcfa,
       ROUND(
           100.0 * (SUM(montant_fcfa) - LAG(SUM(montant_fcfa)) OVER (ORDER BY date_vente))
           / NULLIF(LAG(SUM(montant_fcfa)) OVER (ORDER BY date_vente), 0)
       , 2) AS variation_pct
FROM ventes
WHERE date_vente >= '2025-11-01'
GROUP BY date_vente
ORDER BY date_vente;

Vous obtenez le CA quotidien, le CA de la veille, l’écart en FCFA et l’écart en pourcentage. Parfait pour un dashboard alerte qui ping le directeur quand le CA chute de plus de 20%.

Étape 7 — Cumul glissant avec SUM OVER

SUM OVER calcule un total mobile sans GROUP BY. Sur la même ligne vous obtenez la valeur du jour ET le cumul depuis le début du mois. Très utile pour les rapports de gestion.

WITH ca_journalier AS (
    SELECT date_vente, SUM(montant_fcfa) AS ca_jour
    FROM ventes
    WHERE date_vente BETWEEN '2025-12-01' AND '2025-12-31'
    GROUP BY date_vente
)
SELECT date_vente,
       ca_jour,
       SUM(ca_jour) OVER (ORDER BY date_vente) AS ca_cumule_mois,
       AVG(ca_jour) OVER (
           ORDER BY date_vente
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       )::INT AS moy_mobile_7j
FROM ca_journalier
ORDER BY date_vente;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW définit une fenêtre glissante de 7 jours. La moyenne mobile lisse les pics et donne une tendance fiable, exactement comme les indicateurs boursiers.

Étape 8 — INNER JOIN, LEFT JOIN, FULL JOIN expliqués

INNER JOIN ne garde que les lignes présentes des deux côtés. LEFT JOIN garde toutes les lignes de gauche, NULL à droite si manquantes. FULL JOIN garde tout. Cas réel : trouver les clients qui n’ont jamais acheté en 2025.

-- Clients qui n'ont rien acheté en 2025 (clients dormants)
SELECT c.id, c.nom_client, c.telephone
FROM clients c
LEFT JOIN ventes v
       ON v.client_id   = c.id
      AND v.date_vente >= '2025-01-01'
WHERE v.id IS NULL;

-- Boutiques qui n'ont aucun produit en stock (jointure FULL)
SELECT b.nom_boutique, p.nom_produit
FROM boutiques b
FULL JOIN stock s ON s.boutique_id = b.id
FULL JOIN produits p ON p.id = s.produit_id
WHERE s.quantite IS NULL OR s.quantite = 0;

La condition de date dans le ON (et non WHERE) est cruciale : sinon LEFT JOIN se comporte comme INNER JOIN. Erreur classique qui invalide 80% des analyses de churn.

Étape 9 — Auto-jointure sur la même table

Joindre une table à elle-même résout des problèmes hiérarchiques. Exemple : afficher chaque employé avec le nom de son manager.

SELECT e.id_employe,
       e.nom_complet           AS employe,
       e.poste,
       m.nom_complet           AS manager,
       m.poste                 AS poste_manager
FROM employes e
LEFT JOIN employes m ON m.id_employe = e.manager_id
ORDER BY m.nom_complet, e.nom_complet;

L’employé sans manager (le PDG) apparaît avec NULL. Un LEFT JOIN garantit qu’il n’est pas exclu de la liste.

Étape 10 — CTE récursive pour explorer une hiérarchie

Une CTE récursive se référence elle-même. Utilité : descendre toute l’arborescence d’un manager, ou remonter une chaîne de parrainage. Ici, tous les subordonnés directs et indirects du directeur Dakar.

WITH RECURSIVE arbre_equipe AS (
    -- Cas de base : le directeur
    SELECT id_employe, nom_complet, manager_id, 0 AS niveau
    FROM employes
    WHERE nom_complet = 'Aliou Sow'

    UNION ALL

    -- Cas récursif : les enfants des lignes déjà trouvées
    SELECT e.id_employe, e.nom_complet, e.manager_id, a.niveau + 1
    FROM employes e
    JOIN arbre_equipe a ON e.manager_id = a.id_employe
)
SELECT REPEAT('  ', niveau) || nom_complet AS hierarchie, niveau
FROM arbre_equipe
ORDER BY niveau, nom_complet;

Le mot-clé RECURSIVE est obligatoire avec PostgreSQL. UNION ALL combine la ligne de base avec les itérations. Le moteur s’arrête quand aucune nouvelle ligne n’apparaît.

Étape 11 — NTILE pour segmenter par quartile

NTILE(n) divise les lignes en n groupes équilibrés. Indispensable pour segmenter une clientèle en quartiles ou déciles selon le CA.

WITH ca_par_client AS (
    SELECT client_id, SUM(montant_fcfa) AS ca_client
    FROM ventes
    WHERE date_vente >= '2025-01-01'
    GROUP BY client_id
)
SELECT client_id,
       ca_client,
       NTILE(4) OVER (ORDER BY ca_client DESC) AS quartile
FROM ca_par_client
ORDER BY ca_client DESC;

Le quartile 1 = top 25% du CA (à fidéliser), quartile 4 = bottom 25% (à réactiver ou laisser partir). Logique RFM appliquée en 8 lignes de SQL pur.

Étape 12 — Lire le plan d’exécution EXPLAIN ANALYZE

Une requête lente devient compréhensible avec EXPLAIN ANALYZE. PostgreSQL affiche chaque étape, son coût estimé et son temps réel.

EXPLAIN (ANALYZE, BUFFERS)
SELECT b.nom_boutique, SUM(v.montant_fcfa) AS ca
FROM ventes v
JOIN boutiques b ON b.id = v.boutique_id
WHERE v.date_vente >= '2025-01-01'
GROUP BY b.nom_boutique;

HashAggregate  (cost=2150.00..2152.50 rows=12 width=42)
                (actual time=125.342..125.358 rows=12 loops=1)
  ->  Hash Join  (cost=1.27..1875.00 rows=22000 width=18)
                  (actual time=0.150..98.221 rows=22318 loops=1)
        Hash Cond: (v.boutique_id = b.id)
        ->  Seq Scan on ventes v  (cost=0.00..1620.00 rows=22000 width=14)
              Filter: (date_vente >= '2025-01-01')
              Rows Removed by Filter: 22900

Seq Scan = lecture séquentielle de toute la table. Avec 45 000 lignes c’est tolérable, avec 50 millions c’est catastrophique. Index manquant sur date_vente !

Étape 13 — Créer les bons index

Un index B-tree sur les colonnes de filtre et de jointure transforme un Seq Scan en Index Scan. Gain typique : x10 à x100.

-- Index sur la date (filtre WHERE)
CREATE INDEX idx_ventes_date ON ventes(date_vente);

-- Index sur la clé étrangère (jointure)
CREATE INDEX idx_ventes_boutique ON ventes(boutique_id);

-- Index composite si filtre + groupement combinés
CREATE INDEX idx_ventes_boutique_date
       ON ventes(boutique_id, date_vente);

-- Mettre à jour les stats du planner
ANALYZE ventes;

Re-lancez EXPLAIN ANALYZE : Seq Scan devient Index Scan, le temps passe de 125 ms à 8 ms. Sur une table à 50 millions de lignes, on passe de 12 secondes à 200 millisecondes. Magie.

Étape 14 — Combiner CTE, window function et JOIN

Exercice final : top 3 produits par boutique en novembre 2025, avec part du CA boutique. Trois CTE chaînées, deux window functions, deux jointures.

WITH ventes_nov AS (
    SELECT boutique_id, produit_id, SUM(montant_fcfa) AS ca_produit
    FROM ventes
    WHERE date_vente BETWEEN '2025-11-01' AND '2025-11-30'
    GROUP BY boutique_id, produit_id
),
classement AS (
    SELECT v.*,
           SUM(ca_produit) OVER (PARTITION BY boutique_id) AS ca_boutique,
           ROW_NUMBER() OVER (
               PARTITION BY boutique_id
               ORDER BY ca_produit DESC
           ) AS rang
    FROM ventes_nov v
)
SELECT b.nom_boutique,
       p.nom_produit,
       c.ca_produit,
       c.ca_boutique,
       ROUND(100.0 * c.ca_produit / c.ca_boutique, 1) AS part_pct
FROM classement c
JOIN boutiques b ON b.id = c.boutique_id
JOIN produits  p ON p.id = c.produit_id
WHERE c.rang <= 3
ORDER BY b.nom_boutique, c.rang;

Cette requête remplace 3 scripts Excel d’une heure de travail. Elle s’exécute en 40 millisecondes et donne au directeur la photo exacte des best-sellers par point de vente.

Erreurs courantes à éviter

Mettre la condition de date dans WHERE après LEFT JOIN. Cela transforme silencieusement la jointure en INNER JOIN et fait disparaître les clients dormants. Toujours mettre la condition dans le ON.

Oublier ORDER BY dans une window function. ROW_NUMBER sans ORDER BY donne un résultat non déterministe : la même requête peut renvoyer des numéros différents à chaque exécution.

Confondre RANK et DENSE_RANK. RANK saute des positions en cas d’égalité (1, 2, 2, 4), DENSE_RANK ne saute pas (1, 2, 2, 3). Le mauvais choix fausse les bonus payés en fin de mois.

Indexer toutes les colonnes par précaution. Chaque index ralentit les INSERT, UPDATE et DELETE de 5 à 10%. N’indexer que les colonnes utilisées dans WHERE, JOIN ou ORDER BY fréquemment.

Lancer une CTE récursive sans condition d’arrêt claire. Une boucle infinie sur 5 millions de lignes plante la base et bloque les autres connexions. Toujours tester sur un petit échantillon d’abord.

Utiliser COUNT(*) sur une sous-requête imbriquée 4 fois. Une CTE rend le code 5 fois plus lisible et permet à un collègue de comprendre la logique sans demander d’explications.

Ne pas faire ANALYZE après un CREATE INDEX. Le planner garde des statistiques obsolètes et continue d’utiliser un Seq Scan. Toujours ANALYZE table_modifiee après changement structurel.

Checklist finale avant mise en production

1. Toutes les sous-requêtes imbriquées de plus de 2 niveaux sont remplacées par des CTE nommées explicitement.
2. Chaque window function a un OVER (PARTITION BY … ORDER BY …) explicite, jamais OVER () vide.
3. Les LEFT JOIN gardent leur condition de date dans le ON, jamais dans le WHERE.
4. EXPLAIN ANALYZE confirme l’usage d’un Index Scan sur les colonnes de filtre et de jointure.
5. Aucune requête de production ne dépasse 500 millisecondes sur le volume actuel et 2 secondes sur le volume projeté à 3 ans.
6. Les CTE récursives ont une profondeur maximale testée (LIMIT, ou condition WHERE niveau < 20).
7. Les index composites respectent l’ordre des colonnes les plus sélectives en premier.
8. VACUUM ANALYZE est planifié toutes les nuits via cron sur les tables à fort volume.
9. Les requêtes lentes sont logguées (log_min_duration_statement = 500 dans postgresql.conf) et revues chaque semaine.
10. Une copie anonymisée de la base est utilisée pour les tests de nouvelles requêtes avant production.