Fonctions window de base pour l'analytics

Avant de commencer, imagine que tu bosses avec une table de milliers de lignes de ventes. Ta mission : savoir qui est le vendeur numéro un dans chaque catégorie, qui est le deuxième, etc. Ou, par exemple, tu veux numéroter toutes les lignes d'une sélection pour suivre l'ordre. Tout ça, c'est super simple avec les fonctions window.

Les fonctions window — ce sont des fonctions SQL qui bossent avec un sous-ensemble de lignes (appelons-le "fenêtre") d'un dataset. Contrairement aux fonctions d'agrégation qui regroupent les lignes en une seule (genre SUM() ou AVG()), les fonctions window laissent les lignes intactes et leur ajoutent des valeurs calculées.

Différence avec les fonctions d'agrégation

Les fonctions d'agrégation "compressent" les données en groupant les lignes :

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Résultat : juste quelques lignes, selon le nombre de départements.

Comparons avec une fonction window — ici, les lignes restent, mais on ajoute une nouvelle colonne, par exemple avec ROW_NUMBER() :

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

Là, tu récupères toutes les lignes, mais avec une colonne en plus rank_within_department, où chaque employé a un numéro dans son département.

Fonctions window principales

Syntaxe de OVER()

La partie la plus importante de chaque fonction window — c'est le mot magique OVER(). Il définit sur quelle "fenêtre" de données la fonction va bosser. À l'intérieur de OVER(), tu peux préciser la division en groupes (PARTITION BY) et/ou l'ordre de tri (ORDER BY).

Syntaxe générale :

<fonction_window>() OVER (
   [PARTITION BY <groupe>] 
   [ORDER BY <ordre>]
)

Composants :

• PARTITION BY : On divise les lignes en groupes. Par exemple, "sépare les données par département".
• ORDER BY : On indique l'ordre de traitement des lignes. Genre, "trie les employés par salaire décroissant".

Fonction ROW_NUMBER()

La fonction ROW_NUMBER() numérote les lignes à partir de 1 dans la "fenêtre" indiquée. C'est pratique pour juste créer un numéro de ligne dans une table temporaire ou pour déterminer la position d'un enregistrement.

Exemple. Table sales (ventes) :

Requête :

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Résultat :

Comment ça marche :

1. Les données sont divisées en groupes par product_category.
2. Chaque groupe est trié par revenue (du plus grand au plus petit).
3. Les lignes dans chaque groupe reçoivent un numéro d'ordre.

Fonction RANK()

La fonction RANK() sert à classer les lignes. Contrairement à ROW_NUMBER(), elle prend en compte les valeurs identiques et saute des numéros (rangs) si les valeurs sont les mêmes.

Exemple :

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Résultat :

Fonction DENSE_RANK()

DENSE_RANK() ressemble à RANK(), sauf sur un point : il ne saute pas de numéros de rang s'il y a des valeurs identiques.

Exemple. On ajoute une vente avec le même revenu :

Requête :

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Résultat :

Exemples d'utilisation : numérotation des lignes

Mission : numéroter toutes les commandes dans la table orders, triées par date.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Résultat : tu obtiens la liste des commandes avec une numérotation dans l'ordre d'exécution.

Exemples d'utilisation : top-3 des vendeurs dans chaque catégorie

Mission : trouver les trois meilleurs vendeurs dans chaque catégorie de produits.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

Exemples d'utilisation : détection des valeurs identiques

Mission : voir s'il y a des vendeurs avec le même revenu dans chaque catégorie.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Maintenant tu peux voir les rangs où les valeurs "collent" ensemble.