Introduction
------------

MariaDB 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.

Aggregate 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.

Two key concepts for window functions are Partition and Frame:

* 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.
* 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.

Window functions are applied after joins, group by, and having clauses are
calculated.

Syntax
------

A window function is applied in the select clause using the following syntax:

function_name ([expression [, expression ... ]]) OVER ( window_definition )

where window_definition is defined as:

[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

PARTITION BY:

* Divides the window result set into groups based on one or more expressions.
* An expression may be a constant, column, and non window function expressions.
* A query is not limited to a single partition by clause. Different partition
clauses can be used across different window function applications.
* The partition by columns do not need to be in the select list but do need to
be available from the query result set.
* If there is no PARTITION BY clause, all rows of the result set define the
group.

ORDER BY

* Defines the ordering of values within the partition.
* Can be ordered by multiple keys which may be a constant, column or non
window function expression.
* The order by columns do not need to be in the select list but need to be
available from the query result set.
* Use of a select column alias from the query is not supported.
* ASC (default) and DESC options allow for ordering ascending or descending.
* 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.

and the optional frame_clause is defined as:

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end

and the optional frame_start and frame_end are defined as (value being a
numeric expression):

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

RANGE/ROWS:

* Defines the windowing clause for calculating the set of rows that the
function applies to for calculating a given rows window function result.
* Requires an ORDER BY clause to define the row order for the window.
* 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.
* RANGE specifies the window as a logical offset. If the 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.
* UNBOUNDED PRECEDING indicates the window starts at the first row of the
partition.
* UNBOUNDED FOLLOWING indicates the window ends at the last row of the
partition.
* CURRENT ROW specifies the window start or ends at the current row or value.
* If omitted, the default is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Supported Functions
-------------------

+--------------------------------+-------------------------------------------+
| Function                       | Description                               |
+--------------------------------+-------------------------------------------+
| AVG()                          | The average of all input values.          |
+--------------------------------+-------------------------------------------+
| COUNT()                        | Number of input rows.                     |
+--------------------------------+-------------------------------------------+
| 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.                        |
+--------------------------------+-------------------------------------------+
| DENSE_RANK()                   | Ranks items in a group leaving no gaps    |
|                                | in ranking sequence when there are ties.  |
+--------------------------------+-------------------------------------------+
| FIRST_VALUE()                  | The value evaluated at the row that is    |