TOP X pour chaque groupe de données

Que ce soit les 10 chansons les plus vendus par style de musique dans une boutique en ligne, les 3 meilleurs pointeurs par équipes, les 5 dernières nouvelles par catégories, il y a des cas où on veut dans nos rapport un nombre prédéfini de résultat selon un groupe de données en particulier. Malheureusement, SQL Server ne nous donne pas un outil ou instruction pour le faire de façon simple et efficace.

Qu'à cela ne tienne, on peut contourner cette limitation en employant l'un ou l'autre des techniques ci-dessous.

Technique de la numérotation

La première technique consiste à numéroter chaque ligne de résultat selon le groupe de données désiré puis d'y appliquer un filtre sur les numéros pour ne retourner que les X résultats souhaités par sous-groupe de données.

Par exemple, disons que nous voulons connaitre les trois produits qui se sont le plus vendus par années pour la compagnie AdventureWork. Il faudra tout d'abord construire une requête pour numéroter le résultat.

Ensuite, on y applique un filtre pour ne retourner que les valeurs désirées.

Voila, un top 3 des meilleurs vendeurs par année.

Cette technique fonctionne bien sur des jeux de données plus ou moin petit. Lorsque le volume de données devient considérable, et surtout si le nombre de valeurs distinctes reste significativement petit comparer à l'ensemble des données, la deuxième technique peut s'avérer plus rapide et plus efficace.

Technique de l'APPLY

En reprenant l'exemple lors de la technique par numérotation, la technique du cross apply consiste à utiliser l'opérateur APPLY pour calculer le top 3 des meilleur vendeurs selon l'année présente dans un premier jeu de données.

Pour commencer, il faut se construire un jeu de données qui ne contient que les valeurs distinctes qui constituent notre groupe de données distinct.

Avec ce petit jeu de données, on applique l'opérateur APPLY pour calculer le top 3 des meilleurs vendeurs selon l'année.

Cette requête prends un temps d'exécution considérable, compte tenu du temps d'exécution de la première technique. On peut améliorer ce temps d'exécution en se créant une table commune pour réduire le nombre d'enregistrements qui appelleront l'opérateur APPLY.

Avec cette nouvelle version, le temps d'exécution s'est grandement amélioré pour rejoindre celui de la première technique.

Mais peu importe la technique que vous utilisez, il restera important tester la performance en fonction de votre jeu de données.

Références:

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

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

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

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

La pagination rendu facile

Un jour ou l'autre, on a tous besoin de faire une pagination sur les résultats qu'on obtient, spécialement lorsqu'on obtient une grande quantité de données et qu'on veut seulement en afficher une partie pour obtenir une interface qui répond plus rapidement à l'utilisateur. Imagine seulement si, à chaque requête effectuée sur Google, on obtient l'ensemble des résultats qui correspond à nos critères de recherche. Le temps pour traiter les données et les transmettre sur le réseau prendraient un temps fou qu'il serait impensable de penser que l'utilisateur attendrait tout ce temps pour ne cliquer que sur l'un des premiers résultat.

Avec l'arrivée de SQL Server 2005, on a vu l'apparition de la fonction ROW_NUMBER qui nous permettait d'identifier les rangées de façon unique et ainsi pouvoir les filtrer selon leur position. 

Prenons par exemple le cas d'un bottin d'un entreprise. La pagination faites dans la requête pour retourner seulement un sous-ensemble du bottin pouvait ressembler à quelque chose comme ceci pour la troisième page du bottin.

Avec l'arrivé de SQL Server 2012, la pagination des données s'est améliorer et il ne suffit plus que de spécifier l'OFFSET du premier enregistrement ainsi que le nombre d'enregistrements à retourner et le tour est joué.

L'instruction OFFSET indique le nombre d'enregistrement à ignorer avant de retourner le premier enregistrement et l'instruction FETCH NEXT indique le nombre d'enregistrement à retourner. Les valeurs qu'on y indique peuvent être soit des valeurs fixes, soit des variables, comme montré précédemment, ou des requêtes SQL scalaires.

Références:

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

Les fonctions analytiques de SQL Server

Peu connues, SQL Server offre quelques fonctions analytiques qui permettent d'obtenir plus d'informations sur l'ensemble de données qui est traité. De les huit différentes fonctions analytiques que SQL Server nous fournis, on peut les diviser en deux catégories: les fonctions analytiques de positionnement et les fonctions analytiques sur la distribution des valeurs.

Pour démontrer chacune des fonctions, je vais prendre le même ensemble de données, soit le total des items vendus par mois ainsi que le total des ventes mensuel de la base de données AdventureWorks. Pour restreindre le jeu de données et pour ne pas être submergé de données, je vais limiter les ventes qu'aux années 2011 et 2012. La requête utilisée pour nous donner le jeu de données est la suivante.

Fonctions analytiques de positionnement

SQL Server offre 4 fonctions analytique pour connaitre les différentes valeurs des enregistrements précédents ou suivants. L

FIRST_VALUE

