LAG (Transact-SQL)
Gilt für:
SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-Endpunkt in Microsoft FabricWarehouse in Microsoft Fabric
Greift im selben Resultset auf Daten in einer vorherigen Zeile zu, ohne dass ein Selbstjoin ab SQL Server 2012 (11.x) verwendet wird. LAG ermöglicht den Zugriff auf eine Zeile mit einem bestimmten physischen Offset vor der aktuellen Zeile. Verwenden Sie diese analytische Funktion in einer SELECT-Anweisung, um Werte in der aktuellen Zeile mit Werten in einer vorherigen Zeile zu vergleichen.
Transact-SQL-Syntaxkonventionen
Syntax
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Hinweis
Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 (12.x) oder früher finden Sie unter Dokumentation zu früheren Versionen.
Argumente
scalar_expression
Der zurückzugebende Wert auf Grundlage des angegebenen Offsets. Dies ist ein Ausdruck eines beliebigen Typs, der einen einzelnen Skalarwert zurückgibt. scalar_expression darf keine analytische Funktion sein.
offset
Die Anzahl der Zeilen vor der aktuellen Zeile, aus der ein Wert abgerufen werden soll. Wenn nichts angegeben ist, wird der Standardwert 1 verwendet. offset kann eine Spalte, eine Unterabfrage oder ein anderer Ausdruck sein, der eine positive ganze Zahl ergibt, kann aber auch implizit in einen Wert vom Typ bigint konvertiert werden. offset darf kein negativer Wert bzw. keine analytische Funktion sein.
default
Der Wert, der zurückgegeben wird, wenn sich offset außerhalb des Partitionsbereichs befindet. Wenn kein Standardwert angegeben ist, wird NULL zurückgegeben. default kann eine Spalte, eine Unterabfrage oder ein anderer Ausdruck sein, jedoch keine analytische Funktion. default muss mit scalar_expression typkompatibel sein.
[ IGNORE NULLS | RESPECT NULLS ]
Gilt für: SQL Server (ab SQL Server 2022 (16.x)), Azure SQL-Datenbank und Azure SQL Managed Instance, Azure SQL Edge
IGNORE NULLS: Hiermit werden NULL-Werte im Dataset beim Berechnen des ersten Werts einer Partition ignoriert.
RESPECT NULLS: Hiermit werden NULL-Werte im Dataset beim Berechnen des ersten Werts einer Partition berücksichtigt. RESPECT NULLS Dies ist das Standardverhalten, wenn die Option NULLS nicht angegeben ist.
Fehlerkorrektur in SQL Server 2022 CU4 im Zusammenhang mit IGNORE NULLS in LAG und LEAD.
Weitere Informationen zu diesem Argument in Azure SQL Edge finden Sie unter Imputing fehlender Werte.
OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause unterteilt das von der FROM-Klausel erzeugte Resultset in Partitionen, auf die die Funktion angewendet wird. Wird dies nicht angegeben, verarbeitet die Funktion alle Zeilen des Abfrageresultsets als einzelne Gruppe. order_by_clause bestimmt die Reihenfolge der Daten, bevor die Funktion angewendet wird. Wenn partition_by_clause angegeben wird, wird hierdurch die Reihenfolge der Daten in der Partition bestimmt. order_by_clause ist erforderlich. Weitere Informationen finden Sie unter OVER-Klausel (Transact-SQL).
Rückgabetypen
Der Datentyp des angegebenen scalar_expression-Ausdrucks. NULL wird zurückgegeben, wenn scalar_expression auf NULL festgelegt werden kann oder default auf NULL festgelegt ist.
Allgemeine Hinweise
LAG ist nicht deterministisch. Weitere Informationen finden Sie unter Deterministic and Nondeterministic Functions.
Beispiele
A. Vergleichen von Werten aus verschiedenen Jahren
Im folgenden Beispiel wird mithilfe der LAG-Funktion die Differenz der Verkaufszahlen für einen bestimmten Mitarbeiter im Verlauf der Vorjahre zurückgegeben. Beachten Sie, dass der Standardwert 0 (null) zurückgegeben wird, da für die erste Zeile kein LAG-Wert verfügbar ist.
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');
Hier ist das Resultset.
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. Vergleichen von Werten innerhalb von Partitionen
Im folgenden Beispiel werden mithilfe der LAG-Funktion die Verkaufszahlen des laufenden Jahres verschiedener Mitarbeiter verglichen. Um die Zeilen im Resultset nach Vertriebsgebiet zu unterteilen, wird die PARTITION BY-Klausel angegeben. Die LAG-Funktion wird auf jede Partition einzeln angewendet, und die Berechnung wird für jede Partition neu gestartet. Die ORDER BY-Klausel in der OVER-Klausel sortiert die Zeilen in jeder Partition. Die ORDER BY-Klausel in der SELECT-Anweisung sortiert die Zeilen im gesamten Resultset. Beachten Sie, dass der Standardwert 0 (null) zurückgegeben wird, da für die erste Zeile jeder Partition kein LAG-Wert verfügbar ist.
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;
Hier ist das Resultset.
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. Angeben willkürlicher Ausdrücke
Im folgenden Beispiel wird das Angeben verschiedener willkürlicher Ausdrücke in der Syntax der LAG-Funktion veranschaulicht.
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;
Hier ist das Resultset.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. Verwenden von IGNORE NULLS zum Suchen von Nicht-NULL-Werten
Die folgende Beispielabfrage veranschaulicht die Verwendung des ARGUMENTS IGNORE NULLS.
Das ARGUMENT IGNORE NULLS wird sowohl mit LAG als auch mit LEAD verwendet, um die Ersetzung von NULL-Werten für vorangehende oder nächste nicht NULL-Werte zu veranschaulichen.
- Wenn die vorangehende Zeile NULL mit
LAGenthält, verwendet die aktuelle Zeile den letzten Wert ungleich NULL. - Wenn die nächste Zeile einen NULL-Wert mit
LEADenthält, verwendet die aktuelle Zeile den nächsten verfügbaren Wert ungleich 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. Verwenden von RESPECT NULLS, um NULL-Werte beizubehalten
Die folgende Beispielabfrage veranschaulicht die Verwendung des ARGUMENTS RESPECT NULLS, bei dem es sich um das Standardverhalten handelt, falls nicht angegeben, im Gegensatz zum IGNORE NULLS-Argument im vorherigen Beispiel.
- Wenn die vorangehende Zeile NULL mit
LAGenthält, verwendet die aktuelle Zeile den letzten Wert. - Wenn die nächste Zeile einen NULL-Wert mit
LEADenthält, verwendet die aktuelle Zeile den nächsten Wert.
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
Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)
A. Vergleichen von Werten aus verschiedenen Quartalen
Im folgenden Beispiel wird die LAG-Funktion vorgestellt. Die Abfrage verwendet die LAG-Funktion, um die Differenz der Sollvorgaben für den Verkauf für einen bestimmten Mitarbeiter im Verlauf der vorherigen Kalenderquartale zurückgegeben. Beachten Sie, dass der Standardwert 0 (null) zurückgegeben wird, da für die erste Zeile kein LAG-Wert verfügbar ist.
-- 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;
Hier ist das Resultset.
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
Nächste Schritte
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für