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_DATEPRODUCT_IDQTY
25/09/2007100020
26/09/2007200015
27/09/200710008
28/09/2007200012
29/09/200720002
30/09/200710004

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

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

ORDER_DATEPRODUCT_IDQTY
100025/09/2007
200026/09/200725/09/2007
100027/09/200726/09/2007
200028/09/200727/09/2007
200029/09/200728/09/2007
100030/09/200729/09/2007

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

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

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

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

ORDER_DATEPRODUCT_IDQTY
200026/09/2007
200028/09/200726/09/2007
200029/09/200728/09/2007

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

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

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

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

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

PRODUCT_IDORDER_DATEPREV_ORDER_DATE
10002007/09/25NULL
10002007/09/272007/09/25
10002007/09/302007/09/27
20002007/09/26NULL
20002007/09/282007/09/26
20002007/09/292007/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.