SQL Server функция LEAD — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать функцию 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:

SELECT dept_id, last_name, salary,
LEAD (salary,1) OVER (ORDER BY salary) AS next_highest_salary
FROM employees;

SELECT dept_id, last_name, salary,

LEAD (salary,1) OVER (ORDER BY salary) AS next_highest_salary

FROM employees;

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

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:

SELECT dept_id, last_name, salary,
LEAD (salary,1) OVER (PARTITION BY dept_id ORDER BY salary) AS next_highest_salary
FROM employees;

SELECT dept_id, last_name, salary,

LEAD (salary,1) OVER (PARTITION BY dept_id ORDER BY salary) AS next_highest_salary

FROM employees;

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

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.