Comme son nom l'indique, cette fonction nous donne la valeur du premier enregistrement selon l'ordre spécifié ainsi que selon le partitionnement des données. Cette valeur n'est pas nécessairement la première valeur retournée dans le résultat, mais bien la première valeur selon la clause OVER qui est spécifié. Par exemple, on veut avoir la première valeur des items vendu par mois, ordonnancé par mois

Comme on a ordonnancé par mois seulement et que le plus petit mois est 1, on a donc cette valeur, soit 3967. Par contre, si on veut la première valeur par année, il faut partitionner nos données par année.

En partitionnant nos données, la première valeur est calculée pour chaque partitionnement en prenant la valeur associé au mois le plus petit, soit le 5ième mois pour l'année 2011 et pour le premier mois de 2012.

LAG

La fonction LAG nous permet de connaitre les valeurs précédentes à l'enregistrement courant. Par défaut, la fonction LAG retourne la valeur précédente, mais on peut aussi lui indiquer la n-ième valeur que nous souhaitons connaitre. Lorsque la valeur demandée n'existe pas, la valeur NULL est retournée.

Ainsi, dans notre exemple, on peut connaitre le nombre d'items vendus pour les mois précédents.

LEAD

De la même façon que la fonction LAG, la fonction LEAD permet de connaitre les valeurs suivantes à l'enregistrement courant. Ainsi, dans notre exemple, on peut connaitre le nombre d'items vendus pour les mois suivants.

LAST_VALUE

La dernière fonction analytique de positionnement est la fonction LAST_VALUE qui est sensiblement comme la fonction FIRST_VALUE, mais qui comporte une petite spécifité qui peut en mystifier plus d'un.

On se rappel que, lorsqu'on a calculé la FIRST_VALUE plus tôt, nous avons partionné nos données par année pour avoir une valeur correspondant à la première valeur par année.

Sans partitionnement

Avec partitionnement

Pour la fonction LAST_VALUE, c'est différent. Voyons ce qu'il arrive lorsqu'on utilise la même clause OVER que pour FIRST_VALUE

On remarque que pour FIRST_VALUE, on a bien la bonne valeur, mais pour LAST_VALUE, on a la même valeur que pour ItemVendusMois. Et cette valeur peut nous mystifier un peu plus si nous omettons la clause PARTITION BY où il semble prendre n'importe quelle valeur.

Dans les faits, LAST_VALUE calcule la dernière valeur en fonction de la clause PARTITION BY ainsi que pour chaque valeur distinctes de la clause ORDER BY. Dans notre exemple, comme on veut la dernière valeur de l'année, on doit ordonnancer par année, ce qui nous donnera la bonne valeur.

On peut valider ceci en prenant des valeurs arbitraires pour y appliquer la fonction LAST_VALUE.

Par exemple, dans la requête suivante, on se créer des couples de données (A,B) pour en calculer la dernière valeur. On remarque que dans la dernière colonne (LAST_VALUE ORDER BY A), on a la valeur B qui est associé à la dernière occurence de la valeur A (les enregistrements bleutées).

À l'inverse, lorsqu'on trie par B pour avoir la dernière valeur de A, on a le même phénomène. Pour chaque valeur distinct de B, on a la valeur de la dernière occurence de A.

Fonctions analytiques sur la distribution des valeurs

Les quatres dernières fonctions permettent d'avoir des informations quant à la distribution des données. Les fonctions PERCENTILE_CONT et PERCENTILE_DISC permettent d'obtenir le percentile d'une distribution de données tandis que les fonctions CUME_DIST et PERCENT_RANK permettent de déterminer le rang de la donnée relativement à la distribution.

PERCENTILE_CONT et PERCENTILE_DISC

Les fonctions PERCENTILE_CONT et PERCENTILE_DISC permettent de calculer le percentile d'une distribution basé sur un ordonnancement et une partition de données spécifiée. La différence entre ces deux fonctions est que, pour la première, il va traiter la distribution numérique comme étant une distribution continu, qui peut prendre une infinité de valeur, tandis qu'avec la fonction PERCENTILE_DISC, il traite la distribution comme étant un ensemble de valeurs discrètes et fini. Dans le premier cas, la valeur retournée peut ne pas exister dans la distribution tandis que dans le deuxième cas, la valeur retournée doit obligatoirement exister dans la distribution.

Dans le cadre de notre exemple avec les items vendus par mois, on peut calculer le 33ième percentile de chaque année de cette façon :

Dans le résultat, on peut affirmer que, dans le cas d'une distribution continu, 35% des données sont égal ou en deça de 497.75, tandis que dans une distribution discrète, 35% des données sont égal ou en deça de 230

CUME_DIST et PERCENT_RANK

Ces deux fonctions permettent de calculer la position relative en pourcentage de l'occurrence courante par rapport à l'ensemble du jeu de données.

La fonction CUME_DIST (distribution cumulative) permet d'obtenir la position relative par rapport au jeu de données où la valeur retournée indique le pourcentage de valeur qui est égale ou inférieur à la valeur courante. De façon plus concrète, la valeur retournée est égale à r/n où r la position de l'occurrence courante et n est le nombre total du jeu de donnée.

