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

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

Синтаксические обозначения в Transact-SQL (Transact-SQL)

Синтаксис

LEAD ( scalar_expression [ ,offset ] , [ default ] ) 
    OVER ( [ partition_by_clause ] order_by_clause )

Аргументы

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

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

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

• 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. Если параметр scalar_expression допускает значение NULL или параметр default установлен равным NULL, то возвращается NULL.

Примеры

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

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

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

Б.Сравнение значений внутри секций

В следующем примере функция LEAD используется для сравнения объемов продаж за текущий год между сотрудниками. Предложение PARTITION BY указывается для секционирования строк результирующего набора по территориям продаж. Функция LEAD применяется к каждой секции отдельно, и вычисление начинается заново для каждой секции. Предложение ORDER BY, указанное в предложении OVER, сортирует строки в каждой из секций перед применением функции. Предложение ORDER BY в инструкции SELECT упорядочивает строки во всем результирующем наборе. Обратите внимание, что для последней строки возвращается значение по умолчанию, т. е. нуль (0), так как последующее значение для последней строки отсутствует.

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

В.Указание произвольных выражений

В следующем примере демонстрируется указание ряда произвольных выражений в синтаксисе функции 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) OVER (ORDER BY a) AS i
FROM T;

Ниже приводится результирующий набор.

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

См. также

Справочник

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