Предложение ORDER BY
может быть использовано с секционированием или без него.Предложение ORDER BY
внутри OVER
задаёт порядок, в котором оконная функция будет обрабатывать строки.Этот порядок не обязан совпадать с порядком вывода строк.
Для стандартных агрегатных функций, предложение ORDER BY
внутри предложения OVER
заставляет возвращать частичные результаты агрегации по мере обработки записей.
Example 1. Сортировка в OVER
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 состоит из всех строк от начала раздела до текущей строки и строк, равных текущей по значению выражения ORDER BY
(т.е.RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
). Без ORDER BY
рамка по умолчанию состоит из всех строк раздела.Подробней о рамке окна (кадрах окна) будет рассказано далее.
Вы можете использовать несколько окон с различными сортировками, и дополнять предложение ORDER BY
опциями ASC
/DESC
и NULLS {FIRST | LAST}
.
С секциями предложение ORDER BY
работает таким же образом, но на границе каждой секции агрегаты сбрасываются.
Все агрегатные функции могут использовать предложение ORDER BY
, за исключением LIST()
.
Следующий пример показывает сумму кредита, накопленную сумму выплат и остаток по выплатам.
Example 2. Использование OVER(ORDER BY …)
для кумулятивных сумм
SELECT
payments.id AS id,
payments.bydate AS bydate,
credit.amount AS credit_amount,
payments.amount AS pay,
SUM(payments.amount) OVER(ORDER BY payments.bydate) AS s_amount,
SUM(payments.amount) OVER(ORDER BY payments.bydate,
payments.id) AS s_amount2,
credit.amount - SUM(payments.amount) OVER(ORDER BY payments.bydate,
payments.id) AS balance
FROM credit
JOIN payments ON payments.credit_id = credit.id
WHERE credit.id = 1
ORDER BY payments.bydate
Результат
ID BYDATE CREDIT_AMOUNT PAY S_AMOUNT S_AMOUNT2 BALANCE
-- ---------- ------------- ------ -------- --------- ----------
1 15.01.2015 1000000 100000 100000 100000 900000
2 15.02.2015 1000000 150000 250000 250000 750000
3 15.03.2015 1000000 130000 400000 380000 620000
4 15.03.2015 1000000 20000 400000 400000 600000
5 15.04.2015 1000000 200000 600000 600000 400000
6 15.05.2015 1000000 150000 750000 750000 250000
7 15.06.2015 1000000 150000 1000000 900000 100000
8 15.06.2015 1000000 100000 1000000 1000000 0