Partie 4 · Navigation

LAG, LEAD, FIRST_VALUE, LAST_VALUE

Les fonctions de navigation permettent de se déplacer à l'intérieur d'une partition ordonnée : LAG lit une valeur passée, LEAD une valeur future, FIRST_VALUE la première valeur de la fenêtre, LAST_VALUE la dernière.

Elles ne calculent pas d'agrégats : elles extraient des valeurs repérées dans un contexte. Il est utile de distinguer les fonctions qui lisent une valeur de celles qui résument plusieurs valeurs.

Fonction	Ce qu'elle lit
`LAG(close)`	le prix précédent
`LEAD(close)`	le prix suivant
`FIRST_VALUE(close)`	la 1re valeur de la fenêtre
`LAST_VALUE(close)`	la dernière valeur de la fenêtre

LEAD : regarder vers le futur

LEAD(close) OVER (PARTITION BY ticker ORDER BY date)

Cette fonction renvoie le prix suivant. Elle sert à construire un rendement futur, à mesurer l'évolution de t à t+1 ou à définir une cible supervisée en machine learning. Attention : utiliser LEAD sans s'en rendre compte dans un signal censé être disponible à la date t est une fuite d'information. Sur le dernier jour d'un actif, LEAD vaut NULL : il n'y a pas de ligne suivante dans la partition.

À toi de coder

LEAD : le rendement du lendemain

LEAD lit la ligne suivante. On s'en sert pour construire une cible (« que va-t-il se passer demain ? ») mais jamais pour un signal censé être utilisable à la date t.

Écris une requête qui renvoie date, ticker, fwd avec fwd = LEAD(close) - close par actif (trié par ticker, date). La dernière ligne de chaque actif doit valoir NULL.

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, ticker, ___(close) OVER (PARTITION BY ticker ORDER BY date) - close AS fwd
FROM prices
ORDER BY ticker, date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Vérifie que tu as compris

Sur la dernière ligne d'un actif, LEAD(close) OVER (PARTITION BY ticker ORDER BY date) renvoie…

FIRST_VALUE et LAST_VALUE

FIRST_VALUE(close) OVER (
    PARTITION BY ticker ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Pour chaque ligne, on rappelle le premier prix observé dans l'historique jusqu'à la date courante : on peut ainsi exprimer une performance relative par rapport à un point initial.

Avec la même fenêtre, LAST_VALUE(close) renvoie simplement la valeur courante. Piège : LAST_VALUE est souvent mal compris. Sans fenêtre explicite adaptée (par exemple jusqu'à UNBOUNDED FOLLOWING), on ne récupère pas « la dernière valeur globale ». (Hors cours : dans la norme SQL, avec un ORDER BY et sans cadre, la fenêtre par défaut s'arrête à la ligne courante.)

À toi de coder

Performance relative au premier prix

FIRST_VALUE rappelle sur chaque ligne le premier prix de l'historique : utile pour exprimer une performance relative à un point initial. Renvoie ticker, date, perf avec perf = close / FIRST_VALUE(close) - 1, 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 / ___(close) OVER (PARTITION BY ticker ORDER BY date
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 1 AS perf
FROM prices
ORDER BY ticker, date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

À toi de coder

LAST_VALUE : la fenêtre explicite change tout

Avec le cadre par défaut (jusqu'à la ligne courante), LAST_VALUE renvoie simplement la valeur courante. Pour retrouver la dernière valeur de tout l'historique de l'actif sur chaque ligne, il faut étendre la fenêtre vers le futur.

Renvoie ticker, date, dernier (le dernier prix de l'actif sur chaque ligne), 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,
       LAST_VALUE(close) OVER (PARTITION BY ticker ORDER BY date
                               ROWS BETWEEN UNBOUNDED PRECEDING AND ___) AS dernier
FROM prices
ORDER BY ticker, date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Vérifie que tu as compris

LAST_VALUE(close) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) renvoie…