Оконные функции
Оконные функции предоставляют возможность выполнять вычисления по наборам строк, связанных с текущей строкой запроса. Вводную информацию по этой функциональной возможности см. в разделе Оконные функции, а подробную информацию о синтаксисе — в подразделе Вызовы оконных функций.
Встроенные оконные функции перечислены в Таблице 62. Обратите внимание, что эти функции должны вызываться с использованием синтаксиса оконных функций, то есть необходимо предложение OVER.
В дополнение к этим функциям в качестве оконной функции может использоваться любой встроенный или обычный пользовательский агрегат (т. е. не упорядочивающие или гипотезирующие агрегаты); список встроенных агрегатов см. в разделе Агрегатные функции. Агрегатные функции действуют как оконные только тогда, когда за вызовом следует предложение OVER; в противном случае они ведут себя как простые агрегаты и возвращают единственную строку для всего набора.
Таблица 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 ( число_блоков integer ) → integer | ||
| Возвращает целое в диапазоне от 1 до значения аргумента, разделяя партицию на максимально равные по размеру части. | ||
| lag ( значение anycompatible [, смещение integer [, значение_по_умолчанию anycompatible ]] ) → anycompatible | ||
| Возвращает значение, вычисленное для строки, отстоящей на смещение строк от текущей к началу партиции; если такой строки нет, вместо этого возвращает значение_по_умолчанию (которое должно быть совместимого со значением типа). Как смещение, так и значение_по_умолчанию вычисляются относительно текущей строки. Если они опускаются, по умолчанию смещение равно 1, а значение_по_умолчанию — NULL. | ||
| lead ( значение anycompatible [, смещение integer [, значение_по_умолчанию anycompatible ]] ) → anycompatible | ||
| Возвращает значение, вычисленное для строки, отстоящей на смещение строк от текущей к концу партиции; если такой строки нет, вместо этого возвращает значение_по_умолчанию (которое должно быть совместимого со значением типа). Как смещение, так и значение_по_умолчанию вычисляются относительно текущей строки. Если они опускаются, по умолчанию смещение равно 1, а значение_по_умолчанию — NULL. | ||
| first_value ( значение anyelement ) → anyelement | ||
| Возвращает значение, вычисленное для первой строки в рамке окна. | ||
| last_value ( значение anyelement ) → anyelement | ||
| Возвращает значение, вычисленное для последней строки в рамке окна. | ||
| nth_value ( значение anyelement, n integer ) → anyelement | ||
| Возвращает значение, вычисленное для n-й строки в рамке окна (считая с 1); возвращает NULL, если такой строки нет. | ||
Все функции, перечисленные в Таблице 62, зависят от порядка сортировки, заданного в предложении ORDER BY соответствующего определения окна. Строки, не различающиеся при рассмотрении только столбцов ORDER BY, называются одноранговыми. Четыре функции ранжирования (включая cume_dist) определены так, чтобы давать одинаковый ответ для всех строк одноранговой группы.
Обратите внимание, что функции first_value, last_value и nth_value учитывают только строки в «рамке окна», которая по умолчанию содержит строки от начала партиции до последней строки, одноранговой с текущей. Скорее всего, у last_value, а иногда и nth_value, это приведет к бесполезным результатам. Рамку можно переопределить, добавив подходящую спецификацию рамки (RANGE, ROWS или GROUPS) в предложение OVER. Дополнительную информацию о спецификациях рамок см. в подразделе Вызовы оконных функций.
Когда в качестве оконной функции используется агрегатная, она агрегирует по строкам в рамке окна текущей строки. Агрегат, используемый с ORDER BY и определением рамки окна по умолчанию, создает тип поведения «суммирование с нарастающим итогом», что может как соответствовать, так и не соответствовать желаемому результату. Чтобы получить агрегацию по всей партиции, опустите ORDER BY или воспользуйтесь ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Для получения других результатов можно использовать другие спецификации рамок.
Примечание
В стандарте SQL определены параметры RESPECT NULLS или IGNORE NULLS для функций lead, lag, first_value, last_value и nth_value. В QHB это не реализовано: поведение этих функций всегда соответствует описанному в стандарте как поведение по умолчанию, а именно, с параметром RESPECT NULLS. Аналогичным образом для функции nth_value не реализованы стандартные параметры FROM FIRST или FROM LAST: поддерживается только поведение по умолчанию — с параметром FROM FIRST. (Результата как у параметра FROM LAST можно добиться, изменив порядок ORDER BY на обратный.)