Partie 1 · Contexte

Pourquoi SQL avancé ?

SQL n'est pas seulement un langage d'interrogation, ni un outil pour filtrer des lignes : il permet de définir des transformations analytiques sur des données structurées. L'enjeu d'un SQL avancé n'est pas d'écrire des requêtes plus longues, mais de comprendre comment une table devient un objet calculable.

Le cours a un fil rouge : une table de prix financiers prices(date, ticker, close). Chaque ligne contient une date, un actif et un prix de clôture. Nous allons calculer des rendements, des moyennes glissantes, des signaux, des classements, une performance cumulée et un pipeline complet.

date	ticker	close
2024-01-01	A	100
2024-01-02	A	102
2024-01-01	B	50
2024-01-02	B	49

(Les petites valeurs A et B de ce site sont un exemple ajouté ; le cours utilise des données de marché réelles.)

La question de départ

Peut-on calculer en SQL le rendement simple ret_t = close_t − close_(t−1) ? La question est simple en apparence, mais elle force à réfléchir à trois idées : qu'est-ce qu'une ligne ? comment définir la ligne précédente ? sur quel groupe de lignes travaille-t-on ?

Le SQL relationnel élémentaire raisonne comme si les lignes étaient indépendantes, interchangeables et non ordonnées. Or ici : le rendement du jour dépend du prix d'hier, la moyenne glissante dépend d'un voisinage temporel, le classement d'un actif dépend des autres actifs à la même date. Dès qu'une ligne dépend d'autres lignes, le SQL basique ne suffit plus.

Vérifie que tu as compris

Une requête SELECT ... WHERE close > 100 traite chaque ligne isolément. Laquelle de ces quantités ne peut PAS s'écrire ainsi ?

Une rupture conceptuelle

Une ligne n'est plus traitée isolément : elle doit être interprétée dans son contexte (son passé, son futur, son voisinage local, son groupe d'appartenance, l'ensemble des lignes comparables). Dans une table ordonnée, une quantité calculée sur la ligne i s'écrit u_i ↦ f(u_(i−k), …, u_i, …, u_(i+ℓ)) : la valeur produite dépend d'un voisinage de la ligne.

Forme	Type de dépendance
`u_i − u_(i−1)`	ponctuelle vers le passé
moyenne de `u_(i−4)` à `u_i`	locale glissante
somme des `u_j` pour `j ≤ i`	cumulative

Les window functions permettent précisément d'exprimer ces dépendances en SQL.

Vérifie que tu as compris

La quantité « moyenne des cinq dernières valeurs, ligne courante comprise » est une dépendance…

La méthode générale

Face à une question analytique, pose-toi toujours ces quatre questions :

Quelle est l'unité de calcul ? Une ligne ? Une date ? Un actif ? Un couple (date, actif) ?
De quelles lignes dépend cette ligne ?
Faut-il séparer les calculs par groupe ? ⇒ PARTITION BY
Faut-il imposer un ordre ? ⇒ ORDER BY

Dans le TP, l'unité de calcul de prices est un couple (date, actif) auquel on associe un prix de clôture. Et avant tout calcul, on valide le support : nombre de lignes, observations par actif, première et dernière date (COUNT(*), GROUP BY, MIN(date), MAX(date)). Une requête peut être correcte syntaxiquement et fausse analytiquement.

À toi de coder

Contrôler la table avant tout calcul

Un pipeline sérieux commence par la validation du support de calcul (TP, acte I) : combien de lignes, combien d'observations par actif, première et dernière date. Sans cela, tu pourrais calculer des rendements sur des séries incomplètes sans t'en rendre compte.

La table prices(date, ticker, close) est déjà créée (deux actifs A et B, 6 jours). Complète n_total (nombre de lignes de la table) puis la requête sql qui renvoie, par actif et trié par actif : ticker, COUNT(*), MIN(date), MAX(date).

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE prices(date TEXT, ticker TEXT, close REAL)")
A = [100, 102, 101, 105, 107, 108]
B = [50, 49, 51, 50, 52, 55]
for i in range(6):
    d = f"2024-01-0{i + 1}"
    con.execute("INSERT INTO prices VALUES (?, ?, ?)", (d, "A", A[i]))
    con.execute("INSERT INTO prices VALUES (?, ?, ?)", (d, "B", B[i]))

n_total = con.execute("SELECT ___ FROM prices").fetchone()[0]

sql = """
SELECT ticker, COUNT(*), MIN(date), MAX(date)
FROM prices
GROUP BY ___
ORDER BY ticker
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Vérifie que tu as compris

Dans la méthode générale, quel mot-clé répond à « faut-il séparer les calculs par groupe ? »