Partie 2 · Syntaxe

OVER, PARTITION BY et ORDER BY

Une window function calcule une quantité sur chaque ligne en utilisant la ligne courante, d'autres lignes du même groupe, éventuellement un ordre et éventuellement une fenêtre explicite. Forme générale : fonction(...) OVER (...). La clause OVER définit le contexte : groupe de travail, ordre interne, fenêtre active.

SQL classique	SQL analytique
f(ligne)	f(ligne, voisinage)
dépend du contenu de la ligne courante	dépend d'un contexte défini dans OVER(...)

Le SQL analytique ajoute une notion de mémoire et de structure aux tables. Dans fonction(...) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...) :

PARTITION BY sépare les données en sous-ensembles indépendants ;
ORDER BY impose un ordre à l'intérieur de chaque sous-ensemble ;
ROWS/RANGE précise la fenêtre exacte utilisée autour de la ligne courante.

Vérifie que tu as compris

Dans fonction(...) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...), quel élément précise la fenêtre exacte autour de la ligne courante ?

Exemple central : le rendement journalier

On veut ret_t = close_t − close_(t−1). L'idée naturelle :

close - LAG(close) OVER (
    PARTITION BY ticker
    ORDER BY date
)

PARTITION BY ticker : calcul séparé pour chaque actif. ORDER BY date : définit ce que veut dire « précédent ». LAG(close) : récupère la valeur passée.

date	close	LAG(close)	ret
2024-01-01	100	NULL	NULL
2024-01-02	102	100	2
2024-01-03	101	102	−1
2024-01-04	105	101	4

Lire une expression analytique

Une requête analytique se lit comme une procédure logique de construction du contexte :

on prend la table ;
on la découpe par ticker ;
dans chaque sous-table, on ordonne par date ;
sur chaque ligne, on lit la valeur de close de la ligne précédente.

Vérifie que tu as compris

Sur la 3e ligne de la table ci-dessus (close = 101), que vaut close - LAG(close) OVER (PARTITION BY ticker ORDER BY date) ?

Pourquoi PARTITION BY, pourquoi ORDER BY ?

Si la table contient deux actifs A et B, sans PARTITION BY la ligne précédente d'une ligne de B pourrait être une ligne de A, ce qui n'a aucun sens financier : oublier PARTITION BY revient à mélanger des univers qui ne doivent pas l'être. Il ne sert pas « à faire joli », il définit les frontières du calcul.

Et LAG n'a de sens que si l'on sait ce que signifie « la ligne précédente ». Une table SQL n'est pas une liste ordonnée par nature : sans ORDER BY il n'y a pas de chronologie définie, le moteur n'a aucune notion de premier ou deuxième élément, le résultat peut être non déterministe ou sémantiquement faux.

Pour qu'une window function soit bien définie, réponds à trois questions : sur quel groupe calcule-t-on ? dans quel ordre lit-on les lignes ? sur quelle portion du groupe porte le calcul ?

À toi de coder

Corriger le rendement : PARTITION BY ticker

Voici la requête de l'« erreur guidée » du TP : close - LAG(close) OVER (ORDER BY date). Elle est syntaxiquement correcte mais analytiquement fausse : sans PARTITION BY, la « ligne précédente » d'un actif peut être une ligne d'un autre actif.

Complète la fenêtre pour que le rendement soit calculé séparément par actif, dans l'ordre chronologique. Le résultat doit être trié par ticker, date (colonnes : date, ticker, close, ret) ; la premiè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,
       close - LAG(close) OVER (PARTITION BY ___ ORDER BY ___) AS ret
FROM prices
ORDER BY ticker, date
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Vérifie que tu as compris

Que se passe-t-il si on oublie PARTITION BY ticker dans un LAG sur une table contenant plusieurs actifs ?