Window functions SQLite : OVER, PARTITION BY, frames

Une fonction de fenêtrage ajoute une colonne sans écraser les lignes

GROUP BY regroupe plusieurs lignes en une seule. Les window functions de SQLite font autre chose : elles calculent une valeur sur un ensemble de lignes liées, mais conservent chaque ligne d'origine dans le résultat. Vous obtenez à la fois le détail ligne par ligne et l'agrégat, côte à côte.

La syntaxe est toujours la même : une fonction, suivie de OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

La colonne total_all affiche le total général sur chaque ligne, répété à l'identique. Les lignes d'origine, elles, restent intactes. À comparer avec SELECT SUM(amount) FROM sales : même résultat numérique, mais une seule ligne en retour. Les window functions de SQLite te donnent les deux vues d'un coup.

PARTITION BY : agréger par groupes

Un OVER () vide effectue l'agrégation sur toute la table. En ajoutant PARTITION BY, vous agrégez par groupes, un peu à la manière d'un GROUP BY — sauf qu'ici, encore une fois, les lignes ne sont pas écrasées.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Chaque ligne récupère le total de sa région ainsi que sa part dans ce total. Avec un simple GROUP BY, on perdrait le détail par employé. C'est tout l'intérêt des fonctions de fenêtrage : avoir le détail et l'agrégat dans une seule requête.

Classement avec ROW_NUMBER, RANK et DENSE_RANK

Les fonctions de classement numérotent les lignes selon un ORDER BY placé dans la clause OVER. Les trois variantes se distinguent par leur façon de gérer les ex æquo.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

Lecture du résultat :

• ROW_NUMBER() renvoie toujours une valeur unique — en cas d'égalité, le départage se fait de façon arbitraire. À utiliser quand on veut un numéro stable et distinct pour chaque ligne.
• RANK() attribue le même rang aux lignes ex æquo, puis saute les numéros suivants. Deux joueurs à égalité au rang 1 sont suivis par le rang 3.
• DENSE_RANK() gère aussi les égalités, mais sans sauter de numéro. Le rang suivant reste 2.

Pour récupérer le « top N par groupe », on combine une fonction de classement avec PARTITION BY, puis on filtre dans une requête englobante — la clause WHERE ne peut pas référencer directement une fonction de fenêtrage :

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

Top deux salaires par région.

LAG et LEAD : aller voir les lignes voisines

LAG(col) renvoie la valeur de col issue de la ligne précédente dans la fenêtre. LEAD(col), lui, va chercher la suivante. Ces deux fonctions sont idéales dès qu'on veut suivre une évolution dans le temps.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS hier,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

La première ligne affiche NULL dans hier — logique, il n'y a rien avant. Vous pouvez fournir une valeur par défaut : LAG(celsius, 1, celsius) OVER (ORDER BY day) reprendra la valeur du jour courant quand aucune ligne précédente n'existe.

LEAD fait exactement l'inverse. En combinant les deux avec PARTITION BY, vous obtenez des séquences par groupe : par exemple, comparer les ventes de ce mois-ci à celles du mois précédent, région par région.

Calcul du total cumulé avec les fenêtres SQLite

Ajoutez ORDER BY à l'intérieur du OVER, et les fonctions d'agrégation comme SUM, AVG ou COUNT se mettent à calculer de manière cumulative :

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Deux points à bien remarquer :

• SUM(amount) OVER (ORDER BY day) calcule un total cumulé. Quand on écrit ORDER BY sans préciser de frame, la frame par défaut va « du début de la fenêtre jusqu'à la ligne courante ».
• La deuxième colonne, elle, définit une frame explicite : ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. C'est une fenêtre glissante de 3 lignes — autrement dit, une moyenne mobile.

Pour bien comprendre les frames, retenez ceci : chaque fonction de fenêtrage s'évalue sur une frame de lignes, définie par rapport à la ligne courante. Les frames les plus courantes :

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — total cumulé (c'est la valeur par défaut implicite).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW — fenêtre glissante sur les N lignes précédentes.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — la partition entière.

ROWS compte des lignes physiques. Il existe aussi RANGE, qui regroupe par valeur — pratique quand vous avez des ex æquo dans la colonne du ORDER BY et que vous voulez les traiter comme un seul palier.

FIRST_VALUE, LAST_VALUE et NTILE

Voici quelques autres fonctions de fenêtrage qui méritent qu'on s'y attarde :

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE et LAST_VALUE renvoient la première ou la dernière valeur du cadre. Avec LAST_VALUE, attention au cadre : par défaut il s'arrête à CURRENT ROW, donc en pratique il faut écrire ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING pour vraiment obtenir la dernière valeur de la partition.
• NTILE(n) répartit les lignes en n paquets de taille à peu près égale — pratique pour les quartiles, les centiles ou des découpages de type A/B.

Nommer une fenêtre avec WINDOW

Quand plusieurs colonnes partagent la même clause OVER (...), la répétition devient vite pénible. SQLite permet de nommer une fenêtre une seule fois et de la réutiliser ensuite :

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

Même requête, moins de bruit. La clause WINDOW se place après WHERE/GROUP BY/HAVING et avant ORDER BY.

Fonctions de fenêtrage vs GROUP BY en SQLite

Les deux font de l'agrégation, mais ne répondent pas à la même question :

• GROUP BY réduit. Une ligne par groupe. À utiliser quand seul le résumé t'intéresse.
• Les fonctions de fenêtrage (window functions) conservent les lignes. Chaque ligne d'entrée reste présente, avec des colonnes calculées en plus.

Si vous vous retrouves à faire un GROUP BY puis à rejoindre les agrégats sur la table d'origine, c'est le signe évident qu'une window function ferait tout ça en une seule requête.

Quelques pièges à éviter

• WHERE ne peut pas référencer une window function. Les filtres s'appliquent avant le calcul des fenêtres. Il faut envelopper la requête dans une sous-requête ou une CTE, puis filtrer au niveau supérieur.
• Les frames implicites font mal. SUM(x) OVER (ORDER BY y) produit un total cumulé, parce que la frame par défaut est RANGE UNBOUNDED PRECEDING. Si vous vouliez la somme sur toute la partition, écris OVER (PARTITION BY ...) sans ORDER BY, ou définis la frame explicitement.
• LAST_VALUE surprend toujours la première fois. Avec la frame par défaut qui s'arrête à la ligne courante, la fonction renvoie la valeur courante, pas la dernière de la partition. Il faut redéfinir la frame.
• Les window functions nécessitent SQLite 3.25+ (sorti en 2018). Toute installation un minimum récente les gère, mais certains environnements embarqués traînent encore derrière.

La suite : les colonnes générées

Les fonctions de fenêtrage, c'est du calcul au moment de la requête. La page suivante aborde le calcul au moment du stockage : les colonnes générées (generated columns), dont la valeur est définie par une expression et mise à jour automatiquement quand les données sous-jacentes changent.