Partie 3 · Voisinages

Fenêtres : glissantes et cumulatives

Une fois le groupe et l'ordre fixés, on précise combien de lignes participent au calcul. Exemple :

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

C'est une moyenne glissante sur 5 observations. Pour la ligne d'indice i, la fenêtre est {i−4, i−3, i−2, i−1, i} : on inclut la ligne courante et on remonte de quatre lignes, soit 5 lignes au maximum. ROWS transforme une table ordonnée en une suite de voisinages glissants.

Avec la série 100, 102, 101, 105, 107, 108, la fenêtre à la date 6 contient {102, 101, 105, 107, 108}, donc ma5(6) = (102+101+105+107+108)/5 = 104,6.

Vérifie que tu as compris

Série 100, 102, 101, 105, 107, 108 : que vaut la moyenne glissante sur 5 lignes à la date 6 ?

Ponctuelle, glissante, cumulative

Type	Dépend de…	Exemple
Ponctuelle	une ou quelques lignes précises	`LAG`, `LEAD`
Glissante	un voisinage local	moyenne glissante
Cumulative	tout le passé jusqu'à la ligne courante	somme cumulative

Chaque type de fenêtre encode une forme différente de mémoire. Pour un cumul :

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

On reste dans un actif, on remonte jusqu'au début de l'historique et on somme tous les rendements jusqu'à la date courante.

À toi de coder

Moyenne glissante sur 5 jours

La fenêtre ROWS BETWEEN 4 PRECEDING AND CURRENT ROW regroupe la ligne courante et les 4 précédentes (5 lignes au maximum). Écris la requête qui renvoie ticker, date, ma5 trié par ticker, date.

Attention aux bords : au début de la série il y a moins de 5 lignes, la moyenne porte alors sur les lignes disponibles.

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]))

sql = """
SELECT ticker, date,
       AVG(close) OVER (PARTITION BY ticker ORDER BY date
                        ROWS BETWEEN ___ PRECEDING AND ___) AS ma5
FROM prices
ORDER BY ticker, date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Local ou global ?

Compare AVG(close) OVER (PARTITION BY ticker) et AVG(close) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW). Le premier calcule une moyenne globale par actif (la même valeur sur toutes ses lignes) ; le second une moyenne locale glissante. Confondre calcul global et calcul local est une des erreurs critiques du SQL analytique.

À toi de coder

Écart à la moyenne globale de l'actif

AVG(close) OVER (PARTITION BY ticker) (sans ORDER BY) est une moyenne globale par actif, répétée sur chaque ligne ; elle utilise donc le futur. C'est pratique pour une analyse descriptive, dangereux pour un signal. Renvoie ticker, date, ecart avec ecart = close - moyenne de l'actif, 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]
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]))

sql = """
SELECT ticker, date,
       close - AVG(close) OVER (PARTITION BY ___) AS ecart
FROM prices
ORDER BY ticker, date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Que se passe-t-il au début de la série ?

Sur les premières dates, LAG renvoie souvent NULL ; une moyenne glissante de taille 5 peut être calculée sur moins de 5 observations si le moteur l'autorise. Les effets de bord ne sont pas des détails techniques : ils modifient la signification du calcul. Par exemple à la date 2 de la série 100, 102, … la fenêtre ne contient que {100, 102}.

Le choix de la taille de fenêtre est une hypothèse de modèle : une moyenne sur 3 jours est plus réactive, une moyenne sur 10 jours plus stable. Définir une fenêtre, c'est répondre à « quelles sont exactement les lignes qui influencent la ligne courante ? ».

À toi de coder

Compter les lignes réellement utilisées

Les effets de bord font partie du sens du calcul. Pour une moyenne glissante sur 3 lignes (2 PRECEDING AND CURRENT ROW), écris une requête qui renvoie, pour l'actif A seulement (trié par date), date, ma3, n où n = COUNT(close) OVER (...) avec la même fenêtre : le nombre de prix réellement moyennés.

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]))

sql = """
SELECT date,
       AVG(close) OVER w AS ma3,
       COUNT(close) OVER w AS n
FROM prices
WHERE ticker = 'A'
WINDOW w AS (ORDER BY date ROWS BETWEEN ___ PRECEDING AND ___)
ORDER BY date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

À toi de coder

Somme cumulative des rendements

Un cumul est une dépendance cumulative : tout le passé jusqu'à la ligne courante. On calcule d'abord ret dans une CTE (WITH), puis on cumule dans la requête finale. Renvoie ticker, date, cum trié par ticker, date.

Remarque : SUM ignore les NULL ; le cumul de la première ligne (rendement NULL) est donc NULL, puis il démarre au 2e 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]
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]))

sql = """
WITH r AS (
  SELECT date, ticker,
         close - LAG(close) OVER (PARTITION BY ticker ORDER BY date) AS ret
  FROM prices
)
SELECT ticker, date,
       SUM(ret) OVER (PARTITION BY ticker ORDER BY date
                      ROWS BETWEEN ___ AND CURRENT ROW) AS cum
FROM r
ORDER BY ticker, date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée