SQL Server функция LEAD

В этом учебном пособии вы узнаете, как использовать функцию LEAD в SQL Server (Transact-SQL) с синтаксисом и примерами.

Описание

В SQL Server (Transact-SQL) функция LEAD является аналитической функцией, которая позволяет запрашивать более одной строки в таблице одновременно без необходимости присоединяться к самой таблице. Это возвращает значения из следующей строки в таблице. Чтобы вернуть значение из предыдущей строки, попробуйте использовать функцию LAG.

Синтаксис

Синтаксис функции LEAD в SQL Server (Transact-SQL):

LEAD ( expression [, offset [, default] ] )
OVER ( [ query_partition_clause ] order_by_clause )

Параметры или аргументы

expression - выражение, которое может содержать другие встроенные функции, но не может содержать аналитические функций.
offset - необязательный. Это физическое смещение от текущей строки в таблице. Если этот параметр опущен, значением по умолчанию является 1.
default - необязательный. Это значение возвращается, если смещение выходит за границы таблицы. Если этот параметр опущен, значение по умолчанию равно null.
query_partition_clause - необязательный. Он используется для разделения результатов на группы на основе одного или нескольких выражений.
order_by_clause - необязательный. Он используется для упорядочивания данных в каждом разделе.

Применение

Функция LEAD может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012

Пример

Давайте посмотрим на пример. Если бы у нас была таблица employees, которая содержала следующие данные:

employee_number last_name first_name salary dept_id
12009 Матвеева Галина 54000 45
34974 Шапошников Семён 80000 45
34987 Верещагин Павел 42000 45
45001 Сухов Фёдор 57500 30
75623 Смирнов Андрей 65000 30

И мы выполнили следующую оператор SQL:

Это вернет следующий результат:

dept_id last_name salary next_highest_salary
45 Верещагин 42000 54000
45 Матвеева 54000 57500
30 Сухов 57500 65000
30 Смирнов 65000 80000
45 Шапошников 80000 NULL

В этом примере функция LEAD будет сортировать по возрастанию все значения salary в таблице employees и затем возвращать следующую самую высокую зарплату, поскольку мы использовали offset 1.

Если бы мы использовали offset 2 вместо этого, это вернуло бы зарплату, которая составляет на 2 зарплаты выше. Если бы мы использовали offset 3, это вернуло бы зарплату, которая была бы выше на 3 ... и так далее.

Использование partitions

Теперь давайте рассмотрим более сложный пример, в котором мы в запросе используем условие partitions, чтобы вернуть следующую самую высокую зарплату каждому сотруднику в своем отделе.
Введите следующий оператор SQL:

Это вернет следующий результат:

dept_id last_name salary next_highest_salary
30 Сухов 57500 65000
30 Смирнов 65000 NULL
45 Верещагин 42000 54000
45 Матвеева 54000 80000
45 Шапошников 80000 NULL

В этом примере функция LEAD будет разбивать результаты на dept_id, а затем сортировать по salary, как указано PARTITION BY dept_id ORDER BY. Это означает, что функция LEAD будет оценивать только значение зарплаты, если dept_id соответствует dept_id текущей записи. Когда встречается новый dept_id, функция LEAD перезапускает свои вычисления и использует соответствующий раздел dept_id.

Как вы можете видеть, вторая запись в результирующем наборе имеет значение NULL для next_highest_salary, потому что это последняя запись для раздела, где dept_id - 30 (отсортировано по зарплате). Это также относится к 5-й записи, где dept_id - 45.