LAG (Transact-SQL)
gäller för:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-analysslutpunkt i Microsoft FabricWarehouse i Microsoft Fabric
Kommer åt data från en tidigare rad i samma resultatuppsättning utan att använda en självkoppling från och med SQL Server 2012 (11.x). LAG ger åtkomst till en rad vid en viss fysisk förskjutning som kommer före den aktuella raden. Använd den här analysfunktionen i en SELECT-instruktion för att jämföra värden i den aktuella raden med värden i en föregående rad.
Transact-SQL syntaxkonventioner
Syntax
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Argument
scalar_expression
Det värde som ska returneras baserat på den angivna förskjutningen. Det är ett uttryck av alla typer som returnerar ett enda värde (skalärt). scalar_expression kan inte vara en analysfunktion.
förskjutning
Antalet rader tillbaka från den aktuella raden som ett värde ska hämtas från. Om det inte anges är standardvärdet 1.
förskjutning kan vara en kolumn, underfråga eller ett annat uttryck som utvärderas till ett positivt heltal eller implicit kan konverteras till bigint.
förskjutning får inte vara ett negativt värde eller en analysfunktion.
standard
Värdet som ska returneras när förskjutning ligger utanför partitionens omfång. Om inget standardvärde har angetts returneras NULL.
standard kan vara en kolumn, underfråga eller ett annat uttryck, men det kan inte vara en analysfunktion.
standard måste vara typkompatibel med scalar_expression.
[ IGNORERA NULLS | RESPECT NULLS ]
gäller för: SQL Server 2022 (16.x) och senare versioner, Azure SQL Database, Azure SQL Managed Instance och Azure SQL Edge
IGNORE NULLS – Ignorera null-värden i datamängden när du beräknar det första värdet över en partition.
RESPECT NULLS – Respektera null-värden i datamängden när du beräknar det första värdet över en partition.
RESPECT NULLS är standardbeteendet om inget NULLS-alternativ har angetts.
Det uppstod en felkorrigering i SQL Server 2022 CU4 relaterad till IGNORE NULLS i LAG och LEAD.
Mer information om det här argumentet i Azure SQL Edge finns i Imputing missing values.
ÖVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause delar upp resultatuppsättningen som skapas av FROM-satsen i partitioner som funktionen tillämpas på. Om det inte anges behandlar funktionen alla rader i frågeresultatuppsättningen som en enda grupp. order_by_clause avgör ordningen på data innan funktionen tillämpas. Om partition_by_clause anges avgörs ordningen på data i partitionen. order_by_clause krävs. Mer information finns i OVER-satsen (Transact-SQL).
Returtyper
Datatypen för den angivna scalar_expression. NULL returneras om scalar_expression är null eller standard är inställt på NULL.
Allmänna kommentarer
LAG är icke-terministisk. Mer information finns i Deterministiska och nondeterministiska funktioner.
Exempel
A. Jämför värden mellan år
I följande exempel används funktionen LAG för att returnera skillnaden i försäljningskvoter för en viss medarbetare jämfört med tidigare år. Observera att eftersom det inte finns något tillgängligt fördröjningsvärde för den första raden returneras standardvärdet noll (0).
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');
Här är resultatuppsättningen.
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. Jämför värden i partitioner
I följande exempel används funktionen LAG för att jämföra försäljning hittills i år mellan anställda. PARTITION BY-satsen anges för att dela upp raderna i resultatuppsättningen efter försäljningsområde. FUNKTIONEN LAG tillämpas på varje partition separat och beräkningen startas om för varje partition. ORDER BY-satsen i OVER-satsen beställer raderna i varje partition. ORDER BY-satsen i SELECT-instruktionen sorterar raderna i hela resultatuppsättningen. Observera att eftersom det inte finns något tillgängligt fördröjningsvärde för den första raden i varje partition returneras standardvärdet noll (0).
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;
Här är resultatuppsättningen.
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. Ange godtyckliga uttryck
I följande exempel visas hur du anger olika godtyckliga uttryck och ignorerar NULL-värden i syntaxen för FUNKTIONEN 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;
Här är resultatuppsättningen.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. Använd IGNORE NULLS för att hitta icke-NULL-värden
Följande exempelfråga visar hur du använder argumentet IGNORE NULLS.
ARGUMENTET IGNORE NULLS används med både LAG och LEAD- för att demonstrera ersättning av NULL-värden för föregående eller nästa icke-NULL-värden.
- Om föregående rad innehöll NULL med
LAGanvänder den aktuella raden det senaste icke-NULL-värdet. - Om nästa rad innehåller en NULL med
LEADanvänder den aktuella raden nästa tillgängliga icke-NULL-värde.
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. Använd RESPECT NULLS för att behålla NULL-värden
Följande exempelfråga visar hur du använder argumentet RESPECT NULLS, vilket är standardbeteendet om det inte anges, i motsats till argumentet IGNORE NULLS i föregående exempel.
- Om föregående rad innehöll NULL med
LAGanvänder den aktuella raden det senaste värdet. - Om nästa rad innehåller en NULL med
LEADanvänder den aktuella raden nästa värde.
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
Exempel: Azure Synapse Analytics and Analytics Platform System (PDW)
A. Jämför värden mellan kvartal
I följande exempel visas funktionen LAG. Frågan använder funktionen LAG för att returnera skillnaden i försäljningskvoter för en viss anställd under tidigare kalenderk kvartal. Observera att eftersom det inte finns något tillgängligt fördröjningsvärde för den första raden returneras standardvärdet noll (0).
-- 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;
Här är resultatuppsättningen.
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