имя_функции(<аргумент>,< аргумент >, . . . )
over (<конструкция_фрагментации><конструкция_упорядочения><конструкция_окна>)
Рассмотрим основные части данного синтаксиса.
1. <конструкция_фрагментации>
Синтаксис для задания конструкции фрагментации выглядит следующим образом
partition by выражение [, выражение] [, выражение]
Данная конструкция логически разбивает результирующее множество на N групп
по критериям, задаваемым выражениями фрагментации. Аналитические функции применяются к каждой группе независимо, - для каждой новой группы они сбрасываются. Если не указать конструкцию фрагментации, все результирующее множество считается
одной группой.
2. <конструкция_упорядочения>
Конструкция упорядочения имеет следующий синтаксис
order by выражение [asc | desc] [nulls first | nulls last]
Конструкция 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 (
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) 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 | ALL] выражение) | Используется для вычисления среднего значения выражения в пределах группы и окна. Для поиска среднего после удаления дублирующихся значений можно указывать ключевое слово DISTINCT |
| CORR(выражение, выражение) | Выдает коэффициент корреляции для пары выражений, возвращающих числовые значения. В статистическом смысле, корреляция — это степень связи между переменными. Связь между переменными означает, что значение одной переменной можно в определенной степени предсказать по значению другой. Коэффициент корреляции представляет степень корреляции в виде числа в диапазоне от -1 (высокая обратная корреляция) до 1 (высокая корреляция). Значение 0 соответствует отсутствию корреляции |
| COUNT([DISTINCT][*] [выражение]) | Эта функция считает строки в группах. Если указать * или любую константу, кроме 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 |