SQL avancé et MongoDB — Cours comparatif

Le schéma de démonstration

Un système e-commerce simple : utilisateurs, commandes, produits et lignes de commande.

SQL — création du schéma

-- Utilisateurs
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    country VARCHAR(50) DEFAULT 'FR',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Catégories de produits
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES categories(id)  -- Auto-référence pour hierarchie
);

-- Produits
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    category_id INT REFERENCES categories(id)
);

-- Commandes
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW(),
    shipped_at TIMESTAMP
);

-- Lignes de commande
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(id),
    product_id INT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL  -- Prix au moment de l'achat
);

1. Jointures avancées

📖 Terme : JOIN (INNER/LEFT/SELF)

Définition : Fusion de données provenant de plusieurs tables en une seule requête, basée sur une clé de relation.

But : Récupérer des données liées sans faire plusieurs requêtes séquentielles.

Pourquoi ici : INNER JOIN retourne seulement les lignes ayant une correspondance dans les deux tables. LEFT JOIN retourne tous les lignes de la table gauche, même sans correspondance à droite (remplissage avec NULL).

INNER JOIN : Seules les commandes liées à des utilisateurs (user_id existe) sont retournées. LEFT JOIN : Tous les utilisateurs sont retournés, même ceux sans commandes (COUNT=0, total=0).

SQL — jointures

-- ── INNER JOIN : commandes avec détail utilisateur ──
SELECT
    o.id AS order_id,
    u.name AS customer,
    u.email,
    o.status,
    o.created_at,
    COUNT(oi.id) AS items_count,
    SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY o.id, u.name, u.email, o.status, o.created_at
ORDER BY total DESC;

-- ── LEFT JOIN : utilisateurs AVEC ou SANS commandes ──
SELECT
    u.name,
    u.email,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS lifetime_value
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'completed'
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY u.id, u.name, u.email
ORDER BY lifetime_value DESC;

-- ── SELF JOIN : catégories parent/enfant ──
SELECT
    child.name AS sous_categorie,
    parent.name AS categorie_parent
FROM categories child
LEFT JOIN categories parent ON child.parent_id = parent.id
ORDER BY parent.name, child.name;

2. Agrégations et window functions

📖 Terme : Fonction de fenêtrage (Window Function)

Définition : Fonction qui opère sur un sous-ensemble de lignes (fenêtre) défini par PARTITION BY, tout en conservant les lignes individuelles (contrairement à GROUP BY qui les agrège).

But : Calculer des agrégations partielles (rank, cumulative, previous value) sans perdre les détails ligne par ligne.

Pourquoi ici : PARTITION BY divise les lignes en groupes (ex: par catégorie), ORDER BY les trie dans chaque partition. RANK() classe par ventes décroissantes, LAG() accède à la ligne précédente.

RANK() vs ROW_NUMBER() : RANK() donne le même rang aux ex-aequo (1, 1, 3), ROW_NUMBER() donne des rangs uniques (1, 2, 3). PARTITION BY c.id crée des fenêtres séparées par catégorie.

SQL — window functions (PostgreSQL)

-- ── Classement des produits les plus vendus par catégorie ──
SELECT
    c.name AS category,
    p.name AS product,
    SUM(oi.quantity) AS total_sold,
    -- RANK() — même rang pour les ex-aequo
    RANK() OVER (
        PARTITION BY c.id
        ORDER BY SUM(oi.quantity) DESC
    ) AS rank_in_category,
    -- Chiffre d'affaires cumulé dans la catégorie
    SUM(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY c.id
        ORDER BY SUM(oi.quantity) DESC
    ) AS cumulative_revenue
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN order_items oi ON oi.product_id = p.id
GROUP BY c.id, c.name, p.id, p.name
ORDER BY c.name, rank_in_category;

-- ── Évolution du CA mois par mois avec variation ──
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.created_at) AS month,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status = 'completed'
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100, 2
    ) AS growth_pct
FROM monthly_revenue
ORDER BY month;

3. CTEs récursives

📖 Terme : CTE (Common Table Expression) récursive

Définition : Requête nommée (WITH clause) qui s'appelle elle-même, permettant de traiter des structures hiérarchiques ou graphiques.

But : Parcourir des arbres de données (catégories parent/enfant, org charts) sans code applicatif complexe.

Pourquoi ici : Une CTE récursive a deux parties : le cas de base (catégories racines) et le cas récursif (enfants de chaque parent).

Cas de base : SELECT toutes les catégories où parent_id IS NULL (racines), depth=0. Cas récursif : JOIN la CTE avec categories pour trouver les enfants, incrémenter depth. La récursion s'arrête quand aucune nouvelle ligne n'est trouvée.

SQL — CTE récursive pour hiérarchie

