Les données d'un projet ne vivent pas éternellement dans des fichiers CSV posés sur un disque. Dès qu'elles sont mises à jour, partagées, ou dépassent quelques gigaoctets, il faut une base de données. Ce module part du modèle relationnel — ce qu'est une table, une clé, une jointure — puis va jusqu'aux fonctions de fenêtre et aux plans d'exécution, avant d'expliquer ce que les familles NoSQL apportent et ce qu'elles abandonnent en échange.

1. Pourquoi une base de données

Un fichier CSV convient parfaitement à un jeu de données figé qu'une seule personne analyse. Il cesse de convenir dès que l'une de ces situations apparaît :

• Plusieurs écrivains — deux scripts qui écrivent en même temps corrompent le fichier. Une base gère les accès concurrents.
• Mises à jour partielles — modifier une ligne au milieu d'un CSV de 10 Go impose de le réécrire entièrement.
• Recherche sélective — trouver les commandes d'un client demande de lire tout le fichier ; une base indexée y accède directement.
• Cohérence — rien n'empêche un CSV de contenir deux fois le même identifiant, ou une référence vers un client qui n'existe pas. Une base peut le garantir.
• Reprise après incident — si le programme meurt au milieu d'une écriture, une base revient à un état cohérent ; un fichier reste à moitié écrit.

SQL est par ailleurs un langage déclaratif : on décrit le résultat voulu, et le moteur choisit l'algorithme — parcours complet ou index, ordre des jointures, méthode de tri. C'est un renversement par rapport à Python, où l'on décrit la marche à suivre, et cela demande un temps d'adaptation : optimiser une requête SQL consiste à donner au moteur les moyens de bien choisir, pas à lui dicter ses étapes.

2. Le modèle relationnel

Une table est un tableau : des colonnes typées, des lignes. Chaque table a une clé primaire, une colonne (ou un groupe) qui identifie chaque ligne de façon unique. Une clé étrangère est une colonne qui pointe vers la clé primaire d'une autre table : c'est ce qui relie les tables entre elles.

CREATE TABLE clients (
    client_id    INTEGER PRIMARY KEY,          -- identifiant unique
    nom          TEXT NOT NULL,                -- valeur obligatoire
    email        TEXT UNIQUE,                  -- pas deux fois le même
    ville        TEXT,
    cree_le      TIMESTAMP DEFAULT now()
);

CREATE TABLE commandes (
    commande_id  INTEGER PRIMARY KEY,
    client_id    INTEGER NOT NULL REFERENCES clients(client_id),   -- clé étrangère
    date_cmd     DATE NOT NULL,
    montant      NUMERIC(10, 2) CHECK (montant >= 0)               -- contrainte métier
);

CREATE INDEX idx_cmd_client ON commandes(client_id);               -- voir section 8

Deux garanties sont posées ici et méritent d'être soulignées. La clé étrangère interdit d'insérer une commande pour un client inexistant, et empêche de supprimer un client qui a des commandes : c'est l'intégrité référentielle, impossible à obtenir avec des fichiers. La contrainte CHECK refuse un montant négatif au niveau de la base, donc quel que soit le programme qui écrit — bien plus robuste qu'une validation dans un seul script.

La normalisation consiste à ne stocker chaque information qu'à un seul endroit. Si la ville du client était recopiée dans chaque commande, un déménagement obligerait à mettre à jour des milliers de lignes, avec la certitude d'en oublier. La règle pratique : une information qui décrit un client vit dans clients, une information qui décrit une commande vit dans commandes. En analytique, on dénormalise parfois volontairement — on recopie pour éviter des jointures coûteuses — mais c'est une optimisation consciente, pas un point de départ.

3. Les bases de SQL

SELECT   ville, montant                 -- 5. colonnes renvoyées
FROM     commandes                      -- 1. table de départ
JOIN     clients USING (client_id)      -- 2. tables associées
WHERE    date_cmd >= DATE '2025-01-01'  -- 3. filtre sur les LIGNES
ORDER BY montant DESC                   -- 6. tri
LIMIT    10;                            -- 7. nombre de lignes renvoyées

L'ordre d'écriture n'est pas l'ordre d'exécution : le moteur commence par FROM et WHERE, agrège ensuite, et n'applique SELECT puis ORDER BY qu'à la fin. C'est ce qui explique une erreur fréquente — on ne peut pas utiliser dans WHERE un alias défini dans SELECT, puisqu'il n'existe pas encore à ce moment-là.

