Оконные функции
Оконные функции предоставляют возможность выполнять вычисления по наборам строк, связанных с текущей строкой запроса. Вводную информацию по этой функциональной возможности см. в разделе Оконные функции, а подробную информацию о синтаксисе — в подразделе Вызовы оконных функций.
В этом разделе перечислены встроенные оконные функции перечислены. Обратите внимание, что эти функции должны вызываться с использованием синтаксиса оконных функций, то есть необходимо предложение OVER.
В дополнение к этим функциям в качестве оконной функции может использоваться любой встроенный или обычный пользовательский агрегат (т. е. не упорядочивающие или гипотезирующие агрегаты); список встроенных агрегатов см. в разделе Агрегатные функции. Агрегатные функции действуют как оконные только тогда, когда за вызовом следует предложение OVER; в противном случае они ведут себя как простые агрегаты и возвращают единственную строку для всего набора.
row_number
row_number () → bigint
Возвращает номер текущей строки в ее партиции, считая с 1.
rank
rank () → bigint
Возвращает ранг текущей строки с пропусками, то есть row_number первой строки в одноранговой с ней группе.
dense_rank
dense_rank () → bigint
Возвращает ранг текущей строки без пропусков; в сущности, эта функция считает одноранговые группы.
percent_rank
percent_rank () → double precision
Возвращает относительный ранг текущей строки, то есть (rank - 1) / (общее число строк партиции - 1). Таким образом, значение лежит в диапазоне от 0 до 1, включая границы.
cume_dist
cume_dist () → double precision
Возвращает кумулятивное распределение, то есть (число строк партиции, предшествующих или одноранговых с текущей строкой) / (общее число строк партиции). Таким образом, значение лежит в диапазоне от 1/N до 1.
ntile
ntile ( число_блоков integer ) → integer
Возвращает целое в диапазоне от 1 до значения аргумента, разделяя партицию на максимально равные по размеру части.
lag
lag ( значениеanycompatible [,смещениеinteger [,значение_по_умолчанию anycompatible ]] ) → anycompatible
Возвращает значение, вычисленное для строки, отстоящей на смещение строк к началу партиции от текущей; если такой строки нет, вместо этого возвращает значение_по_умолчанию (которое должно быть совместимого со значением типа). Как смещение, так и значение_по_умолчанию вычисляются относительно текущей строки. Если они опускаются, по умолчанию смещение равно 1, а значение_по_умолчанию — NULL.
lead
lead ( значениеanycompatible [,смещениеinteger [,значение_по_умолчанию anycompatible ]] ) → anycompatible
Возвращает значение, вычисленное для строки, отстоящей на смещение строк к концу партиции от текущей; если такой строки нет, вместо этого возвращает значение_по_умолчанию (которое должно быть совместимого со значением типа). Как смещение, так и значение_по_умолчанию вычисляются относительно текущей строки. Если они опускаются, по умолчанию смещение равно 1, а значение_по_умолчанию — NULL.
first_value
first_value ( значение anyelement ) → anyelement
Возвращает значение, вычисленное для первой строки в рамке окна.
last_value
last_value ( значение anyelement ) → anyelement
Возвращает значение, вычисленное для последней строки в рамке окна.
nth_value
nth_value ( значениеanyelement,n integer ) → anyelement
Возвращает значение, вычисленное для n-й строки в рамке окна (считая с 1); возвращает NULL, если такой строки нет.
Примечания
Все функции, перечисленные в этом разделе, зависят от порядка сортировки, заданного в предложении 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 на обратный.)