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.
Aucun commentaire:
Publier un commentaire