Функции LAG и LEAD

Синтаксис:

LAG | LEAD (< скалярное выражение > [,< сдвиг >] [, < значение по умолчанию >])
    OVER ( [ < предложение partition by >] < предложение order by > )

Оконные функции LAG и LEAD появились в SQL Server, начиная с версии 2012.

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

select  code,
LAG(code) OVER(ORDER BY code) prev_code,
LEAD(code) OVER(ORDER BY code) next_code
from printer;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
codeprev_codenext_code
1NULL2
213
324
435
546
65NULL

Обратите внимание, что если следующей или предыдущей строки (в порядке возрастания значения code) не существует, то используется NULL-значение. Однако такое поведение можно поменять с помощью необязательного (третьего) параметра каждой функции. Значение этого параметра будет использоваться в том случае, если соответствующей строки не существует. В нижеследующем примере используется значение -999, если предыдущей строки не существует, и 999, если не существует следующей строки.

select  code,
LAG(code,1,-999) OVER(ORDER BY code) prev_code,
LEAD(code,1,999) OVER(ORDER BY code)  next_code
from printer;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
codeprev_codenext_code
1-9992
213
324
435
546
65999

Чтобы указать третий параметр, нам пришлось использовать и второй необязательный параметр с значением 1, которое принимается по умолчанию. Этот параметр определяет, какую из предыдущих (последующих) строк следует использовать, т.е. на сколько данная строка отстоит от текущей. В следующем примере берется строка, идущая через одну от текущей.

select  code,
LAG(code,2,-999) OVER(ORDER BY code) prev_code,
LEAD(code,2,999) OVER(ORDER BY code) next_code
from printer;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
codeprev_codenext_code
1-9993
2-9994
315
426
53999
64999

В заключение отметим, что порядок, в котором выбираются следующие и предыдущие строки задаётся  предложением ORDER BY в предложении OVER, а не сортировкой, используемой в запросе. Вот пример, который иллюстрирует сказанное.

select  code,
LAG(code) OVER(ORDER BY code) prev_code,
LEAD(code) OVER(ORDER BY code) next_code
from printer
order by code desc;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
codeprev_codenext_code
65NULL
546
435
324
213
1NULL2

Чтобы оценить преимущество, которое предоставляет появление в языке SQL данных функций, рассмотрим “классические” решения данной задачи.

Самосоединение

select p1.code,p3.code,p2.code
from printer p1 left join Printer p2 on p1.code=p2.code-1
left join Printer p3 on p1.code=p3.code+1;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

Коррелирующий подзапрос

select p1.code,
(select max(p3.code) from Printer p3 where p3.code < p1.code) prev_code,
(select min(p2.code) from Printer p2 where p2.code > p1.code) next_code
from printer p1;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

Рекомендуемые упражнения: 126, 130145