Description\n-----------\n\nIntroduction\n\nMariaDB ColumnStore provides support for window functions broadly following the SQL 2003 specification. A window function allows for calculations relating to a window of data surrounding the current row in a result set. This capability provides for simplified queries in support of common business questions such as cumulative totals, rolling averages, and top 10 lists.\n\nAggregate functions are utilized for window functions however differ in behavior from a group by query because the rows remain ungrouped. This provides support for cumulative sums and rolling averages, for example.\n\nTwo key concepts for window functions are Partition and Frame:\n\n A Partition is a group of rows, or window, that have the same value for a specific column, for example a Partition can be created over a time period such as a quarter or lookup values.\n The Frame for each row is a subset of the row's Partition. The frame typically is dynamic allowing for a sliding frame of rows within the Partition. The Frame determines the range of rows for the windowing function. A Frame could be defined as the last X rows and next Y rows all the way up to the entire Partition.\n\nWindow functions are applied after joins, group by, and having clauses are calculated.\n\nSyntax\n\nA window function is applied in the select clause using the following syntax:\n\n``sql\nfunction_name ([expression [, expression ... ]]) OVER ( window_definition )\n`\n\nwhere _window_definition_ is defined as:\n\n`sql\n[ PARTITION BY expression [, ...] ]\n[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]\n[ frame_clause ]\n`\n\nPARTITION BY:\n\n Divides the window result set into groups based on one or more expressions.\n An expression may be a constant, column, and non window function expressions.\n A query is not limited to a single partition by clause. Different partition clauses can be used across different window function applications.\n The partition by columns do not need to be in the select list but do need to be available from the query result set.\n If there is no PARTITION BY clause, all rows of the result set define the group.\n\nORDER BY:\n\n Defines the ordering of values within the partition.\n Can be ordered by multiple keys which may be a constant, column or non window function expression.\n The order by columns do not need to be in the select list but need to be available from the query result set.\n Use of a select column alias from the query is not supported.\n ASC (default) and DESC options allow for ordering ascending or descending.\n NULLS FIRST and NULL_LAST options specify whether null values come first or last in the ordering sequence. NULLS_FIRST is the default for ASC order, and NULLS_LAST is the default for DESC order.\n\nand the optional _frame_clause_ is defined as:\n\n`sql\n{ RANGE | ROWS } frame_start\n{ RANGE | ROWS } BETWEEN frame_start AND frame_end\n`\n\nand the optional _frame_start_ and _frame_end_ are defined as (value being a numeric expression):\n\n`sql\nUNBOUNDED PRECEDING\nvalue PRECEDING\nCURRENT ROW\nvalue FOLLOWING\nUNBOUNDED FOLLOWING\n`\n\nRANGE/ROWS:\n\n Defines the windowing clause for calculating the set of rows that the function applies to for calculating a given rows window function result.\n Requires an ORDER BY clause to define the row order for the window.\n ROWS specify the window in physical units, i.e. result set rows and must be a constant or expression evaluating to a positive numeric value.\n RANGE specifies the window as a logical offset. If the expression evaluates to a numeric value then the ORDER BY expression must be a numeric or DATE type. If the expression evaluates to an interval value then the ORDER BY expression must be a DATE data type.\n UNBOUNDED PRECEDING indicates the window starts at the first row of the partition.\n UNBOUNDED FOLLOWING indicates the window ends at the last row of the partition.\n CURRENT ROW specifies the window start or ends at the current row or value.\n* If omitted, the default is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.\n\nSupported Functions\n\n| Function               | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |\n| ---------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |\n| AVG()                  | The average of all input values.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |\n| COUNT()                | Number of input rows.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |\n| CUME_DIST()           | Calculates the cumulative distribution, or relative rank, of the current row to other rows in the same partition. Number of peer or preceding rows / number of rows in partition.                                                                                                                                                                                                                                                                                                                                                  |\n| DENSE_RANK()          | Ranks items in a group leaving no gaps in ranking sequence when there are ties.                                                                                                                                                                                                                                                                                                                                                                                                                                                    |\n| FIRST_VALUE()         | The value evaluated at the row that is the first row of the window frame (counting from 1); null if no such row.                                                                                                                                                                                                                                                                                                                                                                                                                   |\n| LAG()                  | The 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. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null. LAG provides access to more than one row of a table at the same time without a self-join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position. |\n| LAST_VALUE()          | The value evaluated at the row that is the last row of the window frame (counting from 1); null if no such row.                                                                                                                                                                                                                                                                                                                                                                                                                    |\n| LEAD()                 | Provides access to a row at a given physical offset beyond that position. 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. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null.                                                                                                                                                                         |\n| MAX()                  | Maximum value of expression across all input values.                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |\n| MEDIAN()               | An inverse distribution function that assumes a continuous distribution model. It takes a numeric or datetime value and returns the middle value or an interpolated value that would be the middle value once the values are sorted. Nulls are ignored in the calculation.                                                                                                                                                                                                                                                         |\n| MIN()                  | Minimum value of expression across all input values.                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |\n| NTH_VALUE()           | The value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.                                                                                                                                                                                                                                                                                                                                                                                                                     |\n| NTILE()                | Divides an ordered data set into a number of buckets indicated by expr and assigns the appropriate bucket number to each row. The buckets are numbered 1 through expr. The expr value must resolve to a positive constant for each partition. Integer ranging from 1 to the argument value, dividing the partition as equally as possible.                                                                                                                                                                                         |\n| PERCENT_RANK()        | relative rank of the current row: (rank - 1) / (total rows - 1).                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |\n| PERCENTILE_CONT()     | An inverse distribution function that assumes a continuous distribution model. It takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification. Nulls are ignored in the calculation.                                                                                                                                                                                                                                    |\n| PERCENTILE_DISC()     | An inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.                                                                                                                                                                                                                                                                                                                       |\n| RANK()                 | rank of the current row with gaps; same as row_number of its first peer.                                                                                                                                                                                                                                                                                                                                                                                                                                                          |\n| ROW_NUMBER()          | number of the current row within its partition, counting from 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |\n| STDDEV() STDDEV_POP() | Computes the population standard deviation a\n\n[Truncated — full content at mariadb.com/docs]