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) – 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