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    |
|                                | the first row of the window frame         |
|                                | (counting from 1); null if no such row.   |
+--------------------------------+-------------------------------------------+
| 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.            |
+--------------------------------+-------------------------------------------+
| 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.   |
+--------------------------------+-------------------------------------------+
| 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. |
+--------------------------------+-------------------------------------------+
| MAX()                          | Maximum value of expression across all    |
|                                | input values.                             |
+--------------------------------+-------------------------------------------+
| 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.     |
+--------------------------------+-------------------------------------------+
| MIN()                          | Minimum value of expression across all    |
|                                | input values.                             |
+--------------------------------+-------------------------------------------+
| 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.   |
+--------------------------------+-------------------------------------------+
| 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.                   |
+--------------------------------+-------------------------------------------+
| PERCENT_RANK()                 | relative rank of the current row: (rank   |
|                                | - 1) / (total rows - 1).                  |
+--------------------------------+-------------------------------------------+
| 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.               |
+--------------------------------+-------------------------------------------+
| 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.                              |
+--------------------------------+-------------------------------------------+
| RANK()                         | rank of the current row with gaps; same   |
|                                | as row_number of its first peer.          |
+--------------------------------+-------------------------------------------+
| ROW_NUMBER()                   | number of the current row within its      |
|                                | partition, counting from 1                |
+--------------------------------+-------------------------------------------+
| STDDEV() STDDEV_POP()          | Computes the population standard          |
|                                | deviation and returns the square root of  |
|                                | the population variance.                  |
+--------------------------------+-------------------------------------------+
| STDDEV_SAMP()                  | Computes the cumulative sample standard   |
|                                | deviation and returns the square root of  |
|                                | the sample variance.                      |
+--------------------------------+-------------------------------------------+
| SUM()                          | Sum of expression across all input        |
|                                | values.                                   |
+--------------------------------+-------------------------------------------+
| VARIANCE() VAR_POP()           | Population variance of the input values   |
|                                | (square of the population standard        |
|                                | deviation).                               |
+--------------------------------+-------------------------------------------+
| VAR_SAMP()                     | Sample variance of the input values       |
|                                | (square of the sample standard            |
|                                | deviation).                               |
+--------------------------------+-------------------------------------------+

Examples
--------

Example Schema
--------------

The examples are all based on the following simplified sales opportunity table:

create table opportunities (
id int,
accountName varchar(20),
name varchar(128),
owner varchar(7),
amount decimal(10,2),
closeDate date,
stageName varchar(11)
) engine=columnstore;

Some example values are (thanks to https://www.mockaroo.com for sample data
generation):

