Функции LAG и LEAD
Синтаксис:
Оконные функции LAG и LEAD появились в SQL Server, начиная с версии 2012.
Эти функции возвращают значение выражения, вычисленного для предыдущей строки (LAG) или следующей строки (LEAD) результирующего набора соответственно. Рассмотрим простой пример запроса, выводящего коды (code) принтеров вместе с кодами из предыдущей и следующей строк:
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, если не существует следующей строки.
code | prev_code | next_code |
---|
1 | -999 | 2 |
2 | 1 | 3 |
3 | 2 | 4 |
4 | 3 | 5 |
5 | 4 | 6 |
6 | 5 | 999 |
Чтобы указать третий параметр, нам пришлось использовать и второй необязательный параметр с значением 1, которое принимается по умолчанию. Этот параметр определяет, какую из предыдущих (последующих) строк следует использовать, т.е. на сколько данная строка отстоит от текущей. В следующем примере берется строка, идущая через одну от текущей.
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, а не сортировкой, используемой в запросе. Вот пример, который иллюстрирует сказанное.
code | prev_code | next_code |
---|
6 | 5 | NULL |
5 | 4 | 6 |
4 | 3 | 5 |
3 | 2 | 4 |
2 | 1 | 3 |
1 | NULL | 2 |
Чтобы оценить преимущество, которое предоставляет появление в языке SQL данных функций, рассмотрим “классические” решения данной задачи.
Самосоединение
Коррелирующий подзапрос
Рекомендуемые упражнения: 126, 130, 145