Оконные функции SQL

1.

Курс «Хранилища данных»
Тема: Оконные функции SQL
Барабанщиков
Игорь Витальевич

2.

Оконные функции
• Ранжирующие функции полезны при сравнении
элементов для фиксированного окна времени.
• Оконные функции позволяют вычислить
обобщающие значения для окна, которое
изменяется по мере продвижения данных.
• Окно обобщения можно определить, указав:
- набор строк «от текущей строки до конца
раздела»
- временной интервал «за 30 дней,
предшествующих дате транзакции»
- диапазон значений
2

3.

Окно
• Оконная функция применяется к набору строк.
• Окно – стандартный термин SQL, служащий для
описания контекста, в котором работает
функция.
• Для указания окна используется предложение
OVER.
• Предложение OVER определяет окно или
точный набор строк по отношению к текущей
строке, указание об упорядоченности (если
нужно), другие элементы.
• Упорядочение необходимо для ранжирования
строк.
3

4.

Скользящее окно
• Скользящее окно – это набор данных, границы
которого изменяются с течением времени.
4

5.

Границы скользящего окна
• Задаются фразой
ROWS BETWEEN нижняя гр AND верхняя гр
• Границы окна:
- PRECEDING: предшествующий (нижняя
граница)
- FOLLOWING: следующий (верхняя граница)
• Значения границ окна:
- UNBOUNDED: не ограничено
- CURRENT ROW: текущая строка
- ЧИСЛО: конкретное значение
5

6.

Пример
Month
SELECT month, SUM(sales)
FROM orders
WHERE year = 2015
AND region_id = 5
GROUP BY month
ORDER BY month
SUM(sales)
1
100
2
50
3
100
4
100
5
200
6
200
7
100
8
100
9
250
10
100
11
100
12
1006

7.

Неограниченное окно
SELECT month, SUM(sales) monthly_sales,
SUM(SUM(sales)) OVER (ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING
and UNBOUNDED FOLLOWING) total_sales
FROM orders
WHERE year = 2015
AND region_id = 5
GROUP BY month
ORDER BY month
7

8.

Результат запроса
Month
Monthly_Sales
Total_Sales
1
100
1500
2
50
1500
3
100
1500
4
100
1500
5
200
1500
6
200
1500
7
100
1500
8
100
1500
9
250
1500
10
100
1500
11
100
1500
12
100
1500
8

9.

Нарастающий итог
SELECT month, SUM(sales) monthly_sales,
SUM(SUM(sales)) OVER (ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING
and CURRENT ROW) total_sales
FROM orders
WHERE year = 2015
AND region_id = 5
GROUP BY month
ORDER BY month
9

10.

Результат запроса
Month
Monthly_Sales
Total_Sales
1
100
100
2
50
150
3
100
250
4
100
350
5
200
550
6
200
750
7
100
850
8
100
950
9
250
1200
10
100
1300
11
100
1400
12
100
1500
10

11.

Скользящее окно
Средний объем продаж за текущий,
предыдущий и следующий месяцы:
SELECT month, SUM(sales) monthly_sales,
AVG(SUM(sales)) OVER (ORDER BY month
ROWS BETWEEN 1 PRECEDING
and 1 FOLLOWING) rolling_avg
FROM orders
WHERE year = 2015
AND region_id = 5
GROUP BY month
ORDER BY month
11

12.

Результат запроса
Month
Monthly_Sales
Rolling_Avg
1
100
75
2
50
83.33
3
100
83.33
4
100
133.33
5
200
166.66
6
200
166.66
7
100
133.33
8
100
150
9
250
150
10
100
150
11
100
100
12
100
100
12

13.

Функции LAG / LEAD
• Позволяют ссылаться на строки по их
позиции относительно текущей строки,
подобно инструкциям PRECEDING и
FOLLOWING.
• Удобны при сравнении одной строки
результирующего множества с другой
строкой того же результирующего множества.
• Пример: Вычислить общий объем продаж по
месяцам для региона Урал, включив процент
изменения по отношению к предыдущему
месяцу.
13

14.

Пример использования LAG
SELECT month, SUM(sales) monthly_sales,
LAG(SUM(sales)), 1) OVER (ORDER BY month)
prev_month_sales
FROM orders
WHERE year = 2015
AND region_id = 5
GROUP BY month
ORDER BY month
14

15.

Результат запроса c LAG
Month
Monthly_Sales
Prev_Month_Sales
1
100
2
50
100
3
100
50
4
100
100
5
200
100
6
200
200
7
100
200
8
100
100
9
250
100
10
100
250
11
100
100
12
100
100
15

16.

Функции для создания отчетов
• Подобно оконным функциям, позволяют
выполнить различные обобщающие операции
(MIN, MAX, SUM, COUNT, AVG) над
результирующим множеством.
• В отличие от оконных функций, не могут
указывать локальные окна и поэтому выводят
результат для всего раздела.
• Все то, что можно сделать с помощью функции
для создания отчета, можно сделать и с помощью
оконной функции, только первый вариант обычно
будет более эффективным.
16

17.

Пример
Month
SELECT month,
SUM(sales) monthly_sales,
SUM(SUM(sales)) OVER()
yearly_sales
FROM orders
WHERE year = 2015
AND region_id = 5
GROUP BY month
ORDER BY month
Monthly_
Sales
Yearly_
Sales
1
100
1500
2
100
1500
3
200
1500
4
100
1500
5
100
1500
6
150
1500
7
150
1500
8
100
1500
9
100
1500
10
100
1500
11
100
1500
12
200
1500
17

18.

Разбиение отчетов
• Как и ранжирующие отчеты, функции для
создания отчетов могут содержать инструкцию
PARTITION BY для разбиения результирующего
множества на несколько частей.
• Это позволяет выполнить несколько
суммирований для различных подмножеств
результирующего множества.
• Пример: Вывести общий объем продаж для
каждого продавца по регионам, а также общий
объем продаж региона (для сравнения).
18

19.

Запрос
SELECT region_id, salesman_id
SUM(sales) AS sm_sales,
SUM(SUM(sales)) OVER (PARTITION BY
region_id) AS region_sales
FROM orders
WHERE year = 2016
GROUP BY region_id, salesman_id
ORDER BY region_id, salesman_id
19

20.

Результат запроса
Region_ID
SalesMan_ID
SM_sales
Region_Sales
1
1
100
600
1
2
200
600
1
3
300
600
2
4
150
750
2
5
250
750
2
6
200
750
2
7
150
750
3
8
50
350
3
9
75
350
3
10
125
350
3
11
100
350
20

21.

Заключение
• Для решения задач
бизнес-аналитики
приходится писать
сложные SQL-запросы.
• Оконные функции
позволяют вычислить
обобщающие
значения для окна,
которое изменяется по
мере продвижения
данных.
21