Предложение LEAD (Transact-SQL)

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Управляемый экземпляр SQL Azure Конечная точка аналитики аналитики Synapse Analytics Analytics (PDW)SQL Analyticsв Microsoft FabricХранилище в Microsoft Fabric

Получает доступ к данным из последующей строки в том же результирующем наборе без использования самостоятельного соединения, начиная с SQL Server 2012 (11.x). Функция LEAD обеспечивает доступ к строке на заданном физическом смещении после текущей строки. Используйте данную аналитическую функцию в инструкции SELECT для сравнения значений текущей строки со значениями из последующей.

Соглашения о синтаксисе Transact-SQL

Синтаксис

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

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

scalar_expression

Возвращаемое значение основано на указанном смещении. Это выражение любого типа, возвращающее единичное (скалярное) значение. scalar_expression не может быть аналитической функцией.

offset
Количество строк перед текущей строкой, из которых необходимо получить значение. Если значение аргумента не указано, то по умолчанию принимается 1. offset может быть столбцом, вложенным запросом или другим выражением, с помощью которого вычисляется целая положительная величина, или другим типом, который может быть неявно преобразован в bigint. offset не может быть отрицательным значением или аналитической функцией.

default
Возвращаемое значение, когда offset находится за пределами секции. Если значение по умолчанию не задано, то возвращается NULL. default может быть столбцом, вложенным запросом или другим выражением, но не может быть аналитической функцией. Аргумент default должен быть совместим по типу с аргументом scalar_expression.

[ IGNORE NULLS | RESPECT NULLS ]

Область применения: SQL Server (начиная с SQL Server 2022 (16.x)), База данных SQL Azure, Управляемый экземпляр SQL Azure, Azure SQL Edge

IGNORE NULLS. Игнорировать значения NULL в наборе данных при вычислении первого значения по секции.

RESPECT NULLS — уважение значений NULL в наборе данных при вычислении первого значения по секции. RESPECT NULLS — это поведение по умолчанию, если параметр NULLS не указан.

Исправлена ошибка в SQL Server 2022 CU4, связанная с пропуском значений NULLS и LAGLEAD.

Дополнительные сведения об этом аргументе в Azure SQL Edge см. в разделе "Очиска отсутствующих значений".

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется функция. Если этот параметр не указан, функция обрабатывает все строки результирующего набора запроса как отдельные группы. order_by_clause определяет порядок данных перед применением функции. Если аргумент partition_by_clause задан, он определяет порядок данных в каждой секции. Аргумент order_by_clause является обязательным. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).

Типы возвращаемых данных

Тип данных указанного выражения scalar_expression. Значение NULL возвращается в случае, если аргумент scalar_expression может принимать значение NULL или аргумент default имеет значение NULL.

Функция LEAD не детерминирована. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.

Примеры

А. Сравнение значений по годам

В запросе используется функция LEAD для возврата разности в квотах продаж для указанного работника за последующие годы. Обратите внимание, что для последней строки возвращается значение по умолчанию, т. е. нуль (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');  

Результирующий набор:

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. Сравнение значений внутри секций

В следующем примере функция LEAD используется для сравнения объемов продаж за текущий год между сотрудниками. Предложение PARTITION BY указывается для секционирования строк результирующего набора по территориям продаж. Функция LEAD применяется к каждой секции отдельно, и вычисление начинается заново для каждой секции. Предложение ORDER BY, указанное в предложении OVER, сортирует строки в каждой из секций перед применением функции. Предложение ORDER BY в инструкции SELECT упорядочивает строки во всем результирующем наборе. Обратите внимание, что для последней строки каждой секции возвращается значение по умолчанию, т. е. нуль (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;  

Результирующий набор:

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. Указание произвольных выражений

В следующем примере показано указание различных произвольных выражений и пропуска значений NULL в синтаксисе функции 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;  

Результирующий набор:

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

D. Использование IGNORE NULLS для поиска значений, отличных от NULL

В следующем примере запроса показано использование аргумента IGNORE NULLS.

Аргумент IGNORE NULLS используется вместе с LAG и LEAD для демонстрации подстановки значений NULL для предыдущих или следующих значений, отличных от NULL.

  • Если предыдущая строка содержит значение NULL, текущая строка использует последнее значение, отличное от NULL LAG.
  • Если следующая строка содержит значение NULL, текущая строка использует следующее доступное значение, отличное от NULL LEAD.
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

Д. Использование RESPECT NULLS для хранения значений NULL

Следующий пример запроса демонстрирует использование аргумента RESPECT NULLS, который является поведением по умолчанию, если оно не указано, в отличие от аргумента IGNORE NULLS в предыдущем примере.

  • Если предыдущая строка содержит значение NULL LAG, текущая строка использует последнее значение.
  • Если следующая строка содержит значение NULL, LEADтекущая строка использует следующее значение.
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

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

А. Сравнение значений по кварталам

В приведенном ниже примере показано использование функции LEAD. Запрос возвращает разницу между значениями квот на продажу для указанного сотрудника в следующие друг за другом кварталы. Обратите внимание, что для последней строки используется значение по умолчанию, то есть нуль (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;  

Результирующий набор:

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

Следующие шаги