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

Источник: akarak

Место указания аналитических функций в 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
---------- --------- ----------

10	CLERK	1300	<- - одна группа
10	MANAGER	2450	<- - одна группа
10	PRESIDENT	5000	<- - одна группа
20	CLERK	6000	<- - одна группа
20	MANAGER	1900
20	PRESIDENT	2975
30	CLERK	950
30	MANAGER	2850
30	PRESIDENT	5600

9 rows selected.

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

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

MILLER	10	CLERK	1300	<- - одна группа
CLARK	10	MANAGER	2450	<- - еще одна группа
KING	10	PRESIDENT	5000	<- - еще одна группа
SCOTT	20	ANALYST	6000	<- - еще одна группа
FORD	20	ANALYST	6000
SMITH	20	CLERK	1900	<- - еще одна группа
ADAMS	20	CLERK	1900
JONES	20	MANAGER	2975	<- - еще одна группа
JAMES	30	CLERK	950	<- - еще одна группа
BLAKE	30	MANAGER	2850	<- - еще одна группа
ALLEN	30	SALESMAN	5600	<- - еще одна группа
MARTIN	30	SALESMAN	5600
TURNER	30	SALESMAN	5600
WARD	30	SALESMAN	5600

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
---------- ---------- --------- ----------

SMITH	20	CLERK	29025	<- - единственная группа,
ALLEN	30	SALESMAN	29025	и сумма на всех одна
WARD	30	SALESMAN	29025
JONES	20	MANAGER	29025
MARTIN	30	SALESMAN	29025
BLAKE	30	MANAGER	29025
CLARK	10	MANAGER	29025
SCOTT	20	ANALYST	29025
KING	10	PRESIDENT	29025
TURNER	30	SALESMAN	29025
ADAMS	20	CLERK	29025
JAMES	30	CLERK	29025
FORD	20	ANALYST	29025
MILLER	10	CLERK	29025

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
---------- ---------- --------- ----------

MILLER	10	CLERK	1300
CLARK	10	MANAGER	2450
KING	10	PRESIDENT	5000
FORD	20	ANALYST	3000	<- - порядок и сумма изменились
SCOTT	20	ANALYST	6000
SMITH	20	CLERK	800	<- - порядок и сумма изменились
ADAMS	20	CLERK	1900
JONES	20	MANAGER	2975
JAMES	30	CLERK	950
BLAKE	30	MANAGER	2850
ALLEN	30	SALESMAN	1600	<- - порядок и сумма изменились
WARD	30	SALESMAN	2850
TURNER	30	SALESMAN	4350
MARTIN	30	SALESMAN	5600

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
---------- ---------- --------- ----------

MILLER	10	CLERK	1300
CLARK	10	MANAGER	2450
KING	10	PRESIDENT	5000
FORD	20	ANALYST	3000	<- - зарплата FORD'а
SCOTT	20	ANALYST	6000	<- - сумма FORD'а и SCOTT'а
SMITH	20	CLERK	800	<- - зарплата SMITH'а
ADAMS	20	CLERK	1900	<- - сумма SMITH'а и ADAMS'а
JONES	20	MANAGER	2975
JAMES	30	CLERK	950
BLAKE	30	MANAGER	2850
ALLEN	30	SALESMAN	1600	<- - зарплата ALLEN'а
WARD	30	SALESMAN	2850	<- - сумма ALLEN'а и WARD'а
TURNER	30	SALESMAN	4350	<- - ALLEN+WARD+TURNER
MARTIN	30	SALESMAN	5600	<- - ALLEN+WARD+TURNER+MARTIN

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
---------- --------- ---------- ---------- ----------

SMITH	17-DEC-80	800	800	800
ALLEN	20-FEB-81	1600	2400	2400
WARD	22-FEB-81	1250	3650	3650
JONES	02-APR-81	2975	6625	6625
BLAKE	01-MAY-81	2850	9475	9475
CLARK	09-JUN-81	2450	11925	11925
TURNER	08-SEP-81	1500	13425	13425
MARTIN	28-SEP-81	1250	14675	14675
KING	17-NOV-81	5000	19675	19675
JAMES	03-DEC-81	950	20625	23625
FORD	03-DEC-81	3000	23625	23625
MILLER	23-JAN-82	1300	24925	24925
SCOTT	19-APR-87	3000	27925	27925
ADAMS	23-MAY-87	1100	29025	29025

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
-------- --------- ------ ---------- --------- ----------- ----------

SMITH	17-DEC-80	800	800	800	800	800
ALLEN	20-FEB-81	1600	800	1600	1600	1600
WARD	22-FEB-81	1250	800	1250	1600	1250
JONES	02-APR-81	2975	1600	2975	2975	2975
BLAKE	01-MAY-81	2850	1250	2850	2850	2850
CLARK	09-JUN-81	2450	2975	2450	2450	2450
TURNER	08-SEP-81	1500	2850	1500	1500	1500
MARTIN	28-SEP-81	1250	2450	1250	1250	1250
KING	17-NOV-81	5000	1500	5000	5000	5000
JAMES	03-DEC-81	950	1250	950	950	3000
FORD	03-DEC-81	3000	5000	3000	950	3000
MILLER	23-JAN-82	1300	950	1300	1300	1300
SCOTT	19-APR-87	3000	3000	3000	3000	3000
ADAMS	23-MAY-87	1100	1300	1100	1100	1100

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
---------- --------- ---------- ----------

SMITH	17-DEC-80	800	800
ALLEN	20-FEB-81	1600	1200
WARD	22-FEB-81	1250	1216.66667
JONES	02-APR-81	2975	1656.25
BLAKE	01-MAY-81	2850	1895
CLARK	09-JUN-81	2450	1987.5
TURNER	08-SEP-81	1500	2443.75
MARTIN	28-SEP-81	1250	2205
KING	17-NOV-81	5000	2550
JAMES	03-DEC-81	950	2358.33333
FORD	03-DEC-81	3000	2358.33333
MILLER	23-JAN-82	1300	2166.66667
SCOTT	19-APR-87	3000	3000
ADAMS	23-MAY-87	1100	2050

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;

Ссылки по теме