Introduction aux fonctions de la fenêtre T-SQL

j’ai passé les six dernières années à voyager aux États-Unis pour parler aux professionnels de la base de données des fonctions de la fenêtre T-SQL lors des samedis SQL et d’autres événements. Je suis étonné de voir à quel point peu de gens ont entendu parler de ces fonctions et encore moins qui les utilisent. À la fin de chaque présentation, une ou plusieurs personnes viennent dire qu’elles auraient souhaité connaître ces fonctions des années plus tôt parce qu’elles auraient pu être bénéfiques pour tant de requêtes.,

Ces fonctions ont été promues pour améliorer les performances par rapport à d’autres méthodes plus traditionnelles. Je suis partiellement d’accord. Ils facilitent l’écriture de nombreuses requêtes et, parfois, améliorent les performances.

rien à voir avec le système D’exploitation Windows

ces fonctions font partie des normes ANSI SQL 2003 et, dans le cas de SQL Server, sont des fonctions T-SQL utilisées pour écrire des requêtes. Ils n’ont rien à voir avec le système D’exploitation Windows ou les appels D’API. D’autres systèmes de base de données, tels Qu’Oracle, les ont également inclus dans leur propre langage SQL.,

Les fonctions Window (également, windowing ou windowed) effectuent un calcul sur un ensemble de lignes. J’aime penser à » regarder à travers la fenêtre” les lignes qui sont renvoyées et avoir une dernière chance d’effectuer un calcul. La fenêtre est définie par la clause OVER qui détermine si les lignes sont partitionnées en ensembles plus petits et si elles sont ordonnées. En fait, si vous utilisez une fonction window, vous utiliserez toujours une clause OVER. La clause OVER fait également partie de la valeur NEXT pour la syntaxe requise pour l’objet sequence, mais, sinon, elle est utilisée avec les fonctions window.,

la clause OVER peut contenir une option PARTITION BY. Cela divise les lignes en ensembles plus petits. Vous pourriez penser que C’est la même chose que GROUP BY, mais ce n’est pas le cas. Lors du regroupement, une ligne par groupe unique est renvoyée. Lors de L’utilisation de PARTITION BY, toutes les lignes de détail sont renvoyées avec les calculs. Si vous avez une fenêtre dans votre maison qui est divisée en volets, chaque volet est une fenêtre. Lorsque l’on pense aux fonctions de fenêtre, l’ensemble des résultats est une partition, mais lorsque L’on utilise PARTITION BY, chaque partition peut également être considérée comme une fenêtre., PARTITION BY est pris en charge – et facultatif-pour toutes les fonctions de fenêtrage.

la clause OVER peut également contenir une option ORDER BY. Ceci est indépendant de la clause ORDER BY de la requête. Certaines fonctions nécessitent ORDER BY, et ce n’est pas pris en charge par les autres. Lorsque l’ordre des lignes est important lors de l’application du calcul, l’ORDRE est nécessaire.

Les fonctions de fenêtre ne peuvent être utilisées que dans les clauses SELECT et ORDER BY d’une requête. Ils sont appliqués après toute jointure, filtrage ou regroupement.,

fonctions de classement

Les fonctions de fenêtre les plus couramment utilisées, les fonctions de classement, sont disponibles depuis 2005. C’est alors que Microsoft a introduit ROW_NUMBER, RANK, DENSE_RANK et NTILE. ROW_NUMBER est utilisé très fréquemment, pour ajouter des numéros de ligne uniques à une partition ou à l’ensemble des résultats. L’ajout d’un numéro de ligne, ou l’une des autres fonctions de classement, n’est généralement pas l’objectif, mais c’est une étape vers la solution.

ORDER BY est requis dans la clause OVER lorsque vous utilisez ROW_NUMBER et les autres fonctions de ce groupe., Cela indique au moteur de base de données l’ordre dans lequel les numéros doivent être appliqués. Si les valeurs des colonnes ou des expressions utilisées dans ORDER BY ne sont pas uniques, RANK et DENSE_RANK traiteront des liens, tandis que ROW_NUMBER ne se soucie pas des liens. NTILE est utilisé pour diviser les lignes en seaux en fonction de L’ordre par.

