LAG ФУНКЦИЯ

В этом учебном пособии вы узнаете, как использовать 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

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

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

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 запрос:

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

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:

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

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.