LEAD (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 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 in corrispondenza di un determinato offset fisico che segue la riga corrente. Utilizzare questa funzione analitica in un'istruzione SELECT per confrontare i valori nella riga corrente con i valori in una riga seguente.
Convenzioni relative alla 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. Si tratta di un'espressione di qualsiasi 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, NULL viene restituito . 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, SQL Edge di Azure
IGNORE NULLS - Ignorare NULL i valori nel set di dati durante il calcolo del primo valore su una partizione.
RESPECT NULLS - Rispettare NULL i valori 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 divide il set di risultati generato dalla
FROMclausola in partizioni a cui 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 edizione Standard LECT - OVER.
Tipi restituiti
Tipo dei dati dell'elemento scalar_expression specificato. NULL viene restituito se scalar_expression è nullable o il valore predefinito è impostato su NULL.
LEAD è non deterministico. Per altre informazioni, vedere Funzioni deterministiche e non deterministiche.
Esempi
Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.
R. Confronto di valori tra anni
La query usa la LEAD funzione per restituire la differenza nelle quote di vendita per un dipendente specifico negli anni successivi. Poiché non è disponibile alcun valore lead per l'ultima riga, viene restituito il valore predefinito 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 usata la LEAD funzione per confrontare le vendite da anno a data tra i dipendenti. La PARTITION BY clausola viene specificata per partizionare le righe nel set di risultati in base al territorio di vendita. La LEAD funzione viene applicata a ogni partizione separatamente e il calcolo viene riavviato per ogni partizione. La ORDER BY clausola specificata nella OVER clausola ordina le righe in ogni partizione prima dell'applicazione della funzione. La ORDER BY clausola nell'istruzione SELECT ordina le righe nell'intero set di risultati. Poiché non è disponibile alcun valore lead per l'ultima riga di ogni partizione, viene restituito il valore predefinito 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. Specificare espressioni arbitrarie
Nell'esempio seguente viene illustrato come specificare varie espressioni arbitrarie e ignorare NULL i valori nella sintassi della LEAD funzione.
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 LEAD per dimostrare la sostituzione dei NULL valori per i valori precedenti o successivi non NULL.
- Se la riga precedente contenuta
NULLconLAG, la riga corrente usa il valore nonNULL- più recente. - Se la riga successiva contiene un
NULLoggetto conLEAD, la riga corrente usa il successivo valore nonNULLdisponibile.
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 NULL i valori
La query di esempio seguente illustra l'utilizzo dell'argomento RESPECT NULLS , ovvero il comportamento predefinito se non specificato, anziché l'argomento nell'esempio IGNORE NULLS precedente.
- Se la riga precedente contenuta
NULLconLAG, la riga corrente usa il valore più recente. - Se la riga successiva contiene un
NULLoggetto conLEAD, 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 illustrata la LEAD funzione . La query ottiene la differenza nei valori di quota di vendite per un dipendente specifico in trimestri di calendario successivi. Poiché non è disponibile alcun valore lead dopo l'ultima riga, viene usato il valore predefinito 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
Contenuto correlato
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