Новое в стандарте и реализациях языка SQL

Оконные функции

Синтаксис:

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;

select  code, LAG(code) OVER(ORDER BY code) prev_code, LEAD(code) OVER(ORDER BY code) next_code from printer;

    Схема

Нет схемы

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

select  code, LAG(code,1,-999) OVER(ORDER BY code) prev_code, LEAD(code,1,999) OVER(ORDER BY code)  next_code from printer;

    Схема

Нет схемы

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

select  code, LAG(code,2,-999) OVER(ORDER BY code) prev_code, LEAD(code,2,999) OVER(ORDER BY code) next_code from printer;

    Схема

Нет схемы

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

select  code, LAG(code) OVER(ORDER BY code) prev_code, LEAD(code) OVER(ORDER BY code) next_code from printer order by code desc;

    Схема

Нет схемы

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

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;

    Схема

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