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