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

Оконные функции выполняют вычисления по строкам результата запроса. Они выполняются после предложения HAVING, но до предложения ORDER BY. Вызов оконной функции требует специального синтаксиса с использованием предложения OVER для задания окна. Например, следующий запрос ранжирует заказы каждого клерка по цене:

SELECT orderkey, clerk, totalprice,
       rank() OVER (PARTITION BY clerk
                    ORDER BY totalprice DESC) AS rnk
FROM orders
ORDER BY clerk, rnk

Окно можно задать двумя способами (см. Предложение WINDOW):

Ссылкой на именованную спецификацию окна, определенную в предложении WINDOW,
Встроенной спецификацией окна, которая позволяет определить компоненты окна, а также ссылаться на компоненты окна, заранее определенные в предложении WINDOW.

Агрегатные функции#

Все Агрегатные функции можно использовать как оконные функции, добавив предложение OVER. Агрегатная функция вычисляется для каждой строки по строкам в пределах оконного фрейма текущей строки. Обратите внимание, что ordering during aggregation не поддерживается.

Например, следующий запрос формирует скользящую сумму цен заказов по дням для каждого клерка:

SELECT clerk, orderdate, orderkey, totalprice,
       sum(totalprice) OVER (PARTITION BY clerk
                             ORDER BY orderdate) AS rolling_sum
FROM orders
ORDER BY clerk, orderdate, orderkey

Функции ранжирования#

cume_dist() → bigint#: Возвращает кумулятивное распределение значения в группе значений. Результат — это количество строк, предшествующих текущей строке или равных ей, в порядке окна раздела окна, деленное на общее число строк в разделе окна. Таким образом, одинаковые значения в порядке будут иметь одно и то же значение распределения. Оконный фрейм не должен быть задан.

dense_rank() → bigint#: Возвращает ранг значения в группе значений. Это похоже на rank(), за исключением того, что равные значения не создают пропусков в последовательности. Оконный фрейм не должен быть задан.

ntile(n) → bigint#

Разделяет строки каждого раздела окна на n корзин с номерами от 1 до максимум n. Значения корзин отличаются не более чем на 1. Если количество строк в разделе не делится нацело на количество корзин, остаток распределяется по одному значению на корзину, начиная с первой корзины.

Например, для 6 строк и 4 корзин значения корзин будут следующими: 1 1 2 2 3 4

Для функции ntile() оконный фрейм не должен быть задан.

percent_rank() → double#: Возвращает процентный ранг значения в группе значений. Результат вычисляется как (r - 1) / (n - 1), где r — rank() строки, а n — общее количество строк в разделе окна. Оконный фрейм не должен быть задан.

rank() → bigint#: Возвращает ранг значения в группе значений. Ранг — это единица плюс количество строк, предшествующих текущей строке и не равных ей. Таким образом, равные значения в порядке создают пропуски в последовательности. Ранжирование выполняется для каждого раздела окна. Оконный фрейм не должен быть задан.

row_number() → bigint#: Возвращает уникальный последовательный номер для каждой строки, начиная с единицы, в соответствии с порядком строк в пределах раздела окна. Оконный фрейм не должен быть задан.

Функции значений#

По умолчанию значения null учитываются. Если указан IGNORE NULLS, все строки, где x равно null, исключаются из вычисления. Если указан IGNORE NULLS, и x равно null для всех строк, возвращается default_value, а если оно не задано, возвращается null.

first_value(x) → [same as input]#: Возвращает первое значение окна.

last_value(x) → [same as input]#: Возвращает последнее значение окна.

nth_value(x, offset) → [same as input]#: Возвращает значение по указанному смещению от начала окна. Смещения начинаются с 1. Смещение может быть любым скалярным выражением. Если смещение равно null или больше количества значений в окне, возвращается null. Ошибкой является смещение, равное нулю или отрицательное.

lead(x[, offset[, default_value]]) → [same as input]#: Возвращает значение на offset строк после текущей строки в разделе окна. Смещения начинаются с 0, что соответствует текущей строке. Смещение может быть любым скалярным выражением. Значение offset по умолчанию — 1. Если смещение равно null, возникает ошибка. Если смещение указывает на строку вне раздела, возвращается default_value, а если оно не задано, возвращается null. Функция lead() требует, чтобы был задан порядок окна. Оконный фрейм не должен быть задан.

lag(x[, offset[, default_value]]) → [same as input]#: Возвращает значение на offset строк до текущей строки в разделе окна. Смещения начинаются с 0, что соответствует текущей строке. Смещение может быть любым скалярным выражением. Значение offset по умолчанию — 1. Если смещение равно null, возникает ошибка. Если смещение указывает на строку вне раздела, возвращается default_value, а если оно не задано, возвращается null. Функция lag() требует, чтобы был задан порядок окна. Оконный фрейм не должен быть задан.