Сортировка

Предложение 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

Рамка окна

Набор строк внутри секции, которым оперирует оконная функция, называется рамкой окна (кадры окна).Рамка окна определяет, какие строки следует учитывать для текущей строки при оценке оконной функции.

Рамка окна состоит из трёх частей: единица (unit), начальная граница и конечная граница.В качестве единицы может быть использовано ключевые слова RANGE или ROWS, которые определяют, каким образом будут работать границы окна.Границы окна определяются следующими выражениями:

<expr> PRECEDING
<expr> FOLLOWING
CURRENT ROW

Предложения ROWS и RANGE требуют, чтобы было указано предложение ORDER BY.Если предложение ORDER BY отсутствует, то для агрегатных функций рамка окна состоит из всех строк в разбиении.Если задано предложение ORDER BY, то по умолчанию рамка окна состоит из всех строк, от начала разбиения до текущей строки, плюс любые следующие строки, которые равны текущей строке в соответствии с предложением ORDER BY,т.е. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Предложение ROWS ограничивает строки внутри секции путем указания фиксированного числа строк, предшествующих или следующих после текущей строки.В качестве альтернативы предложение RANGE логически ограничивает строки внутри секции путем указания диапазона значений в отношении к значению текущей строки.Предшествующие и последующие строки определяются на основании порядка, заданного в предложении ORDER BY.

Если рамка окна задаётся с помощью предложения RANGE, то предложение ORDER BY может содержать только одно выражение и выражение должно быть числового типа, DATE, TIME или TIMESTAMP.Для <expr> PRECEDING выражение expr вычитается из выражения в ORDER BY, а для <expr> FOLLOWING — добавляется.Для CURRENT ROW выражение в ORDER BY используется как есть.

Затем все строки (внутри секции) между границам считаются частью результирующей рамки окна.
Если рамка окна задаётся с помощью предложения ROWS, то на предложение ORDER BY не накладывается ограничений на количество и типы выражений.В этом случае фраза <expr> PRECEDING указывает количество строк предшествующее текущей строке, соответственно фраза <expr> FOLLOWING указывает количество строк после текущей строки.

UNBOUNDED PRECEDING и UNBOUNDED FOLLOWING работают одинаково для предложений ROWS и RANGE.Фраза UNBOUNDED PRECEDING указывает, что окно начинается с первой строки секции. UNBOUNDED PRECEDING может быть указано только как начальная точка окна.Фраза UNBOUNDED FOLLOWING указывает, что окно заканчивается последней строкой секции. UNBOUNDED FOLLOWING может быть указано только как конечная точка окна.

Фраза CURRENT ROW указывает, что окно начинается или заканчивается на текущей строке при использовании совместно с предложением ROWS,или что окно заканчивается на текущем значении при использовании с предложением RANGE.CURRENT ROW может быть задана и как начальная, и как конечная точка.

Предложение BETWEEN используется совместно с ключевым словом ROWS или RANGE для указания нижней (начальной) или верхней (конечной) граничной точки окна.Верхняя граница не может быть меньше нижней границы.

Note	Если указана только начальная точка окна, то конечной точкой окна считается `CURRENT ROW`.Например, если указано `ROWS 1 PRECEDING`, то это аналогично указанию `ROWS BETWEEN 1 PRECEDING AND CURRENT ROW`.

Некоторые оконные функции игнорируют выражение рамки:

ROW_NUMBER, LAG и LEAD всегда работают как ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
DENSE_RANK, RANK, PERCENT_RANK и CUME_DIST работают как RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
FIRST_VALUE, LAST_VALUE и NTH_VALUE работают на рамке, но RANGE работает идентично ROWS.

Таким образом, предложения ROWS и RANGE позволяют довольно гибко настроить размер плавающего окна.Чаще всего встречаются следующие варианты:

Нижняя граница фиксирована (совпадает с первой строкой упорядоченной группы), а верхняя граница ползёт (совпадает с текущей строкой упорядоченной группы). В этом случае получаем нарастающий итог (кумулятивный агрегат). В этом случае размер окна меняется (расширяется в одну сторону) и само окно движется за счёт расширения. Возможна и обратная ситуация, когда нижняя граница ползёт, а верхняя зафиксирована. В этом случае окно будет сужаться.
Если верхняя и нижняя границы фиксированы относительно текущей строки, например 1 строка до текущей и 2 после текущей, то получаем скользящий агрегат. В этом случае размер окна фиксирован, а само окно скользит.