LAG ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать Oracle/PLSQL функцию LAG с синтаксисом и примерами.

Описание

Oracle/PLSQL функция LAG аналитическая функция, которая позволяет запрашивать более одной строки в таблице, в то время, не имея присоединенной к себе таблицы. Это возвращает значения из предыдущей строки в таблице. Для возврата значения из следующего ряда, попробуйте использовать функцию LEAD.

Синтаксис

Синтаксис Oracle/PLSQL функции LAG:

LAG ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )

Параметры или аргументы

expression - выражение, которое может содержать другие встроенные функции, но не может содержать аналитические функции.

offset - необязательный. Это физическое смещение от текущей строки в таблице. Если этот параметр не указан, то по умолчанию 1.

default - необязательный. Это значение, которое возвращается, если offset выходит за границы таблицы. Если этот параметр не указан, то по умолчанию Null.

query_partition_clause - необязательный. Он используется для разделения результатов на группы на основе одного или нескольких выражений.

order_by_clause - необязательный. Он используется для упорядочения данных в каждом разделе.

Функция LAG возвращает значения из предыдущей строки в таблице.

Применение

Функцию LAG можно использовать в следующих версиях Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Пример

Функция LAG может быть использована в Oracle/PLSQL.

Давайте посмотрим на пример. Если у нас есть таблица orders, которая содержит следующие данные:

ORDER_DATE	PRODUCT_ID	QTY
25/09/2007	1000	20
26/09/2007	2000	15
27/09/2007	1000	8
28/09/2007	2000	12
29/09/2007	2000	2
30/09/2007	1000	4

И мы выполним следующий запрос:

select product_id, 
       order_date,
	   LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date
  from orders;

select product_id,

order_date,

LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date

from orders;

То получим следующий результат:

ORDER_DATE	PRODUCT_ID	QTY
1000	25/09/2007
2000	26/09/2007	25/09/2007
1000	27/09/2007	26/09/2007
2000	28/09/2007	27/09/2007
2000	29/09/2007	28/09/2007
1000	30/09/2007	29/09/2007

Так как мы использовали offset = 1, запрос возвращает предыдущий ORDER_DATE.

Если бы мы использовали offset = 2 вместо 1, то запрос вернул бы ORDER_DATE на 2 позиции ранее. Если бы мы использовали offset = 3, то запрос вернул бы ORDER_DATE на 3 позиции ранне .... и так далее.

Если мы хотим получить только заказы для данного product_id, то мы выполним следующий SQL запрос:

SELECT product_id, 
       order_date,
       LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date
  FROM orders
 WHERE product_id = 2000;

SELECT product_id,

order_date,

LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date

FROM orders

WHERE product_id = 2000;

Получим результат:

ORDER_DATE	PRODUCT_ID	QTY
2000	26/09/2007
2000	28/09/2007	26/09/2007
2000	29/09/2007	28/09/2007

В этом примере, запрос вернул ORDER_DATE для product_id = 2000 и игнорировал все другие записи.

Использование partition

Теперь давайте рассмотрим более сложный пример, в котором мы используем параметр partition для возврата предыдущей order_date для каждого product_id.

Введите следующий оператор SQL:

SELECT product_id, 
       order_date,
	   LAG (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date
  FROM orders;

SELECT product_id,

order_date,

LAG (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date

FROM orders;

Это вернет следующий результат:

PRODUCT_ID	ORDER_DATE	PREV_ORDER_DATE
1000	2007/09/25	NULL
1000	2007/09/27	2007/09/25
1000	2007/09/30	2007/09/27
2000	2007/09/26	NULL
2000	2007/09/28	2007/09/26
2000	2007/09/29	2007/09/28

В этом примере функция LAG разделит результаты по product_id, а затем отсортирует по order_date, как указано в PARTITION BY product_id ORDER BY order_date. Это означает, что функция LAG будет оценивать значение order_date, только если product_id совпадает с product_id текущей записи. Когда встречается новый product_id, функция LAG перезапускает свои вычисления и использует соответствующий раздел product_id.

Как вы можете видеть, первая запись в наборе результатов имеет значение NULL для prev_order_date, потому что это первая запись для раздела, где product_id равен 1000 (отсортировано по order_date), поэтому нет более низкого значения order_date. Это также верно для 4-й записи, где product_id равен 2000.