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

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

 

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

Fonction de fenêtrage (Windowing functions) – 1ière partie

Les fonctions de fenêtrage de SQL Server nous permettent de partitionner et/ou d'ordonnancer les données de notre résultat pour en calculer une valeur qui sera utile à notre traitement. L'utilisation la plus connue est la fonction ROW_NUMBER() qui nous retourne un numéro de ligne, ou la position en fonction de l'utilisation qu'on en fait, selon l'ordonnancement spécifié et du partitionnement choisi.

Prenons par exemple, les dix premier BusinessEntityID la table Person.Person de la base de données AdventuresWorks.

On peut obtenir le numéro de ligne, ou la position, selon l'ordre alphabétique du prénom suivi du nom de famille.

On remarquera que l'ordre du résultat a changé. Comme on n'a pas spécifié de clause ORDER BY, le résultat est ordonnancé selon le moteur interne du SGBD. Pour ordonnancer le résultat selon l'ordre original, il faut utiliser la clause ORDER BY tel que nous la connaissons.

Nous avons donc maintenant le numéro de ligne ordonnancer par le prénom et le nom, le tout ordonnancer par le champ BusinessEntityID.

Compliquons un peu les choses. Nous désirons toujours obtenir le numéro de ligne ordonnancer par le prénom et le nom de famille, mais nous aimerions que les numéros recommencent à zéro lorsque la première lettre du prénom change. Pour cela, il faut partitionner les données selon la première lettre du prénom. À chaque changement de première lettre du prénom, le numéro retourner par la fonction ROW_NUMBER recommencera à 1, comme on peut le voir dans le résultat suivant.

Ces fonctions peuvent être à la fois dans la clause SELECT, ainsi que dans la clause WHERE.

C'est ce que les fonctions de fenêtrage nous permettent de faire, calculer une valeur sur une partition de données, qui peut être l'ensemble des données ou non, ordonnancée ou non, une fois que les critères de filtre de la clause WHERE ont été appliqués.

Dans le prochain post, on verra quelles fonctions, autre que ROW_NUMBER, peuvent être utilisées avec la clause OVER et quelles sont les autres fonctions qui en sont affectées.

Référence:

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

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

Ordonnancer le résultat d'une requête selon un ordre défini ou aléatoire

Le langage SQL nous permet d'ordonnancer le résultat d'une requête avec la clause ORDER BY, mais cet clause n'est pas assez flexible pour lui indiquer un ordre prédéfini qui n'est pas nécessairement ascendant ou descendant.

Par contre, il existe des techniques pour contourner cette limitation. La première technique consiste à se définir un ensemble de données qui va contenir l'ordre dans lequel on veut nos résultat.

Par exemple, prenons la table HumanResources.Department de la base de données AdventuresWorks.

Nous voudrions ordonnancer notre résultat en fonction de l'emplacement du départemant dans l'usine, de sorte qu'un personne puisse suivre le rapport tout en marchant dans l'entreprise dans un ordre logique, selon la disposition des locaux, informations qui n'est pas dans la base de données.

L'ordre dans lequel on veut notre résultat est le suivant :

• Executive General and Administration
• Manufacturing
• Quality assurance
• Inventory Management
• Sales and Marketing
• Research and Development

Pour se faire, on se créer un ensemble de données statique qu'on vient joindre à la requête pour ensuitre ordonnancer le résultat selon l'ordre qu'on lui a donné.

Nos données sont maintenant ordonnancées selon l'ordre voulu.

Bien que cette méthode fonctionne bien, elle a le désavantage d'augmenter la taille de notre ensemble de données en plus d'ajouter une jointure, d'être statique et que tous les éléments doivent être définis.

Pour palier à ces problèmes, nous avons deux options, soit modifier la jointure pour un LEFT JOIN, soit utiliser une deuxième technique, utiliser une logique dans la clause ORDER BY.

Modifier la jointure pour un LEFT JOIN se fait facilement, il suffit de mettre dans notre ordre de présentation seulement les valeurs qu'on veut prioriser et de substituer les valeurs manquantes par une valeur par défaut dans la clause ORDER BY.

Dans la dernière requête, on voulait mettre de l'avant les départements qui nous semblait les plus prioritaire, soit “Quality Assurance” et “Manufacturing”. Les autres départements n'étant pas prioritaire, ils sont simplement affichés à la suite selon un ordre choisi par le SGBD, ce qui ne sera pas nécessairement un ordre logique, comme on peut le voir avec les enregistrements 8 à13.

Nous aurions pus avoir le même résultat en utilisant la deuxième technique. En utilisant l'instruction CASE WHEN dans un ORDER BY au lieu du LEFT JOIN, nous pouvons supprimer la jointure et la logique est exécutée seulement sur les occurrences contenues dans le résultat plutôt qu'à l'ensemble des données.

Une dernière technique d'ordonnancement des données nous permet d'ordonnancer les données de façon quasi-aléatoire. Lorsqu'on a besoin, par exemple, d'un exemple de données et qu'on ne veut pas toujours avoir les mêmes données, nous pouvons utiliser la fonction NEWID() pour l'ordonnancement. La fonction NEWID() retourne une valeur de type GUID qui change à chaque occurence, ce qui nous donne donc un ordre différent à chaque exécution.

Références:

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

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

https://msdn.microsoft.com/fr-ca/library/ms190348(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