Partie 2 · Comparer

Classement et analyse cross-sectionnelle

Jusqu'ici nous avons raisonné dans le temps, actif par actif. Une autre forme d'analyse est essentielle : comparer plusieurs actifs à une même date, sélectionner les meilleurs, produire un classement instantané. On ne regarde plus seulement le passé d'un actif, mais sa position relative parmi les autres.

Trois fonctions de classement : ROW_NUMBER() attribue un rang strict, RANK() gère les ex æquo avec trous, DENSE_RANK() les gère sans trous. Selon l'objectif, on ne veut pas toujours traiter les ex æquo de la même façon.

Valeur	ROW_NUMBER	RANK	DENSE_RANK
10	1	1	1
10	2	1	1
8	3	3	2
7	4	4	3

Vérifie que tu as compris

Dans le tableau, la valeur 8 reçoit 3 avec RANK mais 2 avec DENSE_RANK. Pourquoi ?

À toi de coder

ROW_NUMBER, RANK, DENSE_RANK avec ex æquo

Trois fonctions de classement, trois façons de traiter les ex æquo : ROW_NUMBER donne un rang strict, RANK gère les ex æquo avec des trous, DENSE_RANK sans trous. Table notes(nom, val) déjà remplie avec les valeurs 10, 10, 8, 7 (comme dans le tableau du cours).

Renvoie nom, val, rn, rk, dr, trié par val DESC, nom. ROW_NUMBER départage avec nom ; RANK et DENSE_RANK ne classent que sur val DESC.

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE notes(nom TEXT, val INTEGER)")
con.executemany("INSERT INTO notes VALUES (?, ?)", [("a", 10), ("b", 10), ("c", 8), ("d", 7)])

sql = """
SELECT nom, val,
       ROW_NUMBER() OVER (ORDER BY val DESC, nom) AS rn,
       ___() OVER (ORDER BY val DESC) AS rk,
       ___() OVER (ORDER BY val DESC) AS dr
FROM notes
ORDER BY val DESC, nom
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Rang des actifs à chaque date

ROW_NUMBER() OVER (PARTITION BY date ORDER BY close DESC)

Pour chaque date, on classe les actifs du plus grand prix au plus petit et on attribue un numéro de ligne. Pour sélectionner les 5 premiers :

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY date ORDER BY close DESC) AS rank_
    FROM prices
) t
WHERE rank_ <= 5

On obtient une sélection top-k par date. (Hors cours : le résultat d'une window function ne peut pas être filtré dans le WHERE de la même requête, d'où la sous-requête ou une CTE.)

Ce classement peut servir à sélectionner les actifs les plus chers, les plus performants ou les plus liquides, construire une stratégie « top performers », ordonner un univers d'investissement à chaque date, comparer des signaux standardisés entre plusieurs actifs.

À toi de coder

Top 2 des actifs à chaque date

Classement cross-sectionnel : à chaque date on classe les actifs du plus grand prix au plus petit. On ne peut pas filtrer directement sur le résultat d'une window function dans le WHERE de la même requête : on la place dans une sous-requête.

Renvoie date, ticker, rang pour les 2 meilleurs actifs de chaque date, trié par date, rang.

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

Ctrl/⌘+Entrée

Vérifie que tu as compris

Pourquoi PARTITION BY date (et non ticker) dans le classement des actifs ?