Oracle lag функция описание. Функции LAG и LEAD. Параметры или аргументы
Оконные функции смещения делятся на две категории. Первая категория - функции, смещение которых указывается по отношению к текущей строке. Это LAG и LEAD. В функциях второй категории смещение указывается по отношению к началу или концу оконного кадра. Сюда относятся функции FIRST_VALUE, LAST_VALUE и NTH_VALUE. SQL Server 2012 поддерживает LAG, LEAD, FIRST_VALUE и LAST_VALUE и не поддерживает NTH_VALUE.
Функции первой категории (LAG и LEAD) поддерживают предложение секционирования, а также упорядочения окна. Ясно, что вторая часть вносит смысл в смещение. Функции из второй категории (FIRST_VALUE, LAST_VALUE и NTH_VALUE) помимо предложения секционирования и упорядочения окна поддерживают предложение оконного кадра.
Функции LAG и LEAD
Функции LAG и LEAD позволяют возвращать выражение значения из строки в секции окна, которая находится на заданном смещении перед (LAG) или после (LEAD) текущей строки. Смещение по умолчанию - «1», оно применяется, если смещение не указать.Например, следующий запрос возвращает текущую стоимость для каждого клиентского заказа, а также стоимости предыдущего и последующего заказов этого же клиента:
SELECT custid, orderdate, orderid, val, LAG(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS prevval, LEAD(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS nextval FROM Sales.OrderValues;
Так как мы явно не задали смещение, по умолчанию предполагается смещение в единицу. Так как данные в функции секционируются по custid, поиск строк выполняется только в рамках той же секции, содержащей данные одного клиента. Что касается упорядочения окон, то понятия «предыдущий» и «следующий» определяются упорядочением по orderdate и orderid в качестве дополнительного параметра. Заметьте, что в результатах запроса LAG возвращает NULL для первой строки оконной секции, потому что перед первой строкой других строк нет; аналогично LEAD возвращает NULL для последней строки.
Если нужно смещение, отличное от единицы, нужно указать его после входного выражения значения, как в этом запросе:
SELECT custid, orderdate, orderid, LAG(val, 3) OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS prev3val FROM Sales.OrderValues;
Как говорилось, LAG и LEAD по умолчанию возвращают NULL, если по заданному смещению нет строки. Если нужно возвращать другое значение, можно указать его в качестве третьего аргумента функции. Например, LAG(val, 3, 0.00) возвращает «0.00», если по смещению 3 перед текущей строкой строки вообще нет.
Для реализации подобного поведения в LAG и LEAD на версии SQL Server, предшествующей SQL Server 2012, можно применить следующий подход:
Напишите запрос, который возвращает номера строк с требуемыми параметрами секционирования и упорядочения, и создайте на его основе табличное выражение.
Соедините множественные табличные выражения так, чтобы они представляли текущую, предыдущую и следующую строки.
В предикате соединения сопоставьте столбцы секционирования различных экземпляров (текущего с предыдущим или последующим). Также в предикате соединения вычислите разницу между числом строк текущего и предыдущего или следующего экземпляра, а затем отфильтруйте на основе значения смещения, которое требуется в ваших вычислениях.
Вот запрос, реализующий этот подход и возвращающий для каждого заказа значения текущего, предыдущего и следующего заказа клиента:
WITH OrdersRN AS (SELECT custid, orderdate, orderid, val, ROW_NUMBER() OVER(ORDER BY custid, orderdate, orderid) AS rn FROM Sales.OrderValues) SELECT C.custid, C.orderdate, C.orderid, C.val, P.val AS prevval, N.val AS nextval FROM OrdersRN AS C LEFT OUTER JOIN OrdersRN AS P ON C.custid = P.custid AND C.rn = P.rn + 1 LEFT OUTER JOIN OrdersRN AS N ON C.custid = N.custid AND C.rn = N.rn - 1;
Ясно, что решить эту задачу можно также с помощью простых вложенных запросов.
Функции FIRST_VALUE, LAST_VALUE и NTH_VALUE
В предыдущем разделе я рассказал о функциях смещения LAG и LEAD, которые позволяют задавать смещение относительно текущей строки. Этот раздел посвящен функциям, которые позволяют определять смещение относительно начала или конца оконного кадра. Это функции FIRST_VALUE, LAST_VALUE и NTH_VALUE, причем последняя не реализована в SQL Server 2012.
Напомню, что LAG и LEAD поддерживают предложения секционирования и упорядочение, но не поддерживают предложение кадрирования окна. Это разумно, если смещение указывается относительно текущей строки. В функциях, в которых смещение указывается по отношению к началу или концу окна, кадрирование имеет смысл. Функции FIRST_VALUE и LAST_VALUE возвращают запрошенное выражение значения соответственно из первой и последней строки в кадре. Вот запрос, демонстрирующий, как возвращать с каждым заказом клиента текущее значение этого заказа, а также значения первого и последнего заказа:
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS val_firstorder, LAST_VALUE(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS val_lastorder FROM Sales.OrderValues;
С технической точки зрения нам нужны значения из первой и последней строки секции. С FIRST_VALUE просто, потому что можно использовать кадрирование по умолчанию. Как вы помните, если поддерживается кадрирование и не указать предложение кадрирования окна, по умолчанию будет применяться RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Но с LAST_VALUE кадрирование по умолчанию бесполезно, потому что последней является текущая строка. Поэтому в этом примере используется явное определение кадра с UNBOUNDED FOLLOWING в качестве нижней границы кадра.
Обычно не возвращают первое или последнее значение вместе со всеми подробностями строк, как в предыдущем примере - в вычислениях обычно работают с одной цифрой и значением, возвращенным оконной функцией. В следующем примере запрос возвращает, вместе с каждым клиентским заказом, стоимость текущего заказа, а также разницу между ней и стоимостью первого и последнего заказа клиента:
SELECT custid, orderdate, orderid, val, val - FIRST_VALUE(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS difffirst, val - LAST_VALUE(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS difflast FROM Sales.OrderValues;
Как я говорил, стандартная функция NTH_VALUE не реализована в SQL Server 2012. Эта функция позволяет запрашивать выражение значения, которое находится на заданном смещении, выраженном в числе строк, от первой или последней строки в оконном кадре. Смещение задается во втором входном значении после выражения значения и ключевого слова FROM_FIRST или FROM_LAST, которое указывает, от какой строки отсчитывать смещение - от первой или последней. Например, следующее выражение возвращает значение из третьей строки, если считать от самой нижней в секции.
This Oracle tutorial explains how to use the Oracle/PLSQL LAG function with syntax and examples.
Description
The Oracle/PLSQL LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the LEAD function .
Syntax
The syntax for the LAG function in Oracle/PLSQL is:
LAG (expression [, offset [, default] ]) OVER ([ query_partition_clause ] order_by_clause)
Parameters or Arguments
expression An expression that can contain other built-in functions, but can not contain any analytic functions. offset Optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1. default Optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null. query_partition_clause Optional. It is used to partition the results into groups based on one or more expressions. order_by_clause Optional. It is used to order the data within each partition.Returns
The LAG function returns values from a previous row in the table.
Applies To
The LAG function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example
The LAG function can be used in Oracle/PLSQL.
Let"s look at an example. If we had an orders table that contained the following data:
| ORDER_DATE | PRODUCT_ID | QTY |
|---|---|---|
| 2007/09/25 | 1000 | 20 |
| 2007/09/26 | 2000 | 15 |
| 2007/09/27 | 1000 | 8 |
| 2007/09/28 | 2000 | 12 |
| 2007/09/29 | 2000 | 2 |
| 2007/09/30 | 1000 | 4 |
And we ran the following SQL statement:
SELECT product_id, order_date, LAG (order_date,1) OVER (ORDER BY order_date) AS prev_order_date FROM orders;
| PRODUCT_ID | ORDER_DATE | PREV_ORDER_DATE |
|---|---|---|
| 1000 | 2007/09/25 | NULL |
| 2000 | 2007/09/26 | 2007/09/25 |
| 1000 | 2007/09/27 | 2007/09/26 |
| 2000 | 2007/09/28 | 2007/09/27 |
| 2000 | 2007/09/29 | 2007/09/28 |
| 1000 | 2007/09/30 | 2007/09/29 |
In this example, the LAG function will sort in ascending order all of the order_date values in the orders table and then return the previous order_date since we used an offset of 1.
If we had used an offset of 2 instead, it would have returned the order_date from 2 orders earlier. If we had used an offset of 3, it would have returned the order_date from 3 orders earlier....and so on.
Using Partitions
Now let"s look at a more complex example where we use a query partition clause to return the previous order_date for each product_id .
Enter the following SQL statement:
SELECT product_id, order_date, LAG (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date FROM orders;
It would return the following result:
| 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 |
In this example, the LAG function will partition the results by product_id and then sort by order_date as indicated by PARTITION BY product_id ORDER BY order_date . This means that the LAG function will only evaluate an order_date value if the product_id matches the current record"s product_id . When a new product_id is encountered, the LAG function will restart its calculations and use the appropriate product_id partition.
As you can see, the 1st record in the result set has a value of NULL for the prev_order_date because it is the first record for the partition where product_id is 1000 (sorted by order_date) so there is no lower order_date value. This is also true for the 4th record where the product_id is 2000.
В этом учебном пособии вы узнаете, как использовать 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 |
И мы выполним следующий запрос:
Oracle PL/SQL
select product_id, order_date, LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date from orders;
Если бы мы использовали offset = 2 вместо 1, то запрос вернул бы ORDER_DATE на 2 позиции ранее. Если бы мы использовали offset = 3, то запрос вернул бы ORDER_DATE на 3 позиции ранне …. и так далее.
Если мы хотим получить только заказы для данного product_id, то мы выполним следующий SQL запрос:
Oracle PL/SQL
Получим результат:
В этом примере функция 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.