un avantage de ROW_NUMBER est la possibilité de transformer des lignes non uniques en lignes uniques. Cela pourrait être utilisé pour éliminer les lignes en double, par exemple.

pour montrer comment cela fonctionne, commencez par une table temporaire contenant des lignes en double., La première étape consiste à créer la table et à la remplir.

L’ajout de ROW_NUMBER et le partitionnement par chaque colonne redémarreront les numéros de ligne pour chaque ensemble unique de lignes. Vous pouvez identifier les lignes uniques en trouvant celles dont le numéro de ligne est égal à un.,

SELECT Col1, Col2,

ROW_NUMBER() OVER(PARTITION BY Col1, Col2 COMMANDE PAR Col1) COMME RowNum

#DE Doublons;

Maintenant, tout ce que vous avez à faire est de supprimer toutes les lignes qui ont un numéro de ligne supérieure à un., Le problème est que vous ne pouvez pas ajouter de fonctions de fenêtre à la clause WHERE.,

1 2	DELETE #Duplicates WHERE ROW_NUMBER() OVER(PARTITION BY Col1, Col2 ORDER BY Col1) <> 1;

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., Vous pouvez ensuite supprimer les lignes directement du CTE.

le Succès! Les lignes supplémentaires ont été supprimées et un ensemble unique de lignes reste.

pour voir la différence entre ROW_NUMBER, RANK et DENSE_RANK, exécutez cette requête:

La ORDER BY pour chaque clause OVER est OrderDate qui n’est pas unique. Ce client a passé deux commandes le 24/10/2013. ROW_NUMBER a simplement continué à attribuer des numéros et n’a rien fait de différent même s’il y a une date en double., Rang attribué 6 aux deux lignes, puis rattrapé à ROW_NUMBER avec un 8 sur la ligne suivante. DENSE_RANK a également attribué 6 aux deux lignes mais 7 à la ligne suivante.

Deux expliquent la différence, pensez à ROW_NUMBER comme positionnel. Le rang est à la fois positionnel et logique. Ces deux lignes sont classées logiquement de la même manière, mais la ligne suivante est classée par la position dans l’ensemble. DENSE_RANK les classe logiquement. L’ordre 2013-11-04 est la 7e date unique.

la fonction finale de ce groupe s’appelle NTILE. Il attribue des numéros de compartiment aux lignes au lieu de numéros de ligne ou de rangs., Voici un exemple:

NTILE a un paramètre, dans ce cas 4, qui est le nombre de compartiments que vous voulez voir dans les résultats. La commande par est appliquée à la somme des ventes. Les lignes avec les 25% les plus faibles sont assignées 1, les lignes avec les 25% les plus élevés sont assignées 4. Enfin, les résultats de NTILE sont multipliés par 1000 pour arriver au montant du bonus. Comme 14 ne peut pas être divisé uniformément par 4, une rangée supplémentaire va dans chacun des deux premiers seaux.

agrégats de fenêtres

les agrégats de fenêtres ont également été introduits avec SQL Server 2005., Ceux-ci facilitent l’écriture de requêtes délicates, mais fonctionneront souvent moins bien que les techniques plus anciennes. Ils vous permettent d’ajouter votre fonction d’agrégation préférée à une requête non agrégée. Disons, par exemple, que vous souhaitez afficher toutes les commandes des clients avec le sous-total pour chaque client., En ajoutant une somme en utilisant la clause OVER, vous pouvez accomplir cela très facilement:

sélectionnez CustomerID, OrderDate, SalesOrderID, totaldue,

Sum(totaldue) over(partition by CustomerID) as subtotal

from sales.,SalesOrderHeader;

Par l’ajout de la PARTITION PAR un sous-total est calculé pour chaque client. Toute fonction d’agrégation peut être utilisée et ORDER BY dans la clause OVER n’est pas prise en charge.

améliorations des agrégats de fenêtres en 2012

à partir de 2012, vous pouvez ajouter un ordre Par à la clause OVER aux agrégats de fenêtres pour produire des totaux courants et des moyennes mobiles, par exemple. Dans le même temps, Microsoft a introduit le concept de cadrage. Ajouter une PARTITION par est comme diviser une fenêtre en volets., Ajouter un encadrement, c’est comme créer un vitrail. Chaque ligne a une fenêtre individuelle où l’expression sera appliquée.

