Avancé · 35 min

Window functions : calculer sur une fenêtre de lignes

Parfois on veut calculer un rang, une moyenne mobile, ou un agrégat sans perdre le detail des lignes.

Contexte

Parfois on veut calculer un rang, une moyenne mobile, ou un agrégat sans perdre le detail des lignes. Les window functions permettent de faire des calculs sur un sous-ensemble de lignes (la fenêtre) defini par OVER, sans GROUP BY qui reducerait le resultat.

Explication

Une window function applique un calcul sur une 'fenêtre' de lignes definie par OVER.

Syntaxe : SELECT ..., fonction() OVER (PARTITION BY colonne ORDER BY colonne) AS alias FROM ...;

Fonctions courantes :

ROW_NUMBER() : numero unique par ligne
RANK() : rang avec sauts (ex æquo = meme rang, saut apres)
DENSE_RANK() : rang sans saut (ex æquo = meme rang, pas de saut)
LAG(valeur, offset) : valeur de la ligne precedente
LEAD(valeur, offset) : valeur de la ligne suivante
AVG, SUM, COUNT, MIN, MAX aussi disponibles en window

Exemple guidé

Objectif : calculer le rang de chaque note sans perdre le detail.

1. ROW_NUMBER() donne un numero unique order by note DESC 2. On peut aussi utiliser RANK() pour les ex æquo 3. La fenêtre est definie par OVER (ORDER BY note DESC)

SELECT id, note, ROW_NUMBER() OVER (ORDER BY note DESC) AS rang
FROM notes
ORDER BY rang;

id	note	rang
12	18	1
9	17.5	2
5	16.5	3
13	16	4
7	15.5	5
6	15	6
25	15	7
14	14.5	8
3	14	9
22	14	10
20	13.5	11
23	13.5	12
8	13	13
26	12.5	14
1	12	15
17	12	16
18	12	17
15	11.5	18
24	11	19
11	10.5	20
4	10	21
21	10	22
10	9.5	23
19	9	24
2	8.5	25
16	8	26
27	7.5	27

À retenir

OVER () definit la fenêtre de la window function. PARTITION BY divise en groupes (comme GROUP BY mais sans reduire les lignes). ROW_NUMBER = numero unique, RANK = rang avec sauts, DENSE_RANK = sans sauts. LAG/LEAD pour naviguer entre les lignes adjacentes.

Exercices de cette leçon

Faire les exercices →