Condividi tramite


LEAD (Transact-SQL)

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW) Endpoint di analisi SQL in Microsoft Fabric Warehouse 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 )

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 FROM clausola 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 SELECT - 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 o AdventureWorksDW2022, 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');

Il set di risultati è il seguente.

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;

Il set di risultati è il seguente.

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;

Il set di risultati è il seguente.

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 NULL con LAG, la riga corrente usa il valore nonNULL - più recente.
  • Se la riga successiva contiene un NULL oggetto con LEAD, la riga corrente usa il successivo valore nonNULL 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 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 NULL con LAG, la riga corrente usa il valore più recente.
  • Se la riga successiva contiene un NULL oggetto 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 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;

Il set di risultati è il seguente.

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