Avec cette amélioration, vous pouvez créer des totaux en cours d’exécution même sans ajouter la syntaxe de cadrage., Here is an example that returns a running total by customer:

SELECT CustomerID, OrderDate, SalesOrderID, TotalDue,

SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)

AS RunningTotal

FROM Sales.,SalesOrderHeader;

la trame par défaut, qui est utilisée si une trame n’est pas spécifiée, est comprise entre la ligne précédente et la ligne actuelle Non bornée. Malheureusement, cela ne fonctionnera pas aussi bien que si vous spécifiez ce cadre à la place: lignes entre la ligne précédente et la ligne actuelle Non bornée. La différence est le mot lignes. RANGE n’est que partiellement implémenté pour le moment, et il est destiné à travailler avec des périodes de temps, tandis que ROWS est positionnel., Le cadre, lignes entre la ligne précédente et la ligne actuelle Non bornée, signifie que la fenêtre se compose de la première ligne de la partition et de toutes les lignes jusqu’à la ligne actuelle. Chaque calcul est effectué sur un ensemble de lignes différent. Par exemple, lors du calcul de la ligne 4, les lignes 1 à 4 sont utilisées.

Lors de l’exécution du calcul de la ligne 5, les lignes de 1 à 5. La fenêtre s’agrandit à mesure que vous passez d’une rangée à l’autre.,

Vous pouvez également utiliser la syntaxe lignes entre N précédant et la ligne actuelle ou lignes entre la ligne actuelle et N suivant. Cela pourrait être utile pour calculer une moyenne mobile de trois mois, par exemple. La figure suivante représente les lignes entre 2 lignes précédentes et actuelles.

Quand 5 est la ligne actuelle, la fenêtre se déplace; il ne change pas de taille.,

Voici la liste de conditions que vous devez savoir lors de l’écriture de l’option de cadrage:

j’avoue que cette syntaxe est un peu confus, mais à l’aide de SQL Prompt permet l’écriture de l’option de cadrage plus facile!

Offset Functions

également inclus avec la version de SQL Server 2012 sont quatre fonctions qui vous permettent d’inclure des valeurs d’autres lignes – sans faire une auto-jointure. Microsoft appelle ces « fonctions analytiques », mais je les appelle toujours « fonctions de décalage » lors de la présentation sur ce sujet., Deux des fonctions vous permettent d’extraire des colonnes ou des expressions d’une ligne avant (LAG) ou après (LEAD) la ligne en cours. Les deux autres fonctions vous permettent de renvoyer des valeurs de la première ligne de la partition (FIRST_VALUE) ou de la dernière ligne de la partition (LAST_VALUE). FIRST_VALUE et LAST_VALUE nécessitent également un cadrage, alors assurez-vous d’inclure le cadre lors de l’utilisation de ces fonctions. Les quatre fonctions nécessitent L’option ORDER BY de la clause OVER. Cela a du sens, car le moteur de base de données doit connaître l’ordre des lignes pour déterminer quelle ligne contient la valeur à renvoyer.,

Certaines personnes ont un groupe préféré; certaines personnes ont un film préféré. J’ai une fonction préférée-LAG. Il est facile à utiliser (pas de cadre!) et effectue une grande., Here is an example:

SELECT CustomerID, OrderDate, SalesOrderID,

LAG(SalesOrderID) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID

) AS PrevOrder

FROM Sales.,SalesOrderHeader

COMMANDE PAR code client;

GAL et de PLOMB besoin d’un argument – la colonne ou l’expression que vous souhaitez retourner. Par défaut, LAG renvoie la valeur de la ligne précédente et LEAD renvoie la valeur de la ligne suivante. Vous pouvez modifier cela en fournissant une valeur pour le paramètre OFFSET, qui est 1 par défaut. Notez que la première ligne de la partition renvoie NULL. Si vous souhaitez remplacer les valeurs nulles, vous pouvez fournir une valeur par défaut., Here is a similar query that goes back two rows and has a default value:

SELECT CustomerID, OrderDate, SalesOrderID,

