</>
Plan du cours — 12/64

Fonctions de fenêtre

Intermédiaire

Une fonction de fenêtre calcule une valeur sur un ensemble de lignes liées à la ligne courante, sans les regrouper : on garde le détail et on ajoute un calcul.

SELECT client_id, id, total, cree_le,
       row_number() OVER (PARTITION BY client_id ORDER BY cree_le DESC) AS rang,
       sum(total)   OVER (PARTITION BY client_id)                       AS total_client,
       sum(total)   OVER (PARTITION BY client_id ORDER BY cree_le)      AS cumul,
       lag(total)   OVER (PARTITION BY client_id ORDER BY cree_le)      AS commande_precedente
FROM   commandes;

Les fonctions à connaître#

FonctionRôle
row_number()Numéro unique dans la partition (1, 2, 3…)
rank() / dense_rank()Rang avec ex æquo (rank laisse des trous, dense_rank non)
lag(x) / lead(x)Valeur de la ligne précédente / suivante
first_value(x) / last_value(x)Première / dernière valeur de la fenêtre
sum(x), avg(x), count(x)Agrégat en fenêtre : total, cumul, moyenne mobile
ntile(n)Découpe en n tranches (quartiles, déciles)

Top N par groupe#

Le cas d'usage classique : la dernière commande de chaque client.

SELECT *
FROM (
    SELECT o.*,
           row_number() OVER (PARTITION BY client_id ORDER BY cree_le DESC) AS rn
    FROM   commandes o
) t
WHERE rn = 1;

Piège : on ne peut pas filtrer directement sur une fonction de fenêtre dans le WHERE (elle est évaluée après). Il faut une sous-requête ou une CTE, comme ci-dessus.

← Leçon précédenteUNION, INTERSECT, EXCEPTLeçon suivante →DISTINCT ON et LATERAL