Les fonctions de fenêtre (ou Windowing) sont un excellent moyen d’obtenir différentes perspectives sur un ensemble de données sans avoir à faire des appels répétés au serveur pour ces données. Par exemple, nous pouvons rassembler la somme d’une colonne et l’afficher côte à côte avec les données de niveau détail, de sorte que « SalesAmount » et « SUM(SalesAmount) » peuvent apparaître dans la même ligne. Nous pouvons également faire des fonctions analytiques comme PERCENT_RANK et des fonctions de classement comme ROW_NUMBER, le tout sans altérer la granularité de l’ensemble des résultats ou faire des voyages supplémentaires pour obtenir les mêmes données sources encore et encore.
« Regardez comme je balance sans effort deux des calculs rénaux de Val Kilmer. Tout à fait tyrannique ! »
Les fonctions de fenêtre utilisent toutes la clause OVER(), qui sert à définir comment la fonction est évaluée. La clause OVER() accepte trois arguments différents :
- PARTITION BY : Remet son compteur à zéro chaque fois que la ou les colonnes indiquées changent de valeur.
- ORDER BY : Ordonne les lignes que la fonction va évaluer. Cela n’ordonne pas l’ensemble des résultats, seulement la façon dont la fonction procède à travers les lignes.
- ROWS BETWEEN : Spécifie comment limiter davantage les lignes évaluées par la fonction.
Disons que nous examinons des données simplifiées d’une compétition d’haltérophilie. Voici quelques exemples de code (nous les ferons tous dans une seule instruction SELECT car l’ajout/la suppression de fonctions de fenêtre ne change en rien le nombre de lignes que nous récupérons) :
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
SELECT
LiftID
, LiftDate
, LiftPersonID
, LiftWeight
/* ROW_NUMBER listera le numéro de la ligne, ordonné par LiftID.
Le compteur sera remis à zéro avec chaque nouvelle combinaison de LiftDate et de LiftPersonID */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM va additionner les poids soulevés.
Le premier SUM montrera le grand total pour l’ensemble des résultats.
Le deuxième SUM montrera le poids total levé pour la date de levée de cette ligne.
Le troisième SUM montrera le poids total du levage pour la date de levage et la personne de cette ligne. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG montrera le poids moyen soulevé.
La première AVG montrera le poids moyen soulevé pour la date de levage de cette ligne.
Le second AVG indiquera le poids moyen soulevé pour la date de soulèvement de cette rangée et la personne. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG et LEAD permettent à la rangée actuelle de faire un rapport sur les données des rangées derrière ou devant elle.
Cette fonction LAG renverra le LiftWeight de 1 rang derrière elle (dans l’ordre de LiftID) et si aucune valeur n’est trouvée, elle renverra 0 au lieu de NULL.
La fonction LEAD obtiendra le LiftWeight de 3 rangs devant. Puisque nous n’avons pas spécifié la valeur par défaut optionnelle (comme le « 0 » que nous avons donné à la fonction LAG, elle retournera NULL s’il n’y a pas de rangée 3 rangées devant. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE AND LAST_VALUE retournera la première et la dernière valeur de la colonne spécifiée dans le jeu de résultats.
Cette fonction PREMIER_VALUE renverra le premier poids de levage dans l’ensemble de résultats.
Cette fonction DERNIER_VALUE renverra le dernier poids de levage dans l’ensemble de résultats.
***Attention : sans les ROWS BETWEEN dans la fonction LAST_VALUE, vous pouvez obtenir des résultats inattendus.***
*/
, FIRST_VALUE(LiftWeight) OVER (ORDER BY LiftDate) AS FirstLift
, LAST_VALUE(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastLift
/* SUM utilisant ROWS BETWEEN va réduire la portée évaluée par la fonction fenêtre.
La fonction commencera et se terminera là où les ROWS BETWEEN le spécifient.
Le premier SUM ajoutera toutes les valeurs de LiftWeight dans les rangs jusqu’au rang actuel inclus.
Le second SUM ajoutera toutes les valeurs de LiftWeight dans les rangs entre le rang actuel et les 3 rangs qui le précèdent.
*/
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WeightRunningTotal
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS WeightSumLast4
FROM dbo.Lifts
|
Considérations pour les fonctions de fenêtre
Si vous n’avez pas SQL Server 2012 ou une version ultérieure, votre placard de fonctions de fenêtre est plutôt vide ; SQL Server 2005 jusqu’à 2008 R2 n’autorisait que PARTITION BY dans la clause OVER d’une fonction agrégée, et vous aviez RANK() et ROW_NUMBER(). Et c’est à peu près tout. Si vous êtes un développeur qui utilise encore l’une de ces versions antérieures, il s’agit d’un argument convaincant pour passer à 2012 ou à une version ultérieure. Pensez au temps que vous pourriez gagner en n’écrivant pas de multiples CTE et à la rapidité de vos requêtes.
En parlant de rapidité…
En évitant les allers-retours vers le serveur pour les mêmes données, nous réduisons les E/S sur ces tables. Si nous frappons les index, nous pouvons vraiment réduire les lectures impliquées. Il y a un compromis à faire, mais il est généralement très favorable. Les fonctions de fenêtre exigent que SQL Server construise la fenêtre et calcule la fonction (représentée par des tâches telles que Window Spool, Segment, Sequence Project et Compute Scalar). Ce faisant, il ajoute des lectures à la table de travail. Néanmoins, cela est généralement moins coûteux que de retourner chercher les données sources plusieurs fois, de les agréger si nécessaire et de les joindre toutes ensemble. De plus, Worktable existe dans tempdb, qui – idéalement – se trouve sur votre niveau de stockage le plus rapide.
Enfin, rappelez-vous que les limitations que vous mettez sur une fonction de fenêtre – PARTITION BY, ORDER BY ou ROWS BETWEEN – sont là pour appliquer le contexte à la fonction de fenêtre et ne s’appliquent en aucun cas au jeu de résultats dans son ensemble. En d’autres termes, votre instruction SELECT ne sera pas affectée par ce que vous demandez à une fonction fenêtre.