Основы баз данных и 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.2020
GlowByte 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 – первое значение столбца

SELECT
ACC_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.2020
GlowByte 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.2020
GlowByte 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.2020
GlowByte Consulting
27

28. Вопросы, пожелания?

12.02.2020
GlowByte Consulting
28