Аналитические функции в OracleВладимир Пржиялковский
Содержание
Общие положенияОбщая информацияВ версии СУБД Oracle 8.1.6 появился новый класс из 26 функций, названных аналитическими, и получившим дальнейшее развитие в версии 9. Их описания были созданы совместными усилиями фирм IBM, Informix, Oracle и Compaq путем разработки так называемых "улучшений" некоторых конструкций, имеющихся в стандарте SQL1999. В отличие от обычных скалярных функций аналитические функции берут аргументом SQL-таблицу, представляющую логический промежуточный результат обработки SQL-оператора, где использовано обращение к такой функции, и возвращают в качестве своего результата обычно тоже SQL-таблицу. Цели введения аналитических функций в OracleТехническая цель введения аналитических функций - дать лаконичную формулировку и увеличить скорость выполнения "аналитических запросов" к БД, то есть запросов, имеющих смыслом выявление внутренних соотношений и зависимостей в данных. Более точно, пользование аналитическими функциями может дать следующие выгоды перед обычными SQL-операторами:
Стратегическая цель введения в Oracle аналитических функций - дать базовое средство для построения ИС типа "складов данных" (data warehouse, DW), ИС "аналитического характера" (business intelligence systems, BI) или OLAP-систем. По представлениям разработчиков, набор таких базовых средств помимо аналитических функций формируют еще и прочие средства Oracle, такие как
Классификация видов аналитических функций в OracleСогласно классификации из документации по Oracle, аналитические функции могут быть следующих видов: (a) функции ранжирования Основные технические особенностиМесто указания аналитических функций в SQL-предложенииАналитические функции принимают в качестве аргумента столбец промежуточного результата вычисления SQL-предложения и возвращают тоже столбец. Поэтому местом их использования в SQL-предложении могут быть только фразы ORDER BY и SELECT, выполняющие завершающую обработку логического промежуточного результата. Сравнение с обычными функциями агрегированияМногие аналитические функции действуют подобно обычным скалярным функциям агрегирования SUM, MAX и прочим, примененным к группам строк, сформированным с помощью GROUP BY. Однако обычные функции агрегирования уменьшают степень детализации, а аналитические функции нет. Поясняющий сравнительный пример: SELECT deptno, job, SUM(sal) sum_sal SELECT ename, deptno, job, Результат первого запроса: 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 SELECT empno, deptno, job, SET AUTOTRACE OFF Обратим внимание на однопроходность и специальный шаг плана второго запроса (шаг WINDOW). Разбиение данных на группы для вычисленийАналитические функции агрегируют данные порциями (partitions; группами), количество и размер которых можно регулировать специальной синтаксической конструкцией. Ниже она указана на примере агрегирующей функции SUM: SUM(выражение 1) OVER([PARTITION BY выражение 2 [, выражение 3 [, …]]]) Пример использования такой конструкции см. выше. Если PARTITION BY не указано, то в качестве единственной группы для вычислений будет взят полный набор строк: SELECT ename, deptno, job, Результат последнего запроса: ENAME DEPTNO JOB SUM_SAL
14 rows selected. Упорядочение в границах отдельной группыС помощью синтаксической конструкции ORDER BY строки в группах вычислений можно упорядочивать. Синтаксис иллюстрируется на примере агрегирующей функции SUM: SUM(выражение 1) OVER([PARTITION …] Правила работы ORDER BY - как в обычных SQL-операторах. Пример: SELECT ename, deptno, job, ENAME DEPTNO JOB SUM_SAL
14 rows selected. В группах из более одной строки появился заданный порядок. Природа изменения поля SUM_SAL в пределах групп из нескольких строк станет ясна из следующего раздела. Выполнение вычислений для строк в группе по плавающему окну (интервалу)Для некоторых аналитических функций, например, агрегирующих, можно дополнительно указать объем строк, участвующих в вычислении, выполняемом для каждой строки в группе. Этот объем, своего рода контекст строки, называется "окном", а границы окна могут задаваться различными способами. {ROWS / RANGE} {{UNBOUNDED / выражение} PRECEDING / CURRENT ROW } {ROWS / RANGE} Фразы PRECEDING и FOLLOWING задают верхнюю и нижнюю границы агрегирования (то есть интервал строк, "окно" для агрегирования). Вот поясняющий пример, воспроизводящий результат из предыдущего раздела: SELECT ename, deptno, job, ENAME DEPTNO JOB SUM_SAL
14 rows selected. Здесь в пределах каждой группы (использована фраза PARTITION BY) сотрудники упорядочиваются по времени найма на работу (фраза ORDER BY) и для каждого в группе вычисляется сумма зарплат: его и всех его предшественников (фраза ROWS BETWEEN формулирует "окошко суммирования" от первого в группе до текущего рассматриваемого). Выделенная в последнем запросе жирным цветом фраза подразумевается по умолчанию, если она попросту отсутствует (ср. с запросом из предыдущего раздела). Обратите внимание, что плавающий интервал задается в терминах упорядоченных строк (ROWS) или значений (RANGE), для чего фраза ORDER BY в определении группы обязана присутствовать. Формирование интервалов агрегирования "по строкам" и "по значениям"Разницу между ROWS и RANGE (определяющими, как говорится в документации, "физические" и "логические" интервалы-окна) удобно продемонстрировать следующим примером: SELECT ename, hiredate, sal, ENAME HIREDATE SAL ROWS_SAL RANGE_SAL
14 rows selected. JAMES и FORD поступили на работу одновременно, и с точки зрения интервала суммирования неразличимы. Поэтому суммирование "по значению" присвоило им один и тот же общий для "мини-группы", образованной этой парой, результат - максимальную сумму, которая при всех возможных порядках перечисления сотрудников внутри этой пары будет всегда одинакова. Суммирование "по строкам" (ROWS) поступило иначе: оно упорядочило сотрудников в "мини-группе", образованной равными датами (на самом деле чисто произвольно) и подсчитало суммы, как будто бы у этих сотрудников был задан порядок следования. Функции FIRST_VALUE и LAST_VALUE для интервалов агрегированияЭти функции позволяют для каждой строки выдать первое значение ее окна и последнее. Пример: SELECT ename, hiredate, sal, 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, ENAME HIREDATE SAL AVG_SAL
14 rows selected. Вот другая запись для того же запроса, но позволяющая использовать для числа месяцев обычное числовое выражение: SELECT ename, hiredate, sal, Виды аналических функцийВ качестве базовой в аналитической функции могут быть указаны традиционные для Oracle статистические (агрегатные, то есть обобщающие) функции COUNT, MIN, MAX, SUM, AVG и другие ("стандартные агрегатные функции" по документации). Примеры приводились выше. Можно обратить внимание на то, что аналитические функции со статистическими агрегатами разумно обрабатывают NULL: SELECT ename, hiredate, sal, Ниже приводится полный перечень аналитических функций в версии СУБД 9.2:
Звездочкой помечены функции, допускающие использование плавающего интервала расчета. Некоторые из этих функций рассматриваются ниже. Функции ранжированияФункции ранжирования позволяют "раздать" строкам "места" в зависимости от имеющихся в них значениях. Некоторые примеры: SELECT ename, sal, (раздать сотрудникам места в порядке убывания/возрастания зарплат) Функции подсчета долейФункции подсчета долей позволяют одной SQL-операцией получить для каждой строки ее "вес" в таблице в соответствии с ее значениями. Некоторые примеры: SELECT ename, sal, RATIO_TO_REPORT(sal) OVER () AS salshare FROM emp; (доли сотрудников в общей сумме зарплат) Пример выдачи доли сотрудников с меньшей или равной зарплатой, чем у "текущего": SELECT job, ename, sal, JOB ENAME SAL CUME_DIST
14 rows selected. (видно, что три четверти клерков имеют зарплату, меньше чем ADAMS). Проранжировать эту выдачу по доле сотрудников в группе можно функцией PERCENT_RANK: SELECT job, ename, sal, JOB ENAME SAL CUME_DIST PCT_RANK
14 rows selected. Процентный ранг отсчитывается от 0 и изменяется до 1. Некоторые жизненные примеры аналитических запросовДля типов сегментов, более других расходующих дисковое пространство, выдать главных пользователей, ответственных за такой расходПостроить такой запрос на основе таблицы SYS.DBA_SEGMENTS, можно пошагово. Шаг 1. Выдать типы сегментов в БД, общий объем памяти на диске для каждого типа и долю числа типов с равным или меньшим общим объемом памяти: SELECT segment_type, Шаг 2. Отобрать 40% "наиболее расточительных" по дисковой памяти типов: SELECT * Шаг 3. Отобрать пользователей, занимающих первые пять мест по расходованию памяти среди "наиболее расточительных" типов сегментов: SELECT * Выдать список периодов наиболее активного переключения журнальных файлов БДСписок переключений журнальных файлов хранится в динамической таблице v$loghist. Ниже приводится один из вариантов запроса. var treshold number SELECT Пояснения
Ссылки по теме
|