Аналитические функции в Oracle (Часть 2)

Место указания аналитических функций в SQL-предложении

Аналитические функции принимают в качестве аргумента столбец промежуточного результата вычисления SQL-предложения и возвращают тоже столбец. Поэтому местом их использования в SQL-предложении могут быть только фразы ORDER BY и SELECT, выполняющие завершающую обработку логического промежуточного результата.

Сравнение с обычными функциями агрегирования

Многие аналитические функции действуют подобно обычным скалярным функциям агрегирования SUM, MAX и прочим, примененным к группам строк, сформированным с помощью GROUP BY. Однако обычные функции агрегирования уменьшают степень детализации, а аналитические функции нет. Поясняющий сравнительный пример:

SELECT deptno, job, SUM(sal) sum_sal
FROM emp
GROUP BY deptno, job;

SELECT ename, deptno, job,
            SUM(sal) OVER (PARTITION BY deptno, job) sum_sal    
FROM emp;

Результат первого запроса:

DEPTNO             JOB                       SUM_SAL
----------                  ---------                         ----------

9 rows selected.

Результат второго запроса:

ENAME                    DEPTNO           JOB                SUM_SAL
----------                        ----------             ---------                 ----------

14 rows selected.

Особенности обработки

Построим в SQL*Plus планы для двух запросов выше:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT deptno, job, SUM(sal) sum_sal
FROM emp
GROUP BY deptno, job;

SELECT empno, deptno, job,
SUM(sal) OVER (PARTITION BY deptno, job) sum_sal
FROM emp;

SET AUTOTRACE OFF

Обратим внимание на однопроходность и специальный шаг плана второго запроса (шаг WINDOW).

Разбиение данных на группы для вычислений

Аналитические функции агрегируют данные порциями (partitions; группами), количество и размер которых можно регулировать специальной синтаксической конструкцией. Ниже она указана на примере агрегирующей функции SUM:

SUM(выражение 1) OVER([PARTITION BY выражение 2 [, выражение 3 [, …]]])

Пример использования такой конструкции см. выше.

Если PARTITION BY не указано, то в качестве единственной группы для вычислений будет взят полный набор строк:

SELECT ename, deptno, job,
SUM(sal) OVER () sum_sal
FROM emp;

Результат последнего запроса:

ENAME DEPTNO JOB SUM_SAL
---------- ---------- --------- ----------

14 rows selected.

Упорядочение в границах отдельной группы

С помощью синтаксической конструкции ORDER BY строки в группах вычислений можно упорядочивать. Синтаксис иллюстрируется на примере агрегирующей функции SUM:

SUM(выражение 1) OVER([PARTITION …]
ORDER BY выражение 2 [,…] [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}])

Правила работы ORDER BY - как в обычных SQL-операторах. Пример:

SELECT ename, deptno, job,
SUM(sal) OVER (PARTITION BY deptno, job ORDER BY hiredate) sum_sal
FROM emp;

ENAME             DEPTNO           JOB                            SUM_SAL
----------                    ----------               ---------                    ----------

14 rows selected.

В группах из более одной строки появился заданный порядок. Природа изменения поля SUM_SAL в пределах групп из нескольких строк станет ясна из следующего раздела.

Выполнение вычислений для строк в группе по плавающему окну (интервалу)

Для некоторых аналитических функций, например, агрегирующих, можно дополнительно указать объем строк, участвующих в вычислении, выполняемом для каждой строки в группе. Этот объем, своего рода контекст строки, называется "окном", а границы окна могут задаваться различными способами.

{ROWS / RANGE} {{UNBOUNDED / выражение} PRECEDING / CURRENT ROW }

