Partie 1 · Agréger

Des agrégats qui ne réduisent pas la table

En SQL classique, une agrégation avec GROUP BY réduit généralement le nombre de lignes : SELECT ticker, AVG(close) FROM prices GROUP BY ticker donne une ligne par actif. Une agrégation analytique, AVG(close) OVER (...), conserve toutes les lignes.

`GROUP BY`	`OVER`
agrège par groupe	calcule par ligne
réduit la table	conserve la table
une ligne par groupe	enrichit chaque ligne d'informations contextuelles

Le SQL analytique est souvent un SQL d'enrichissement ligne par ligne.

À toi de coder

GROUP BY réduit, OVER conserve

Même moyenne, deux effets opposés. Avec GROUP BY on obtient une ligne par actif ; avec AVG(...) OVER (PARTITION BY ...) on garde toutes les lignes en ajoutant la moyenne de l'actif à chacune.

La table prices contient maintenant trois actifs A, B, C sur 6 jours. Complète sql_group (ticker, AVG(close), trié par ticker) et sql_over (ticker, date, close, moy, trié par ticker, 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]
C = [80, 90, 83, 110, 79, 120]
for i in range(6):
    d = f"2024-01-0{i + 1}"
    for t, s in (("A", A), ("B", B), ("C", C)):
        con.execute("INSERT INTO prices VALUES (?, ?, ?)", (d, t, s[i]))

sql_group = """
SELECT ticker, AVG(close) FROM prices GROUP BY ___ ORDER BY ticker
"""
sql_over = """
SELECT ticker, date, close, AVG(close) OVER (PARTITION BY ___) AS moy
FROM prices ORDER BY ticker, date
"""
rows_group = con.execute(sql_group).fetchall()
rows_over = con.execute(sql_over).fetchall()

Ctrl/⌘+Entrée

Vérifie que tu as compris

Une table de 18 lignes (3 actifs). SELECT ticker, AVG(close) FROM prices GROUP BY ticker renvoie 3 lignes. Et SELECT ticker, AVG(close) OVER (PARTITION BY ticker) FROM prices ?

De la tendance locale à la décision

La moyenne glissante sur 5 jours donne un indicateur local de tendance :

AVG(close) OVER (PARTITION BY ticker ORDER BY date
                 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)

On peut la transformer en décision avec CASE :

CASE WHEN close > AVG(close) OVER (PARTITION BY ticker ORDER BY date
                                  ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
     THEN 1 ELSE 0 END

Formellement : signal_t = 1 si close_t > ma5(t), sinon 0. Si le prix courant est au-dessus de sa moyenne locale, on considère qu'il y a une tendance favorable ; sinon, on s'abstient. Cette règle est volontairement simple : l'objectif n'est pas d'optimiser une stratégie mais de comprendre comment SQL enchaîne des calculs analytiques.

À toi de coder

Un signal binaire avec CASE

On transforme une mesure en décision : signal = 1 si le prix est au-dessus de sa moyenne glissante sur 5 lignes, sinon 0. Renvoie ticker, date, signal trié par ticker, date, en calculant la moyenne directement dans le CASE.

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]
C = [80, 90, 83, 110, 79, 120]
for i in range(6):
    d = f"2024-01-0{i + 1}"
    for t, s in (("A", A), ("B", B), ("C", C)):
        con.execute("INSERT INTO prices VALUES (?, ?, ?)", (d, t, s[i]))

sql = """
SELECT ticker, date,
       CASE WHEN close > AVG(close) OVER (PARTITION BY ticker ORDER BY date
                                            ROWS BETWEEN ___ PRECEDING AND CURRENT ROW)
            THEN ___ ELSE ___ END AS signal
FROM prices
ORDER BY ticker, date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Performance cumulée

Une fois strategy_ret calculé, on accumule les gains et pertes :

SUM(strategy_ret) OVER (PARTITION BY ticker ORDER BY date
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

On construit ainsi une courbe de performance discrète.

Fonction	Rôle principal
`LAG`/`LEAD`	lire une valeur décalée dans l'ordre
`AVG OVER`	calculer une tendance locale
`SUM OVER`	cumuler une quantité dans le temps
`CASE WHEN`	transformer une mesure en décision

Un pipeline analytique SQL est souvent une composition : lecture du contexte → résumé local → décision → agrégation.

Vérifie que tu as compris

Dans l'enchaînement « lecture du contexte → résumé local → décision → agrégation », quel outil réalise la « décision » ?