Основы баз данных и SQL. Аналитические функции v2.0 презентация

Содержание
Что это, зачем и как работает
Примеры
Аналоги агрегатных. Ограничения окна
10.06.2019
GlowByte Consulting

Знакомьтесь – модельная таблица
История операций по банковским счетам
10.06.2019
GlowByte Consulting

Общий синтаксис
10.06.2019
GlowByte Consulting
функция(аргументы) OVER (параметры окна)
Видите слово OVER – значит, перед вами аналитическая функция. Еще их называют оконными.

Зачем нужны аналитические функции?
Вычислить значение, которое зависит от порядка строк*
10.06.2019
GlowByte Consulting
SELECT
H.*,
-- нумерует начисления на счет
ROW_NUMBER() OVER (
PARTITION BY ACC_ID ORDER BY DT
) RN
FROM ACC_HIST H;
* Исходно же в реляционной модели у строк нет порядка,
вычисления по ним проводятся независимо

Зачем еще нужны?
Вычислить метрику* и не схлопывать группу строк в одну
10.06.2019
GlowByte Consulting
SELECT
H.*, -- суммарные начисления на счет
SUM(VALUE) OVER (
PARTITION BY ACC_ID
) SUM_V
FROM ACC_HIST H
ORDER BY ACC_ID, DT;
* Подробно см. далее про аналоги агрегатных функций

Еще типичные задачи на оконные функции
Посчитать промежуточную сумму
Посчитать процент ЗП сотрудника от общей ЗП по отделу
Запросить первых top N сотрудников по ЗП
Посчитать скользящие метрики
Отранжировать сотрудников по ЗП
10.06.2019
GlowByte Consulting
См. книгу – Томас Кайт «Oracle для профессионалов», глава 12

Аналитические vs Однострочные
Что общего: не меняют детализацию выборки, просто добавляют к ней столбец.
В чем разница: однострочные считаются независимо для каждой строки.
Аналитические учитывают порядок строк/разбивку по группам.
10.06.2019
GlowByte Consulting

Аналитические vs Агрегатные
Что общего: при вычислении обращаются к набору строк.
В чем разница: агрегатные схлопывают группу строк в одну.
Аналитические не меняют количество строк выборки.
10.06.2019
GlowByte Consulting

Еще про синтаксис – параметры окна
PARTITION BY – разбивает строки на группы.
ORDER BY – задает порядок строк.
10.06.2019
GlowByte Consulting
SELECT
H.*,
-- нумерует начисления на счет
ROW_NUMBER() OVER (
PARTITION BY ACC_ID ORDER BY DT
) RN
FROM ACC_HIST H;

Важно!
Аналитические функции вычисляются
только в SELECT или ORDER BY.
Хочешь поставить в WHERE? Делай подзапрос.
10.06.2019
GlowByte Consulting

Фильтруем по аналитической функции
Найдем последний платеж по каждому счету.
Это популярное применение ROW_NUMBER.
10.06.2019
GlowByte Consulting
SELECT ACC_ID, VALUE, DT
FROM (
SELECT
ACC_ID, VALUE, DT,
ROW_NUMBER() OVER (
PARTITION BY ACC_ID
ORDER BY DT DESC
) RN
FROM ACC_HIST
) H
WHERE RN = 1;

С ROW_NUMBER понятно.
Какие еще бывают?
10.06.2019
GlowByte Consulting

Ранжирование – RANK, DENSE_RANK
Ранжируют строки по полю, по которому сортируем
10.06.2019
GlowByte Consulting
SELECT
H.*,
RANK() OVER (ORDER BY VALUE) R,
DENSE_RANK() OVER (ORDER BY VALUE) DR
FROM ACC_HIST H;

