LAG (Transact-SQL)
platí pro:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsPlatform Platform System (PDW)koncový bod SQL Analytics ve službě Microsoft FabricWarehouse v Microsoft Fabric
Přistupuje k datům z předchozího řádku ve stejné sadě výsledků bez použití samoobslužného spojení počínaje SQL Serverem 2012 (11.x). LAG poskytuje přístup k řádku na daném fyzickém posunu, který přichází před aktuálním řádkem. Pomocí této analytické funkce v příkazu SELECT můžete porovnat hodnoty v aktuálním řádku s hodnotami v předchozím řádku.
Syntax
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Argumenty
scalar_expression
Hodnota, která se má vrátit na základě zadaného posunu. Jedná se o výraz libovolného typu, který vrací jednu (skalární) hodnotu. scalar_expression nemůže být analytická funkce.
posunu
Početřádkůch Pokud není zadáno, výchozí hodnota je 1.
posunu může být sloupec, poddotaz nebo jiný výraz, který se vyhodnotí jako kladné celé číslo nebo lze implicitně převést na bigint.
posun nemůže být záporná hodnota ani analytická funkce.
výchozí
Hodnota, která se má vrátit, když posun je nad rámec oddílu. Pokud není zadána výchozí hodnota, vrátí se hodnota NULL.
výchozí může být sloupec, poddotaz nebo jiný výraz, ale nemůže to být analytická funkce.
výchozí musí být kompatibilní s scalar_expression.
[ IGNOROVAT HODNOTY NULL | RESPECT NULLS ]
platí pro: SQL Server 2022 (16.x) a novější verze, Azure SQL Database, Azure SQL Managed Instance a Azure SQL Edge
IGNORE NULLS – Při výpočtu první hodnoty v oddílu v datové sadě ignorujte hodnoty null.
RESPECT NULLS – Při výpočtu první hodnoty v oddílu respektujte hodnoty null v datové sadě.
RESPECT NULLS je výchozí chování, pokud není zadána možnost NULLS.
V SQL Serveru 2022 CU4 došlo k
Další informace o tomto argumentu v Azure SQL Edge najdete v tématu Imputing missing values.
OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause rozdělí sadu výsledků vytvořenou klauzulí FROM na oddíly, na které se funkce použije. Pokud není zadáno, funkce zachází se všemi řádky sady výsledků dotazu jako s jednou skupinou. order_by_clause určuje pořadí dat před použití funkce. Pokud je zadán partition_by_clause, určuje pořadí dat v oddílu. Vyžaduje se order_by_clause. Další informace naleznete v tématu OVER – klauzule (Transact-SQL).
Návratové typy
Datový typ zadaného scalar_expression. Hodnota NULL se vrátí, pokud je scalar_expression s možnou hodnotou null nebo výchozí nastavena na hodnotu NULL.
Obecné poznámky
LAG je nedeterministické. Další informace naleznete v tématu Deterministické a nedeterministické funkce.
Příklady
A. Porovnání hodnot mezi roky
Následující příklad používá funkci LAG k vrácení rozdílu v prodejních kvótách pro konkrétního zaměstnance za předchozí roky. Všimněte si, že vzhledem k tomu, že pro první řádek není k dispozici žádná prodleva, vrátí se výchozí hodnota nula (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');
Tady je sada výsledků.
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. Porovnání hodnot v rámci oddílů
Následující příklad používá funkci LAG k porovnání prodeje mezi zaměstnanci mezi roky a datem. Klauzule PARTITION BY je určena k rozdělení řádků v sadě výsledků podle prodejního teritoria. Funkce LAG se použije pro každý oddíl samostatně a pro každý oddíl se restartuje výpočty. Klauzule ORDER BY v klauzuli OVER seřadí řádky v jednotlivých oddílech. Klauzule ORDER BY v příkazu SELECT seřadí řádky v celé sadě výsledků. Všimněte si, že protože pro první řádek každého oddílu není k dispozici žádná prodleva, vrátí se výchozí hodnota nula (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;
Tady je sada výsledků.
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. Určení libovolných výrazů
Následující příklad ukazuje zadání různých libovolných výrazů a ignorování hodnot NULL v syntaxi funkce 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;
Tady je sada výsledků.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. Použití funkce IGNORE NULLS k vyhledání hodnot, které nejsou null
Následující ukázkový dotaz ukazuje použití argumentu IGNORE NULLS.
Argument IGNORE NULLS se používá s LAG a LEAD demonstrovat nahrazení hodnot NULL pro předchozí nebo další hodnoty, které nemají hodnotu NULL.
- Pokud předchozí řádek obsahoval hodnotu NULL s
LAG, použije aktuální řádek nejnovější hodnotu, která není null. - Pokud další řádek obsahuje hodnotu NULL s
LEAD, použije aktuální řádek další dostupnou hodnotu, která není 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. Použití FUNKCE RESPECT NULLS k zachování hodnot NULL
Následující ukázkový dotaz ukazuje použití argumentu RESPECT NULLS, což je výchozí chování, pokud není zadáno, na rozdíl od argumentu IGNORE NULLS v předchozím příkladu.
- Pokud předchozí řádek obsahoval hodnotu NULL s
LAG, použije aktuální řádek nejnovější hodnotu. - Pokud další řádek obsahuje hodnotu NULL s
LEAD, použije aktuální řádek další hodnotu.
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
Příklady: Azure Synapse Analytics a Analytický platformový systém (PDW)
A. Porovnání hodnot mezi čtvrtletími
Následující příklad ukazuje funkci LAG. Dotaz používá funkci LAG k vrácení rozdílu v prodejních kvótách pro konkrétního zaměstnance v předchozích kalendářních čtvrtletích. Všimněte si, že vzhledem k tomu, že pro první řádek není k dispozici žádná prodleva, vrátí se výchozí hodnota nula (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;
Tady je sada výsledků.
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
Další kroky
-
LEAD (Transact-SQL) -
FIRST_VALUE (Transact-SQL) - LAST_VALUE (Transact-SQL)
- SELECT – klauzule OVER (Transact-SQL)