LAG(SalesOrderID,2,0) OVER(PARTITION BY CustomerID

ORDER BY SalesOrderID) AS Back2Orders

FROM Sales.,SalesOrderHeader;

PREMIÈRE_VALEUR et LAST_VALUE peut être utilisé pour trouver une valeur dans la première ligne ou la dernière ligne de la partition. Assurez-vous de spécifier le cadre, non seulement pour des raisons de performances, mais parce que le cadre par défaut ne fonctionne pas comme prévu avec LAST_VALUE. La trame par défaut, comprise entre la ligne précédente et la ligne actuelle Non bornée, ne remonte qu’à la ligne actuelle. La dernière ligne de la partition n’est pas inclus., Pour obtenir les résultats attendus, assurez-vous de spécifier des lignes entre la ligne actuelle et la ligne non bornée suivante lorsque vous utilisez LAST_VALUE., Voici un exemple utilisant FIRST_VALUE:

select CustomerID, OrderDate, SalesOrderID,

first_value(SalesOrderID) over(partition by CustomerID

ORDER BY SalesOrderID

rows between unbounded preceding and current row) as firstorder

from sales.,SalesOrderHeader;

les Fonctions Statistiques

Microsoft groupes, ces quatre fonctions – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – avec le décalage fonctions appel à tous les huit fonctions analytiques. Puisque j’aime les distinguer des fonctions de décalage, je les appelle statistiques.

PERCENT_RANK et CUME_DIST fournissent un classement pour chaque ligne sur une partition. Ils diffèrent légèrement. PERCENT_RANK renvoie le pourcentage de lignes dont le rang est inférieur à la ligne en cours., « Mon score est supérieur à 90% des scores. »CUME_DIST, ou distribution cumulative, renvoie le rang exact. « Mon score est à 90% des scores. »Voici un exemple en utilisant la température moyenne élevée à St. Louis pour chaque mois. Notez que les rangs ont été déterminés par la température Fahrenheit.

Les rangs ne sont pas déterminés par les valeurs relatives, mais les positions des lignes. Notez que Mars et novembre ont la même température moyenne élevée, ils ont donc été classés de la même manière.

vous vous demandez peut-être comment calculer PERCENT_RANK et CUME_DIST., Voici les formules:

1 2	PERCENT_RANK = (Rang -1)/(nombre de lignes -1) CUME_DIST = (Rang)/(nombre de lignes)

PERCENTILE_DISC et PERCENTILE_CONT travaillent dans le sens inverse. Étant donné un rang en pourcentage, trouvez la valeur à ce rang., Ils diffèrent en ce que PERCENTILE_DISC retournera une valeur qui existe dans L’ensemble tandis que PERCENTILE_CONT calculera une valeur exacte si aucune des valeurs de l’ensemble ne tombe précisément à ce rang. Vous pouvez utiliser PERCENTILE_CONT pour calculer une médiane en fournissant 0,5 comme rang en pourcentage. Par exemple, quelle température se classe à 50% à St. Louis?

la fonction PERCENTILE_CONT prend la moyenne des deux valeurs les plus proches du milieu, 67 et 69, et les fait la moyenne. PERCENTILE_DISC renvoie une valeur exacte, 67., Notez également que ces deux fonctions ont une clause supplémentaire non vue dans les autres fonctions, dans le groupe, qui contient L’ordre Par au lieu de dans la clause OVER.

résumé

Cet article est un aperçu très rapide des fonctions de la fenêtre T-SQL. Deux types de fonctions ont été publiés avec SQL Server 2005, les fonctions de classement et les agrégats de fenêtres. Avec 2012, vous avez amélioré l’agrégat de fenêtres avec le cadrage et les fonctions analytiques. J’aime séparer les fonctions analytiques en deux groupes, les fonctions offset et statistiques., Les fonctions de fenêtre facilitent l’écriture de nombreuses requêtes, et je crois que c’est le principal avantage. Dans certains cas, les requêtes fonctionneront mieux, aussi, mais c’est une discussion pour un autre jour.

j’espère que cet article vous a inspiré, pour en savoir plus sur ces fonctions fantastiques!

Redgate Hub (Français)