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:
Aucun commentaire:
Publier un commentaire