PARTITION BY et cadres de fenêtre

Avec partition by, une fonction de fenêtre recommence son calcul dans chaque groupe de lignes. Avec un cadre, elle ne regarde que quelques lignes voisines, comme les trois derniers jours.

Un directeur veut les deux meilleurs vendeurs de chaque magasin. Un restaurateur veut une courbe de recettes moins en dents de scie.

Sans partition by, la fenêtre est toute la table. Avec partition by magasin, chaque magasin a sa propre fenêtre.

Le rang repart de 1 dans chaque magasin. Chaque ligne reste dans le résultat : on ne regroupe rien.

-- Le classement des vendeurs recommence dans chaque magasin.
select nom, magasin, ventes,
  rank() over (partition by magasin order by ventes desc) as rang
from vendeurs
order by magasin, rang;
-- Retirez "partition by magasin" : le classement devient celui de toute la chaîne.

On veut les deux meilleurs de chaque magasin. Le rang doit d'abord être calculé, puis filtré.

Placez le calcul dans une sous-requête, et filtrez dehors avec where rang <= 2. DuckDB propose aussi qualify, un filtre posé directement sur la fenêtre.

-- Les deux meilleurs vendeurs de chaque magasin.
select nom, magasin, ventes, rang
from (
  select nom, magasin, ventes,
    row_number() over (partition by magasin order by ventes desc) as rang
  from vendeurs
)
where rang <= 2
order by magasin, rang;
-- Changez 2 en 1 pour ne garder que le meilleur de chaque magasin.

-- Le même résultat avec qualify, propre à DuckDB : un filtre sur la fenêtre.
select nom, magasin, ventes,
  row_number() over (partition by magasin order by ventes desc) as rang
from vendeurs
qualify rang <= 2
order by magasin, rang;
-- Quelle écriture trouvez-vous la plus facile à relire ?

Avec order by, la fenêtre va du début jusqu'à la ligne courante. Un cadre la rétrécit.

rows between 2 preceding and current row veut dire : les deux lignes d'avant et la ligne courante. Pour une moyenne, cela donne une moyenne mobile sur trois jours.

-- La moyenne des trois derniers jours, jour par jour.
select jour, montant,
  round(avg(montant) over (order by jour rows between 2 preceding and current row), 1) as moyenne_3j
from recettes
order by jour;
-- Changez 2 preceding en 1 preceding : la moyenne ne porte plus que sur deux jours.

Quand over contient un order by et aucun cadre, DuckDB additionne du début jusqu'à la ligne courante et jusqu'à ses ex aequo.

Avec rows between unbounded preceding and current row, on compte les lignes une par une. Les deux écritures diffèrent seulement quand des valeurs sont égales.