Similar presentations:
Основы баз данных и SQL. Аналитические функции v2.0
1. Основы баз данных и SQL
1.1. Аналитические функции12.02.2020
GlowByte Consulting
2. Содержание
1. Что это, зачем и как работает2. Примеры
3. Аналоги агрегатных. Ограничения окна
12.02.2020
GlowByte Consulting
2
3. Знакомьтесь – модельная таблица
История операций по банковским счетамACC_ID VALUE DT
1
100 01.01.2019
1
-30 02.01.2019
2
80 01.01.2019
2
-20 02.01.2019
2
-20 03.01.2019
3
70 01.01.2019
3
50 02.01.2019
3
-10 05.01.2019
3
-60 06.01.2019
12.02.2020
GlowByte Consulting
3
4. Общий синтаксис
Видите слово OVER – значит, перед вами аналитическаяфункция. Еще их называют оконными.
функция(аргументы) OVER (параметры окна)
12.02.2020
GlowByte Consulting
4
5. Зачем нужны аналитические функции?
Вычислить значение, которое зависит от порядка строк*SELECT
H.*,
-- нумерует начисления на счет
ROW_NUMBER() OVER (
PARTITION BY ACC_ID ORDER BY DT
) RN
FROM ACC_HIST H;
ACC_ID VALUE DT
RN
1
100 01.01.2019 1
1
-30 02.01.2019 2
2
80 01.01.2019 1
2
-20 02.01.2019 2
2
-20 03.01.2019 3
3
70 01.01.2019 1
3
50 02.01.2019 2
3
-10 05.01.2019 3
3
-60 06.01.2019 4
* Исходно же в реляционной модели у строк нет порядка,
вычисления по ним проводятся независимо
12.02.2020
GlowByte Consulting
5
6. Зачем еще нужны?
Вычислить метрику* и не схлопывать группу строк в однуSELECT
H.*,
-- суммарные начисления на счет
SUM(VALUE) OVER (
PARTITION BY ACC_ID
) SUM_V
FROM ACC_HIST H
ORDER BY ACC_ID, DT;
ACC_ID VALUE DT
SUM_V
1
100 01.01.2019
70
1
-30 02.01.2019
70
2
80 01.01.2019
40
2
-20 02.01.2019
40
2
-20 03.01.2019
40
3
70 01.01.2019
50
3
50 02.01.2019
50
3
-10 05.01.2019
50
3
-60 06.01.2019
50
* Подробно см. далее про аналоги агрегатных функций
12.02.2020
GlowByte Consulting
6
7. Еще типичные задачи на оконные функции
• Посчитать промежуточную сумму• Посчитать процент ЗП сотрудника от общей ЗП по отделу
• Запросить первых top N сотрудников по ЗП
• Посчитать скользящие метрики
• Отранжировать сотрудников по ЗП
См. книгу – Томас Кайт «Oracle для профессионалов», глава 12
12.02.2020
GlowByte Consulting
7
8. Аналитические vs Однострочные
Что общего: не меняют детализацию выборки, просто добавляют кней столбец.
В чем разница: однострочные считаются независимо для каждой
строки.
Аналитические учитывают порядок строк/разбивку по группам.
12.02.2020
GlowByte Consulting
8
9. Аналитические vs Агрегатные
Что общего: при вычислении обращаются к набору строк.В чем разница: агрегатные схлопывают группу строк в одну.
Аналитические не меняют количество строк выборки.
12.02.2020
GlowByte Consulting
9
10. Еще про синтаксис – параметры окна
PARTITION BY – разбивает строки на группы.ORDER BY – задает порядок строк.
SELECT
H.*,
-- нумерует начисления на счет
ROW_NUMBER() OVER (
PARTITION BY ACC_ID ORDER BY DT
) RN
FROM ACC_HIST H;
12.02.2020
GlowByte Consulting
ACC_ID VALUE DT
RN
1
100 01.01.2019 1
1
-30 02.01.2019 2
2
80 01.01.2019 1
2
-20 02.01.2019 2
2
-20 03.01.2019 3
3
70 01.01.2019 1
3
50 02.01.2019 2
3
-10 05.01.2019 3
3
-60 06.01.2019 4
10
11. Важно!
Аналитические функции вычисляютсятолько в SELECT или ORDER BY.
Хочешь поставить в WHERE? Делай подзапрос.
12.02.2020
GlowByte Consulting
11
12. Фильтруем по аналитической функции
Найдем последний платеж по каждому счету.Это популярное применение ROW_NUMBER.
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;
12.02.2020
ACC_ID VALUE DT
1
-30 02.01.2019
2
-20 03.01.2019
3
-60 06.01.2019
GlowByte Consulting
12
13. С ROW_NUMBER понятно. Какие еще бывают?
12.02.2020GlowByte Consulting
13
14. Ранжирование – RANK, DENSE_RANK
Ранжируют строки по полю, по которому сортируемSELECT
H.*,
RANK() OVER (ORDER BY VALUE) R,
DENSE_RANK() OVER (ORDER BY VALUE) DR
FROM ACC_HIST H;
12.02.2020
GlowByte Consulting
ACC_ID VALUE DT
R DR
3
-60 06.01.2019 1 1
1
-30 02.01.2019 2 2
2
-20 02.01.2019 3 3
2
-20 03.01.2019 3 3
3
-10 05.01.2019 5 4
3
50 02.01.2019 6 5
3
70 01.01.2019 7 6
2
80 01.01.2019 8 7
1
100 01.01.2019 9 8
14
15. RANK и DENSE_RANK – пример
Топ 3 снятий со счетаSELECT ACC_ID, VALUE, DT
FROM (
SELECT
ACC_ID, VALUE, DT,
RANK() OVER (ORDER BY VALUE) R
FROM ACC_HIST
) H
WHERE R <= 3;
ACC_ID VALUE DT
3
-60 06.01.2019
1
-30 02.01.2019
2
-20 02.01.2019
2
-20 03.01.2019
Т.е. учитываем, что некоторые снятия могут совпадать.
Еще можно вычислять места участников состязаний по их результатам.
12.02.2020
GlowByte Consulting
15
16. FIRST_VALUE – первое значение столбца
SELECTACC_ID, VALUE, DT,
FIRST_VALUE(VALUE) OVER (
PARTITION BY ACC_ID ORDER BY DT
) FIRST_VAL
FROM ACC_HIST;
12.02.2020
GlowByte Consulting
ACC_ID VALUE DT
FIRST_VAL
1
100 01.01.2019
100
1
-30 02.01.2019
100
2
80 01.01.2019
80
2
-20 02.01.2019
80
2
-20 03.01.2019
80
3
70 01.01.2019
70
3
50 02.01.2019
70
3
-10 05.01.2019
70
3
-60 06.01.2019
70
16
17. LAG и LEAD – идем в соседние строки
Найдем величину предыдущей и следующей операции по счету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;
12.02.2020
ACC_ID VALUE DT
PREV_VAL NEXT_VAL
1
100 01.01.2019NULL
-30
1
-30 02.01.2019
100NULL
2
80 01.01.2019NULL
-20
2
-20 02.01.2019
80
-20
2
-20 03.01.2019
-20NULL
3
70 01.01.2019NULL
50
3
50 02.01.2019
70
-10
3
-10 05.01.2019
50
-60
3
-60 06.01.2019
-10NULL
GlowByte Consulting
17
18. Аналоги агрегатных функций
12.02.2020GlowByte Consulting
18
19. Аналоги агрегатных функций
Пишем к агрегатной функции OVER (…) – получаем оконную.Только не меняем детализацию выборки.
SELECT
H.*,
-- суммарные начисления на счет
SUM(VALUE) OVER (
PARTITION BY ACC_ID
) SUM_V
FROM ACC_HIST H
ORDER BY ACC_ID, DT;
12.02.2020
ACC_ID VALUE DT
SUM_V
1
100 01.01.2019
70
1
-30 02.01.2019
70
2
80 01.01.2019
40
2
-20 02.01.2019
40
2
-20 03.01.2019
40
3
70 01.01.2019
50
3
50 02.01.2019
50
3
-10 05.01.2019
50
3
-60 06.01.2019
50
GlowByte Consulting
19
20. А если добавим ORDER BY?
Получим скользящую метрику:ACC_ID VALUE DT
SUM_V
1
100 01.01.2019
100
1
-30 02.01.2019
70
2
80 01.01.2019
80
2
-20 02.01.2019
60
2
-20 03.01.2019
40
3
70 01.01.2019
70
3
50 02.01.2019
120
3
-10 05.01.2019
110
3
-60 06.01.2019
90*
3
40 06.01.2019
90*
SELECT
H.*,
-- денег на счету после операции
SUM(VALUE) OVER (
PARTITION BY ACC_ID
ORDER BY DT
) SUM_V
FROM ACC_HIST H
ORDER BY ACC_ID, DT;
* У операций одинаковая дата. Очередность неизвестна.
Считается, что они произошли одновременно.
12.02.2020
GlowByte Consulting
20
21. Ограничиваем окно строк
12.02.2020GlowByte Consulting
21
22. Ограничиваем окно – ROWS
Сумма по 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;
ACC_ID VALUE DT
SUM_V
1
100 01.01.2019
100
1
-30 02.01.2019
70
2
80 01.01.2019
80
2
-20 02.01.2019
60
2
-20 03.01.2019
-40
3
70 01.01.2019
70
3
50 02.01.2019
120
3
-10 05.01.2019
40
3
-60 06.01.2019
-70*
3
40 06.01.2019
-20*
* Платежи были одновременные –
результат запроса может быть неоднозначен!
12.02.2020
GlowByte Consulting
22
23. ROWS – между чем и чем?
ROWS BETWEEN <a> AND <b><a> и <b> могут быть:
• CURRENT ROW –строка, в которой считаем функцию
• UNBOUNDED PRECEDING – первая строка группы
• UNBOUNDED FOLLOWING – последняя строка группы
• N PRECEDING – N предыдущих строк
• N FOLLOWING – N следующих строк
12.02.2020
GlowByte Consulting
23
24. Ограничиваем окно – RANGE
Сумма платежей за последние 2 дня: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;
12.02.2020
GlowByte Consulting
ACC_ID VALUE DT
SUM_V
1
100 01.01.2019
100
1
-30 02.01.2019
70
2
80 01.01.2019
80
2
-20 02.01.2019
60
2
-20 03.01.2019
40
3
70 01.01.2019
70
3
50 02.01.2019
120
3
-10 05.01.2019
-10
3
-60 06.01.2019
-30
3
40 06.01.2019
-30
24
25. RANGE – особенности
• Между чем и чем считает – синтаксис, как у ROWS.• В SQL Server не работает с N PRECEDING/FOLLOWING. В Oracle
работает.
• Тип данных поля, по которому сортируем – число или дата-время.
12.02.2020
GlowByte Consulting
25
26. ! Не всегда детерминирован результат
Запускаем запрос 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:
ACC_ID VALUE DT
RN
3
70 01.01.2019 1
3
50 02.01.2019 2
3
-10 05.01.2019 3
3
-60 06.01.2019 4
3
40 06.01.2019 5
12.02.2020
GlowByte Consulting
ACC_ID VALUE DT
RN
3
70 01.01.2019 1
3
50 02.01.2019 2
3
-10 05.01.2019 3
3
-60 06.01.2019 5
3
40 06.01.2019 4
26
27. Задачи на дом – см. на вики «Основы баз данных и SQL»
12.02.2020GlowByte Consulting
27
28. Вопросы, пожелания?
12.02.2020GlowByte Consulting
28