Функции 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;| 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;| 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;| 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;| 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;Коррелирующий подзапрос
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;