Fonctions de fenêtre

Au-delà des agrégats

Les fonctions d'agrégation comme SUM() et COUNT() sont puissantes, mais elles ont une limite : elles regroupent plusieurs lignes en une seule. Que faire si vous voulez calculer une somme cumulative ou classer des lignes tout en conservant chaque ligne individuelle ? C'est là qu'interviennent les fonctions de fenêtre.

Window functions enable running totals, rankings, and moving averages without affecting row-level granularity.

Une fonction de fenêtre effectue un calcul sur un ensemble de lignes de table qui sont liées d'une manière ou d'une autre à la ligne actuelle. Cet ensemble de lignes est appelé la "fenêtre". Contrairement aux fonctions d'agrégation, qui réduisent le nombre de lignes retournées, les fonctions de fenêtre retournent une valeur pour chaque ligne, sans altérer le jeu de résultats original.

La syntaxe OVER

La magie des fonctions de fenêtre réside dans la clause OVER(). C'est cette clause qui signale à SQL d'exécuter une fonction sur une fenêtre de lignes spécifique.

```sql
FONCTION() OVER (
  PARTITION BY colonne_de_partition
  ORDER BY colonne_de_tri
)

Décortiquons cela :

FONCTION() : Il peut s'agir d'une fonction d'agrégation (SUM, AVG) ou d'une fonction de fenêtre spécifique (RANK, ROW_NUMBER).
OVER() : La clause qui définit la fenêtre.
PARTITION BY : Divise les lignes en groupes, ou partitions. La fonction est appliquée indépendamment à chaque partition. C'est similaire à GROUP BY, mais sans regrouper les lignes.
ORDER BY : Trie les lignes au sein de chaque partition. L'ordre est important pour les fonctions qui dépendent de la séquence, comme les classements ou les totaux cumulés.

Classer les lignes

Une utilisation très courante des fonctions de fenêtre est le classement des données. Supposons que nous ayons une table des employés et que nous souhaitions les classer par salaire au sein de chaque département.

nom	departement	salaire
Alice	Ventes	70000
Bob	Ventes	80000
Charlie	Ventes	70000
David	Ingénierie	95000
Eve	Ingénierie	110000

Nous pouvons utiliser trois fonctions de classement différentes :

ROW_NUMBER() : Attribue un numéro de ligne unique et séquentiel à chaque ligne au sein de sa partition.
RANK() : Attribue un rang à chaque ligne. Les lignes avec la même valeur dans la clause ORDER BY reçoivent le même rang. Il y a un saut dans le classement après des valeurs identiques (par ex., 1, 2, 2, 4).
DENSE_RANK() : Similaire à RANK(), mais sans sauts dans le classement après des valeurs identiques (par ex., 1, 2, 2, 3).

```sql
SELECT
  nom,
  departement,
  salaire,
  ROW_NUMBER() OVER(PARTITION BY departement ORDER BY salaire DESC) as row_num,
  RANK() OVER(PARTITION BY departement ORDER BY salaire DESC) as rank,
  DENSE_RANK() OVER(PARTITION BY departement ORDER BY salaire DESC) as dense_rank
FROM employes;

Cette requête partitionne les données par departement, puis trie les employés dans chaque département par salaire décroissant. Ensuite, elle applique les trois fonctions de classement.

nom	departement	salaire	row_num	rank	dense_rank
Eve	Ingénierie	110000	1	1	1
David	Ingénierie	95000	2	2	2
Bob	Ventes	80000	1	1	1
Alice	Ventes	70000	2	2	2
Charlie	Ventes	70000	3	2	2

Remarquez comment RANK() saute de 2 à 4 pour le classement des ventes, car Alice et Charlie partagent le rang 2. DENSE_RANK(), en revanche, ne saute pas, passant directement au rang suivant disponible.

Agrégats sur une fenêtre

Vous pouvez également utiliser des fonctions d'agrégation familières comme SUM(), AVG() et COUNT() en tant que fonctions de fenêtre. Au lieu de regrouper les lignes, elles calculent une valeur d'agrégat pour chaque ligne en fonction de sa fenêtre.

Imaginons que nous voulions afficher le salaire de chaque employé à côté du salaire moyen de son département. Sans les fonctions de fenêtre, cela nécessiterait une sous-requête ou une jointure. Avec elles, c'est simple.

```sql
SELECT
  nom,
  departement,
  salaire,
  AVG(salaire) OVER (PARTITION BY departement) as salaire_moyen_dept
FROM employes;

Ici, AVG(salaire) est calculé pour chaque partition de département et le résultat est ajouté à chaque ligne de cette partition.

nom	departement	salaire	salaire_moyen_dept
Eve	Ingénierie	110000	102500.00
David	Ingénierie	95000	102500.00
Alice	Ventes	70000	73333.33
Bob	Ventes	80000	73333.33
Charlie	Ventes	70000	73333.33

Un autre exemple puissant est le calcul d'un total cumulé. En ajoutant ORDER BY à la fenêtre, nous pouvons calculer la somme des salaires jusqu'à la ligne actuelle au sein de chaque département.

```sql
SELECT
  nom,
  departement,
  salaire,
  SUM(salaire) OVER (PARTITION BY departement ORDER BY nom) as total_cumule
FROM employes;

Dans cette requête, pour chaque ligne, SUM(salaire) additionne les salaires de toutes les lignes précédentes (selon l'ordre alphabétique des noms) au sein de la même partition.

nom	departement	salaire	total_cumule
David	Ingénierie	95000	95000
Eve	Ingénierie	110000	205000
Alice	Ventes	70000	70000
Bob	Ventes	80000	150000
Charlie	Ventes	70000	220000

Les fonctions de fenêtre ouvrent un nouveau monde de possibilités analytiques en SQL, vous permettant d'écrire des requêtes complexes de manière concise et efficace.

Quiz Questions 1/5

Quelle est la principale différence entre une fonction d'agrégation standard (par exemple, SUM()) et une fonction de fenêtre ?

Quiz Questions 2/5

Quelle clause est indispensable pour utiliser une fonction de fenêtre en SQL ?

Maîtriser les fonctions de fenêtre est une étape clé pour devenir plus compétent en SQL. Elles permettent de résoudre des problèmes analytiques complexes avec élégance.

SQL Avancé