Fonction de fenêtrage (Windowing functions) – 3ième partie

Dans les deux premières parties, nous avons vu comment la clause OVER peut nous permettre de partitionner nos données pour y effectuer des calculs et nous avons aussi vu comment cette clause peut modifier le comportement des fonctions d’agrégations.

Dans cette dernière partie, nous allons voir comment, en plus de partitionner et d'ordonnancer nos données dans les fenêtres, on peut indiquer à SQL Server quelle partie de la fenêtre utiliser pour les différents calculs.

Reprenons encore la table Sales.SalesOrderDetail de la base de données AdventureWorks. Disons que nous voulons suivre l'évolution des ventes mensuelles des produits 723 et 806. Nous obtenons les ventes mensuelles effectuées avec la requête suivante :

Maintenant, nous voulons suivre l'évolution des ventes mensuelles. Nous voulons suivre quels ont été les quantités minimales et maximales, ainsi que les prix minimaux et maximaux selon ce qui était en vigueur au moment. Pour calculer ceci, nous devons spécifier quelles rangées utiliser dans la partition de données avec les instructions « ROWS BETWEEN X AND Y », où X et Y indique la limite inférieur et supérieur des rangées à considérer. Les valeurs permises sont, dans l'ordre :

• UNBOUNDED PRECEDING: Considère les rangées à partir du début de la partition de données
• Nombre PRECEDING: Considère seulement les Nombre rangées précédant la ligne courante
• CURRENT ROW: indique la ligne courante
• Nombre FOLLOWING: Considère seulement les Nombre rangées suivante la ligne courante
• UNBOUNDED FOLLOWING: Considère les rangées jusqu'à la fin de la partition de données.

Avec ces instructions, nous pouvons maintenant délimiter nos partitions de données pour limiter les valeurs sur lesquelles on veut effectuer les calculs. Comme mentionné précédemment, nous voulons suivre l'évolution des ventes mensuelles en suivant les quantités minimale et maximales ainsi que les prix minimaux et maximaux selon ce qui était en vigueur au moment où l'on est rendu, ce qui veut dire que nous limiterons seulement nos calculs aux années et mois précédents celui en cours. Nous aurons ainsi la requête suivante pour suivre nos ventes des deux produits.

Comme on peut le voir pour le produit 723, la quantité maximale vendue pour un mois données était de 1 seulement pour le mois d'octobre 2011. Par contre, cette quantité maxime a augmenter à 24 au mois de mai 2012 puis à 26 au mois de juin 2012. Comme la quantité commandée a été moindre aux mois de juillet 2012, mai et juin 2013, la valeur de la quantité maximale est resté à 26 jusqu'au mois de juillet 2013 où 27 produits ont été vendus, ce qui constituais la quantité maximale jusqu'à ce moment. On remarquera aussi que le prix maximal a changé à partir de 2013, où le prix à l'unité a été augmenté.

Pour le produit 806, on peut voir qu'il y a eu des modifications au niveau du prix à l'unité. Dans les quatre premiers mois, on voit le prix minimal descendre, tandis que le prix maximale du produit augmente dans les premiers mois pour ne jamais dépasser le prix vendu du mois de septembre 2012.

Autre requête pour bien comprendre le fonctionnement de ces instructions. Dans la prochaine requête, nous créons un un ensemble de données qui contient les valeurs de 1 à 5 inclusivement. Sur cet ensemble, nous calculons différentes valeurs selon différentes limites dans la fenêtre.

Comme nous pouvoir voir, la première valeur calculée est un COUNT fait sur seulement les enregistrements qui sont entre les 2 enregistrement précédents et 2 enregistrements suivants, ce qui nous donnes, entre autre, la valeur 3 pour la valeur de V égal à 1. Puisqu'il n'existe pas d'enregistrements précédents, le COUNT s'effectue seulement sur l'enregistrement courant et les deux enregistrements suivants. Ainsi avec la colonne « Nombre de ligne dans la fenetre » on peut voir le nombre d'enregistrements inclus dans la fenêtre.

Avec les colonnes « Valeur minimale dans la fenetre » et « Valeur maximale dans la fenetre », on peut voir les limites inférieurs et supérieurs dans la fenêtre de données. Les colonnes « Nombre de ligne traitée » et « Nombre de ligne restante à traiter » nous indiquent le nombre de rangées traitées ou à traiter dans la fenêtre. La dernière colonne sert à démontrer qu'il n'est pas nécessaire d'inclure la rangée courante dans notre calcul.

Références :

https://msdn.microsoft.com/fr-ca/library/ms189461(v=sql.110).aspx

 

Fonction de fenêtrage (Windowing functions) – 2ième partie

Dans la première partie, nous avons vu, à l'aide de la fonction ROW_NUMBER, comment les clauses OVER, PARTITION BY et ORDER BY peuvent découper les jeux de données pour permettre le calcul de valeur sur une partie des données.Dans ce post, nous allons voir comment ces clauses peuvent impacter les fonctions d'aggréations sans l'utilisation d'une clause GROUP BY.

Les fonctions d'aggrégations (MIN, MAX, SUM, COUNT, …) peuvent être utilisé avec les clauses de partitionnement de données, mais il faudrait faire attention car elle ne fonctionnent pas de la même façon.

Sans la clause OVER, les fonctions d'aggrégation ont besoin de la clause GROUP BY pour pouvoir effectuer les calculs sur un sous-ensemble de données. Essayer d'utiliser une fonction d'aggrégation sans clause GROUP BY provoquera immédiatement une erreur de syntaxe.

En utilisant la clause OVER, nous pouvons omettre la clause GROUP BY, mais, puisque les données ne seront pas regroupées, nous devrons porter une attention sur le résultat. Par exemple, avec une clause GROUP BY, nous avons ce résultat 

En utilisant une clause OVER et en supprimant la clause GROUP BY, on peut obtenir la même valeur dans la colonne « Nombre », mais comme les données du résultat ne sont pas regroupées sur le champ ProductID, on aura des duplicats.

Si c'est ce que l'on veut, c'est bien, sinon on peut ajouter une clause DISTINCT pour obtenir le même résultat.

Mais dans quels cas on voudrait avoir ce comportement? Puisque le SELECT est évalué avant la clause GROUP BY, en utilisant la clause OVER nous pouvons spécifier une granularité différente de la clause GROUP BY et ainsi avoir une plus grande flexibilité. Comme par exemple, nous voudrions ajouter les informations suivantes, le nombre d'item vendu d'un produit dans l'ensemble de notre jeu de données ainsi que le nombre de produit différents vendu par vente.

Comme on peut le voir, le nombre d'item vendu du produit fait la somme des items vendus sans égard à la facture, tandis que nombre d'item dans la commande comptabilise seulement les différents produits dans une commande, sans égard aux produits.

En ayant cette flexibilité, nous pouvons effectuer des calculs plus simplement que de les calculer dans des tables temporaires ou sous-requêtes pour ensuite les joindre à notre ensemble de données.

Un autre exemple de la puissance et de la flexibilité du partitionnement de données est qu'on peut calculer le ratio d'une donnée par rapport à l'ensemble. Par exemple, si on veut calculer le pourcentage des produits vendus par ventes, on peut facilement diviser la quantité vendu d'une vente par la quantité total vendues.

Références :

https://msdn.microsoft.com/en-us/library/ms189461(v=sql.110).aspx

https://msdn.microsoft.com/en-us/library/ms173454(v=sql.110).aspx