+----+---------------+------+--------+---------+-------------+-------------+
| id | accountName   | name | owner  | amount  | closeDate   | stageName   |
+----+---------------+------+--------+---------+-------------+-------------+
| 1  | Browseblab    | Mult | Bob    | 26444.8 | 2016-10-20  | Negotiating |
|    |               | -lat |        |         |             |             |
|    |               | ral  |        |         |             |             |
|    |               | exec |        |         |             |             |
|    |               | tive |        |         |             |             |
|    |               | func |        |         |             |             |
|    |               | ion  |        |         |             |             |
+----+---------------+------+--------+---------+-------------+-------------+
| 2  | Mita          | Orga | Maria  | 477878. | 2016-11-28  | ClosedWon   |
|    |               | ic   |        | 1       |             |             |
|    |               | dema |        |         |             |             |
|    |               | d-dr |        |         |             |             |
|    |               | ven  |        |         |             |             |
|    |               | benc |        |         |             |             |
|    |               | mark |        |         |             |             |
+----+---------------+------+--------+---------+-------------+-------------+
| 3  | Miboo         | De-e | Olivie | 80181.7 | 2017-01-05  | ClosedWon   |
|    |               | gine |        |         |             |             |
|    |               | red  |        |         |             |             |
|    |               | hybr |        |         |             |             |
|    |               | d    |        |         |             |             |
|    |               | grou |        |         |             |             |
|    |               | ware |        |         |             |             |
+----+---------------+------+--------+---------+-------------+-------------+
| 4  | Youbridge     | Ente | Chris  | 946245. | 2016-07-02  | ClosedWon   |
|    |               | pris |        | 9       |             |             |
|    |               | -wid |        |         |             |             |
|    |               |      |        |         |             |             |
|    |               | bott |        |         |             |             |
|    |               | m-li |        |         |             |             |
|    |               | e    |        |         |             |             |
|    |               | Grap |        |         |             |             |
|    |               | ic   |        |         |             |             |
|    |               | Inte |        |         |             |             |
|    |               | face |        |         |             |             |
+----+---------------+------+--------+---------+-------------+-------------+
| 5  | Skyba         | Reve | Maria  | 696241. | 2017-02-17  | Negotiating |
|    |               | se-e |        | 2       |             |             |
|    |               | gine |        |         |             |             |
|    |               | red  |        |         |             |             |
|    |               | fres |        |         |             |             |
|    |               | -thi |        |         |             |             |
|    |               | king |        |         |             |             |
|    |               | stan |        |         |             |             |
|    |               | ardi |        |         |             |             |
|    |               | atio |        |         |             |             |
|    |               |      |        |         |             |             |
+----+---------------+------+--------+---------+-------------+-------------+
| 6  | Eayo          | Fund | Bob    | 765605. | 2016-08-27  | Prospecting |
|    |               | ment |        | 2       |             |             |
|    |               | l    |        |         |             |             |
|    |               | well |        |         |             |             |
|    |               | modu |        |         |             |             |
|    |               | ated |        |         |             |             |
|    |               | arti |        |         |             |             |
|    |               | icia |        |         |             |             |
|    |               |      |        |         |             |             |
|    |               | inte |        |         |             |             |
|    |               | lige |        |         |             |             |
|    |               | ce   |        |         |             |             |
+----+---------------+------+--------+---------+-------------+-------------+
| 7  | Yotz          | Exte | Chris  | 319624. | 2017-01-06  | ClosedLost  |
|    |               | ded  |        | 0       |             |             |
|    |               | seco |        |         |             |             |
|    |               | dary |        |         |             |             |
|    |               | infr |        |         |             |             |
|    |               | stru |        |         |             |             |
|    |               | ture |        |         |             |             |
+----+---------------+------+--------+---------+-------------+-------------+
| 8  | Oloo          | Conf | Chris  | 321016. | 2017-03-08  | ClosedLost  |
|    |               | gura |        | 6       |             |             |
|    |               | le   |        |         |             |             |
|    |               | web- |        |         |             |             |
|    |               | nabl |        |         |             |             |
|    |               | d    |        |         |             |             |
|    |               | data |        |         |             |             |
|    |               | ware |        |         |             |             |
|    |               | ouse |        |         |             |             |
+----+---------------+------+--------+---------+-------------+-------------+
| 9  | Kaymbo        | Mult | Bob    | 690881. | 2017-01-02  | Developing  |
|    |               | -lat |        | 1       |             |             |
|    |               | ral  |        |         |             |             |
|    |               | web- |        |         |             |             |
|    |               | nabl |        |         |             |             |
|    |               | d    |        |         |             |             |
|    |               | defi |        |         |             |             |
|    |               | itio |        |         |             |             |
|    |               |      |        |         |             |             |
+----+---------------+------+--------+---------+-------------+-------------+
| 10 | Rhyloo        | Publ | Chris  | 965477. | 2016-11-07  | Prospecting |
|    |               | c-ke |        | 4       |             |             |
|    |               |      |        |         |             |             |
|    |               | cohe |        |         |             |             |
|    |               | ent  |        |         |             |             |
|    |               | infr |        |         |             |             |
|    |               | stru |        |         |             |             |
|    |               | ture |        |         |             |             |
+----+---------------+------+--------+---------+-------------+-------------+

The schema, sample data, and queries are available as an attachment to this
article.

Cumulative Sum and Running Max Example
--------------------------------------

Window functions can be used to achieve cumulative / running calculations on a
detail report. In this case a won opportunity report for a 7 day period adds
columns to show the accumulated won amount as well as the current highest
opportunity amount in preceding rows.

select owner, 
accountName, 
CloseDate, 
amount, 
sum(amount) over (order by CloseDate rows between unbounded preceding and
current row) cumeWon, 
max(amount) over (order by CloseDate rows between unbounded preceding and
current row) runningMax
from opportunities 
where stageName='ClosedWon' 
and closeDate >= '2016-10-02' and closeDate <= '2016-10-09' 
order by CloseDate;

with example results:

