LAG (Transact-SQL)
Si applica a:
SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)Endpoint di analisi SQL in Microsoft FabricWarehouse in Microsoft Fabric
Accede ai dati da una riga precedente nello stesso set di risultati senza l'utilizzo di un self-join che inizia con SQL Server 2012 (11.x). LAG fornisce l'accesso a una riga situata a una distanza fisica specificata e precedente rispetto alla riga corrente. Utilizzare questa funzione analitica in un'istruzione SELECT per confrontare valori nella riga corrente con i valori in una riga precedente.
Convenzioni relative alla sintassi Transact-SQL
Sintassi
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Argomenti
scalar_expression
Valore da restituire basato sull'offset specificato. Tale valore può essere un'espressione di ogni tipo che restituisce un singolo valore scalare. scalar_expression non può essere una funzione analitica.
offset
Numero di righe in posizioni precedenti rispetto alla riga corrente dalla quale ottenere un valore. Se non specificato, il valore predefinito è 1.
offset può essere una colonna, una sottoquery o un'altra espressione che restituisce un valore intero positivo o che può essere convertita in modo implicito in un tipo di dati bigint.
offset non può essere un valore negativo o una funzione analitica.
default
Il valore da restituire quando offset non rientra nell'ambito della partizione. Se non viene specificato un valore predefinito, viene restituito NULL.
default può essere una colonna, una sottoquery o un'altra espressione, ma non può essere una funzione analitica.
default deve essere compatibile a livello di tipo con scalar_expression.
[ IGNORE NULLS | RESPECT NULLS ]
si applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure, Istanza gestita di SQL di Azure e SQL Edge di Azure
IGNORE NULLS - Ignora i valori Null nel set di dati quando si calcola il primo valore in una partizione.
RESPECT NULLS - Rispetta i valori Null nel set di dati quando si calcola il primo valore in una partizione.
RESPECT NULLS è il comportamento predefinito se non è specificata un'opzione NULLS.
È stata apportata una correzione di bug in SQL Server 2022 CU4 correlata a IGNORE NULLS in LAG e LEAD.
Per altre informazioni su questo argomento in SQL Edge di Azure, vedere Imputazione di valori mancanti.
OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause suddivide il set di risultati generato dalla clausola FROM in partizioni alle quali viene applicata la funzione. Se non specificato, la funzione tratta tutte le righe del set di risultati della query come un unico gruppo. order_by_clause determina l'ordine dei dati prima che venga applicata la funzione. Se specificato, partition_by_clause determina l'ordine dei dati nella partizione. order_by_clause è obbligatorio. Per altre informazioni, vedere Clausola OVER (Transact-SQL).
Tipi restituiti
Tipo dei dati dell'elemento scalar_expression specificato. Se scalar_expression ammette valori Null o se default è impostato su NULL, viene restituito NULL.
Osservazioni generali
LAG è non deterministico. Per altre informazioni, vedere Funzioni deterministiche e non deterministiche.
Esempi
R. Confronto di valori tra anni
Nell'esempio seguente viene utilizzata la funzione LAG per restituire la differenza nelle quote vendite per un dipendente specifico negli anni precedenti. Si noti che poiché non è presente alcun valore di ritardo disponibile per la prima riga, viene restituita l'impostazione predefinita zero (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');
Il set di risultati è il seguente.
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. Confronto di valori all'interno di partizioni
Nell'esempio seguente viene utilizzata la funzione LAG per confrontare le vendite dei dipendenti a partire dall'inizio dell'anno. La clausola PARTITION BY è specificata per dividere le righe nel set di risultati in base al territorio di vendita. La funzione LAG viene applicata a ogni singola partizione e il calcolo viene riavviato per ogni partizione. La clausola ORDER BY specificata nella clausola OVER ordina le righe in ogni partizione. La clausola ORDER BY nell'istruzione SELECT ordina le righe nell'intero set di risultati. Si noti che poiché non è presente alcun valore di ritardo per la prima riga di ogni partizione, viene restituita l'impostazione predefinita zero (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;
Il set di risultati è il seguente.
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. Specifica di espressioni arbitrarie
Nell'esempio seguente viene illustrato come specificare varie espressioni arbitrarie e ignorare i valori NULL nella sintassi della funzione 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;
Il set di risultati è il seguente.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. Usare IGNORE NULLS per trovare valori non NULL
La query di esempio seguente illustra l'utilizzo dell'argomento IGNORE NULLS.
L'argomento IGNORE NULLS viene usato sia con LAG che con LEAD per dimostrare la sostituzione dei valori NULL per i valori precedenti o successivi non NULL.
- Se la riga precedente contiene NULL con
LAG, la riga corrente usa il valore non NULL più recente. - Se la riga successiva contiene un valore NULL con
LEAD, la riga corrente usa il valore successivo non NULL disponibile.
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. Usare RESPECT NULLS per mantenere i valori NULL
La query di esempio seguente illustra l'utilizzo dell'argomento RESPECT NULLS, ovvero il comportamento predefinito se non specificato, anziché l'argomento IGNORE NULLS nell'esempio precedente.
- Se la riga precedente contiene NULL con
LAG, la riga corrente usa il valore più recente. - Se la riga successiva contiene un valore NULL con
LEAD, la riga corrente usa il valore successivo.
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
Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)
R. Confronto di valori fra trimestri
L'esempio seguente illustra l'utilizzo della funzione LAG. La query usa la funzione LAG per restituire la differenza nelle quote vendite per un dipendente specifico nei trimestri del calendario precedenti. Si noti che poiché non è presente alcun valore di ritardo disponibile per la prima riga, viene restituita l'impostazione predefinita zero (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;
Il set di risultati è il seguente.
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