Оконные (аналитические) функции
Согласно SQL спецификации оконные функции (также известные как аналитические функции) являются своего рода агрегатными функциями, не уменьшающими степень детализации. При этом агрегированные данные выводятся вместе с неагрегированными.
Синтаксически вызов оконной функции это указание её имени, за которым всегда следует
ключевое слово OVER() с возможными аргументами внутри скобок. В этом и заключается её
синтаксическое отличие от обычной функции или агрегатной функции. Оконные функции могут
находиться только в списке SELECT и предложении ORDER BY.
Предложение OVER может содержать указания выполнить действия с разбивкой по группам
(«секционирование») и сортировку.
Начнем с простого примера, который показывает как меняются результаты в зависимости от указаний внутри OVER.
SELECT A, B, C,
SUM(C) OVER(),
SUM(C) OVER(ORDER BY A, B),
SUM(C) OVER(PARTITION BY A),
SUM(C) OVER(PARTITION BY A ORDER BY B)
FROM T
Потенциальные результаты могут быть следующими.
A |
B |
C |
SUM |
SUM1 |
SUM2 |
SUM3 |
|---|---|---|---|---|---|---|
1 |
1 |
30 |
141 |
30 |
60 |
30 |
1 |
2 |
20 |
141 |
50 |
60 |
50 |
1 |
3 |
10 |
141 |
60 |
60 |
60 |
2 |
1 |
25 |
141 |
85 |
40 |
25 |
2 |
2 |
15 |
141 |
100 |
40 |
40 |
3 |
1 |
41 |
141 |
141 |
41 |
41 |
Столбцы A, B, C содержат некие данные. В запросе использована оконная функция SUM
по столбцу C, но с разными указаниям к выполнению суммирования. Посмотрим на них более внимательно.
SUM(C) OVER() не содержит никаких указаний и для каждой строки выдает один и тот же результат
(столбец SUM в результате)):
сумму всех значений столбца C.
Такое же значение мы бы получили при простом использовании функции SUM как аггрегатной и
без предложения GROUP BY.
Но заметьте что здесь количество строк не уменьшается и не равно количеству групп.
Значение выдается для каждом строки.
SUM(C) OVER(ORDER BY A, B) (столбец SUM1) содержит указание к порядку использования значений C в соответствие со
значениями столбцов A и B. В таком случае для каждой строки результат SUM(C) будет выдавать сумму значений
столбца C с первого до текущего по указанному порядку суммирования. Последнее значение как и следует ожидать
будет равно сумме всех значений C.
SUM(C) OVER(PARTITION BY A) (столбец SUM2) снова не содержит указания к порядку выполнения и там мы видим
снова одинаковые суммы, но разбитые на группы. Это потому что есть указания к сегментированию,
разделению общего набора данных на части. Суммируются значения столбца C всех строк,
в которых значения поля A равно значению поля A текущей строки.
SUM(C) OVER(PARTITION BY A ORDER BY B) (столбец SUM3) содержит указания и к сегментированию, и к порядку.
В таком случае сумма вычисляется по значениям, входящим в указанную группу начиная с первого до текущего, в указанном порядке.
Суммы, вычисленные по порядку еще называют сумма, вычисленная нарастающим итогом.
Оконные функции могут быть использованы в выражениях списка выбора.
Допустим, у нас есть таблица EMPLOYEE со столбцами ID, NAME и SALARY. Нам необходимо
показать для каждого сотрудника, соответствующую ему заработную плату и процент от фонда заработной платы.
Простым запросом это решается следующим образом:
SELECT ID, DEPARTMENT, SALARY,
100 * SALARY / (SELECT SUM(SALARY) FROM EMPLOYEE) PERCENTAGE
FROM EMPLOYEE
ORDER BY ID
Результат:
id |
department |
salary |
percentage |
|---|---|---|---|
1 |
R&D |
10.00 |
20.40 |
2 |
SALES |
12.00 |
24.48 |
3 |
SALES |
8.00 |
16.32 |
4 |
R&D |
9.00 |
18.36 |
5 |
R&D |
10.00 |
20.40 |
Запрос повторяется и может работать довольно долго, особенно если EMPLOYEE является
сложным представлением.
Этот запрос может быть переписан в более быстрой и элегантной форме с использованием оконных функций:
SELECT ID, DEPARTMENT, SALARY,
100 * SALARY / SUM(SALARY) OVER () PERCENTAGE
FROM EMPLOYEE
ORDER BY ID
Здесь SUM(SALARY) OVER () вычисляет сумму всех зарплат из запроса (таблицы сотрудников).
Секционирование
Как и для агрегатных функций, которые могут работать отдельно или по отношению к группе, оконные функции тоже могут работать для групп, которые называются «секциями» (partition) или разделами.
Синтаксис:
<window function>(...) OVER (PARTITION BY <expr> [, <expr> ...])
Для каждой строки, оконная функция обсчитывает только строки, которые попадают в то же самую секцию, что и текущая строка.
Агрегирование над группой может давать более одной строки, таким образом, к результирующему набору, созданному секционированием, присоединяются результаты из основного запроса, используя тот же список выражений, что и для секции. Продолжая пример с сотрудниками, вместо того чтобы считать процент зарплаты каждого сотрудника от суммарной зарплаты сотрудников, посчитаем процент от суммарной зарплаты сотрудников того же отдела:
SELECT ID, DEPARTMENT, SALARY,
100 * SALARY / SUM(SALARY) OVER (PARTITION BY DEPARTMENT) PERCENTAGE
FROM EMPLOYEE
ORDER BY ID
Результат:
id |
department |
salary |
percentage |
|---|---|---|---|
1 |
R&D |
10.00 |
34.48 |
2 |
SALES |
12.00 |
60.00 |
3 |
SALES |
8.00 |
40.00 |
4 |
R&D |
9.00 |
31.03 |
5 |
R&D |
10.00 |
34.48 |
Сортировка
Предложение ORDER BY может быть использовано с секционированием или без него.
Предложение ORDER BY внутри OVER задаёт порядок, в котором оконная функция будет
обрабатывать строки. Этот порядок не обязан совпадать с порядком вывода строк.
Есть ещё одно важное понятие, связанное с оконными функциями: для каждой строки
существует набор строк в её разделе, называемый рамкой окна (кадры окна). По умолчанию, с
указанием ORDER BY рамка состоит из всех строк от начала раздела до текущей строки и строк,
равных текущей по значению выражения ORDER BY. Без ORDER BY рамка по умолчанию
состоит из всех строк раздела.
Таким образом, для стандартных агрегатных функций, предложение ORDER BY заставляет
возвращать частичные результаты агрегации по мере обработки записей.
Пример:
SELECT ID, SALARY,
SUM(SALARY) OVER (ORDER BY SALARY) AS CUMUL_SALARY
FROM EMPLOYEE
ORDER BY SALARY;
Результат:
ID |
SALARY |
CUMUL_SALARY |
|---|---|---|
3 |
8.00 |
8.00 |
4 |
9.00 |
17.00 |
1 |
10.00 |
37.00 |
5 |
10.00 |
37.00 |
2 |
12.00 |
49.00 |
В этом случае CUMUL_SALARY возвращает частичную/накопительную агрегацию (функции SUM).
Может показаться странным, что значение 37,00 повторяется для идентификаторов 1 и 5, но так
и должно быть. Сортировка (ORDER BY) ключей группирует их вместе, и агрегат вычисляется
единожды (но суммируя сразу два значения 10,00). Чтобы избежать этого, вы можете добавить
поле ID в конце предложения ORDER BY.
Вы можете использовать несколько окон с различными сортировками, и дополнять
предложение ORDER BY опциями ASC/DESC и NULLS FIRST/LAST.
С секциями предложение ORDER BY работает таким же образом, но на границе каждой секции
агрегаты сбрасываются.
Все агрегатные функции могут использовать предложение ORDER BY, за исключением LIST().