LEAD (Transact-SQL)

Άρθρο
09/03/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

Transact-SQL syntax conventions

Syntax

LEAD ( scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause )

Arguments

scalar_expression

The value to be returned based on the specified offset. It's an expression of any type that returns a single (scalar) value. scalar_expression can't be an analytic function.

offset

The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset can't be a negative value or an analytic function.

default

The value to return when offset is beyond the scope of the partition. If a default value isn't specified, NULL is returned. default can be a column, subquery, or other expression, but it can't be an analytic function. default must be type-compatible with scalar_expression.

[ IGNORE NULLS | RESPECT NULLS ]

Applies to: SQL Server 2022 (16.x) and later versions, Azure SQL Database, Azure SQL Managed Instance, Azure SQL Edge

IGNORE NULLS - Ignore NULL values in the dataset when computing the first value over a partition.

RESPECT NULLS - Respect NULL values in the dataset when computing first value over a partition. RESPECT NULLS is the default behavior if a NULLS option isn't specified.

There was a bug fix in SQL Server 2022 CU4 related to IGNORE NULLS in LAG and LEAD.

For more information on this argument in Azure SQL Edge, see Imputing missing values.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
order_by_clause determines the order of the data before the function is applied.

When partition_by_clause is specified, it determines the order of the data in each partition. The order_by_clause is required. For more information, see SELECT - OVER Clause.

Return types

The data type of the specified scalar_expression. NULL is returned if scalar_expression is nullable or default is set to NULL.

LEAD is nondeterministic. For more information, see Deterministic and Nondeterministic Functions.

Examples

The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A. Compare values between years

The query uses the LEAD function to return the difference in sales quotas for a specific employee over subsequent years. Because there's no lead value available for the last row, the default of zero (0) is returned.

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');

Here's the result set.

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. Compare values within partitions

The following example uses the LEAD function to compare year-to-date sales between employees. The PARTITION BY clause is specified to partition the rows in the result set by sales territory. The LEAD function is applied to each partition separately and computation restarts for each partition. The ORDER BY clause specified in the OVER clause orders the rows in each partition before the function is applied. The ORDER BY clause in the SELECT statement orders the rows in the whole result set. Because there's no lead value available for the last row of each partition, the default of zero (0) is returned.

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;

Here's the result set.

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. Specify arbitrary expressions

The following example demonstrates specifying various arbitrary expressions and ignoring NULL values in the LEAD function syntax.

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;

Here's the result set.

b           c           i
----------- ----------- -----------
1           5           -2
2           NULL        NULL
3           1           0
1           NULL        2
2           4           2
1           -3          8

D. Use IGNORE NULLS to find non-NULL values

The following sample query demonstrates using the IGNORE NULLS argument.

The IGNORE NULLS argument is used with both LAG and LEAD to demonstrate substitution of NULL values for preceding or next non-NULL values.

If the preceding row contained NULL with LAG, then the current row uses the most recent non-NULL value.
If the next row contains a NULL with LEAD, then the current row uses the next available non-NULL value.

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. Use RESPECT NULLS to keep `NULL` values

The following sample query demonstrates using the RESPECT NULLS argument, which is the default behavior if not specified, as opposed to the IGNORE NULLS argument in the previous example.

If the preceding row contained NULL with LAG, then the current row uses the most recent value.
If the next row contains a NULL with LEAD, then the current row uses the next value.

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

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

A. Compare values between quarters

The following example demonstrates the LEAD function. The query obtains the difference in sales quota values for a specified employee over subsequent calendar quarters. Because there's no lead value available after the last row, the default of zero (0) is used.

-- 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;

Here's the result set.

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

Κοινή χρήση μέσω

LEAD (Transact-SQL)

Syntax

Arguments

scalar_expression

offset

default

[ IGNORE NULLS | RESPECT NULLS ]

OVER ( [ partition_by_clause ] order_by_clause )

Return types

Examples

A. Compare values between years

B. Compare values within partitions

C. Specify arbitrary expressions

D. Use IGNORE NULLS to find non-NULL values

E. Use RESPECT NULLS to keep `NULL` values

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

A. Compare values between quarters

Σχόλια

Πρόσθετοι πόροι

Κοινή χρήση μέσω

LEAD (Transact-SQL)

Syntax

Arguments

scalar_expression

offset

default

[ IGNORE NULLS | RESPECT NULLS ]

OVER ( [ partition_by_clause ] order_by_clause )

Return types

Examples

A. Compare values between years

B. Compare values within partitions

C. Specify arbitrary expressions

D. Use IGNORE NULLS to find non-NULL values

E. Use RESPECT NULLS to keep NULL values

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

A. Compare values between quarters

Related content

Σχόλια

Πρόσθετοι πόροι

E. Use RESPECT NULLS to keep `NULL` values