WHERE montant BETWEEN 100 AND 500          -- bornes incluses
WHERE ville IN ('Grenoble', 'Lyon')        -- appartenance à une liste
WHERE email LIKE '%@exemple.fr'            -- motif ; % = n'importe quelle suite
WHERE email ILIKE '%@Exemple.FR'           -- idem, insensible à la casse (PostgreSQL)
WHERE ville IS NULL                        -- valeur absente : voir section 7
WHERE date_cmd >= now() - INTERVAL '30 days'

-- expressions conditionnelles et conversions
SELECT CASE WHEN montant > 1000 THEN 'grand'
            WHEN montant > 100  THEN 'moyen'
            ELSE 'petit' END AS segment,
       CAST(montant AS INTEGER)  AS montant_entier,
       COALESCE(ville, 'inconnue') AS ville     -- remplace NULL par une valeur
FROM commandes;

4. Jointures

Une jointure associe les lignes de deux tables selon une condition — presque toujours l'égalité d'une clé étrangère et d'une clé primaire. Quatre variantes couvrent tous les cas :

clients            commandes
id | nom           client_id | montant
1  | Alice         1         | 100
2  | Bob           1         | 250
3  | Chloé         4         | 80        ← client 4 : inexistant dans clients

INNER JOIN  → uniquement les correspondances des DEUX côtés
   Alice|100, Alice|250                        (Bob et Chloé disparaissent)

LEFT JOIN   → toutes les lignes de GAUCHE, complétées par NULL à droite
   Alice|100, Alice|250, Bob|NULL, Chloé|NULL  (utile : "qui n'a rien commandé ?")

FULL OUTER JOIN → l'union des deux, avec NULL des deux côtés
   Alice|100, Alice|250, Bob|NULL, Chloé|NULL, NULL|80

