Partie 5 · Rigueur

Pièges, méthode, SQL et Python

Les erreurs les plus fréquentes

oublier PARTITION BY ;
oublier ORDER BY ;
confondre calcul global et calcul local ;
mal définir la fenêtre ;
utiliser le futur sans le vouloir ;
mal interpréter les NULL de bord.

Comparer AVG(close) OVER (PARTITION BY ticker) (moyenne globale par actif) à la version avec ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW (moyenne locale glissante).

Fuite d'information

Il y a fuite d'information lorsqu'un calcul censé être disponible au temps t utilise des données du futur : une fenêtre qui inclut t+1, t+2… ou un LEAD utilisé sans s'en rendre compte. En finance cela conduit à des stratégies artificiellement excellentes mais irréalistes : l'interprétation causale du calcul est détruite, l'évaluation est invalide, les comparaisons entre méthodes sont faussées. Dès qu'un calcul est censé être utilisable en temps réel, vérifie qu'il n'utilise que l'information disponible à cette date.

Vérifie que tu as compris

Quelle fenêtre contient une fuite d'information pour un signal calculé au jour t ?

À toi de coder

Appliquer le signal de la veille (hors cours)

Hors cours. Dans le pipeline du cours, strategy_ret = signal * ret utilise le signal du jour t (calculé avec close_t) pour pondérer le rendement close_t - close_(t-1) : le signal « connaît » déjà le prix de la journée qu'il est censé prédire. Pour une stratégie utilisable en temps réel, on applique plutôt le signal de la veille : LAG(signal).

Complète le pipeline pour que la performance totale par actif (ticker, perf, triée par ticker) utilise LAG(signal) à la place de signal. Compare avec l'exercice du pipeline : 9, 7, 78 avec le signal du jour.

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 = """
WITH returns AS (
  SELECT *, close - LAG(close) OVER (PARTITION BY ticker ORDER BY date) AS ret
  FROM prices
),
ma AS (
  SELECT *, AVG(close) OVER (PARTITION BY ticker ORDER BY date
                             ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS ma5
  FROM returns
),
signal AS (
  SELECT *, CASE WHEN close > ma5 THEN 1 ELSE 0 END AS signal
  FROM ma
),
veille AS (
  SELECT *, ___(signal) OVER (PARTITION BY ticker ORDER BY date) AS sig_veille
  FROM signal
)
SELECT ticker, SUM(sig_veille * ret) AS perf
FROM veille
GROUP BY ticker
ORDER BY ticker
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

ROWS contre RANGE, ordre, NULL

Dans beaucoup de moteurs, ROWS compte un nombre de lignes, alors que RANGE raisonne sur des valeurs de l'ordre et peut inclure plusieurs lignes ex æquo. Les confondre peut changer subtilement ou fortement le résultat ; pour un premier apprentissage rigoureux, ROWS est plus simple à interpréter.

Une table relationnelle n'a pas d'ordre intrinsèque : LAG, LEAD, ROW_NUMBER et les cumuls exigent un ORDER BY explicite. Le fait qu'une requête « tourne » ne signifie pas qu'elle calcule quelque chose de bien défini.

Les NULL apparaissent souvent : début de série avec LAG, variance locale nulle, données absentes. Faut-il les conserver, les filtrer, les remplacer ? Il n'y a pas de réponse universelle : cela dépend du sens du calcul.

À toi de coder

ROWS contre RANGE avec des ex æquo

Avec des ex æquo sur la clé d'ordre, RANGE traite toutes les lignes de même valeur comme un bloc, alors que ROWS compte les lignes une à une. Table t(d, v) : (1,10), (2,20), (2,30), (3,5).

Complète sql_rows (d, v, cum avec ORDER BY d, v et ROWS) et sql_range (même colonnes, ORDER BY d et RANGE).

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t(d INTEGER, v INTEGER)")
con.executemany("INSERT INTO t VALUES (?, ?)", [(1, 10), (2, 20), (2, 30), (3, 5)])

sql_rows = """
SELECT d, v, SUM(v) OVER (ORDER BY d, v ___ BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum
FROM t ORDER BY d, v
"""
sql_range = """
SELECT d, v, SUM(v) OVER (ORDER BY d ___ BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum
FROM t ORDER BY d, v
"""
cum_rows = [r[2] for r in con.execute(sql_rows)]
cum_range = [r[2] for r in con.execute(sql_range)]

Ctrl/⌘+Entrée

Lire, concevoir, vérifier

Lire une requête complexe : (1) la table de départ, (2) chaque CTE, (3) ce que chaque étape ajoute, (4) tous les PARTITION BY, (5) tous les ORDER BY, (6) la fenêtre de chaque calcul, (7) l'absence de fuite d'information.

Concevoir : (1) la grandeur finale, (2) sa décomposition en sous-quantités, (3) l'unité de calcul de chaque sous-quantité, (4) groupe, ordre et fenêtre, (5) implémentation étape par étape dans des CTE lisibles.

Valider : sens métier, unité de calcul, cohérence des partitions et de l'ordre, définition des fenêtres, traitement des bords, absence de futur non désiré. En SQL analytique, la correction ne se réduit pas à l'absence d'erreur de syntaxe.

SQL et pandas

SQL	pandas
`LAG(x)`	`shift(1)`
`LEAD(x)`	`shift(-1)`
`AVG(x) OVER (... ROWS ...)`	`rolling(...).mean()`
`SUM(x) OVER (...)`	`cumsum()`
`ROW_NUMBER()`	`rank(method='first')` ou index après tri

La logique mathématique est la même ; seul le langage change. SQL décrit ce que l'on veut calculer ; Python décrit souvent comment enchaîner les opérations.

Vérifie que tu as compris

Quel équivalent pandas de LAG(x) le cours propose-t-il ?