LAG (Transact-SQL)
van toepassing op:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-eindpunt in Microsoft FabricWarehouse in Microsoft Fabric
Hiermee opent u gegevens uit een vorige rij in dezelfde resultatenset zonder gebruik te maken van een self-join vanaf SQL Server 2012 (11.x). LAG biedt toegang tot een rij op een bepaalde fysieke offset die vóór de huidige rij wordt geleverd. Gebruik deze analytische functie in een SELECT-instructie om waarden in de huidige rij te vergelijken met waarden in een vorige rij.
Transact-SQL syntaxisconventies
Syntaxis
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Argumenten
scalar_expression
De waarde die moet worden geretourneerd op basis van de opgegeven offset. Het is een expressie van elk type dat één (scalaire) waarde retourneert. scalar_expression kan geen analytische functie zijn.
verschuiving
Het aantal rijen terug uit de huidige rij waaruit een waarde moet worden opgehaald. Als dit niet is opgegeven, is de standaardwaarde 1.
verschuiving kan een kolom, subquery of andere expressie zijn die resulteert in een positief geheel getal of impliciet kan worden geconverteerd naar bigint-.
verschuiving kan geen negatieve waarde of analysefunctie zijn.
standaard
De waarde die moet worden geretourneerd wanneer verschuiving buiten het bereik van de partitie valt. Als er geen standaardwaarde is opgegeven, wordt NULL geretourneerd.
standaard kan een kolom, subquery of andere expressie zijn, maar het kan geen analytische functie zijn.
standaard moet type-compatibel zijn met scalar_expression.
[ NULL-WAARDEN NEGEREN | RESPECT NULLS ]
Van toepassing op: SQL Server 2022 (16.x) en latere versies, Azure SQL Database, Azure SQL Managed Instance en Azure SQL Edge
IGNORE NULLS - Negeer null-waarden in de gegevensset bij het berekenen van de eerste waarde boven een partitie.
RESPECT NULLS : respecteer null-waarden in de gegevensset bij het berekenen van de eerste waarde boven een partitie.
RESPECT NULLS is het standaardgedrag als er geen NULLS-optie is opgegeven.
Er is een opgelost in SQL Server 2022 CU4- met betrekking tot IGNORE NULLS in LAG en LEAD.
Zie Ontbrekende waardeninvoeren voor meer informatie over dit argument in Azure SQL Edge.
OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause verdeelt de resultatenset die door de FROM-component wordt geproduceerd in partities waarop de functie wordt toegepast. Als deze niet is opgegeven, behandelt de functie alle rijen van de queryresultatenset als één groep. order_by_clause bepaalt de volgorde van de gegevens voordat de functie wordt toegepast. Als partition_by_clause is opgegeven, wordt de volgorde van de gegevens in de partitie bepaald. De order_by_clause is vereist. Zie OVER-component (Transact-SQL) voor meer informatie.
Retourtypen
Het gegevenstype van de opgegeven scalar_expression. NULL wordt geretourneerd als scalar_expression nullable is of standaard is ingesteld op NULL.
Algemene opmerkingen
LAG is niet-deterministisch. Zie Deterministische en niet-deterministische functiesvoor meer informatie.
Voorbeelden
Een. Waarden vergelijken tussen jaren
In het volgende voorbeeld wordt de lag-functie gebruikt om het verschil in verkoopquota voor een specifieke werknemer in de afgelopen jaren te retourneren. U ziet dat omdat er geen vertragingswaarde beschikbaar is voor de eerste rij, de standaardwaarde nul (0) wordt geretourneerd.
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');
Dit is de resultatenset.
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. Waarden in partities vergelijken
In het volgende voorbeeld wordt de lag-functie gebruikt om de verkoop van het jaar tot de datum tussen werknemers te vergelijken. De COMPONENT PARTITION BY wordt opgegeven om de rijen in de resultatenset te delen door verkoopgebied. De LAG-functie wordt afzonderlijk toegepast op elke partitie en de berekening wordt opnieuw gestart voor elke partitie. Met de ORDER BY-component in de OVER-component worden de rijen in elke partitie gerangschikt. De ORDER BY-component in de SELECT-instructie sorteert de rijen in de hele resultatenset. U ziet dat omdat er geen vertragingswaarde beschikbaar is voor de eerste rij van elke partitie, de standaardwaarde nul (0) wordt geretourneerd.
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;
Dit is de resultatenset.
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. Willekeurige expressies opgeven
In het volgende voorbeeld ziet u hoe u verschillende willekeurige expressies opgeeft en NULL-waarden in de syntaxis van de LAG-functie negeert.
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;
Dit is de resultatenset.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. IGNORE NULLS gebruiken om niet-NULL-waarden te zoeken
De volgende voorbeeldquery laat zien hoe u het argument IGNORE NULLS gebruikt.
Het argument IGNORE NULLS wordt gebruikt met zowel LAG als LEAD- om vervanging van NULL-waarden voor voorgaande of volgende niet-NULL-waarden te demonstreren.
- Als de voorgaande rij NULL bevat met
LAG, gebruikt de huidige rij de meest recente niet-NULL-waarde. - Als de volgende rij een NULL met
LEADbevat, gebruikt de huidige rij de volgende beschikbare niet-NULL-waarde.
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. RESPECT NULLS gebruiken om NULL-waarden te behouden
In de volgende voorbeeldquery ziet u hoe u het argument RESPECT NULLS gebruikt. Dit is het standaardgedrag als dit niet is opgegeven, in tegenstelling tot het argument IGNORE NULLS in het vorige voorbeeld.
- Als de voorgaande rij NULL bevat met
LAG, gebruikt de huidige rij de meest recente waarde. - Als de volgende rij een NULL met
LEADbevat, gebruikt de huidige rij de volgende waarde.
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
Voorbeelden: Azure Synapse Analytics and Analytics Platform System (PDW)
Een. Waarden tussen kwartalen vergelijken
In het volgende voorbeeld ziet u de LAG-functie. De query gebruikt de LAG-functie om het verschil in verkoopquota voor een specifieke werknemer in eerdere kalenderkwartalen te retourneren. U ziet dat omdat er geen vertragingswaarde beschikbaar is voor de eerste rij, de standaardwaarde nul (0) wordt geretourneerd.
-- 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;
Dit is de resultatenset.
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