loading..
Русский    English
16:55
листать

Функции LAG и LEAD

Синтаксис:

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

Оконные функции LAG и LEAD появились в  Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server, начиная с версии 2012.

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

Консоль
Выполнить
  1. SELECT  code,
  2. LAG(code) OVER(ORDER BY code) prev_code,
  3. LEAD(code) OVER(ORDER BY code) next_code
  4. 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, если не существует следующей строки.

Консоль
Выполнить
  1. SELECT  code,
  2. LAG(code,1,-999) OVER(ORDER BY code) prev_code,
  3. LEAD(code,1,999) OVER(ORDER BY code)  next_code
  4. 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, которое принимается по умолчанию. Этот параметр определяет, какую из предыдущих (последующих) строк следует использовать, т.е. на сколько данная строка отстоит от текущей. В следующем примере берется строка, идущая через одну от текущей.

Консоль
Выполнить
  1. SELECT  code,
  2. LAG(code,2,-999) OVER(ORDER BY code) prev_code,
  3. LEAD(code,2,999) OVER(ORDER BY code) next_code
  4. 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, а не сортировкой, используемой в запросе. Вот пример, который иллюстрирует сказанное.

Консоль
Выполнить
  1. SELECT  code,
  2. LAG(code) OVER(ORDER BY code) prev_code,
  3. LEAD(code) OVER(ORDER BY code) next_code
  4. FROM printer
  5. 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 данных функций, рассмотрим "классические" решения данной задачи.

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

Консоль
Выполнить
  1. SELECT p1.code,p3.code,p2.code
  2. FROM printer p1 LEFT JOIN Printer p2 ON p1.code=p2.code-1
  3. LEFT JOIN Printer p3 ON p1.code=p3.code+1;

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

Консоль
Выполнить
  1. SELECT p1.code,
  2. (SELECT MAX(p3.code) FROM Printer p3 WHERE p3.code < p1.code) prev_code,
  3. (SELECT MIN(p2.code) FROM Printer p2 WHERE p2.code > p1.code) next_code
  4. FROM printer p1;

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

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX Больше тэгов
Учебник обновлялся
несколько дней назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100