Postgresql с function windows
Оконные функции дают возможность выполнять вычисления с набором строк, каким-либо образом связанным с текущей строкой запроса. Вводную информацию об этом можно получить в Разделе 3.5, а подробнее узнать о синтаксисе можно в Подразделе 4.2.8.
Встроенные оконные функции перечислены в Таблице 9.54. Заметьте, что эти функции должны вызываться именно как оконные, т. е. при вызове необходимо использовать предложение OVER .
В дополнение к этим функциям в качестве оконных можно использовать любые встроенные или пользовательские обычные (но не сортирующие и не гипотезирующие) агрегатные функции (встроенные функции перечислены в Разделе 9.20). Агрегатные функции работают как оконные, только когда за их вызовом следует предложение OVER ; в противном случае они останутся обычными агрегатными.
Таблица 9.54. Оконные функции общего назначения
| Функция | Тип результата | Описание |
|---|---|---|
| row_number() | bigint | номер текущей строки в её разделе, начиная с 1 |
| rank() | bigint | ранг текущей строки с пропусками; то же, что и row_number для первой родственной ей строки |
| dense_rank() | bigint | ранг текущей строки без пропусков; эта функция считает группы родственных строк |
| percent_rank() | double precision | относительный ранг текущей строки: ( rank — 1) / (общее число строк — 1) |
| cume_dist() | double precision | относительный ранг текущей строки: (число строк, предшествующих или родственных текущей) / (общее число строк) |
| ntile( число_групп integer ) | integer | ранжирование по целым числам от 1 до значения аргумента так, чтобы размеры групп были максимально близки |
| lag( значение anyelement [, смещение integer [, по_умолчанию anyelement ]]) | тип аргумента значение | возвращает значение для строки, положение которой задаётся смещением от текущей строки к началу раздела; если такой строки нет, возвращается значение по_умолчанию (оно должно иметь тот же тип, что и значение ). Оба параметра смещение и по_умолчанию вычисляются для текущей строки. Если они не указываются, то смещение считается равным 1, а по_умолчанию — NULL |
| lead( значение anyelement [, смещение integer [, по_умолчанию anyelement ]]) | тип аргумента значение | возвращает значение для строки, положение которой задаётся смещением от текущей строки к концу раздела; если такой строки нет, возвращается значение по_умолчанию (оно должно иметь тот же тип, что и значение ). Оба параметра смещение и по_умолчанию вычисляются для текущей строки. Если они не указываются, то смещение считается равным 1, а по_умолчанию — NULL |
| first_value( значение any ) | тип аргумента значение | возвращает значение , вычисленное для первой строки в рамке окна |
| last_value( значение any ) | тип аргумента значение | возвращает значение , вычисленное для последней строки в рамке окна |
| nth_value( значение any , n integer ) | тип аргумента значение | возвращает значение , вычисленное в н-ой строке в рамке окна (считая с 1), или NULL, если такой строки нет |
Результат всех функций, перечисленных в Таблице 9.54, зависит от порядка сортировки, заданного предложением ORDER BY в определении соответствующего окна. Строки, которые являются одинаковыми с точки зрения сортировки ORDER BY , считаются родственными; четыре функции, вычисляющие ранг, реализованы так, что их результат будет одинаковым для любых двух родственных строк.
Заметьте, что функции first_value , last_value и nth_value рассматривают только строки в « рамке окна » , которая по умолчанию содержит строки от начала раздела до последней родственной строки для текущей. Поэтому результаты last_value и иногда nth_value могут быть не очень полезны. В таких случаях можно переопределить рамку, добавив в предложение OVER подходящее указание ( RANGE или ROWS ). Подробнее эти указания описаны в Подразделе 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 . В Postgres Pro такие параметры не реализованы: эти функции ведут себя так, как положено в стандарте по умолчанию (или с подразумеваемым параметром RESPECT NULLS ). Также функция nth_value не поддерживает предусмотренные стандартом параметры FROM FIRST и FROM LAST : реализовано только поведение по умолчанию (с подразумеваемым параметром FROM FIRST ). (Получить эффект параметра FROM LAST можно, изменив порядок ORDER BY на обратный.)
Postgresql с function windows
Оконные функции дают возможность выполнять вычисления с набором строк, каким-либо образом связанным с текущей строкой запроса. Вводную информацию об этом можно получить в Разделе 3.5, а подробнее узнать о синтаксисе можно в Подразделе 4.2.8.
Встроенные оконные функции перечислены в Таблице 9.56. Заметьте, что эти функции должны вызываться именно как оконные, т. е. при вызове необходимо использовать предложение OVER .
В дополнение к этим функциям в качестве оконных можно использовать любые встроенные или пользовательские обычные (но не сортирующие и не гипотезирующие) агрегатные функции (встроенные функции перечислены в Разделе 9.20). Агрегатные функции работают как оконные, только когда за их вызовом следует предложение OVER ; в противном случае они останутся обычными агрегатными.
Таблица 9.56. Оконные функции общего назначения
| Функция | Тип результата | Описание |
|---|---|---|
| row_number() | bigint | номер текущей строки в её разделе, начиная с 1 |
| rank() | bigint | ранг текущей строки с пропусками; то же, что и row_number для первой родственной ей строки |
| dense_rank() | bigint | ранг текущей строки без пропусков; эта функция считает группы родственных строк |
| percent_rank() | double precision | относительный ранг текущей строки: ( rank — 1) / (общее число строк — 1) |
| cume_dist() | double precision | относительный ранг текущей строки: (число строк, предшествующих или родственных текущей) / (общее число строк) |
| ntile( число_групп integer ) | integer | ранжирование по целым числам от 1 до значения аргумента так, чтобы размеры групп были максимально близки |
| lag( значение anyelement [, смещение integer [, по_умолчанию anyelement ]]) | тип аргумента значение | возвращает значение для строки, положение которой задаётся смещением от текущей строки к началу раздела; если такой строки нет, возвращается значение по_умолчанию (оно должно иметь тот же тип, что и значение ). Оба параметра смещение и по_умолчанию вычисляются для текущей строки. Если они не указываются, то смещение считается равным 1, а по_умолчанию — NULL |
| lead( значение anyelement [, смещение integer [, по_умолчанию anyelement ]]) | тип аргумента значение | возвращает значение для строки, положение которой задаётся смещением от текущей строки к концу раздела; если такой строки нет, возвращается значение по_умолчанию (оно должно иметь тот же тип, что и значение ). Оба параметра смещение и по_умолчанию вычисляются для текущей строки. Если они не указываются, то смещение считается равным 1, а по_умолчанию — NULL |
| first_value( значение any ) | тип аргумента значение | возвращает значение , вычисленное для первой строки в рамке окна |
| last_value( значение any ) | тип аргумента значение | возвращает значение , вычисленное для последней строки в рамке окна |
| nth_value( значение any , n integer ) | тип аргумента значение | возвращает значение , вычисленное в н-ой строке в рамке окна (считая с 1), или NULL, если такой строки нет |
Результат всех функций, перечисленных в Таблице 9.56, зависит от порядка сортировки, заданного предложением ORDER BY в определении соответствующего окна. Строки, которые являются одинаковыми с точки зрения сортировки ORDER BY , считаются родственными; четыре функции, вычисляющие ранг, реализованы так, что их результат будет одинаковым для любых двух родственных строк.
Заметьте, что функции first_value , last_value и nth_value рассматривают только строки в « рамке окна » , которая по умолчанию содержит строки от начала раздела до последней родственной строки для текущей. Поэтому результаты last_value и иногда nth_value могут быть не очень полезны. В таких случаях можно переопределить рамку, добавив в предложение OVER подходящее указание ( RANGE или ROWS ). Подробнее эти указания описаны в Подразделе 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 на обратный.)
3.5. Window Functions
A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.
Here is an example that shows how to compare each employee’s salary with the average salary in his or her department:
The first three output columns come directly from the table empsalary, and there is one output row for each row in the table. The fourth column represents an average taken across all the table rows that have the same depname value as the current row. (This actually is the same function as the regular avg aggregate function, but the OVER clause causes it to be treated as a window function and computed across an appropriate set of rows.)
A window function call always contains an OVER clause directly following the window function’s name and argument(s). This is what syntactically distinguishes it from a regular function or aggregate function. The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY list within OVER specifies dividing the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.
You can also control the order in which rows are processed by window functions using ORDER BY within OVER. (The window ORDER BY does not even have to match the order in which the rows are output.) Here is an example:
As shown here, the rank function produces a numerical rank within the current row’s partition for each distinct ORDER BY value, in the order defined by the ORDER BY clause. rank needs no explicit parameter, because its behavior is entirely determined by the OVER clause.
The rows considered by a window function are those of the «virtual table» produced by the query’s FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways by means of different OVER clauses, but they all act on the same collection of rows defined by this virtual table.
We already saw that ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is just one partition containing all the rows.
There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its window frame. Many (but not all) window functions act only on the rows of the window frame, rather than of the whole partition. By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to the ORDER BY clause. When ORDER BY is omitted the default frame consists of all rows in the partition. [1] Here is an example using sum :
Above, since there is no ORDER BY in the OVER clause, the window frame is the same as the partition, which for lack of PARTITION BY is the whole table; in other words each sum is taken over the whole table and so we get the same result for each output row. But if we add an ORDER BY clause, we get very different results:
Here the sum is taken from the first (lowest) salary up through the current one, including any duplicates of the current one (notice the results for the duplicated salaries).
Window functions are permitted only in the SELECT list and the ORDER BY clause of the query. They are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses. This is because they logically execute after the processing of those clauses. Also, window functions execute after regular aggregate functions. This means it is valid to include an aggregate function call in the arguments of a window function, but not vice versa.
If there is a need to filter or group rows after the window calculations are performed, you can use a sub-select. For example:
The above query only shows the rows from the inner query having rank less than 3.
When a query involves multiple window functions, it is possible to write out each one with a separate OVER clause, but this is duplicative and error-prone if the same windowing behavior is wanted for several functions. Instead, each windowing behavior can be named in a WINDOW clause and then referenced in OVER. For example:
More details about window functions can be found in Section 4.2.8, Section 9.19, Section 7.2.4, and the SELECT reference page.
Notes
There are options to define the window frame in other ways, but this tutorial does not cover them. See Section 4.2.8 for details.
