
+----------------------+----------------------------------+------------------+
| 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/