🗊Презентация Аналитические функции Оracle

Аналитические функции ORACLE Графеева Н.Г. 2016

Аналитика, трудно отображаемая средствами стандартного SQL Подсчет нарастающих итогов (показать нарастающие итоги по зарплате построчно для каждого сотрудника); Подсчет процентов в группе (какой процент от общей зарплаты составляет зарплата отдельного сотрудника); Выборка первых N сотрудников с наибольшими зарплатами; Подсчет скользящего среднего (получить среднее значение по предыдущим N строкам); Выполнение ранжирующих запросов (показать ранг зарплаты сотрудника среди других сотрудников )

Назначение аналитических функций Они расширяют язык SQL так, что подобные операции не только проще записываются, но и быстрее выполняются по сравнению с использованием чистого языка SQL. Говорят, что эти расширения сейчас изучаются комитетом ANSI SQL с целью включения в спецификацию языка SQL.

Основные группы аналитических функций В ORACLE имеется по крайней мере 26 аналитических функций, которые достаточно условно могут быть разбиты на 4 группы: функции ранжирования; Функции агрегирования; оконные функции; функции, позволяющие “заглянуть” вперед или “оглянуться” назад.

Контекст использования аналитических функций Имя Функции(<аргумент>,< аргумент >, ...) OVER ( [конструкция фрагментации] [конструкция упорядочения] [конструкция окна] )

Конструкция фрагментации PARTITION BY выражение [, выражение] [, выражение] Конструкция задает область применения аналитических функций (группы). Если не указать конструкцию фрагментации, все результирующее множество считается одной группой.

Пример 1 - запрос select ename , deptno, sal, sum(sal) over (partition by deptno) sum_dept_sal from emp order by deptno

Пример 1 - результат

Пример 2 - запрос select ename , deptno, sal, sum(sal) over () sum_dept_sal from emp order by deptno

Пример 2 - результат

Конструкция упорядочения ORDER BY выражение [, выражение] [, выражение] [[ASC][DESC]] Согласно документации “задает критерий сортировки данных в каждой группе”. Однако в действительности дело не только в сортировке…

Пример 3 - запрос select ename , deptno, sal, sum(sal) over (partition by deptno order by ename) sum_dept_sal from emp order by deptno

Пример 3 - результат

Пример 4 – запрос (нарастающие итоги по зарплате) select ename , deptno, sal, sum(sal) over (order by ename) sum_dept_sal from emp order by ename

Пример 4 - результат

Пример 5 - запрос select ename , deptno, deptno || '.' || row_number() over (partition by deptno order by ename) emp_id from emp order by deptno

Пример 5 - резльтат

Конструкция окна Конструкция окна позволяет задать перемещающееся или жестко привязанное окно (набор) данных в пределах группы, с которым будет работать аналитическая функция. Возможны два типа задания конструкции окна – смещение (ROWS) и задание диапазона (RANGE). Допустимы следующие варианты задания окна: ROWS n PRECEDING ROWS n FOLLOWING RANGE UNBOUNDED PRECEDING RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW RANGE n PRECEDING И т.п. Например, конструкция ROWS n PRECEDING означает: применять аналитическую функцию к каждой строке данной группы с текущей строки до (n-1) предыдущей. Конструкция RANGE n PRECEDING означает: применять аналитическую функцию к каждой строке данной группы у которых значения (по которым работает конструкция ORDER BY) попадают в диапазон от (значения в текущей строке – n) до текущего значения.

Пример 6- запрос (смещение) select empno, ename , sal, avg(sal) over (order by empno rows 3 preceding) moving_avg from emp order by empno

Пример 6- результат

Пример 7 – запрос (окно диапазона) select empno, ename , sal, sum(sal) over (order by empno range unbounded preceding) added_sal from emp order by empno

Пример 7 - результат

Пример 8 – запрос(численное задание дипазона) select empno, ename , sal, (sal- 100) left_window_bound, sal right_window_bound, count(sal) over (order by sal range 100 preceding) count_sal from emp order by sal

Пример 8 – результат

Группы аналитических функций Rankings and percentiles Lag/lead analysis Window calculations First/last analysis

Предназначение аналитических функций

Порядок обработки аналитических функций Обработка запросов с помощью аналитических функций происходит в три этапа: Во-первых, выполняются все соединения, WHERE, GROUP BY и HAVING. Во-вторых, результирующий набор обрабатывается аналитическими функциями. В-третьих, если запрос имеет опцию ORDER BY, выполняется итоговая обработка результирующего множества.

Порядок обработки аналитических функций

Rankings and percentiles analysis RANK DENSE_RANK RATIO_TO_REPORT CUME_DIST PERCENT_RANK NTILE ROW_NUMBER

Синтаксис для использования RANK ( ) OVER ( [partition_clause] order_by_clause ) DENSE_RANK ( ) OVER ( [partition_clause] order_by_clause ) RATIO_TO_REPORT () ( [partition_clause] order_by_clause ) CUME_DIST ( ) OVER ( [partition_clause] order_by_clause ) PERCENT_RANK ( ) OVER ( [partition_clause] order_by_clause ) NTILE (exp) OVER ( [partition_clause] order_by_clause ) ROW_NUMBER ( ) OVER ( [partition_clause] order_by_clause )

Пример 9 (RANK – вычисляет относительный ранг каждой строки)

Пример 10 (RANK)

Пример 11(DENSE_RENK – вычисляет “плотный” ранг каждой строки без промежутков)

Пример 12(RATIO_TO_REPORT –вычисляет соотношение текущего значение к сумме значений по всей группе)

Пример 13(CUME_DIST)

Определение CUME_DIST (в документации ORACLE

Пример 14 (NTILE – классифицирует группы по значению выражения)

Определение NTILE (из документации ORACLE)

Упражнение 1 Классифицируйте клиентов из demo базы ORACLE на 3 категории в зависимости от общей суммы заказов.

Пример 15 (ROW_NUMBER – возвращает смещение строки по отношению к началу упорядоченной группы)

LAG/LEAD analysis Функции обеспечивают доступ к строкам в запросе с заданным смещением относительно текущей строки. Синтаксис для использования: {LAG | LEAD} ( value_expr [, offset] ) OVER ( [partition_clause] order_by_clause )

Пример 16(LAD, LEAD – предыдущее и последующее значения)

Windows functions Позволяют с легкостью вычислять: нарастающие итоги, скользящее среднее, центральное среднее и т.п. Работают совместно с агрегатными функциями: SUM(), AVG(), MAX(), MIN(), COUNT() и порядковыми функциями FIRST_VALUE() и LAST_VALUE()(возвращают первую и последнюю запись в окне).

Пример 17(вычисление нарастающих итогов)

Пример 18 (скользящее среднее)

Пример 19 (центральное среднее)

Пример 20 (вычисление размера окна)

Пример 21(first_value, last_value в окне)

Пример 22 (first_value, last_value в группе)

Домашнее задание 3 Создать приложение, отображающее в виде графиков нарастающие итоги (сумма или количество) по заказам из демонстрационной базы ORACLE (нарастающие по времени). Запрос должен быть написан с использованием аналитических функций. Ссылку на приложение, логин и пароль для входа отправлять по адресу: N.Grafeeva@spbu.ru Тема - Data_Mining_2016_job3