LAG (Transact-SQL)
Dotyczy:
programu SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)punkt końcowy analizy SQL w usłudze Microsoft FabricWarehouse w usłudze Microsoft Fabric
Uzyskuje dostęp do danych z poprzedniego wiersza w tym samym zestawie wyników bez użycia sprzężenia samodzielnego, począwszy od programu SQL Server 2012 (11.x). Opóźnienie zapewnia dostęp do wiersza przy danym przesunięcie fizycznym, które następuje przed bieżącym wierszem. Użyj tej funkcji analitycznej w instrukcji SELECT, aby porównać wartości w bieżącym wierszu z wartościami w poprzednim wierszu.
Transact-SQL konwencje składni
Składnia
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Argumenty
scalar_expression
Wartość, która ma zostać zwrócona na podstawie określonego przesunięcia. Jest to wyrażenie dowolnego typu, które zwraca pojedynczą (skalarną) wartość. scalar_expression nie może być funkcją analityczną.
przesunięcia
Liczba wierszy z powrotem z bieżącego wiersza, z którego ma być uzyskiwana wartość. Jeśli nie zostanie określony, wartość domyślna to 1.
przesunięcie może być kolumną, podzapytaniem lub innym wyrażeniem, które daje w wyniku dodatnią liczbę całkowitą lub może zostać niejawnie przekonwertowane na bigint.
przesunięcie nie może być wartością ujemną ani funkcją analityczną.
domyślne
Wartość, która ma być zwracana, gdy przesunięcie wykracza poza zakres partycji. Jeśli nie określono wartości domyślnej, zwracana jest wartość NULL.
domyślne może być kolumną, podzapytaniem lub innym wyrażeniem, ale nie może być funkcją analityczną.
domyślne muszą być zgodne z typem scalar_expression.
[ IGNORUJ WARTOŚCI NULL | RESPECT NULLS ]
Dotyczy: SQL Server 2022 (16.x) i nowsze wersje, Azure SQL Database, Azure SQL Managed Instance i Azure SQL Edge
IGNORUJ WARTOŚCI NULL — ignoruj wartości null w zestawie danych podczas obliczania pierwszej wartości na partycji.
RESPECT NULLS — uwzględnia wartości null w zestawie danych podczas obliczania pierwszej wartości na partycji.
RESPECT NULLS jest zachowaniem domyślnym, jeśli nie określono opcji NULLS.
Usunięto usterkę w programie SQL Server 2022 CU4 związane z ignorowanie wartości NULL w LAG i LEAD.
Aby uzyskać więcej informacji na temat tego argumentu w usłudze Azure SQL Edge, zobacz Przypisywanie brakujących wartości.
OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause dzieli zestaw wyników generowany przez klauzulę FROM na partycje, do których jest stosowana funkcja. Jeśli nie zostanie określony, funkcja traktuje wszystkie wiersze zestawu wyników zapytania jako pojedynczą grupę. order_by_clause określa kolejność danych przed zastosowaniem funkcji. Jeśli określono partition_by_clause, określa kolejność danych w partycji. Wymagany jest order_by_clause. Aby uzyskać więcej informacji, zobacz OVER Clause (Transact-SQL).
Typy zwracane
Typ danych określonego scalar_expression. Wartość NULL jest zwracana, jeśli scalar_expression jest dopuszczana do wartości null lub domyślna jest ustawiona na wartość NULL.
Uwagi ogólne
Opóźnienie jest nieokreślone. Aby uzyskać więcej informacji, zobacz Funkcje deterministyczne i niedeterministyczne.
Przykłady
A. Porównywanie wartości między latami
W poniższym przykładzie użyto funkcji LAG, aby zwrócić różnicę w limitach przydziału sprzedaży dla określonego pracownika w poprzednich latach. Zwróć uwagę, że ponieważ nie ma dostępnej wartości opóźnienia dla pierwszego wiersza, zwracana jest wartość domyślna zera (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');
Oto zestaw wyników.
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. Porównywanie wartości w partycjach
W poniższym przykładzie użyto funkcji LAG, aby porównać sprzedaż z roku na datę między pracownikami. Klauzula PARTITION BY jest określona, aby podzielić wiersze w zestawie wyników według terytorium sprzedaży. Funkcja LAG jest stosowana do każdej partycji oddzielnie, a ponowne uruchamianie obliczeń dla każdej partycji. Klauzula ORDER BY w klauzuli OVER porządkuje wiersze w każdej partycji. Klauzula ORDER BY w instrukcji SELECT sortuje wiersze w całym zestawie wyników. Zwróć uwagę, że ponieważ nie ma dostępnej wartości opóźnienia dla pierwszego wiersza każdej partycji, zwracana jest wartość domyślna zera (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;
Oto zestaw wyników.
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. Określanie dowolnych wyrażeń
W poniższym przykładzie pokazano określanie różnych wyrażeń dowolnych i ignorowanie wartości NULL w składni funkcji 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;
Oto zestaw wyników.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. Użyj funkcji IGNORE NULLS, aby znaleźć wartości inne niż NULL
Poniższe przykładowe zapytanie demonstruje użycie argumentu IGNORE NULLS.
Argument IGNORE NULLS jest używany zarówno z LAG, jak i LEAD demonstrować podstawienie wartości NULL dla poprzednich lub następnych wartości innych niż NULL.
- Jeśli poprzedni wiersz zawierał wartość NULL z
LAG, bieżący wiersz używa najnowszej wartości innej niż NULL. - Jeśli następny wiersz zawiera wartość NULL z
LEAD, bieżący wiersz używa następnej dostępnej wartości innej niż NULL.
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. Użyj parametru RESPECT NULLS, aby zachować wartości NULL
Poniższe przykładowe zapytanie demonstruje użycie argumentu RESPECT NULLS, który jest zachowaniem domyślnym, jeśli nie zostanie określony, w przeciwieństwie do argumentu IGNORE NULLS w poprzednim przykładzie.
- Jeśli poprzedni wiersz zawierał wartość NULL z
LAG, bieżący wiersz używa najnowszej wartości. - Jeśli następny wiersz zawiera wartość NULL z
LEAD, bieżący wiersz używa następnej wartości.
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
Przykłady: Azure Synapse Analytics and Analytics Platform System (PDW)
A. Porównywanie wartości między kwartałami
W poniższym przykładzie pokazano funkcję LAG. Zapytanie używa funkcji LAG, aby zwrócić różnicę w limitach przydziału sprzedaży dla określonego pracownika w poprzednich kwartałach kalendarzowych. Zwróć uwagę, że ponieważ nie ma dostępnej wartości opóźnienia dla pierwszego wiersza, zwracana jest wartość domyślna zera (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;
Oto zestaw wyników.
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