Chapitre 7 : Window Functions

Les Window Functions sont l'une des caractéristiques les plus puissantes de T-SQL moderne. Elles permettent d'effectuer des calculs sophistiqués sur un ensemble de lignes (appelé "fenêtre" ou "window") sans avoir besoin de GROUP BY. C'est un outil indispensable pour les analyses complexes et les calculs analytiques.

Qu'est-ce qu'une Window Function ?

Une window function est une fonction qui opère sur un ensemble de lignes définies par une clause OVER. Contrairement aux fonctions d'agrégation classiques (SUM, COUNT, AVG), les window functions retournent une valeur pour chaque ligne, pas une ligne unique par groupe.

Composants clés de la clause OVER

1. PARTITION BY : Divise les lignes en partitions (sous-ensembles)

Divise le résultat en groupes logiques. La fonction s'exécute séparément pour chaque partition.

2. ORDER BY : Définit l'ordre des lignes dans la fenêtre

Établit l'ordre logique pour les fonctions qui dépendent de la séquence (comme ROW_NUMBER, RANK, LAG, LEAD).

3. ROWS/RANGE : Définit le cadre (frame) de la fenêtre

Spécifie quelles lignes sont incluses dans le calcul de la fonction. Utile pour les sommes cumulées et les moyennes mobiles.

Les principales Window Functions

1. ROW_NUMBER() - Numérotation séquentielle

Assigne un numéro unique à chaque ligne. Deux lignes identiques reçoivent des numéros différents.

2. RANK() - Classement avec égalités

Assigne un rang à chaque ligne. Les valeurs égales reçoivent le même rang, et le rang suivant est ignoré.

3. DENSE_RANK() - Classement sans trous

Comme RANK(), mais le rang suivant après une égalité n'est pas sauté.

4. LAG() et LEAD() - Accès aux lignes adjacentes

LAG() accède à la valeur d'une colonne de la ligne précédente.

LEAD() accède à la valeur d'une colonne de la ligne suivante.

5. FIRST_VALUE() et LAST_VALUE() - Accès aux extrema

FIRST_VALUE() retourne la première valeur de la fenêtre.

LAST_VALUE() retourne la dernière valeur de la fenêtre.

6. Fonctions d'agrégation comme Window Functions

Les fonctions d'agrégation classiques (SUM, AVG, MIN, MAX, COUNT) peuvent être utilisées comme window functions.

7. NTILE() - Distribution en quantiles

Distribue les lignes dans N groupes de taille égale. Utile pour créer des quartiles, déciles, etc.

Cas d'usage pratiques complexes

Cas 1 : Déterminer le client avec la commande la plus récente par région

Cas 2 : Calculer le pourcentage de contribution de chaque produit aux ventes

Cas 3 : Identifier les ruptures ou changements dans une séquence

Points de performance et bonnes pratiques

✅ Les window functions sont très performantes avec les index appropriés

✅ Utilisez PARTITION BY pour réduire la taille de la fenêtre et améliorer les performances

✅ Attention à LAST_VALUE() : utilisez toujours ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

✅ Les window functions retournent une ligne par ligne d'entrée, contrairement à GROUP BY

✅ Vous pouvez combiner plusieurs window functions dans une même requête

⚠️ Évitez les FRAMES complexes qui peuvent ralentir les requêtes

⚠️ Les window functions ne peuvent pas être utilisées dans WHERE ; utilisez une CTE ou une sous-requête

Erreur courante : LAST_VALUE sans cadre approprié

Comparaison : Window Functions vs GROUP BY

Pivoting Data

Le pivoting transforme des données en format ligne en format colonne. Cela consiste à faire pivoter les valeurs d'une colonne pour qu'elles deviennent des colonnes individuelles.

Unpivoting Data

L'unpivoting est l'opération inverse du pivoting. Il transforme des colonnes en lignes, ce qui est utile pour normaliser des données.

Grouping Sets

Les Grouping Sets permettent de générer plusieurs niveaux d'agrégation dans une seule requête. C'est une alternative plus efficace à l'utilisation de UNION pour combiner plusieurs GROUP BY.

Il existe trois variantes : GROUP BY, CUBE et ROLLUP.

1. GROUP BY standard

2. ROLLUP - Agrégation hiérarchique

ROLLUP génère des subtotaux à différents niveaux de la hiérarchie. C'est idéal pour créer des rapports avec des totaux et des sous-totaux.

3. CUBE - Toutes les combinaisons

CUBE génère des sous-totaux pour TOUTES les combinaisons possibles des colonnes. C'est plus complet que ROLLUP mais produit plus de lignes.

4. GROUPING SETS - Combinaisons personnalisées

GROUPING SETS vous permet de spécifier exactement quelles combinaisons de groupes vous voulez. Plus efficace que CUBE si vous n'avez besoin que de certaines combinaisons.

Fonction GROUPING() - Identifier les rangées de sous-totaux

La fonction GROUPING() retourne 1 si une colonne a participé au groupage (c'est un sous-total), et 0 sinon. C'est utile pour identifier et étiqueter les lignes de totaux.

Conclusion

Les Window Functions sont une addition majeure au T-SQL qui permet de résoudre des problèmes complexes d'une manière élégante et performante. Elles offrent une alternative puissante à GROUP BY et aux sous-requêtes corrélées, tout en gardant une complexité de lecture acceptable.

Les points clés à retenir :

🎯 PARTITION BY divise en groupes logiques

🎯 ORDER BY établit la séquence

🎯 ROWS/RANGE définit le cadre de calcul

🎯 Retournent une ligne par ligne d'entrée (contrairement à GROUP BY)

🎯 Excellentes pour les analyses, les classements et les calculs mobiles