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

9.22. Оконные функции
9.22. Оконные функции
Назад НаверхГлава 9. Функции и операторыНачало Далее

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

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

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

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

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

Функция

Описание

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

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

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

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

first_value ( value anyelement ) → anyelement

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

last_value ( value anyelement ) → anyelement

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

nth_value ( value anyelement, n integer ) → anyelement

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


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

Обратите внимание, что функции first_value, last_value и nth_value учитывают только строки внутри “window frame”, который по умолчанию содержит строки от начала секции до последнего ряда текущей строки. Это может дать неинформативные результаты для функции last_value и иногда также для функции nth_value. Вы можете переопределить frame, добавив подходящую спецификацию frame (RANGE, ROWS или GROUPS) к выражению OVER. См. Раздел 4.2.8 для получения дополнительной информации о спецификациях frame.

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

Примечание

Стандарт SQL определяет опцию RESPECT NULLS или IGNORE NULLS для функций lead, lag, first_value, last_value и nth_value. В Tantor SE-1C эта опция не реализована: поведение всегда соответствует стандартному значению по умолчанию, а именно RESPECT NULLS. Аналогично, опция FROM FIRST или FROM LAST для функции nth_value также не реализована: поддерживается только поведение по умолчанию FROM FIRST. (Вы можете получить результат FROM LAST, поменяв порядок сортировки ORDER BY).

Назад Наверх Далее
9.21. Агрегатные функции Начало 9.23. Подзапросы