+--------+---------------+-------------+---------+----------+--------------+
| owner  | accountName   | CloseDate   | amount  | cumeWon  | runningMax   |
+--------+---------------+-------------+---------+----------+--------------+
| Bill   | Babbleopia    | 2016-10-02  | 437636. | 437636.4 | 437636.47    |
|        |               |             | 7       |          |              |
+--------+---------------+-------------+---------+----------+--------------+
| Bill   | Thoughtworks  | 2016-10-04  | 146086. | 583722.9 | 437636.47    |
|        |               |             | 1       |          |              |
+--------+---------------+-------------+---------+----------+--------------+
| Olivie | Devpulse      | 2016-10-05  | 834235. | 1417958. | 834235.93    |
|        |               |             | 3       | 1        |              |
+--------+---------------+-------------+---------+----------+--------------+
| Chris  | Linkbridge    | 2016-10-07  | 539977. | 2458738. | 834235.93    |
|        |               |             | 5       | 5        |              |
+--------+---------------+-------------+---------+----------+--------------+
| Olivie | Trupe         | 2016-10-07  | 500802. | 1918761. | 834235.93    |
|        |               |             | 9       | 0        |              |
+--------+---------------+-------------+---------+----------+--------------+
| Bill   | Latz          | 2016-10-08  | 857254. | 3315993. | 857254.87    |
|        |               |             | 7       | 2        |              |
+--------+---------------+-------------+---------+----------+--------------+
| Chris  | Avamm         | 2016-10-09  | 699566. | 4015560. | 857254.87    |
|        |               |             | 6       | 8        |              |
+--------+---------------+-------------+---------+----------+--------------+

Partitioned Cumulative Sum and Running Max Example
--------------------------------------------------

The above example can be partitioned, so that the window functions are over a
particular field grouping such as owner and accumulate within that grouping.
This is achieved by adding the syntax "partition by <columns>" in the window
function clause.

select owner,  
accountName,  
CloseDate,  
amount,  
sum(amount) over (partition by owner order by CloseDate rows between unbounded
preceding and current row) cumeWon,  
max(amount) over (partition by owner order by CloseDate rows between unbounded
preceding and current row) runningMax 
from opportunities  
where stageName='ClosedWon' 
and closeDate >= '2016-10-02' and closeDate <= '2016-10-09'  
order by owner, CloseDate;

with example results:

+--------+---------------+-------------+---------+----------+--------------+
| owner  | accountName   | CloseDate   | amount  | cumeWon  | runningMax   |
+--------+---------------+-------------+---------+----------+--------------+
| Bill   | Babbleopia    | 2016-10-02  | 437636. | 437636.4 | 437636.47    |
|        |               |             | 7       |          |              |
+--------+---------------+-------------+---------+----------+--------------+
| Bill   | Thoughtworks  | 2016-10-04  | 146086. | 583722.9 | 437636.47    |
|        |               |             | 1       |          |              |
+--------+---------------+-------------+---------+----------+--------------+
| Bill   | Latz          | 2016-10-08  | 857254. | 1440977. | 857254.87    |
|        |               |             | 7       | 5        |              |
+--------+---------------+-------------+---------+----------+--------------+
| Chris  | Linkbridge    | 2016-10-07  | 539977. | 539977.4 | 539977.45    |
|        |               |             | 5       |          |              |
+--------+---------------+-------------+---------+----------+--------------+
| Chris  | Avamm         | 2016-10-09  | 699566. | 1239544. | 699566.86    |
|        |               |             | 6       | 1        |              |
+--------+---------------+-------------+---------+----------+--------------+
| Olivie | Devpulse      | 2016-10-05  | 834235. | 834235.9 | 834235.93    |
|        |               |             | 3       |          |              |
+--------+---------------+-------------+---------+----------+--------------+
| Olivie | Trupe         | 2016-10-07  | 500802. | 1335038. | 834235.93    |
|        |               |             | 9       | 2        |              |
+--------+---------------+-------------+---------+----------+--------------+

Ranking / Top Results
---------------------

The rank window function allows for ranking or assigning a numeric order value
based on the window function definition. Using the Rank() function will result
in the same value for ties / equal values and the next rank value skipped. The
Dense_Rank() function behaves similarly except the next consecutive number is
used after a tie rather than skipped. The Row_Number() function will provide a
unique ordering value. The example query shows the Rank() function being
applied to rank sales reps by the number of opportunities for Q4 2016.

