Аналитика в SQL

1. Аналитика в SQL

Цели и задачи:
1. Упростить и сократить формулировку запроса
2.
3.
4.
5.
Улучшить эффективность обработки запроса
Расширить реализацию вычислительных агрегатов
Уменьшить нагрузку на сеть
Перенести вычислительный процесс на сервер БД

2. Виды аналитических преобразований

• функции ранжирования
• порядковый номер строки в выборке по заданному условию сортировки с/без учета дубликатов значений
• Например: место спортсмена по очкам, менеджера по сумме продаж итп
• статистические функции для плавающего интервала
• Avg/min/max/… в рамках заданного (динамического) интервала значений
• Например: выбрав все заказы за год, одновременно посчитать среднюю стоимость заказа за месяц (текущей
строки), накопительную сумму по месяцам, кварталам и всему году за один проход
• функции подсчета долей
• Доля текущей строки от суммы за указанный (динамический) период
• Например: выдать все офисы продаж и их доли продаж от общей суммы за год
• статистические функции LAG/LEAD с запаздывающим/опережающим аргументом
• Позволяют “подсматривать” в предыдущие/следующие строки
• Например: посчитать рост продаж текущего месяца относительно предыдущего (выведя данные за весь год)
• статистические функции (например линейная регрессия)

3. Аналитические, векторные или оконные ?

• Область определения этих функций – столбец (окно на входе)
числового типа, возможно промежуточного этапа вычислений
• Множество значений этих функций – тоже столбец (окно на выходе)
• Семантическое место в SQL – операторы SELECT или ORDER BY,
которые завершают логику обработки данных, возможно
промежуточного этапа вычислений
• Похожи на скалярные агрегаты (типа SUM, MIN и тп. ), применяемые
с GROUP BY, но принципиально отличаются от последних степенью
детализации (не «схлопывают» результат в одну строку, а оставляют
оригинальное кол-во строк)

4. Разбиение данных на группы

• Синтаксис:
Аналитическая_функция(аргумент 1) OVER( PARTITION BY арг. 2, арг. 3,…….)
где арг.(умент) := атрибут | выражение
• Семантика:
Агрегация данных выполняется порциями (partition by), размер и количество которых определяется
“арг.(ументами) 2,3,…..”
Аналитическая функция выполняется над каждым множеством строк с одинаковыми значениями (арг
2,3,…)
Partition by похоже на group by, но в отличие от последнего не «схлопывает» строки
Пример:
SELECT department_id, last_name, job_id,
SUM(salary) OVER(PARTITION BY department_id, job_id)
from employees;

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

• Синтаксис:
Аналитическая_функция(арг.1) OVER([PARTITION …]
ORDER BY арг. 2 [,…] [{ASC|DESC}] [{NULLS FIRST|NULLS LAST}]),
где арг.(умент) := атрибут | выражение
• Семантика:
Правила работы ORDER BY - как в обычных SQL-операторах
Пример:
SELECT last_name, department_id, job_id,
SUM(salary) OVER (PARTITION BY department_id, job_id ORDER BY hire_date)
sum_sal
FROM employees;

6. Оконные ограничители строк

• Синтаксис (по строкам или по значению):
1.
{ROWS | RANGE} {{UNBOUNDED | выражение} PRECEDING | CURRENT ROW }
2.
{ROWS | RANGE} BETWEEN
{{UNBOUNDED PRECEDING | CURRENT ROW |
{UNBOUNDED | выражение 1}{PRECEDING | FOLLOWING}} AND
{{UNBOUNDED FOLLOWING | CURRENT ROW |
{UNBOUNDED | выражение 2}{PRECEDING | FOLLOWING}}
• Семантика:
PRECEDING и FOLLOWING задают верхнюю и нижнюю границы агрегирования с возможным префиксом
UNBOUNDED – неограниченный
ROWS – выражение (целое число) задает кол-во строк
RANGE – выражение задает число или интервал дат для значений выражения по которому происходит сортировка
По умолчанию: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (т.е. с начала выборки до текущей
строки)
• Пример:
SELECT last_name, department_id, job_id,
SUM(salary) OVER (PARTITION BY department_id, job_id ORDER BY hire_date ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW) sum_sal
FROM employees;

7. Интервальные временные условия

• Синтаксис:
INTERVAL число {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}
NUMTODSINTERVAL(число, '{DAY | HOUR | MINUTE | SECOND}')
NUMTOYMINTERVAL(число, '{YEAR | MONTH}')
Пример:
SELECT last_name, department_id, job_id,
AVG(salary) OVER (PARTITION BY department_id, job_id ORDER BY hire_date RANGE BETWEEN
INTERVAL '1' YEAR PRECEDING AND CURRENT ROW) sum_sal
FROM employees;

8. Функции взятия первого и последнего

• Функции FIRST_VALUE и LAST_VALUE для интервалов агрегирования
позволяют для каждой строки выдать первое значение ее окна и
последнее
Пример:
SELECT last_name, hire_date, salary,
FIRST_VALUE(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT
ROW),
LAST_VALUE(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT
ROW)
FROM employees;

9. Ранжирование и подсчет долей

Примеры:
SELECT last_name, salary,
ROW_NUMBER () OVER (ORDER BY salary DESC) salbacknumber,
ROW_NUMBER () OVER (ORDER BY salary) salnumber,
RANK() OVER (ORDER BY salary) salrank,
DENSE_RANK() OVER (ORDER BY salary) saldenserank,
CUME_DIST() OVER (ORDER BY salary) salpercentile
FROM employees;
--раздает сотрудникам места в порядке убывания/возрастания зарплат
SELECT last_name, salary,
RATIO_TO_REPORT(salary) OVER () salshare FROM employees
-- выдает доли зарплаты от общей суммы всех зарплат

10. Пример использования аналитики SQL в администрировании Oracle

• Задача: Требуется выдать первых 5 пользователей, которые расходуют
дисковое пространство в разных типах сегментов с расточительностью
более 50%.
Рабочая таблица – SYS.DBA_SEGMENTS(owner, segment_type, bytes)
Шаг 1 . Найдем типы сегментов, общий объем памяти для каждого
сегмента и долю их числа равную или меньшую общему объему
SELECT
segment_type,
SUM(bytes) bytes,
CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_perc
FROM sys.dba_segments
GROUP BY segment_type;

11. Шаг 2. Обернем запрос всеми “расточителями сегментового пространства” более 50%

SELECT * FROM
(SELECT
)
segment_type,
SUM(bytes) bytes,
CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_perс
FROM sys.dba_segments
GROUP BY segment_type
WHERE bytes_perc >= 0.5;

12. Шаг 3. Еще раз обернем запрос для вывода имен первых 5 “расточителей” типов сегментного пространства

SELECT *
FROM
(
SELECT owner,
SUM(bytes) bytes,
RANK() OVER(ORDER BY SUM(bytes) DESC) bytes_rank
FROM sys.dba_segments
WHERE segment_type IN
(SELECT segment_type
FROM
(SELECT segment_type,
SUM(bytes) bytes,
CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_perc
FROM sys.dba_segments
GROUP BY segment_type)
WHERE bytes_perc >= 0.5)
GROUP BY owner
)
WHERE bytes_rank <=5;

13. Задание: аналитический запрос

премию в размере 5% от суммы продаж
Задание: аналитический
менеджеру,
который за предыдущие
запрос
3
месяца продал товаров на самую
большую сумму
Выведите месяц, manager_id,
manager_first_name,
manager_last_name, премию за период
с января по декабрь 2014 года