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

Оконные функции выполняют вычисления над строками результата запроса. Они выполняются после выражения 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. Агрегатная функция вычисляется для каждой строки над строками в пределах фрейма текущей строки. Обратите внимание, что упорядочивание при агрегации не поддерживается.

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

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() требует указания упорядочения окна. Фрейм окна не должен быть указан.