9.22. Оконные функции

Оконные функции дают возможность выполнять вычисления с набором строк, каким-либо образом связанным с текущей строкой запроса. Вводную информацию об этом можно получить в Разделе 3.5, а подробнее узнать о синтаксисе можно в Подразделе 4.2.8.

Встроенные оконные функции перечислены в Таблице 9.62. Заметьте, что эти функции должны вызываться именно как оконные, т. е. при вызове необходимо использовать предложение OVER.

В дополнение к этим функциям в качестве оконных можно использовать любые встроенные или обычные пользовательские агрегатные функции (но не сортирующие и не гипотезирующие); список встроенных агрегатных функций приведён в Разделе 9.21. Агрегатные функции работают как оконные, только когда за их вызовом следует предложение OVER; в противном случае они работают как обычные функции и выдают для всего набора единственную строку.

Таблица 9.62. Оконные функции общего назначения

Функция

Описание

row_number () → bigint

Возвращает номер текущей строки в её разделе, начиная с 1.

rank () → bigint

Возвращает ранг текущей строки с пропусками; то же, что и row_number для первой родственной ей строки.

dense_rank () → bigint

Возвращает ранг текущей строки без пропусков; по сути эта функция считает группы родственных строк.

percent_rank () → double precision

Вычисляет относительный ранг текущей строки, то есть (rank - 1) / (общее число строк раздела - 1). Таким образом, результат лежит в интервале от 0 до 1, включительно.

cume_dist () → double precision

Возвращает кумулятивное распределение, то есть (число строк раздела, предшествующих или родственных текущей строке) / (общее число строк раздела). Таким образом, результат лежит в интервале от 1/N до 1.

ntile ( num_buckets integer ) → integer

Возвращает целое от 1 до значения аргумента для разбиения раздела на части максимально близких размеров.

lag ( value anycompatible [, offset integer [, default anycompatible]] ) → anycompatible

Возвращает значение value, вычисленное для строки, сдвинутой на offset строк от текущей к началу раздела; если такой строки нет, возвращается значение default (оно должно быть совместимого с value типа). Оба аргумента, offset и default, вычисляются для текущей строки. Если они не указываются, offset считается равным 1, а defaultNULL.

lead ( value anycompatible [, offset integer [, default anycompatible]] ) → anycompatible

Возвращает значение value, вычисленное для строки, сдвинутой на offset строк от текущей к концу раздела; если такой строки нет, возвращается значение default (оно должно быть совместимого с value типа). Оба аргумента, offset и default, вычисляются для текущей строки. Если они не указываются, offset считается равным 1, а defaultNULL.

first_value ( value anyelement ) → anyelement

Возвращает значение (value), вычисленное для первой строки в рамке окна.

last_value ( value anyelement ) → anyelement

Возвращает значение (value), вычисленное для последней строки в рамке окна.

nth_value ( value anyelement, n integer ) → anyelement

Возвращает значение (value), вычисленное в n-ой строке в рамке окна (считая с 1), или NULL, если такой строки нет.


Результат всех функций, перечисленных в Таблице 9.62, зависит от порядка сортировки, заданного предложением ORDER BY в определении соответствующего окна. Строки, которые являются одинаковыми при рассмотрении только столбцов ORDER BY, считаются родственными. Четыре функции, вычисляющие ранг (включая cume_dist), реализованы так, что их результат будет одинаковым для всех родственных строк.

Заметьте, что функции first_value, last_value и nth_value рассматривают только строки в «рамке окна», которая по умолчанию содержит строки от начала раздела до последней родственной строки для текущей. Поэтому результаты last_value и иногда nth_value могут быть не очень полезны. В таких случаях можно переопределить рамку, добавив в предложение OVER подходящее указание рамки (RANGE, ROWS или GROUPS). Подробнее эти указания описаны в Подразделе 4.2.8.

Когда в качестве оконной функции используется агрегатная, она обрабатывает строки в рамке текущей строки. Агрегатная функция с ORDER BY и определением рамки окна по умолчанию будет вычисляться как «бегущая сумма», что может не соответствовать желаемому результату. Чтобы агрегатная функция работала со всем разделом, следует опустить ORDER BY или использовать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Используя другие указания в определении рамки, можно получить и другие эффекты.

Примечание

В стандарте SQL определены параметры RESPECT NULLS или IGNORE NULLS для функций lead, lag, first_value, last_value и nth_value. В PostgreSQL такие параметры не реализованы: эти функции ведут себя так, как положено в стандарте по умолчанию (или с подразумеваемым параметром RESPECT NULLS). Также функция nth_value не поддерживает предусмотренные стандартом параметры FROM FIRST и FROM LAST: реализовано только поведение по умолчанию (с подразумеваемым параметром FROM FIRST). (Получить эффект параметра FROM LAST можно, изменив порядок ORDER BY на обратный.)