Comment utiliser SQL: 2011 fonctions de la fenêtre

Ajouté dans SQL: 2011 sont cinq fonctions de la fenêtre qui évaluent une expression dans une rangée R2 qui est quelque part dans le cadre de la ligne de courant R1 de la fenêtre. Les fonctions sont LAG

Ces fonctions vous permettent d'extraire des informations à partir des lignes spécifiées qui sont dans le cadre de la ligne actuelle de la fenêtre.

Regarder en arrière avec la fonction de LAG

La LAG fonction vous permet de récupérer des informations à partir de la ligne en cours dans la fenêtre que vous examinez ainsi que des informations d'une autre ligne que vous spécifiez qui précède la ligne actuelle.

Supposons, par exemple, que vous avez une table qui enregistre les ventes totales pour chaque jour de l'année courante. Une chose que vous pourriez vouloir savoir comment les ventes d'aujourd'hui se comparent à hier. Vous pouvez le faire avec le LAG fonction, comme suit:

SELECT TotalSale AS TodaySale, LAG (TotalSale) OVER (ORDER BY SaleDate) AS PrevDaySaleFROM DailyTotals-

Pour chaque ligne DailyTotals, cette requête reviendrait une rangée Liste des ventes totales du chiffre de cette ligne et de chiffre d'affaires total de la journée précédente. Le décalage par défaut est 1, ce qui est la raison pour laquelle le résultat de la journée précédente est retourné plutôt qu'une autre.

Pour comparer les ventes de la journée en cours à celles d'une semaine avant, vous pouvez utiliser le texte suivant:

SELECT TotalSale AS TodaySale, LAG (TotalSale, 7) OVER (ORDER BY SaleDate) AS PrevDaySaleFROM DailyTotals-

Les sept premières rangées dans un cadre de fenêtre ne seront pas avoir un prédécesseur qui est une semaine plus. La réponse par défaut à cette situation est de retourner un résultat nul pour PrevDaySale. Si vous préférez un autre résultat à un résultat nul, par exemple zéro, vous pouvez spécifier que vous voulez retourner dans cette situation au lieu de la valeur nulle par défaut, par exemple, 0 (zéro), comme indiqué ici:

SELECT TotalSale AS TodaySale, LAG (TotalSale, 7, 0) OVER (ORDER BY SaleDate) AS PrevDaySaleFROM DailyTotals-

Le comportement par défaut est de compter les lignes qui ont un retard de mesure, qui dans ce cas est TotalSale, qui contient une valeur nulle. Si vous voulez sauter sur ces lignes et ne compter que les lignes qui ont une valeur réelle dans la mesure de retard, vous pouvez le faire en ajoutant des mots-clés IGNORE NULLS comme représenté sur la variante suivante de l'exemple:

SELECT TotalSale AS TodaySale, LAG (TotalSale, 7, 0) IGNORE NULLSOVER (ORDER BY SaleDate) AS PrevDaySaleFROM DailyTotals-

Regardez devant vous avec la fonction LEAD

La CONDUIRE fonction de fenêtre fonctionne exactement de la même manière le LAG fonction fonctionne, sauf que, au lieu de regarder en arrière à une rangée précédente, il se tourne vers une rangée suivant la ligne en cours dans le cadre de la fenêtre. Un exemple pourrait être:

SELECT TotalSale AS TodaySale, LEAD (TotalSale, 7, 0) IGNORE NULLSOVER (ORDER BY SaleDate) AS NextDaySaleFROM DailyTotals-

Regardez à une ligne spécifiée avec la fonction nth_value

La Nth_value fonction est similaire à la LAG et CONDUIRE fonctions, sauf qu'au lieu d'évaluer une expression dans une rangée précédant ou suivant la ligne actuelle, il évalue une expression dans une rangée qui est à un décalage spécifié de la première ou la dernière rangée dans le cadre de la fenêtre.

Voici un exemple:

SELECT TotalSale AS ChosenSale, nth_value (TotalSale, 2) DE FIRSTIGNORE NULLSOVER (ORDER BY SaleDate) rangées entre 10 précédente et 10 suivant) AS EarlierSaleFROM DailyTotals-

Dans cet exemple, EarlierSale est évalué comme suit:

• Le cadre de la fenêtre associée à la ligne actuelle est formée. Il comprend les dix précédentes et les dix lignes suivantes.

• TotalSale est évalué dans chaque rangée de la trame de la fenêtre.

• IGNORE NULLS est spécifié, alors toutes les lignes contenant une valeur nulle pour TotalSale sont ignorés.

• A partir de la première valeur restant après l'exclusion des lignes contenant une valeur nulle pour TotalSale, aller de l'avant par deux rangées (en avant, car DE PREMIÈRE a été spécifié).

La valeur de EarlierSale est la valeur de TotalSale à partir de la ligne spécifiée.

Si vous ne voulez pas ignorer les lignes qui ont une valeur nulle pour TotalSale, spécifier NULLS RESPECT plutôt que IGNORE NULLS. La Nth_value fonction fonctionne de façon similaire si vous spécifiez DE LA DERNIÈRE au lieu de DE PREMIÈRE, sauf qu'au lieu de compter en avant du premier enregistrement dans le cadre de la fenêtre, vous comptez à rebours à partir du dernier enregistrement dans le cadre de la fenêtre.

Le nombre spécifiant le nombre de lignes à compter est toujours positif, même si vous comptez en arrière plutôt que vers l'avant.

Regardez à une valeur très spécifique avec FIRST_VALUE et LAST_VALUE

La FIRST_VALUE et LAST_VALUE fonctions sont des cas particuliers de la Nth_value fonction. FIRST_VALUE est équivalent à Nth_value où DE PREMIÈRE est spécifié et le décalage est 0 (zéro). LAST_VALUE est équivalent à Nth_value où DE LA DERNIÈRE est spécifié et le décalage est 0. Avec ces deux, vous pouvez choisir de l'ignorer ou respecter les valeurs NULL.