CROSS JOIN  → le produit cartésien : chaque ligne avec chaque ligne
   3 × 3 = 9 lignes   (presque toujours le signe d'une condition oubliée)

-- les clients sans aucune commande
SELECT c.nom
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.client_id
WHERE o.commande_id IS NULL;            -- aucune ligne appariée à droite

-- même résultat, souvent plus lisible et aussi efficace
SELECT nom FROM clients c
WHERE NOT EXISTS (SELECT 1 FROM commandes o WHERE o.client_id = c.client_id);

5. Agrégations et fonctions de fenêtre

Un GROUP BY écrase les lignes : une ligne par groupe. C'est ce qu'on veut pour un total par client, pas pour comparer chaque commande à la moyenne de son client.

SELECT client_id,
       COUNT(*)        AS n_commandes,
       SUM(montant)    AS ca_total,
       AVG(montant)    AS panier_moyen,
       MAX(date_cmd)   AS derniere_commande
FROM commandes
WHERE date_cmd >= DATE '2025-01-01'     -- filtre AVANT agrégation (sur les lignes)
GROUP BY client_id
HAVING SUM(montant) > 1000              -- filtre APRÈS agrégation (sur les groupes)
ORDER BY ca_total DESC;

Une fonction de fenêtre calcule une agrégation sans écraser : chaque ligne conserve son identité tout en « voyant » son groupe. C'est l'outil décisif pour le feature engineering temporel (module 12), car il permet de calculer moyennes mobiles, rangs et décalages directement dans la base, sur des volumes qui ne tiendraient pas en mémoire.

SELECT
    client_id,
    date_cmd,
    montant,

    -- rang de la commande dans l'historique du client
    ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_cmd) AS n_commande,
    --           └─ OVER : "voici la fenêtre"
    --              └─ PARTITION BY : découpe en groupes (comme GROUP BY, sans écraser)
    --                 └─ ORDER BY : ordre À L'INTÉRIEUR de la fenêtre

    -- montant de la commande précédente du même client : feature de récence
    LAG(montant) OVER (PARTITION BY client_id ORDER BY date_cmd) AS montant_precedent,

    -- écart en jours depuis la commande précédente
    date_cmd - LAG(date_cmd) OVER (PARTITION BY client_id ORDER BY date_cmd) AS delai,

    -- moyenne glissante sur les 3 dernières commandes, celle-ci incluse
    AVG(montant) OVER (
        PARTITION BY client_id ORDER BY date_cmd
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moyenne_3,

    -- part de cette commande dans le total du client
    montant / SUM(montant) OVER (PARTITION BY client_id) AS part
FROM commandes;

La clause ROWS BETWEEN … AND CURRENT ROW n'est pas cosmétique : elle garantit qu'aucune information future n'entre dans la feature. C'est la version SQL de la protection contre la fuite temporelle (module 12) — sans elle, la fenêtre par défaut inclurait toutes les lignes du groupe, y compris celles qui viennent après.

ROW_NUMBER()    numérote 1, 2, 3… sans ex æquo
RANK()          ex æquo au même rang, puis saut (1, 2, 2, 4)
DENSE_RANK()    ex æquo au même rang, sans saut (1, 2, 2, 3)
LAG(x, n)       valeur n lignes avant (défaut n=1)
LEAD(x, n)      valeur n lignes après  → à n'utiliser qu'en analyse, jamais en feature
FIRST_VALUE(x)  première valeur de la fenêtre
NTILE(4)        découpe en 4 groupes de taille égale (quartiles)
SUM/AVG/COUNT   les agrégats classiques, en version fenêtrée

6. CTE : structurer une requête

Une Common Table Expression (WITH) nomme une étape intermédiaire. Une requête de 80 lignes avec quatre sous-requêtes imbriquées devient quatre blocs lisibles, testables séparément — il suffit de remplacer la requête finale par un SELECT * FROM etape_2 pour inspecter un résultat intermédiaire.

WITH commandes_2025 AS (
    SELECT * FROM commandes WHERE date_cmd >= DATE '2025-01-01'
),
agregats_client AS (
    SELECT client_id,
           COUNT(*)           AS n_commandes,
           SUM(montant)       AS ca_total,
           MAX(date_cmd)      AS derniere_commande
    FROM commandes_2025
    GROUP BY client_id
),
segments AS (
    SELECT *,
           NTILE(4) OVER (ORDER BY ca_total) AS quartile_ca
    FROM agregats_client
)
SELECT c.nom, s.n_commandes, s.ca_total, s.quartile_ca,
       CURRENT_DATE - s.derniere_commande AS jours_depuis_achat
FROM segments s
JOIN clients c USING (client_id)
ORDER BY s.ca_total DESC;

7. Le piège des NULL

NULL ne signifie ni « zéro », ni « chaîne vide » : il signifie inconnu. Et toute comparaison avec un inconnu donne un inconnu, traité comme faux.

WHERE montant != 100        -- EXCLUT les lignes où montant est NULL (rarement voulu)
WHERE montant = NULL        -- ne renvoie JAMAIS rien : il faut IS NULL

SELECT COUNT(*),            -- compte TOUTES les lignes
       COUNT(montant),      -- compte seulement les montants NON NULL
       AVG(montant),        -- moyenne des NON NULL
       SUM(montant) / COUNT(*)   -- ≠ AVG(montant) s'il y a des NULL !
FROM commandes;

-- NULL se propage dans les calculs
SELECT 100 + NULL;          -- NULL, pas 100
SELECT COALESCE(montant, 0) + 100;   -- la bonne façon : remplacer d'abord

-- après un LEFT JOIN, les colonnes de droite peuvent être NULL : penser à COALESCE
SELECT c.nom, COALESCE(SUM(o.montant), 0) AS ca
FROM clients c LEFT JOIN commandes o USING (client_id)
GROUP BY c.nom;

8. Index et plans d'exécution

Sans index, répondre à WHERE client_id = 42 oblige le moteur à lire toute la table, ligne par ligne. Un index est une structure annexe — le plus souvent un arbre équilibré, un B-tree — qui range les valeurs d'une colonne dans un ordre permettant la recherche dichotomique : on passe d'un coût proportionnel au nombre de lignes à un coût proportionnel à son logarithme. Sur dix millions de lignes, c'est la différence entre lire dix millions d'enregistrements et en lire vingt-trois.

L'index n'est pas gratuit : il occupe de l'espace disque et ralentit les écritures, puisque chaque insertion doit le mettre à jour. On indexe donc les colonnes de jointure, celles des filtres sélectifs et celles des tris fréquents — pas toutes les colonnes.

EXPLAIN ANALYZE
SELECT * FROM commandes WHERE client_id = 42;

-- AVANT l'index :
-- Seq Scan on commandes  (cost=0.00..18334.00 rows=1 width=44) (actual time=42.1 ms)
--   → "Seq Scan" = parcours complet : 18 000 pages lues pour trouver 1 ligne

CREATE INDEX idx_cmd_client ON commandes(client_id);

-- APRÈS :
-- Index Scan using idx_cmd_client  (cost=0.42..8.44 rows=1) (actual time=0.03 ms)
--   → mille fois plus rapide

Deux notions complémentaires : un index composite (client_id, date_cmd) sert les filtres sur client_id seul ou sur les deux, mais pas sur date_cmd seul — l'ordre des colonnes compte, comme dans un annuaire trié par nom puis prénom. Et ANALYZE met à jour les statistiques que le planificateur utilise pour choisir : sur une table qui vient d'être massivement chargée, des statistiques périmées produisent des plans catastrophiques.

9. Transactions et ACID

Une transaction regroupe plusieurs opérations en une unité indivisible : soit tout réussit, soit rien ne s'applique. L'exemple canonique est un virement — débiter un compte et créditer l'autre — où s'arrêter au milieu serait catastrophique. En data science, le cas courant est l'ingestion : charger dix mille lignes et mettre à jour la table de suivi doit être tout ou rien, sinon on ne sait plus ce qui a été chargé.

BEGIN;                                        -- début de la transaction
  INSERT INTO annonces SELECT * FROM import_temporaire;
  UPDATE suivi_ingestion SET derniere_date = CURRENT_DATE;
COMMIT;                                       -- tout est appliqué d'un coup
-- en cas de problème : ROLLBACK;             -- rien n'a eu lieu

Les quatre garanties résumées par l'acronyme ACID : Atomicité (tout ou rien), Cohérence (les contraintes restent respectées), Isolation (une transaction en cours n'est pas perturbée par les autres), Durabilité (une fois validée, elle survit à une coupure de courant). Ce sont ces garanties que l'on perd en travaillant avec des fichiers — et, en partie, avec certaines bases NoSQL (section 11).

