Partie 4 · Variantes

Deux moyennes, standardisation, comparaison

Croisement de deux moyennes

Une stratégie plus réaliste utilise deux échelles de temps : une moyenne courte et une moyenne longue. Signal : 1 si ma_courte(t) > ma_longue(t), −1 sinon. Dans le pipeline du cours, la moyenne courte utilise ROWS BETWEEN 4 PRECEDING AND CURRENT ROW (5 lignes) et la longue ROWS BETWEEN 19 PRECEDING AND CURRENT ROW (20 lignes), puis CASE WHEN ma_short > ma_long THEN 1 ELSE -1 END.

Cette variante compare une dynamique locale rapide à une dynamique plus lente et plus stable : deux opérateurs de lissage sur des fenêtres différentes. SQL permet de lire le passé et aussi de comparer plusieurs niveaux de mémoire.

À toi de coder

Croisement de deux moyennes

Le cours compare une moyenne courte et une moyenne longue (5 et 20 jours). Nos séries ne durent que 6 jours : (exemple adapté) nous prenons 2 et 4 lignes. Signal : 1 si ma_short > ma_long, sinon -1. Renvoie date, signal pour l'actif A, trié par 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 = """
WITH f AS (
  SELECT date,
         AVG(close) OVER (ORDER BY date ROWS BETWEEN ___ PRECEDING AND CURRENT ROW) AS ma_short,
         AVG(close) OVER (ORDER BY date ROWS BETWEEN ___ PRECEDING AND CURRENT ROW) AS ma_long
  FROM prices WHERE ticker = 'A'
)
SELECT date, CASE WHEN ma_short > ma_long THEN 1 ELSE -1 END AS signal
FROM f ORDER BY date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Standardisation locale : le z-score

On peut standardiser la valeur courante par rapport à son historique local :

(close - AVG(close) OVER (PARTITION BY ticker ORDER BY date
                          ROWS BETWEEN 19 PRECEDING AND CURRENT ROW))
/ NULLIF(STDDEV(close) OVER (PARTITION BY ticker ORDER BY date
                             ROWS BETWEEN 19 PRECEDING AND CURRENT ROW), 0)

On mesure non plus le niveau du prix, mais son écart relatif normalisé. NULLIF(..., 0) évite la division par zéro quand la variance locale est nulle (le résultat est alors NULL). Deux actifs peuvent avoir des niveaux de prix, des volatilités et des amplitudes très différents : la standardisation rend les signaux comparables, permet de faire du classement plus pertinent, et évite d'interpréter naïvement le niveau absolu des prix.

(Hors cours : STDDEV n'existe pas dans SQLite, d'où l'exercice ci-dessous qui normalise par l'amplitude de la fenêtre.)

À toi de coder

Normaliser dans la fenêtre et protéger la division

Comme le z-score du cours, on standardise le prix par rapport à son voisinage, mais avec une quantité disponible partout dans SQLite : la position dans l'amplitude de la fenêtre, (close - MIN) / NULLIF(MAX - MIN, 0). NULLIF(x, 0) renvoie NULL quand l'amplitude est nulle (fenêtre plate) au lieu de diviser par zéro. (Le cours utilise AVG et STDDEV ; SQLite n'a pas de STDDEV, d'où ce choix : exemple adapté.)

Pour l'actif C, fenêtre de 3 lignes (2 PRECEDING), renvoie date, pos trié par 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 = """
SELECT date,
       (close - MIN(close) OVER w) / ___(MAX(close) OVER w - MIN(close) OVER w, 0) AS pos
FROM prices
WHERE ticker = 'C'
WINDOW w AS (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Vérifie que tu as compris

Pourquoi NULLIF(STDDEV(...), 0) au dénominateur du z-score ?

Intra-actif contre inter-actifs

Intra-actif	Inter-actifs
on étudie un actif dans le temps	on compare plusieurs actifs à une date donnée
rendements, moyennes glissantes, cumul	classement, top-k, sélection relative

Un cours avancé de SQL analytique doit couvrir ces deux directions. Elles se combinent : on peut standardiser un signal dans le temps (intra), puis classer les actifs selon ce signal à chaque date (inter).