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

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

Описание

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

Синтаксис

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

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

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

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

Применение

Функция LAG может использоваться в следующих версиях 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,
LAG (salary,1) OVER (ORDER BY salary) AS lower_salary
FROM employees;

SELECT dept_id, last_name, salary,

LAG (salary,1) OVER (ORDER BY salary) AS lower_salary

FROM employees;

И получим следующий результат:

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

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

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

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

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

SELECT dept_id, last_name, salary,

LAG (salary,1) OVER (PARTITION BY dept_id ORDER BY salary) AS lower_salary

FROM employees;

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

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

В этом примере функция LAG будет разбивать результаты на dept_id, а затем сортировать по salary, как указано PARTITION BY dept_id ORDER BY. Это означает, что функция LAG будет оценивать только значение зарплаты, если dept_id соответствует dept_id текущей записи. Когда встречается новый dept_id, функция LAG перезапускает свои вычисления и использует соответствующий раздел dept_id.
Как вы можете видеть, первая запись в результирующем наборе имеет значение NULL для lower_salary, потому что это первая запись для раздела, где dept_id - 30 (отсортировано по salary), поэтому нет более низкого значения зарплаты. Это также относится к третьей записи, где dept_id - 45.