Ajouter un rang sans perdre de ligne
Une fonction de fenêtre calcule une valeur à partir de plusieurs lignes, sans les regrouper : chaque ligne reste dans le résultat. Elle sert à poser un total cumulé ou un rang à côté de chaque ligne.
Une boulangerie note sa recette chaque jour. La gérante veut voir, à côté de chaque jour, tout ce qui a été encaissé depuis le début de la semaine.
À la fin de cette leçon
Remarque
Je peux ajouter un total cumulé ou un rang à côté de chaque ligne sans perdre de ligne.
Mots clés
| Mot | Ce que ça veut dire | Exemple |
|---|---|---|
| Fonction de fenêtre | un calcul sur des lignes voisines qui garde chaque ligne | |
| OVER | le mot qui transforme une fonction en fonction de fenêtre | sum(montant) over () |
| Total cumulé | la somme de la ligne et de toutes celles qui la précèdent | |
| Rang | la place d'une ligne quand on les classe | |
| Ex aequo | deux lignes qui ont la même valeur de classement |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
GROUP BY fait disparaître des lignes
Avec group by, sept lignes de recettes peuvent devenir une seule ligne : le total. Les jours ont disparu.
Souvent, on veut le contraire. On veut garder les sept jours et voir le total à côté. C'est ce que fait une fonction de fenêtre.
OVER dit sur quelles lignes calculer
Écrivez over après une fonction d'agrégat : elle devient une fonction de fenêtre. sum(montant) over () additionne toutes les lignes, mais n'en retire aucune.
Les parenthèses vides veulent dire : la fenêtre est la table entière. Chaque ligne reçoit le même total.
À vous : un total sur chaque ligne
-- Chaque jour garde sa ligne, avec le total de la semaine à côté.
select jour, montant, sum(montant) over () as total_semaine
from recettes
order by jour;
-- Ajoutez une colonne : round(100.0 * montant / sum(montant) over (), 1) as part
ORDER BY dans OVER donne un total cumulé
Ajoutez order by jour dans over. La fenêtre de chaque ligne devient : les lignes du début jusqu'à celle-ci, dans l'ordre des jours.
Le résultat est un total cumulé. Le lundi vaut le lundi, le mardi vaut lundi plus mardi, et ainsi de suite.
Exemple : un total cumulé jour après jour
| Jour | Montant | Calcul du cumul | Cumul |
|---|---|---|---|
| 1er septembre | 120 | 120 | 120 |
| 2 septembre | 150 | 120 + 150 | 270 |
| 3 septembre | 90 | 270 + 90 | 360 |
| 4 septembre | 200 | 360 + 200 | 560 |
| 5 septembre | 180 | 560 + 180 | 740 |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
À vous : le total cumulé
-- Le total cumulé : tout ce qui a été encaissé jusqu'à ce jour.
select jour, montant, sum(montant) over (order by jour) as cumul
from recettes
order by jour;
-- Remplacez sum par count et lisez ce que devient la colonne.
Une erreur fréquente
Attention
Beaucoup pensent que order by dans over trie le résultat affiché. Il fixe seulement l'ordre du calcul. Pour que les lignes s'affichent dans un ordre sûr, écrivez aussi un order by à la fin de la requête.
Trois fonctions numérotent les lignes
Ces trois fonctions donnent une place à chaque ligne selon l'ordre demandé. Elles ne diffèrent que pour les ex aequo.
row_number donne 1, 2, 3 sans jamais répéter. rank répète la place, puis saute des numéros. dense_rank répète la place, sans sauter.
Exemple : trois numérotations, deux ex aequo
| Vendeur | Ventes | row_number | rank | dense_rank |
|---|---|---|---|---|
| Alice | 120 | 1 | 1 | 1 |
| Chloe | 120 | 2 | 1 | 1 |
| Emma | 110 | 3 | 3 | 2 |
| Bruno | 95 | 4 | 4 | 3 |
| Farid | 95 | 5 | 4 | 3 |
| David | 80 | 6 | 6 | 4 |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
À vous : comparer les trois rangs
-- Trois façons de numéroter, avec une égalité entre Alice et Chloe.
select nom, ventes,
row_number() over (order by ventes desc, nom) as numero,
rank() over (order by ventes desc) as rang,
dense_rank() over (order by ventes desc) as rang_dense
from vendeurs
order by ventes desc, nom;
-- Retirez ", nom" dans le over de row_number : l'ordre des deux ex aequo n'est plus garanti.
lag lit la valeur de la ligne précédente
lag(montant) donne le montant de la ligne d'avant, dans l'ordre de la fenêtre. lead donne celui de la ligne d'après.
Pour la première ligne, il n'y a pas de ligne avant : le résultat est NULL. C'est la valeur qui dit « rien ».
À vous : l'écart avec la veille
-- L'écart avec la veille : lag lit la ligne d'avant.
select jour, montant, montant - lag(montant) over (order by jour) as ecart
from recettes
order by jour;
-- Le premier jour n'a pas de veille : que vaut son écart ?