select owner, 
wonCount, 
rank() over (order by wonCount desc) rank 
from (
 select owner,
 count(*) wonCount
 from opportunities
 where stageName='ClosedWon'
 and closeDate >= '2016-10-01' and closeDate < '2016-12-31'
 group by owner
) t
order by rank;

with example results (note the query is technically incorrect by using
closeDate < '2016-12-31' however this creates a tie scenario for illustrative
purposes):

+----------------------+----------------------------------+------------------+
| owner                | wonCount                         | rank             |
+----------------------+----------------------------------+------------------+
| Bill                 | 19                               | 1                |
+----------------------+----------------------------------+------------------+
| Chris                | 15                               | 2                |
+----------------------+----------------------------------+------------------+
| Maria                | 14                               | 3                |
+----------------------+----------------------------------+------------------+
| Bob                  | 14                               | 3                |
+----------------------+----------------------------------+------------------+
| Olivier              | 10                               | 5                |
+----------------------+----------------------------------+------------------+

If the dense_rank function is used the rank values would be 1,2,3,3,4 and for
the row_number function the values would be 1,2,3,4,5.

First and Last Values
---------------------

The first_value and last_value functions allow determining the first and last
values of a given range. Combined with a group by this allows summarizing
opening and closing values. The example shows a more complex case where
detailed information is presented for first and last opportunity by quarter.

select a.year, 
a.quarter, 
f.accountName firstAccountName, 
f.owner firstOwner, 
f.amount firstAmount, 
l.accountName lastAccountName, 
l.owner lastOwner, 
l.amount lastAmount 
from (
 select year,
 quarter,
 min(firstId) firstId,
 min(lastId) lastId
 from (
  select year(closeDate) year,
  quarter(closeDate) quarter,
  first_value(id) over (partition by year(closeDate), quarter(closeDate)
order by closeDate rows between unbounded preceding and current row) firstId, 
  last_value(id) over (partition by year(closeDate), quarter(closeDate)
order by closeDate rows between current row and unbounded following) lastId 
  from opportunities  where stageName='ClosedWon'
 ) t
 group by year, quarter order by year,quarter
) a 
join opportunities f on a.firstId = f.id 
join opportunities l on a.lastId = l.id 
order by year, quarter;

with example results:

+----+------+------------+--------+---------+-----------+-------+--------+
| ye | quar | firstAccou | firstO | firstAm | lastAccou | lastO | lastAm |
| r  | er   | tName      | ner    | unt     | tName     | ner   | unt    |
+----+------+------------+--------+---------+-----------+-------+--------+
| 20 | 3    | Skidoo     | Bill   | 523295. | Skipstorm | Bill  | 151420 |
| 6  |      |            |        | 7       |           |       | 86     |
+----+------+------------+--------+---------+-----------+-------+--------+
| 20 | 4    | Skimia     | Chris  | 961513. | Avamm     | Maria | 112493 |
| 6  |      |            |        | 9       |           |       | 65     |
+----+------+------------+--------+---------+-----------+-------+--------+
| 20 | 1    | Yombu      | Bob    | 536875. | Skaboo    | Chris | 270273 |
| 7  |      |            |        | 1       |           |       | 08     |
+----+------+------------+--------+---------+-----------+-------+--------+

Prior and Next Example
----------------------

Sometimes it useful to understand the previous and next values in the context
of a given row. The lag and lead window functions provide this capability. By
default the offset is one providing the prior or next value but can also be
provided to get a larger offset. The example query is a report of
opportunities by account name showing the opportunity amount, and the prior
and next opportunity amount for that account by close date.

select accountName, 
closeDate,  
amount currentOppAmount, 
lag(amount) over (partition by accountName order by closeDate) priorAmount,
lead(amount) over (partition by accountName order by closeDate) nextAmount 
from opportunities 
order by accountName, closeDate 
limit 9;

with example results:

+--------------+-----------+-------------------+--------------+-------------+
| accountName  | closeDate | currentOppAmount  | priorAmount  | nextAmount  |
+--------------+-----------+-------------------+--------------+-------------+
| Abata        | 2016-09-1 | 645098.45         | NULL         | 161086.82   |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-------------+
| Abata        | 2016-10-1 | 161086.82         | 645098.45    | 350235.75   |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-------------+
| Abata        | 2016-12-1 | 350235.75         | 161086.82    | 878595.89   |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-------------+
| Abata        | 2016-12-3 | 878595.89         | 350235.75    | 922322.39   |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-------------+
| Abata        | 2017-01-2 | 922322.39         | 878595.89    | NULL        |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-------------+
| Abatz        | 2016-10-1 | 795424.15         | NULL         | NULL        |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-------------+
| Agimba       | 2016-07-0 | 288974.84         | NULL         | 914461.49   |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-------------+
| Agimba       | 2016-09-0 | 914461.49         | 288974.84    | 176645.52   |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-------------+
| Agimba       | 2016-09-2 | 176645.52         | 914461.49    | NULL        |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-------------+