La fonction PERCENT_RANK permet aussi de calculer le rang relative de l'occurrence courante par rapport au jeu de donnée, mais la première ligne aura toujours la valeur zéro.

On peut voir les différences entre ces deux fonctions dans le résultat suivant :

Références:

https://msdn.microsoft.com/fr-fr/library/hh213234(v=sql.120).aspx

https://msdn.microsoft.com/fr-fr/library/hh231078(v=sql.120).aspx

https://msdn.microsoft.com/fr-fr/library/hh213018(v=sql.120).aspx

https://msdn.microsoft.com/fr-fr/library/hh231256(v=sql.120).aspx

https://msdn.microsoft.com/fr-fr/library/hh231517(v=sql.120).aspx

https://msdn.microsoft.com/fr-fr/library/hh213125(v=sql.120).aspx

https://msdn.microsoft.com/fr-fr/library/hh231473(v=sql.120).aspx

https://msdn.microsoft.com/fr-fr/library/hh231327(v=sql.120).aspx

https://msdn.microsoft.com/fr-fr/library/hh213573(v=sql.120).aspx

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

 

Séparation d'une chaîne de caractères en jetons

Un jour ou l'autre, on a tous besoin de séparer une chaîne de caractères en jetons. Que ce soit parce que l'on recoit en paramètre une liste d'identifiant ou une liste d'article, il nous faudra découper cette chaîne en différents jetons pour exécuter notre traitement.

SQL Server ne nous offre pas de fonctionnalité de base pour découper une chaîne de caractères selon un caractère de séparation, comme le fait String.Split sur la plateforme .NET, ou comme la fonction strtok en C. Par contre, il existe une technique toute simple en SQL qui nous permet de le faire.

La technique consiste à se créer une table temporaire contenant toutes les positions du caractère de séparation, puis à y appliquer le découpage de la chaîne en fonction de cette table.

Prenons par exemple les 10 premiers produits de la table Production.Product de la base de données AdventureWorks.

Pour séparer cette chaîne, nous allons nous construire une table contenant les positions du caractère ',' en utilisant une expression de table commune recursive.

La petite particularité que nous avons ici est que, pour le dernier enregistrement, nous avons la valeur 0 comme position de fin. Pour contourner ce petit problème, on utilise l'instruction CASE WHEN pour remplacer la valeur zéro par l

a longueur de la chaine, puis on utilise le tableau comme valeur d'entrée à la fonction SUBSTRING en prenant bien soin de supprimer les espaces superflus qui peuvent avoir au début et à la fin de la chaîne de caractères

Si l'ordre est jeton est nécessaire, on peut ajouter la position dans la table temporaire pour nous aider à retrouver un jeton en particulier.

Références:

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

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

Expression de table communes (common table expression, CTE)

Depuis SQL Server 2005, il existe une fonctionnalité puissante qui permet au programmeur d'alléger et de simplifier les requêtes, tout en facilitant la maintenance et le déboggage. Cette fonctionnalité est les expressions de table communes, communément appelé CTE (Common table expression).

Les CTE nous permettent de définir des ensemble de données temporaires qui seront utilisés dans la requête. On peut comparer un CTE à une vue ou à une table dérivée dont la durée de vie n'excèdent pas le temps d'exécution de la requête.

Pour construire notre CTE, on doit utiliser le mot-clef “WITH” suivi du nom de l'expression, des colonnes retournée ainsi que de la définition de l'expression. Une fois définie, il ne suffit plus que d'écrire la requête comme à l'habituel en utilisant le nom de CTE comme si c'était une table ou une vue.

Par exemple, supposons que nous voulons connaître les employés de 50 ans et plus qui travaillent de soir. Nous pouvons diviser l'ensemble de données de la base de donnes AdventuresWorks en deux parties distinctes, soit les employées qui ont plus de 50 ans et les employés qui travaillent de soir, puis joindre ces deux ensembles de données pour obtenir le résultat désiré.

En définissant les CTEs, nous augmentons la lisibilité du code et il nous est plus facile d'effectuer la maintenance et le déboggage en focusant sur le résultat d'une expression plutôt que sur l'ensemble de la requête.

La définition des CTEs peuvent contenir des instructions beaucoup plus complexes comprenant, entre autre, les fonctions de partitionnement de données, comme les fonctions ROW_NUMBER et RANK.

À présent, supposons que nous voulons connaître quels produits se sont vendus par mois, et en quelle quantité. Nous voulons aussi connaître quel a été le meilleur vendeur du produit pour le mois donné.

Pour commencer, nous définissons un premier ensemble de données, ArticleVendusParAnnéeMois, qui contient la quantité de produits vendus par mois.

Le deuxième ensemble de données, ArticleVenduParAnnéeMoisVendeur, contient la quantité de produits vendus par vendeur. On utilise ici la fonction ROW_NUMBER() pour numéroter les vendeurs en fonction du nombre d'item vendu par produit pour un mois donné.

La requête qui suit nous permet d'utiliser ces données pour obtenir le résultat voulu. Ici, nous appliquons un filtre “v.Rang = 1” pour ne faire sortir que les meilleurs vendeurs par produit vendu par mois.

Référence:

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