RANK и DENSE_RANK – пример
Топ 3 снятий со счета
10.06.2019
GlowByte Consulting
SELECT ACC_ID, VALUE, DT
FROM (
SELECT
ACC_ID, VALUE, DT,
RANK() OVER (ORDER BY VALUE) R
FROM ACC_HIST
) H
WHERE R <= 3;
Т.е. учитываем, что некоторые снятия могут совпадать.
Еще можно вычислять места участников состязаний по их результатам.

FIRST_VALUE – первое значение столбца
10.06.2019
GlowByte Consulting
SELECT
ACC_ID, VALUE, DT,
FIRST_VALUE(VALUE) OVER (
PARTITION BY ACC_ID ORDER BY DT
) FIRST_VAL
FROM ACC_HIST;

LAG и LEAD – идем в соседние строки
Найдем величину предыдущей и следующей операции по счету
10.06.2019
GlowByte Consulting
SELECT
ACC_ID, VALUE, DT,
LAG(VALUE) OVER (
PARTITION BY ACC_ID ORDER BY DT
) PREV_VAL,
LEAD(VALUE) OVER (
PARTITION BY ACC_ID ORDER BY DT
) NEXT_VAL
FROM ACC_HIST;

Аналоги агрегатных функций
10.06.2019
GlowByte Consulting

Аналоги агрегатных функций
Пишем к агрегатной функции OVER (…) – получаем оконную.
Только не меняем детализацию выборки.
10.06.2019
GlowByte Consulting
SELECT
H.*,
-- суммарные начисления на счет
SUM(VALUE) OVER (
PARTITION BY ACC_ID
) SUM_V
FROM ACC_HIST H
ORDER BY ACC_ID, DT;

А если добавим ORDER BY?
Получим скользящую метрику:
10.06.2019
GlowByte Consulting
SELECT
H.*,
-- денег на счету после операции
SUM(VALUE) OVER (
PARTITION BY ACC_ID
ORDER BY DT
) SUM_V
FROM ACC_HIST H
ORDER BY ACC_ID, DT;
* У операций одинаковая дата. Очередность неизвестна.
Считается, что они произошли одновременно.

Ограничиваем окно строк
10.06.2019
GlowByte Consulting

Ограничиваем окно – ROWS
10.06.2019
GlowByte Consulting
Сумма по 2 последним платежам:
SELECT
H.*,
SUM(VALUE) OVER ( PARTITION BY ACC_ID
ORDER BY DT
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) SUM_V
FROM ACC_HIST H
ORDER BY ACC_ID, DT;
* Платежи были одновременные –
результат запроса может быть неоднозначен!

ROWS – между чем и чем?
10.06.2019
GlowByte Consulting
ROWS BETWEEN
AND
и
могут быть:
CURRENT ROW –строка, в которой считаем функцию
UNBOUNDED PRECEDING – первая строка группы
UNBOUNDED FOLLOWING – последняя строка группы
N PRECEDING – N предыдущих строк
N FOLLOWING – N следующих строк

Ограничиваем окно – RANGE
Сумма платежей за последние 2 дня:
10.06.2019
GlowByte Consulting
SELECT
H.*,
SUM(VALUE) OVER
(
PARTITION BY ACC_ID
ORDER BY DT
RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
) SUM_V
FROM ACC_HIST H
ORDER BY ACC_ID, DT;

RANGE – особенности
Между чем и чем считает – синтаксис, как у ROWS.
В SQL
Server не работает с N PRECEDING/FOLLOWING. В Oracle работает.
Тип данных поля, по которому сортируем – число или дата-время.
10.06.2019
GlowByte Consulting

! Не всегда детерминирован результат
10.06.2019
GlowByte Consulting
Запускаем запрос 2 раза – получаем разный результат.
Потому
что есть 2 одновременных платежа.
SELECT H.*, ROW_NUMBER() OVER (PARTITION BY ACC_ID ORDER BY DT) RN
FROM ACC_HIST H
WHERE ACC_ID = 3;
Запуск 1:
Запуск 2:

Задачи на дом –
см. на вики
«Основы баз данных и SQL»
10.06.2019
GlowByte Consulting