10. OLTP, OLAP et DuckDB

                 OLTP (transactionnel)        OLAP (analytique)
Exemples         PostgreSQL, MySQL, SQLite    DuckDB, ClickHouse, BigQuery, Snowflake
Requêtes         nombreuses, très courtes     peu nombreuses, très lourdes
Accès typique    quelques lignes entières     2 colonnes sur 50, des millions de lignes
Stockage         par LIGNES                   par COLONNES
Optimisé pour    écriture, cohérence, ACID    lecture, compression, agrégation
Rôle en data     la source de vérité          l'analyse et l'entraînement

Le stockage en colonnes explique l'essentiel. Dans un stockage par lignes, les valeurs d'une même ligne sont contiguës sur le disque : parfait pour lire ou écrire un enregistrement complet. Dans un stockage par colonnes, ce sont les valeurs d'une même colonne qui sont contiguës : un SELECT AVG(montant) ne lit alors que cette colonne, en ignorant les 49 autres. S'ajoute un effet de compression considérable, puisque des valeurs voisines d'une même colonne se ressemblent (mêmes catégories répétées, dates proches, ordres de grandeur similaires).

DuckDB est l'outil à connaître pour un usage local : pas de serveur à installer, un simple fichier (ou même rien du tout, en mémoire), et il interroge directement des fichiers Parquet ou des DataFrames.

import duckdb

# SQL directement sur des fichiers Parquet, sans import préalable
df = duckdb.sql("""
    SELECT client_id, SUM(montant) AS ca
    FROM 'data/commandes_*.parquet'          -- le joker lit tous les fichiers
    WHERE date_cmd >= '2025-01-01'
    GROUP BY client_id
    ORDER BY ca DESC
    LIMIT 10
""").df()                                    # renvoie un DataFrame pandas

# on peut aussi interroger un DataFrame existant comme une table
con = duckdb.connect("analyse.duckdb")       # base persistante dans un fichier
con.execute("CREATE TABLE annonces AS SELECT * FROM df_polars")
con.execute("COPY annonces TO 'export.parquet' (FORMAT PARQUET)")

11. NoSQL et bases vectorielles

NoSQL ne veut pas dire « sans SQL » mais « pas seulement relationnel ». Chaque famille abandonne quelque chose — le plus souvent les jointures et la cohérence forte — pour gagner autre chose : du débit, de la souplesse de schéma, de la distribution.

Clé-valeur    Redis, DynamoDB     accès O(1) par clé. Cache, sessions, features
                                  servies en ligne. Aucune requête complexe.
Document      MongoDB, Firestore  documents JSON imbriqués, schéma souple. Pratique
                                  quand la structure varie d'un enregistrement
                                  à l'autre ; jointures pauvres.
Colonnes      Cassandra, HBase    écriture massive distribuée, séries temporelles,
                                  télémétrie. Modélisation dictée par les requêtes.
Graphe        Neo4j               les relations sont l'objet premier : détection de
                                  fraude, réseaux, recommandation (module 18).

Le cadre théorique est le théorème CAP : dans un système distribué, une partition réseau finit toujours par se produire (des machines ne se voient plus). Il faut alors choisir entre la cohérence — refuser de répondre plutôt que renvoyer une donnée potentiellement périmée — et la disponibilité — répondre quand même, quitte à être en retard. PostgreSQL privilégie la première, Cassandra la seconde avec une cohérence « à terme ».

