Функции 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;

🚫

[[ error ]]

[[ column ]]
NULL [[ value ]]

code	prev_code	next_code
1	NULL	2
2	1	3
3	2	4
4	3	5
5	4	6
6	5	NULL

Обратите внимание, что если следующей или предыдущей строки (в порядке возрастания значения 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;

🚫

[[ error ]]

[[ column ]]
NULL [[ value ]]

code	prev_code	next_code
1	-999	2
2	1	3
3	2	4
4	3	5
5	4	6
6	5	999

Чтобы указать третий параметр, нам пришлось использовать и второй необязательный параметр с значением 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;

🚫

[[ error ]]

[[ column ]]
NULL [[ value ]]

code	prev_code	next_code
1	-999	3
2	-999	4
3	1	5
4	2	6
5	3	999
6	4	999

В заключение отметим, что порядок, в котором выбираются следующие и предыдущие строки задаётся предложением 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;

🚫

[[ error ]]

[[ column ]]
NULL [[ value ]]

code	prev_code	next_code
6	5	NULL
5	4	6
4	3	5
3	2	4
2	1	3
1	NULL	2

Чтобы оценить преимущество, которое предоставляет появление в языке 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;

🚫

[[ error ]]

[[ column ]]
NULL [[ 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;

🚫

[[ error ]]

[[ column ]]
NULL [[ value ]]

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

#ORDER BY #OVER #PARTITION BY #Коррелирующие Подзапросы #Оконные Функции #LAG #LEAD

Другие примеры использования оконных функций Функции FIRST_VALUE и LAST_VALUE