LAG (Transact-SQL)
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL analytics endpoint in Microsoft FabricWarehouse in Microsoft Fabric
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, à compter de SQL Server 2012 (11.x). 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
Syntaxe
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
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 peut être converti en bigint.
offset ne peut pas être une valeur négative ou une fonction analytique.
default
Valeur à retourner quand offset est au-delà de l’étendue de la partition. 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 ne peut pas être une fonction analytique.
default doit être compatible en matière de type avec scalar_expression.
[ IGNORER NULLS | RESPECTER NULLS ]
s’applique à: SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database, Azure SQL Managed Instance et Azure SQL Edge
IGNORER NULLS : Ignore les valeurs Null dans le jeu de données lors du calcul de la première valeur sur une partition.
RESPECTER NULLS : Respecte les valeurs Null dans le jeu de données lors du calcul de la première valeur sur une partition.
RESPECT NULLS est le comportement par défaut si aucune option NULLS n’est spécifiée.
Il y a eu un correctif de bogue dans SQL Server 2022 CU4 lié à IGNORE NULLS dans LAG et LEAD.
Pour plus d’informations sur cet argument dans Azure SQL Edge, consultez Entrée de valeurs manquantes.
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. order_by_clause est requis. Pour plus d’informations, consultez Clause OVER (Transact-SQL).
Types de retour
Type de données de l’argument scalar_expression spécifié. La valeur NULL est renvoyée si scalar_expression est de type nullable ou si default est défini sur NULL.
Remarques d'ordre général
LAG n'est pas déterministe. Pour plus d’informations, consultez Fonctions déterministes et non déterministes.
Exemples
R. 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 que la valeur par défaut zéro (0) est retournée en raison de l'absence d'une valeur de décalage pour la première ligne.
USE AdventureWorks2022;
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 le jeu de 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 que la valeur par défaut zéro (0) est retournée en raison de l'absence d'une valeur de décalage pour la première ligne de chaque partition.
USE AdventureWorks2022;
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 le jeu de 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 montre comment spécifier diverses expressions arbitraires et ignorer les valeurs NULL 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) IGNORE NULLS OVER (ORDER BY a) AS i
FROM T;
Voici le jeu de résultats.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. Utiliser IGNORE NULLS pour rechercher des valeurs non NULL
L’exemple de requête suivant illustre l’utilisation de l’argument IGNORE NULLS.
L’argument IGNORE NULLS est utilisé avec LAG et LEAD pour illustrer la substitution de valeurs NULL pour les valeurs non NULL précédentes ou suivantes.
- Si la ligne précédente contenait NULL avec
LAG, la ligne actuelle utilise la valeur non NULL la plus récente. - Si la ligne suivante contient une valeur NULL avec
LEAD, la ligne actuelle utilise la valeur non NULL disponible suivante.
DROP TABLE IF EXISTS #test_ignore_nulls;
CREATE TABLE #test_ignore_nulls (column_a int, column_b int);
GO
INSERT INTO #test_ignore_nulls VALUES
(1, 8),
(2, 9),
(3, NULL),
(4, 10),
(5, NULL),
(6, NULL),
(7, 11);
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) IGNORE NULLS OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) IGNORE NULLS OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--cleanup
DROP TABLE #test_ignore_nulls;
column_a column_b Previous value for column_b Next value for column_b
1 8 NULL 9
2 9 8 10
3 NULL 9 10
4 10 9 11
5 NULL 10 11
6 NULL 10 11
7 11 10 NULL
E. Utiliser RESPECT NULLS pour conserver les valeurs NULL
L’exemple de requête suivant illustre l’utilisation de l’argument RESPECT NULLS, qui est le comportement par défaut s’il n’est pas spécifié, par opposition à l’argument IGNORE NULLS dans l’exemple précédent.
- Si la ligne précédente contenait NULL avec
LAG, la ligne actuelle utilise la valeur la plus récente. - Si la ligne suivante contient une valeur NULL avec
LEAD, la ligne actuelle utilise la valeur suivante.
DROP TABLE IF EXISTS #test_ignore_nulls;
CREATE TABLE #test_ignore_nulls (column_a int, column_b int);
GO
INSERT INTO #test_ignore_nulls VALUES
(1, 8),
(2, 9),
(3, NULL),
(4, 10),
(5, NULL),
(6, NULL),
(7, 11);
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) RESPECT NULLS OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) RESPECT NULLS OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--Identical output
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--cleanup
DROP TABLE #test_ignore_nulls;
column_a column_b Previous value for column_b Next value for column_b
1 8 NULL 9
2 9 8 NULL
3 NULL 9 10
4 10 NULL NULL
5 NULL 10 NULL
6 NULL NULL 11
7 11 NULL NULL
Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)
R. Comparer des valeurs entre des trimestres
L’exemple suivant illustre la fonction LAG. La requête utilise la fonction LAG pour renvoyer la différence dans les quotas de ventes pour un employé spécifique sur les trimestres calendaires précédents. Notez que la valeur par défaut zéro (0) est retournée en raison de l'absence d'une valeur de décalage pour la première ligne.
-- Uses AdventureWorks
SELECT CalendarYear, CalendarQuarter, SalesAmountQuota AS SalesQuota,
LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS PrevQuota,
SalesAmountQuota - LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001, 2002)
ORDER BY CalendarYear, CalendarQuarter;
Voici le jeu de résultats.
Year Quarter SalesQuota PrevQuota Diff
---- ------- ---------- --------- -------------
2001 3 28000.0000 0.0000 28000.0000
2001 4 7000.0000 28000.0000 -21000.0000
2001 1 91000.0000 7000.0000 84000.0000
2002 2 140000.0000 91000.0000 49000.0000
2002 3 7000.0000 140000.0000 -70000.0000
2002 4 154000.0000 7000.0000 84000.0000