Une base vectorielle indexe des embeddings — des vecteurs de quelques centaines de dimensions représentant un texte ou une image — et répond à « quels sont les k vecteurs les plus proches de celui-ci ? ». Les options : pgvector (extension PostgreSQL, à privilégier tant qu'une seule base suffit), FAISS (bibliothèque locale), Qdrant ou Milvus (services dédiés). Le fonctionnement des index approchés et le pipeline RAG complet sont traités au module 22.

12. Interroger une base depuis Python

# 1. SQLite : inclus dans Python, zéro installation, un seul fichier
import sqlite3
with sqlite3.connect("projet.db") as con:
    con.execute("CREATE TABLE IF NOT EXISTS annonces (id INTEGER PRIMARY KEY, prix REAL)")
    con.executemany("INSERT INTO annonces VALUES (?, ?)", lignes)   # insertion par lot
    for ligne in con.execute("SELECT * FROM annonces WHERE prix > ?", (200_000,)):
        print(ligne)

# 2. SQLAlchemy : une interface unique pour PostgreSQL, MySQL, SQLite…
from sqlalchemy import create_engine, text
moteur = create_engine("postgresql+psycopg://user:motdepasse@localhost:5432/ma_base")
with moteur.begin() as con:                      # begin() = transaction automatique
    con.execute(text("UPDATE annonces SET prix = prix * 1.02 WHERE ville = :v"),
                {"v": "Grenoble"})

# 3. Charger directement dans un DataFrame
import polars as pl, pandas as pd
df = pl.read_database_uri("SELECT * FROM annonces", uri="postgresql://…")
df = pd.read_sql("SELECT * FROM annonces WHERE ville = %(v)s", moteur, params={"v": "Lyon"})

Ce module a montré comment interroger des données déjà en place. Le module 7 traite la question d'avant : comment elles y arrivent, sous quel format les stocker, comment organiser un entrepôt pour l'analyse, et comment faire tourner tout cela chaque nuit sans créer de doublons.

What this module covers

• Why a database rather than files: concurrent writers, partial updates, indexed lookups, integrity constraints, crash recovery — and what "declarative" changes in how you optimise.
• The relational model from scratch: tables, primary and foreign keys, constraints, normalisation and deliberate denormalisation.
• SQL basics, the difference between writing order and execution order, and the filtering operators worth knowing.
• Joins illustrated row by row, anti-joins, and the cardinality blow-up that silently falsifies every aggregate downstream.
• Aggregation versus window functions, with an annotated anatomy of an OVER clause and the frame clause that prevents temporal leakage.
• CTEs for readable, testable queries.
• NULL semantics and their consequences on comparisons, counts and averages.
• Indexes explained (B-trees, cost), EXPLAIN ANALYZE, composite indexes, and what makes an index unusable.
• Transactions and the ACID guarantees.
• OLTP versus OLAP, columnar storage, and DuckDB for local analytics over Parquet files.
• The four NoSQL families, the CAP trade-off, vector databases, and a sensible default position.
• Querying from Python with sqlite3, SQLAlchemy and DataFrame loaders — always with parameterised queries.

Course outline

1. Modern Python: typing, decorators, iterators and tooling
2. Concurrency in Python: the GIL, threads, processes, asyncio
3. Numerical toolbox: NumPy, pandas, polars, Dask
4. SQL and databases
5. Docker, Compose and Kubernetes
6. Serving a model: FastAPI, Pydantic, Jinja
7. Data engineering: formats, pipelines and streams
8. Math for ML: optimisation, linear algebra, SVD and PCA
9. Inferential statistics: estimate, test, decide
10. Probability, Bayesian inference and uncertainty quantification
11. Causal inference: measuring the effect of an action
12. Data science in practice: from problem to dataset
13. Learning with few labels
14. Classical machine learning
15. Learning theory: why generalisation happens
16. Deep learning
17. Time series, signals and time-to-event data
18. Non-tabular data: text, images, audio, graphs
19. Gaussian processes and Bayesian optimisation
20. Reinforcement learning
21. Machine learning for simulation and engineering
22. LLMs, embeddings, RAG and adaptation
23. Search, ranking and recommendation
24. From scores to decisions
25. Scaling up: GPUs, distributed training, performance
26. MLOps: taking a model to production
27. Fairness, robustness, privacy and security
28. End-to-end project: property listings, from scraping to price prediction