LEAD (Transact-SQL)
Si applica a:
SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics AnalyticsPlatform System (PDW)SQL analytics endpoint in Microsoft FabricWarehouse in Microsoft Fabric
Accede ai dati da una riga successiva nello stesso set di risultati senza l'uso di un self-join che inizia con SQL Server 2012 (11.x). LEAD fornisce l'accesso a una riga situata a una distanza fisica specificata e successiva alla riga corrente. Utilizzare questa funzione analitica in un'istruzione SELECT per confrontare valori nella riga corrente con i valori in una riga successiva.
Convenzioni di sintassi Transact-SQL
Sintassi
LEAD ( scalar_expression [ , offset ] , [ default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Nota
Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.
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 avanti 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 (a partire da SQL Server 2022 (16.x)), database SQL di Azure, Istanza gestita di SQL di Azure, SQL Edge di Azure
IGNORE NULLS: ignorare i valori NULL nel set di dati durante il calcolo del primo valore su una partizione.
RESPECT NULLS: rispettare i valori NULL nel set di dati durante il calcolo del primo valore su 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. Quando viene specificato, partition_by_clause determina l'ordine dei dati in ogni 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.
LEAD è non deterministico. Per altre informazioni, vedere Funzioni deterministiche e non deterministiche.
Esempi
R. Confronto di valori tra anni
La query utilizza la funzione LEAD per restituire la differenza nelle quote vendite per un dipendente specifico negli anni successivi. Si noti che poiché non è presente alcun valore principale disponibile per l'ultima riga, viene restituita l'impostazione predefinita zero (0).
USE AdventureWorks2022;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');
Questo è il set di risultati.
BusinessEntityID SalesYear CurrentQuota NextQuota
---------------- ----------- --------------------- ---------------------
275 2005 367000.00 556000.00
275 2005 556000.00 502000.00
275 2006 502000.00 550000.00
275 2006 550000.00 1429000.00
275 2006 1429000.00 1324000.00
275 2006 1324000.00 0.00
B. Confronto di valori all'interno di partizioni
Nell'esempio seguente viene utilizzata la funzione LEAD per confrontare le vendite dei dipendenti a partire dall'inizio dell'anno. La clausola PARTITION BY è specificata per suddividere le righe nel set di risultati in base al territorio di vendita. La funzione LEAD 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 prima dell'applicazione della funzione. La clausola ORDER BY nell'istruzione SELECT ordina le righe nell'intero set di risultati. Si noti che poiché non è presente alcun valore principale disponibile per l'ultima riga di ogni partizione, viene restituita l'impostazione predefinita zero (0).
USE AdventureWorks2022;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;
Questo è il set di risultati.
TerritoryName BusinessEntityID SalesYTD NextRepSales
----------------------- ---------------- --------------------- ---------------------
Canada 282 2604540.7172 1453719.4653
Canada 278 1453719.4653 0.00
Northwest 284 1576562.1966 1573012.9383
Northwest 283 1573012.9383 1352577.1325
Northwest 280 1352577.1325 0.00
C. Specifica di espressioni arbitrarie
Nell'esempio seguente viene illustrato come specificare varie espressioni arbitrarie e ignorare i valori NULL nella sintassi della funzione LEAD.
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,
LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) IGNORE NULLS OVER (ORDER BY a) AS i
FROM T;
Questo è il set di risultati.
b c i
----------- ----------- -----------
1 5 -2
2 NULL NULL
3 1 0
1 NULL 2
2 4 2
1 -3 8
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
Nell'esempio seguente viene illustrato l'uso della funzione LEAD. La query ottiene la differenza nei valori di quota di vendite per un dipendente specifico in trimestri di calendario successivi. Si noti che poiché non è presente alcun valore principale disponibile dopo l'ultima riga, viene usata l'impostazione predefinita zero (0).
-- Uses AdventureWorks
SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota,
LEAD(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS NextQuota,
SalesAmountQuota - LEAD(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;
Questo è il set di risultati.
Year Quarter SalesQuota NextQuota Diff
---- ------- ---------- --------- -------------
2001 3 28000.0000 7000.0000 21000.0000
2001 4 7000.0000 91000.0000 -84000.0000
2001 1 91000.0000 140000.0000 -49000.0000
2002 2 140000.0000 7000.0000 7000.0000
2002 3 7000.0000 154000.0000 84000.0000
2002 4 154000.0000 0.0000 154000.0000
Passaggi successivi
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per