LAG (Transact-SQL)
Accède aux données d'une ligne précédente dans le même jeu de résultats sans recourir à une jointure réflexive dans SQL Server 2012. LAG permet d'accéder à une ligne à un décalage physique donné qui précède la ligne actuelle. Utilisez cette fonction analytique dans une instruction SELECT pour comparer des valeurs sur la ligne actuelle avec des valeurs sur une ligne précédente.
Conventions de la syntaxe Transact-SQL (Transact-SQL)
Syntaxe
LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )
Arguments
scalar_expression
Valeur à retourner en fonction du décalage spécifié. Il s'agit d'une expression de tout type qui retourne une valeur (scalaire) unique. scalar_expression ne peut pas être une fonction analytique.offset
Nombre de lignes en arrière de la ligne actuelle à partir de laquelle obtenir une valeur. Si cet argument n'est pas spécifié, la valeur par défaut est 1. offset peut être une colonne, une sous-requête ou une autre expression qui aboutit à un entier positif ou pouvant être converti en bigint. offset ne peut pas être une valeur négative ou une fonction analytique.default
Valeur à retourner lorsque scalar_expression à offset est NULL. Si aucune valeur par défaut n'est spécifiée, la valeur NULL est renvoyée. default peut être une colonne, une sous-requête ou une autre expression, mais il ne peut pas s'agir d'une fonction analytique. Le type default doit être compatible avec scalar_expression.OVER ( [ partition_by_clause ] order_by_clause**)**
partition_by_clause divise le jeu de résultats généré par la clause FROM en partitions auxquelles la fonction est appliquée. S'il n'est pas spécifié, la fonction gère toutes les lignes du jeu de résultats de la requête en un seul groupe. order_by_clause détermine l'ordre des données avant que la fonction soit appliquée. Si partition_by_clause est spécifié, il détermine l'ordre des données dans la partition. L'attribut order_by_clause est requis. Pour plus d'informations, consultez Clause OVER (Transact-SQL).
Type des valeurs retournées
Type de données de l'objet scalar_expression spécifié. NULL est retourné si scalar_expression peut avoir la valeur NULL ou si default a la valeur NULL.
Exemples
A.Comparer des valeurs entre des années
L'exemple suivant utilise la fonction LAG pour retourner la différence dans les quotas de ventes pour un employé spécifique sur les années précédentes. Notez qu'étant donné qu'il n'y a aucune valeur de décalage pour la première ligne, la valeur par défaut de zéro (0) est retournée.
USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');
Voici l'ensemble des résultats.
BusinessEntityID SalesYear CurrentQuota PreviousQuota
---------------- ----------- --------------------- ---------------------
275 2005 367000.00 0.00
275 2005 556000.00 367000.00
275 2006 502000.00 556000.00
275 2006 550000.00 502000.00
275 2006 1429000.00 550000.00
275 2006 1324000.00 1429000.00
B.Comparer des valeurs dans des partitions
L'exemple suivant utilise la fonction LAG pour comparer les ventes annuelles cumulées entre les employés. La clause PARTITION BY est spécifiée pour diviser les lignes du jeu de résultats par secteur de vente. La fonction LAG est appliquée à chaque partition séparément et le calcul redémarre pour chaque partition. La clause ORDER BY de la clause OVER ordonnance les lignes dans chaque partition. La clause ORDER BY dans l'instruction SELECT trie les lignes dans le jeu de résultats entier. Notez qu'étant donné qu'il n'y a aucune valeur de décalage pour la première ligne de chaque partition, la valeur par défaut de zéro (0) est retournée.
USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;
Voici l'ensemble des résultats.
TerritoryName BusinessEntityID SalesYTD PrevRepSales
----------------------- ---------------- --------------------- ---------------------
Canada 282 2604540.7172 0.00
Canada 278 1453719.4653 2604540.7172
Northwest 284 1576562.1966 0.00
Northwest 283 1573012.9383 1576562.1966
Northwest 280 1352577.1325 1573012.9383
C.Spécification d'expressions arbitraires
L'exemple suivant illustre la spécification de diverses expressions arbitraires dans la syntaxe de la fonction LAG.
CREATE TABLE T (a int, b int, c int);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);
SELECT b, c,
LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;
Voici l'ensemble des résultats.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL NULL
1 5 NULL