Quartiles Example
-----------------

The NTile window function allows for breaking up a data set into portions
assigned a numeric value to each portion of the range. NTile(4) breaks the
data up into quartiles (4 sets). The example query produces a report of all
opportunities summarizing the quartile boundaries of amount values.

select t.quartile, 
min(t.amount) min, 
max(t.amount) max 
from (
 select amount,
 ntile(4) over (order by amount asc) quartile
 from opportunities
 where closeDate >= '2016-10-01' and closeDate <= '2016-12-31'
 ) t
group by quartile 
order by quartile;

With example results:

+-----------------------------------------+----------------+----------------+
| quartile                                | min            | max            |
+-----------------------------------------+----------------+----------------+
| 1                                       | 6337.15        | 287634.01      |
+-----------------------------------------+----------------+----------------+
| 2                                       | 288796.14      | 539977.45      |
+-----------------------------------------+----------------+----------------+
| 3                                       | 540070.04      | 748727.51      |
+-----------------------------------------+----------------+----------------+
| 4                                       | 753670.77      | 998864.47      |
+-----------------------------------------+----------------+----------------+

Percentile Example
------------------

The percentile functions have a slightly different syntax from other window
functions as can be seen in the example below. These functions can be only
applied against numeric values. The argument to the function is the percentile
to evaluate. Following 'within group' is the sort expression which indicates
the sort column and optionally order. Finally after 'over' is an optional
partition by clause, for no partition clause use 'over ()'. The example below
utilizes the value 0.5 to calculate the median opportunity amount in the rows.
The values differ sometimes because percentile_cont will return the average of
the 2 middle rows for an even data set while percentile_desc returns the first
encountered in the sort.

select owner,  
accountName,  
CloseDate,  
amount,
percentile_cont(0.5) within group (order by amount) over (partition by owner)
pct_cont,
percentile_disc(0.5) within group (order by amount) over (partition by owner)
pct_disc
from opportunities  
where stageName='ClosedWon' 
and closeDate >= '2016-10-02' and closeDate <= '2016-10-09'  
order by owner, CloseDate;

With example results:

+--------+----------------+-------------+---------+------------+------------+
| owner  | accountName    | CloseDate   | amount  | pct_cont   | pct_disc   |
+--------+----------------+-------------+---------+------------+------------+
| Bill   | Babbleopia     | 2016-10-02  | 437636. | 437636.470 | 437636.47  |
|        |                |             | 7       | 000000     |            |
+--------+----------------+-------------+---------+------------+------------+
| Bill   | Thoughtworks   | 2016-10-04  | 146086. | 437636.470 | 437636.47  |
|        |                |             | 1       | 000000     |            |
+--------+----------------+-------------+---------+------------+------------+
| Bill   | Latz           | 2016-10-08  | 857254. | 437636.470 | 437636.47  |
|        |                |             | 7       | 000000     |            |
+--------+----------------+-------------+---------+------------+------------+
| Chris  | Linkbridge     | 2016-10-07  | 539977. | 619772.155 | 539977.45  |
|        |                |             | 5       | 000000     |            |
+--------+----------------+-------------+---------+------------+------------+
| Chris  | Avamm          | 2016-10-09  | 699566. | 619772.155 | 539977.45  |
|        |                |             | 6       | 000000     |            |
+--------+----------------+-------------+---------+------------+------------+
| Olivie | Devpulse       | 2016-10-05  | 834235. | 667519.110 | 500802.29  |
|        |                |             | 3       | 000000     |            |
+--------+----------------+-------------+---------+------------+------------+
| Olivie | Trupe          | 2016-10-07  | 500802. | 667519.110 | 500802.29  |
|        |                |             | 9       | 000000     |            |
+--------+----------------+-------------+---------+------------+------------+

URL: https://mariadb.com/kb/en/window-functions-columnstore-window-functions/