-- ── Parcourir une hiérarchie de catégories ──
WITH RECURSIVE category_tree AS (
    -- Cas de base : catégories racines (sans parent)
    SELECT id, name, parent_id, 0 AS depth, name::text AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Cas récursif : catégories enfants
    SELECT c.id, c.name, c.parent_id, ct.depth + 1,
           (ct.path || ' > ' || c.name)::text
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    REPEAT('  ', depth) || name AS indented_name,
    depth,
    path
FROM category_tree
ORDER BY path;

4. Index et performances

📖 Terme : Index (B-tree / GIN)

Définition : Structure de données optimisée pour la recherche rapide. B-tree indexe des colonnes scalaires, GIN indexe des types complexes (JSONB, tableaux, texte full-text).

But : Accélérer les filtres (WHERE), sans faire un Seq Scan (balayage complet de la table).

Pourquoi ici : Sans index, PostgreSQL scanne toutes les lignes (Seq Scan). Avec index, il utilise une structure arborescente pour chercher les lignes matching en O(log n).

GIN = Generalized Inverted Index, idéal pour full-text search : to_tsvector() tokenize le texte, GIN indexe les tokens, @@ recherche les tokens matching. Index partiel : WHERE status IN ('pending', ...) réduit la taille en excluant les commandes complètes (rarement filtrées).

SQL — indexation stratégique

-- ── Analyser une requête lente ──
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'pending';

-- Avant index : Seq Scan cost=0..1234 rows=15000

-- ── Créer un index composé ──
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Index partiel : seulement les commandes non-traitées
CREATE INDEX idx_orders_pending ON orders(user_id)
    WHERE status IN ('pending', 'processing');

-- Après index : Index Scan cost=0..8 rows=3

-- ── Index pour recherche full-text ──
CREATE INDEX idx_products_search ON products
    USING GIN(to_tsvector('french', name));

SELECT name, price
FROM products
WHERE to_tsvector('french', name) @@ to_tsquery('french', 'ordinateur & portable')
ORDER BY ts_rank(to_tsvector('french', name), to_tsquery('french', 'ordinateur & portable')) DESC;

-- ── Index unique partiel ──
CREATE UNIQUE INDEX idx_users_active_email ON users(email)
    WHERE deleted_at IS NULL;  -- Email unique seulement pour les comptes actifs

5. Le même modèle en MongoDB

📖 Terme : Dénormalisation

Définition : Copie intentionnelle de données liées dans un même document, au lieu de les référencer.

But : Éviter les JOINs coûteux (qui n'existent pas en MongoDB), réduire les requêtes, améliorer la performance en lecture.

Pourquoi ici : Compromis : dénormalisation favorise la lecture (une requête = un document complet) mais complique la mise à jour (mettre à jour user.name dans toutes les commandes si le nom change).

📖 Terme : Pipeline d'agrégation MongoDB

Définition : Série d'étapes de transformation appliquées séquentiellement sur les documents.

But : Filtrer, grouper, transformer et trier les données sans client-side processing.

Pourquoi ici : Chaque étape $match, $group, $sort produit un stream de documents alimentant l'étape suivante.

En MongoDB, on dénormalise les données pour éviter les jointures coûteuses :

MongoDB — modèle dénormalisé

// ── Document Order (commande complète) ──
{
  _id: ObjectId("..."),
  // Données utilisateur copiées au moment de l'achat
  user: {
    id: "user_42",
    name: "Alderi KAMTCHOUA",
    email: "alderi@example.com"
  },
  status: "completed",
  // Produits inclus dans la commande (pas de jointure nécessaire)
  items: [
    {
      product: {
        id: "prod_1",
        name: "MacBook Pro 16\"",
        sku: "MBP-16-M3"
      },
      quantity: 1,
      unitPrice: 2499.99
    },
    {
      product: { id: "prod_5", name: "Magic Mouse" },
      quantity: 2,
      unitPrice: 79.99
    }
  ],
  totalAmount: 2659.97,
  createdAt: ISODate("2025-01-15T10:30:00Z")
}

// ── Agrégation MongoDB : CA par mois ──
db.orders.aggregate([
  { $match: { status: "completed" } },
  {
    $group: {
      _id: {
        year: { $year: "$createdAt" },
        month: { $month: "$createdAt" }
      },
      totalRevenue: { $sum: "$totalAmount" },
      orderCount: { $sum: 1 },
      avgOrderValue: { $avg: "$totalAmount" }
    }
  },
  { $sort: { "_id.year": -1, "_id.month": -1 } }
]);

// ── Index MongoDB ──
db.orders.createIndex({ "user.id": 1, status: 1 });
db.orders.createIndex({ createdAt: -1 });
db.orders.createIndex({ "items.product.id": 1 });

Dénormalisation : user et items sont imbriqués dans orders, pas de jointures. Au moment de créer la commande, on copie user.name et items[].product.name. Si l'utilisateur met à jour son nom, les vieilles commandes gardent le nom original (snapshot au moment de l'achat).

SQL vs NoSQL : quand choisir quoi ?

📖 Terme : Clé étrangère (Foreign Key)

Définition : Colonne d'une table qui référence la clé primaire d'une autre table, pour enforcer l'intégrité référentielle.

But : Garantir que chaque order.user_id correspond à un user.id existant, éviter les données orphelines.

Pourquoi ici : SQL impose les clés étrangères au niveau base de données. MongoDB, sans schéma obligatoire, ne peut pas enforcer cela (responsabilité du code applicatif).

Comparatif

# PostgreSQL / SQL
✅ Données structurées avec relations complexes
✅ Intégrité des données (ACID, contraintes, clés étrangères)
✅ Rapports et analytiques avec JOINs et agrégations
✅ Transactions multi-tables
✅ Schéma stable et bien défini
❌ Scalabilité horizontale complexe
❌ Schéma rigide (migrations nécessaires)

# MongoDB / NoSQL
✅ Données semi-structurées (documents JSON flexibles)
✅ Scalabilité horizontale native (sharding)
✅ Développement rapide (pas de migrations)
✅ Hiérarchies et listes imbriquées naturelles
✅ Haute disponibilité avec replica sets
❌ Pas de jointures natives → dénormalisation
❌ Pas d'ACID multi-documents (depuis v4.0 seulement)

SQL avancé et comparatif avec MongoDB