Oracle lag функция описание. Oracle. Программирование. Параметры или аргументы
Оконные функции смещения делятся на две категории. Первая категория - функции, смещение которых указывается по отношению к текущей строке. Это 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.
Аналитические функции 43
Сервер Oracle предлагает 26 аналитических функций. Они разбиваются на четыре ос-новн1х класса по возможностям.
Первый класс образуют различные функции ранжирования, позволяющие строить запросы типа первых N . Мы уже использовали одну функцию этого класса, ROW NUMBER, при генерации столбца SEQ в предыдущем примере. Она ранжировала сотрудников в отделах по фамилии (ENAME). Точно так же их можно было бы ранжировать по зарплате (SALARY) или любому другому атрибуту.
Второй класс образуют оконные функции, позволяющие вычислять разнообразные агрегаты. В первом примере этой главы была показана такая функция - мы вычисляли SUM(SAL) по разным группам. Вместо функции SUM можно было использовать и другие функции агрегирования, например COUNT, AVG, MIN, МАХ и т.д.
К третьему классу относятся различные итоговые функции. Они очень похожи на оконные, поэтому имеют те же имена: SUM, MIN, MAX и т.д. Тогда как оконные функции используются для работы с окнами данных, как промежуточная сумма в предыдущем примере, итоговые функции работают со всеми строками фрагмента или группы. Например, если бы в первоначальном запросе использовались обращения:
sum(sal) over () totalsalary,
sum(sal) over (partition by deptno) total salary for department
мы бы получили общие суммы по группам, а не промежуточные. Ключевое отличие итоговой функции от оконной - отсутствие конструкции ORDER BY в операторе OVER. При отсутствии конструкции ORDER BY функция применяется к каждой строке группы. При наличии конструкции ORDER BY функция применяется к окну (подробнее об этом в разделе, описывающем конструкцию окна).
Есть также функции LAG и LEAD, позволяющие получать значения из предыдущих или следующих строк результирующего множества. Это помогает избежать самосоединения данных. Например, если в таблице записаны даты визитов пациентов к врачу и необходимо вычислить время между визитами для каждого их них, очень пригодится функция LAG. Можно просто фрагментировать данные по пациентам и отсортировать их по дате. После этого функция LAG легко сможет вернуть данные предыдущей записи для пациента. Останется вычесть из одной даты другую. До появления аналитических функций для получения этих данных приходилось организовывать сложное соединение таблицы с ней же самой.
Наконец, есть большой класс статистических функций, таких как VAR POP, VAR SAMP, STDEV POP, набор функций линейной регрессии и т.п. Эти функции позволяют вычислять значения статистических показателей для любого неупорядоченного фрагмента.
В конце раздела, посвященного синтаксису, представлена таблица с кратким объяснением назначения всех аналитических функций.
Конструкция фрагментации
Конструкция PARTITION BY логически разбивает результирующее множество на N групп по критериям, задаваемым выражениями фрагментации. Слова фрагмент и груп-
па в этой главе и в документации Oracle используются как синонимы. Аналитические функции применяются к каждой группе независимо, - для каждой новой группы они сбрасываются. Например, ранее при демонстрации функции, вычисляющей промежуточную сумму зарплат, фрагментация выполнялась по столбцу DEPTNO. Когда значение в столбце DEPTNO в результирующем множестве изменялось, происходил сброс промежуточной суммы в ноль, и суммирование начиналось заново.
Если не указать конструкцию фрагментации, все результирующее множество считается одной группой. Во первом примере мы использовали функцию SUM(SAL) без конструкции фрагментации, чтобы получить промежуточные суммы для всего результирующего множества.
Интересно отметить, что каждая аналитическая функция в запросе может иметь уникальную конструкцию фрагментации; фактически уже в простейшем примере в начале главы это и б1ло сделано. Для столбца RUNNING TOTAL конструкция фрагментации не б1ла задана, поэтому целевой группой б1ло все результирующее множество. Для столбца DEPARTMENTAL TOTAL результирующее множество фрагментируется по отделам, что позволило вычислять промежуточные суммы для каждого из них.
Синтаксис конструкции фрагментации прост и очень похож на синтаксис конструкции GROUP BY в обычных SQL-запросах:
PARTITION BY выражение [, выражение] [, выражение]
Конструкция упорядочения
Конструкция ORDER BY задает критерий сортировки данных в каждой группе (в каждом фрагменте). Это, несомненно, влияет на результат выполнения любой аналитической функции. При наличии (или отсутствии) конструкции ORDER BY аналитические функции вычисляются по-другому. В качестве примера рассмотрим, что происходит при использовании функции AVG() с конструкцией ORDER BY и без оной:
scott@TKYTE816> select ename, sal, avg(sal) over () 2 from emp;
SAL AVG(SAL)OVER() |
||
Аналитические функции
scott@TKYTE816> select
3 order by ename
ename, sal, avg(sal) over (ORDER BY ENAME)
SAL AVG(SAL)OVER(ORDERBYENAME)
14 rows selected.
В отсутствие конструкции ORDER BY среднее значение вычисляется по всей группе, и одно и то же значение выдается для каждой строки (функция используется как итоговая). Когда функция AVG() используется с конструкцией ORDER BY, среднее значение в каждой строке является средним по текущей и всем предыдущим строкам (функция используется как оконная). Например, средняя зарплата для пользователя ALLEN в результатах выполнения запроса с конструкцией ORDER BY - 1350 (среднее для значений 1100 и 1600).
Немного забегая вперед, в следующийраздел, посвященный конструкции окна, можно сказать, что наличие конструкции ORDER БУв вызове аналитической функции добавляет стандартную конструкцию окна-RANGE UNBOUNDEDPRECEDING. Это означает, что для вычисления используется набор из всех предыдущих и текущей строки в текущем фрагменте. При отсутствии ORDER БУстандартным окном является весь фрагмент.
...Общий синтаксис для использования аналитических функций следующий
имя_функции(<аргумент>,< аргумент >, . . .
)
over (<конструкция_фрагментации><конструкция_упорядочения><конструкция_окна>)
Рассмотрим основные части данного синтаксиса.
1. <конструкция_фрагментации>
Синтаксис для задания конструкции фрагментации выглядит следующим образом
partition by выражение [, выражение] [, выражение]
Данная конструкция логически разбивает результирующее множество на N групп
по критериям, задаваемым выражениями фрагментации. Аналитические функции применяются к каждой группе независимо, - для каждой новой группы они сбрасываются. Если не указать конструкцию фрагментации, все результирующее множество считается
одной группой.
2. <конструкция_упорядочения>
Конструкция упорядочения имеет следующий синтаксис
order by выражение
Конструкция order by
задает критерий сортировки данных в каждой группе (в каждом фрагменте). Это, несомненно, влияет на результат выполнения любой аналитической функции. При наличии (или отсутствии) конструкции order by
аналитические функции вычисляются по-другому. Например.
- без конструкции order by
select ename, sal, avg(sal) over ()
from emp
С конструкцией order by
select ename, sal, avg(sal) over (order by ename)
from emp
Здесь стоит отметить следующее, на самом деле наличие конструкции order by в вызове аналитической функции добавляет стандартную конструкцию окна — RANGE UNBOUNDED PRECEDING . Это означает, что для вычисления используется набор из всех предыдущих и текущей строки в текущем фрагменте. При отсутствии order by стандартным окном является весь фрагмент. То есть по-сути предыдущий запрос будет выглядеть следующим образом
select ename, sal, avg(sal) over (order by ename RANGE UNBOUNDED PRECEDING)
from emp
3. <конструкция_окна>
Конструкция окна позволяет задать перемещающееся или жестко привязанное окно (набор)
данных в пределах группы, с которым будет работать аналитическая функция.
Например, для создания отчета, показывающего сумму зарплат текущего и двух предыдущих сотрудников отдела, можно выполнить следующий запрос
select deptno, ename, sal,
sum(sal) over (partition by deptno order by ename rows 2 preceding) sliding_total
from emp
order by deptno, ename
Можно создавать окна по двум критериям: по диапазону (RANGE ) значений данных или по
смещению (ROWS ) относительно текущей строки . Использование конструкции range как было сказано ранее в некоторых случаях используется неявно, RANGE UNBOUNDED PRECEDING например. Она требует брать все строки вплоть до текущей, в соответствии с порядком, задаваемым конструкцией order by . Следует помнить, что для использования окон
необходимо задавать конструкцию order by .
Окно определяется диапазоном строк, объединяемых в соответствии с заданным порядком.
Применять конструкцию range
можно либо с числовыми выражениями (NUMBER
), либо с выражениями, значением которого является дата (DATE
). Еще одно ограничение для таких окон состоит в том, что в конструкции order by
может быть только один столбец — диапазоны по природе своей одномерны. Нельзя задать диапазон в N-мерном пространстве. Пример.
Пусть необходимо выбрать зарплату каждого сотрудника и среднюю зарплату всех принятых на работу в течение 100 предыдущих дней, а также среднюю зарплату всех принятых на работу в течение 100 следующих дней. Соответствующий запрос будет выглядеть так:
select ename, hiredate, sal,
avg(sal) over (order by hiredate asc range 100 preceding) avg_sal_100_days_before,
avg(sal) over (order by hiredate desc range 100 preceding) avg_sal_100_days_after
from emp
order by hiredate desc
Помимо определения окна по диапазону (RANGE ), также окна определяются и по количеству строк (ROWS ). Для окон по строкам нет ограничений, присущих окнам по диапазону; данные могут быть любого типа и упорядочивать можно по любому количеству столбцов.
Например, пусть нужно вычислить среднюю зарплату для сотрудника и пяти принятых на работу до него и после него. Запрос можно записать следующим образом
select ename, hiredate, sal,
avg(sal) over (
order by
hiredate asc rows 5
preceding
) avg_5_before,
avg(sal) over (
order by
hiredate desc rows 5 preceding) avg_5_after
from emp
order by hiredate
Зная как определяются окна (по диапазону или по количеству строк), рассмотрим как окончательно задаются окна. В простейшем случае, окно задается с помощью одной из трех следующих взаимоисключающих конструкций.
- UNBOUNDED PRECEDING.
Окно начинается с первой строки текущей группы и заканчивается текущей обрабатываемой строкой.
- CURRENT ROW.
Окно начинается (и заканчивается) текущей строкой.
- Числовое_выражение PRECEDING.
Окно начинается со строки за числовое_выражение
строк до текущей, если оно задается по строкам, или со строки, меньшей по значению столбца, упомянутого в конструкции order by, не более чем на числовое выражение, если оно задается по диапазону.
- Числовое_выражение FOLLOWING.
Окно заканчивается (или начинается) со строки, через числовое_выражение
строк после текущей, если оно задается по строкам, или со строки, большей
по значению столбца, упомянутого в конструкции order by, не более чем на числовое_выражение, если оно задается по диапазону.
Стоит отметить, что окно CURRENT ROW
в простейшем виде, вероятно, никогда не используется, поскольку ограничивает применение аналитической функции одной текущей строкой, а для этого аналитические функции не нужны. В более сложном случае для окна задается также конструкция BETWEEN
. В ней CURRENT ROW
можно указывать в качестве начальной или конечной строки окна. Начальную и конечную строку окна в конструкции BETWEEN
можно задавать с использованием любой из перечисленных выше конструкций. Например, можно задать окно так, что обрабатываемая строка не будет
последней, а окажется где-то в середине окна
select ename, hiredate,
first_value(ename) over (order by hiredate asc range between 100
preceding
and 100 following),
last_value(ename) over (order by hiredate asc range between 100
preceding
and 100 following)
from emp
order by hiredate asc
В данном запросе дополнительно использованы функции first_value() и last_value() , которые возвращают первое значений текущего окна и последнее значение также текущего окна, соответственно, в то время как диапазон окна ограничен слева текущая скользящая дата - 100 дней и справа к текущей скользящей дате + 100 дней, в этом и состоит смысл выражения - between 100 preceding and 100 following .
select
level,
count(*) over (order by level asc rows 2 preceding) asc_count,
count(*) over (order by level desc rows 2 preceding) desc_count
from dual
connect by level <= 10
order by level
Окно rows 2 preceding, как видно из результата запроса, содержит от 1 до 3 строк (это определяется тем, как далеко текущая строка находится от начала группы). Для первой строки группы имеем значение 1 (предыдущих строк нет). Для следующей строки в группе таких строк 2. Наконец, для третьей и далее строк значение count(*) остается постоянным, поскольку мы считаем только текущую строку и две предыдущие.
select
n,
sum(n) over (order by days range 2 preceding) n_sum,
days
from (
select
level n,
(to_date("10.01.2014", "dd.mm.yyyy") + (level - 1)) days
from dual
connect by level <= 10
order by days
В данном случае идет суммирование в пределах окна, диапазон которого составляет скользящий день и 2 предыдущих дня
select
ename,
sal,
rank() over (order by sal) rank,
dense_rank() over (order by sal) dens_rank,
row_number() over (order by sal) row_number
from emp
order by sal
Данный запрос демонстрирует работу ранжирующих функций rank(), dense_rank() и row_number() по окладам работников. Обратите внимание на поведение данных функций в строках с одинаковыми значениями окладов.
select
ename,
deptno,
sal,
rank() over (partition by deptno order by sal) rank,
dense_rank() over (partition by deptno order by sal) dens_rank,
row_number() over (partition by deptno order by sal) row_number
from emp
order by deptno
Можно предыдущий запрос фрагментировать по отделам, то есть разбить на группы по отделам, в пределах которых будут работать аналитические функции, которые при выходе за пределы групп будут сбрасывать результаты. Результат представлен ниже
Рассмотрим пример применения функций lag() и lead(). Для того, чтобы можно было обращаться в текущей строке к предыдущим строкам, необходимо использовать функцию - lag(). Синтаксис ее таков
lag(поле_для_обращения, смещение, значение_для_замещения_null
)
over (partition by выражение
order by выражение
)
Поле_для_обращения - поле, по которому нужно просматривать значения;
смещение - смещенная строка, с которой просматривается поле, по-умолчанию равно 1, если проставить 0, тогда будет просматриваться текущее поле;
значение_для_замещения_null - по-умолчанию равно null, в случае отсутствия значения в просматриваемом поле, возвращает данное значение. Здесь стоит отметить, что подставляемое значение должно быть того же типа, что и просматриваемое поле;
для данной функции обязательно использование order by
with
main as (
from emp
order by sal
),
numerated_main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from main
select
ename,
sal,
lag(sal) over (order by rownum) previous_sal
from numerated_main
Или вот так
with
main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from emp
order by sal
),
numerated_main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from main
select
ename,
sal,
lag(sal, 2, 0) over (order by rownum) previous_sal
from numerated_main
Логика и синтаксис работы функции lead() аналогичен предыдущей функции с одной лишь разницей: просмотр идет не назад, а вперед
With
main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from emp
order by sal
),
numerated_main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from main
select
ename,
sal,
lead(sal, 2, 0) over (order by rownum) previous_sal
from numerated_main
Ниже для ознакомления приведен неполный список аналитических функций. Для более полной информации обращайтесь к документации . Всем успехов.
| Аналитическая функция | Описание |
| AVG( выражение) | Используется для вычисления среднего значения выражения в пределах группы и окна. Для поиска среднего после удаления дублирующихся значений можно указывать ключевое слово DISTINCT |
| CORR(выражение, выражение) | Выдает коэффициент корреляции для пары выражений, возвращающих числовые значения. В статистическом смысле, корреляция — это степень связи между переменными. Связь между переменными означает, что значение одной переменной можно в определенной степени предсказать по значению другой. Коэффициент корреляции представляет степень корреляции в виде числа в диапазоне от -1 (высокая обратная корреляция) до 1 (высокая корреляция). Значение 0 соответствует отсутствию корреляции |
| COUNT([*] [выражение]) | Эта функция считает строки в группах. Если указать * или любую константу, кроме NULL, функция COUNT будет считать все строки. Если указать выражение, функция COUNT будет считать строки, для которых выражение имеет значение не NULL. Можно задавать модификатор DISTINCT, чтобы считать строки в группах после удаления дублирующихся строк |
| COVAR_POP(выражение, выражение) | Возвращает ковариацию генеральной совокупности (population covariance) пары выражений с числовыми значениями |
| COVAR_SAMP(выражение, выражение) | Возвращает выборочную ковариацию (sample covariance) пары выражений с числовыми значениями |
| CUME_DIST | Вычисляет относительную позицию строки в группе. Функция CUME_DIST всегда возвращает число большее 0 и меньше или равное 1. Это число представляет "позицию" строки в группе из N арок. В группе из трех строк, например, возвращаются следующие значения кумулятивного распределения: 1/3, 2/3 и 3/3 |
| DENSE_RANK | Эта функция вычисляет относительный ранг каждой возвращаемой запросом строки по отношению к другим строкам, основываясь на значениях выражений в конструкции ORDER BY. Данные в группе сортируются в соответствии с конструкцией ORDER BY, а затем каждой строке поочередно присваивается числовой ранг, начиная с 1. Ранг увеличивается при каждом изменении значений выражений, входящих в конструкцию ORDER BY. Строки с одинаковыми значениями получают один и тот же ранг (при этом сравнении значения NULL считаются одинаковыми). Возвращаемый этой функцией "плотный" ранг дает ранговые значения без промежутков. Сравните с представленной далее функцией RANK |
| FIRST_VALUE | Возвращает первое значение в группе |
LAG(выражение,<смещение>, <стандартное значение>) | Функция LAG дает доступ к другим строкам результирующего множества, избавляя от необходимости выполнять самосоединения. Она позволяет работать с курсором как с массивом. Можно ссылаться на строки, предшествующие текущей строке в группе. О том, как обращаться к следующим строкам в группе, см. в описании функции LEAD. Смещение - это положительное целое число со стандартным значением 1 (предыдущая строка). Стандартное значение возвращается, если индекс выходит за пределы окна (для первой строки группы будет возвращено стандартное значение) |
| LAST_VALUE | Возвращает последнее значение в группе |
| LEAD(выpaжeниe,<смещение>,<стандартное
значение>) | Функция LEAD противоположна функции LAG. Если функция LAG дает доступ к предшествующим строкам группы, то функция LEAD позволяет обращаться к строкам, следующим за текущей. Смещение — это положительное целое число со стандартным значением 1 (следующая строка). Стандартное значение возвращается, если индекс выходит за пределы окна (для последней строки группы будет возвращено стандартное значение) |
| МАХ(выражение) | Находит максимальное значение выражения в пределах окна в группе |
| МIN(выражение) | Находит минимальное значение выражения в пределах окна в группе |
| NTILE(выражение) | Делит группу на фрагменты по значению выражения. Например, если выражение = 4, то каждой строке в группе присваивается число от 1 до 4 в соответствии с фрагментом, в которую она попадает. Если в группе 20 строк, первые 5 получат значение 1, следующие 5 — значение 2 и т.д. Если количество строк в группе не делится на значение выражения без остатка, строки распределяются так, что ни в одном фрагменте количество строк не превосходит минимальное количество в других фрагментах более чем на 1, причем дополнительные строки будут в группах с меньшими номера фрагмента. Например, если снова выражение = 4, а количество строк = 21, в первом фрагменте будет 6 строк, во втором и последующих - 5 |
| PERCENT RANK | Аналогична функции CUME_DIST (кумулятивное распределение). Вычисляет ранг строки в группе минус 1, деленный на количество обрабатываемых строк минус 1. Эта функция всегда возвращает значения в диапазоне от 0 до 1 включительно |
| RANK | Эта функция вычисляет относительный ранг каждой строки, возвращаемой запросом, на основе значений выражений, входящих в конструкцию ORDER BY. Данные в группе сортируются в соответствии с конструкцией ORDER BY, а затем каждой строке поочередно присваивается числовой ранг, начиная с 1. Строки с одинаковыми значениями выражений, входящих в конструкцию ORDER BY, получают одинаковый ранг, но если две строки получат одинаковый ранг, следующее значение ранга пропускается. Если две строки получили ранг 1, строки с рангом 2 не будет; следующая строка в группе получит ранг 3. В этом отличие от функции DENSE_RANK, которая не пропускает значений |
| RATIO_TO_REPORT(выражение) | Эта функция вычисляет значение выражение / (SUM(выражение)) по строкам группы. Это дает процент, который составляет значение текущей строки по отношению к SUM(выражение) |
| REGR_xxxxxxx(выражение,выражение) | Эти функции линейной регрессии применяют стандартную линейную регрессию по методу наименьших квадратов к паре выражений. Предлагается 9 различных функций регрессии |
| ROW_NUMBER | Возвращает смещение строки по отношению к началу упорядоченной группы. Может использоваться для последовательной нумерации строк, упорядоченных по определенным критериям |
| STDDEV(выражение) | Вычисляет стандартное (среднеквадратичное) отклонение (standard deviation) текущей строки по отношению к группе |
| STDDEV_POP(выражение) | Эта функция вычисляет стандартное отклонение генеральной совокупности (population standard deviation) и возвращает квадратный корень из дисперсии генеральной совокупности (population variance). Она возвращает значение, совпадающее с квадратным корнем из результата функции VAR_POP |
| STDDEV_SAMP(выражение) | Эта функция вычисляет накопленное стандартное отклонение выборки (cumulative sample standard deviation) и возвращает квадратный корень выборочной дисперсии (sample variance). Она возвращает значение, совпадающее с квадратным корнем из результата функции VAR_SAMP |
| SUM(выражение) | Вычисляет общую сумму значений выражения для группы |
| VAR_POP(выражение) | Эта функция возвращает дисперсию генеральной совокупности для набора числовых значений (значения NULL игнорируются) |
| VAR_SAMP(выражение) | Эта функция возвращает выборочную дисперсию длянабора числовых значений (значения NULL игнорируются) |
| VARIANCE(выражение) | Возвращает дисперсию для выражения. Сервер Oracle вычисляет дисперсию как: - 0, если количество строк в группе = 1; -VAR_SAMP, если количество строк в группе больше 1 |