Window functions provide the ability to perform calculations across sets of rows that are related to the current query row.
Note that these functions must be invoked using window function syntax, i.e. an OVER clause is required.
See also Extended SQL:2011 Window Functions in MonetDB.
| Function | Return type | Description | Available since |
|---|---|---|---|
| cume_dist(value any) | double | Calculate the cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows | Apr2019 (11.33.3) |
| dense_rank(value any) | int | rank of the current row without gaps, it counts peer groups | |
| diff(value any) | boolean | whether the current row value is different from other partition rows | |
| diff(boolean, value any) | boolean | whether the current row value is different from other partition rows | |
| first_value(value any) | any | returns value evaluated at the row that is the first row of the window frame | Apr2019 (11.33.3) |
| lag(value any [, offset integer [, default any]]) | same type as value | returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return "default" (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, "offset" defaults to 1 and "default" to null. | Apr2019 (11.33.3) |
| last_value(value any) | same type as value | returns value evaluated at the row that is the last row of the window frame | Apr2019 (11.33.3) |
| lead(value any [, offset integer [, default any]]) | same type as value | returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return "default" (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, "offset" defaults to 1 and "default" to null. | Apr2019 (11.33.3) |
| nth_value(value any, nth bigint) | same type as value | returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row. | Apr2019 (11.33.3) |
| ntile(value any, num_buckects integer) | same type as num_buckects | integer ranging from 1 to the argument value, dividing the partition as equally as possible | Apr2019 (11.33.3) |
| percent_rank(value any) | double | Calculates the relative rank of the current row: (rank() - 1) / (total partition rows - 1) | Apr2019 (11.33.3) |
| rank(value any) | int | rank of the current row with gaps, counting from 1 | |
| row_number(value any) | int | number of the current row within its partition, counting from 1 |
In addition to above window functions, most aggregate functions can be used as a window function. Aggregate functions act as window functions only when an OVER clause follows the call; otherwise they act as non-window aggregates and return a single row for the entire set.
When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame.
An aggregate used with ORDER BY and the default window frame definition produces a “running sum” type of behavior, which may or may not be what's wanted. To obtain aggregation over the whole partition, omit ORDER BY or use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other frame specifications can be used to obtain other effects.
| Function | Return type | Available since |
|---|---|---|
| avg(numeric_expr) | double | Apr2019 (11.33.3) |
| count(col_expr any, distinct boolean) | bigint or hugeint | Apr2019 (11.33.3) |
| max(col_expr any) | same as type of col_expr | Apr2019 (11.33.3) |
| min(col_expr any) | same as type of col_expr | Apr2019 (11.33.3) |
| prod(numeric_expr) | bigint or hugeint or double | Apr2019 (11.33.3) |
| sum(numeric_expr) | depends on type of numeric_expr | Apr2019 (11.33.3) |
| sys.corr(numeric_a, numeric_b) | double | Jun2020 (11.37.7) |
| sys.covar_pop(numeric_a, numeric_b) | double | Jun2020 (11.37.7) |
| sys.covar_samp(numeric_a, numeric_b) | double | Jun2020 (11.37.7) |
| sys.stddev_pop(numeric_expr) | double | Jun2020 (11.37.7) |
| sys.stddev_samp(numeric_expr) | double | Jun2020 (11.37.7) |
| sys.var_pop(numeric_expr) | double | Jun2020 (11.37.7) |
| sys.var_samp(numeric_expr) | double | Jun2020 (11.37.7) |
| sys.group_concat(col_expr clob) | clob | Jun2020 (11.37.7) |
| sys.group_concat(col_expr clob, separator clob) | clob | Jun2020 (11.37.7) |
| listagg(col_expr varchar) | varchar | Jun2020 (11.37.7) |
| listagg(col_expr varchar, separator varchar) | varchar | Jun2020 (11.37.7) |
To query the available window functions in your MonetDB server run query:
SELECT DISTINCT schema_id, name, func, mod, language, system FROM sys.functions WHERE type = 6 ORDER BY name;
You can also create your own window functions, see Create Window Function command.