{ROWS / RANGE}
BETWEEN
{{UNBOUNDED PRECEDING / CURRENT ROW /
{UNBOUNDED / выражение 1}{PRECEDING / FOLLOWING}}
AND
{{UNBOUNDED FOLLOWING / CURRENT ROW /
{UNBOUNDED / выражение 2}{PRECEDING / FOLLOWING}}

Фразы PRECEDING и FOLLOWING задают верхнюю и нижнюю границы агрегирования (то есть интервал строк, "окно" для агрегирования).

Вот поясняющий пример, воспроизводящий результат из предыдущего раздела:

SELECT ename, deptno, job,
SUM(sal)
OVER (PARTITION BY deptno, job ORDER BY hiredate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_sal
FROM emp;

ENAME DEPTNO JOB SUM_SAL
---------- ---------- --------- ----------

14 rows selected.

Здесь в пределах каждой группы (использована фраза PARTITION BY) сотрудники упорядочиваются по времени найма на работу (фраза ORDER BY) и для каждого в группе вычисляется сумма зарплат: его и всех его предшественников (фраза ROWS BETWEEN формулирует "окошко суммирования" от первого в группе до текущего рассматриваемого).

Выделенная в последнем запросе жирным цветом фраза подразумевается по умолчанию, если она попросту отсутствует (ср. с запросом из предыдущего раздела).

Обратите внимание, что плавающий интервал задается в терминах упорядоченных строк (ROWS) или значений (RANGE), для чего фраза ORDER BY в определении группы обязана присутствовать.

Формирование интервалов агрегирования "по строкам" и "по значениям"

Разницу между ROWS и RANGE (определяющими, как говорится в документации, "физические" и "логические" интервалы-окна) удобно продемонстрировать следующим примером:

SELECT ename, hiredate, sal,
SUM(sal)
OVER (ORDER BY hiredate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rows_sal,
SUM(sal)
OVER (ORDER BY hiredate
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) range_sal
FROM emp;

ENAME HIREDATE SAL ROWS_SAL RANGE_SAL
---------- --------- ---------- ---------- ----------

14 rows selected.

JAMES и FORD поступили на работу одновременно, и с точки зрения интервала суммирования неразличимы. Поэтому суммирование "по значению" присвоило им один и тот же общий для "мини-группы", образованной этой парой, результат - максимальную сумму, которая при всех возможных порядках перечисления сотрудников внутри этой пары будет всегда одинакова. Суммирование "по строкам" (ROWS) поступило иначе: оно упорядочило сотрудников в "мини-группе", образованной равными датами (на самом деле чисто произвольно) и подсчитало суммы, как будто бы у этих сотрудников был задан порядок следования.

Функции FIRST_VALUE и LAST_VALUE для интервалов агрегирования

Эти функции позволяют для каждой строки выдать первое значение ее окна и последнее. Пример:

SELECT ename, hiredate, sal,
FIRST_VALUE(sal)
OVER (ORDER BY hiredate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) first_rows,
LAST_VALUE(sal)
OVER (ORDER BY hiredate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) last_rows,
FIRST_VALUE(sal)
OVER (ORDER BY hiredate
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) first_range,
LAST_VALUE(sal)
OVER (ORDER BY hiredate
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) last_range
FROM emp;

ENAME HIREDATE SAL FIRST_ROWS LAST_ROWS FIRST_RANGE LAST_RANGE
--------                      ---------             ------            ----------      ---------     -----------     ----------

14 rows selected.

Интервалы времени

Для интервалов (окон), упорядоченных внутри по значению ("логическом", RANGE) в случае, если это значение имеет тип "дата", границы интервала можно указывать выражением над датой, а не конкретными значениями из строк. Примеры таких выражений:

INTERVAL число {YEAR / MONTH / DAY / HOUR / MINUTE / SECOND}

NUMTODSINTERVAL(число, '{DAY / HOUR / MINUTE / SECOND}')

NUMTOYMINTERVAL(число, '{YEAR / MONTH}')

Пример выдачи зарплат сотрудников и средних зарплат за последние полгода на момент приема нового сотрудника:

SELECT ename, hiredate, sal,
AVG(sal)
OVER (ORDER BY hiredate
RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND CURRENT ROW) avg_sal
FROM emp;

ENAME          HIREDATE             SAL            AVG_SAL
----------                   ---------               ----------            ----------

14 rows selected.

Вот другая запись для того же запроса, но позволяющая использовать для числа месяцев обычное числовое выражение:

SELECT ename, hiredate, sal,
AVG(sal)
OVER (ORDER BY hiredate
RANGE BETWEEN NUMTOYMINTERVAL(6, 'MONTH') PRECEDING
AND CURRENT ROW) avg_sal
FROM emp;