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

Produit Scalaire (ou fonction de multiplication d'agrégation)

Lorsqu'on veut implémenter une certaine logique qui implique, notamment, de faire la multiplication sur un ensemble d'enregistrement, on se retrouve vite pris au dépourvu sans l'aide d'une fonction d'agrégation comme la fonction SUM qui nous permettrait de faire la multiplication d'un champs sur un ensemble d'enregistrements.

Qu'à cela ne tiennent, il existe des techniques pour surmonter ce problème.

Technique des logarithmes

La première façon de faire est d'utiliser les propriétés logarithmiques. Les deux propriétés qui nous intéressent sont les suivantes :

• log (a * b) = log (a) + log (b)
• exp(log(a)) = a

Avec la première propriété, on peut transformer la multiplication en sommation, et avec la deuxième propriété, on peut retrouver le résultat de la multiplication.

Pour démontrer cette solution, prenons par exemple un jeu de données comprenant les valeurs de 1 à 5.

Pour obtenir le résultat de la multiplication en utilisant les propriétés logarithmiques, il suffit de faire la requête suivante

Cette méthode est simple et efficace, mais elle a l'inconvénient d'ajouter un facteur d'erreur dans le résultat. Même si on multiplie des valeurs de type entière, le fait qu'on utilise la fonction LOG et que celui-ci nous retourne un type FLOAT et qu'ensuite, on utilise cette valeur de type FLOAT en paramètre à la fonction EXP qui nous retourne aussi un type FLOAT fera en sorte qu'il existe la possibilité d'arrondissement et ainsi, fausser le résultat.

Technique de la récursivité

Une autre technique consiste à utiliser les CTE et la récursivité pour multiplier les différentes valeurs.

Prenons encore notre ensemble de données avec les valeurs de 1 à 5. Nous construisons ensuite un CTE qui permet de calculer la multiplication entre le chiffre courant et la valeur, déjà multipliée, précédente.

Cette méthode est un peu moins simple que la première, mais elle a l'avantage de ne pas introduire un facteur d'erreur d'arrondissement pour les données de type entière. Il faut néamoin s'assurer de prendre la dernière valeur calcul, d'où le "SELECT TOP 1 ..... ORDER BY id DESC".

Par contre, elle introduit une nouvelle limitation, soit celle du nombre maximal de récursion dans la requête. Par défaut, le nombre maximal est de 100.

Si le nombre de récursion se doit d'être plus élevé, on peut soit utiliser l'option MAXRECURSION ou utiliser la technique "diviser pour régner", mais peu importe la technique choisi, il faudrait aussi tenir compte du type de donnée et s'assurer que le type soit capable de contenir la valeur de retour et ainsi éviter les débordements.

Pour l'option MAXRECURSION, il suffit simplement d'écrire l'option à la fin de l'instruction FROM.

Il est très rare qu'on sache à l'avance le nombre d'enregistrements qu'on devra multiplier ensemble. Pour contrer cette problématique, puisque la multiplication est associative (on peut interchanger les positions sans problèmes), on peut séparer notre ensemble de valeur à multiplier en différentes parties qui seront multipliées entre eux pour ensuite multiplier les résultats et ainsi former notre résultat final.

Pour diviser notre ensemble en sous-ensemble, nous effectuons une division entière de l'id unique par le nombre maximal de valeur qu'on veut par couche jusqu'à un maximum de 100, soit la limite supérieur de récursion. Dans notre exemple avec 102 valeurs, nous divisons notre ensemble par couche de 5 valeurs pour avoir plus d'une partition.

Une fois nos partition créées, il suffit de boucler dans notre second ensemble de données pour multiplier l'ensemble des partitions et obtenir notre résultat final

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

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