Syntax
------

frame_clause:
 {ROWS | RANGE} {frame_border | BETWEEN frame_border AND frame_border}

frame_border:
 | UNBOUNDED PRECEDING
 | UNBOUNDED FOLLOWING
 | CURRENT ROW
 | expr PRECEDING
 | expr FOLLOWING

Description
-----------

A basic overview of window functions is described in Window Functions
Overview. Window frames expand this functionality by allowing the function to
include a specified a number of rows around the current row.

These include:

* All rows before the current row (UNBOUNDED PRECEDING), for example RANGE
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
* All rows after the current row (UNBOUNDED FOLLOWING), for example RANGE
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
* A set number of rows before the current row (expr PRECEDING) for example
RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
* A set number of rows after the current row (expr PRECEDING AND expr
FOLLOWING)  for example RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING
* A specified number of rows both before and after the current row, for
example RANGE BETWEEN 6 PRECEDING AND 3 FOLLOWING

The following functions operate on window frames:

* AVG
* BIT_AND
* BIT_OR
* BIT_XOR
* COUNT
* LEAD
* MAX
* MIN
* NTILE
* STD
* STDDEV
* STDDEV_POP
* STDDEV_SAMP
* SUM
* VAR_POP
* VAR_SAMP
* VARIANCE

Window frames are determined by the frame_clause in the window function
request.

Take the following example:

CREATE TABLE `student_test` (
 name char(10),
 test char(10),
 score tinyint(4)
);

INSERT INTO student_test VALUES 
  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
  ('Tatiana', 'SQL', 87);

SELECT name, test, score, SUM(score) 
 OVER () AS total_score
 FROM student_test;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Chun    | SQL    |    75 |         453 |
| Chun    | Tuning |    73 |         453 |
| Esben   | SQL    |    43 |         453 |
| Esben   | Tuning |    31 |         453 |
| Kaolin  | SQL    |    56 |         453 |
| Kaolin  | Tuning |    88 |         453 |
| Tatiana | SQL    |    87 |         453 |
+---------+--------+-------+-------------+

By not specifying an OVER clause, the SUM function is run over the entire
dataset. However, if we specify an ORDER BY condition based on score (and
order the entire result in the same way for clarity), the following result is
returned:

SELECT name, test, score, SUM(score) 
 OVER (ORDER BY score) AS total_score
 FROM student_test ORDER BY score;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |          31 |
| Esben   | SQL    |    43 |          74 |
| Kaolin  | SQL    |    56 |         130 |
| Chun    | Tuning |    73 |         203 |
| Chun    | SQL    |    75 |         278 |
| Tatiana | SQL    |    87 |         365 |
| Kaolin  | Tuning |    88 |         453 |
+---------+--------+-------+-------------+

The total_score column represents a running total of the current row, and all
previous rows. The window frame in this example expands as the function
proceeds.

The above query makes use of the default to define the window frame. It could
be written explicitly as follows:

SELECT name, test, score, SUM(score) 
 OVER (ORDER BY score RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
total_score 
 FROM student_test ORDER BY score;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |          31 |
| Esben   | SQL    |    43 |          74 |
| Kaolin  | SQL    |    56 |         130 |
| Chun    | Tuning |    73 |         203 |
| Chun    | SQL    |    75 |         278 |
| Tatiana | SQL    |    87 |         365 |
| Kaolin  | Tuning |    88 |         453 |
+---------+--------+-------+-------------+

Let's look at some alternatives:

Firstly, applying the window function to the current row and all following
rows can be done with the use of UNBOUNDED FOLLOWING:

SELECT name, test, score, SUM(score) 
 OVER (ORDER BY score RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS
total_score 
 FROM student_test ORDER BY score;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |         453 |
| Esben   | SQL    |    43 |         422 |
| Kaolin  | SQL    |    56 |         379 |
| Chun    | Tuning |    73 |         323 |
| Chun    | SQL    |    75 |         250 |
| Tatiana | SQL    |    87 |         175 |
| Kaolin  | Tuning |    88 |          88 |
+---------+--------+-------+-------------+

It's possible to specify a number of rows, rather than the entire unbounded
following or preceding set. The following example takes the current row, as
well as the previous row:

SELECT name, test, score, SUM(score) 
 OVER (ORDER BY score ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS
total_score 
 FROM student_test ORDER BY score;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |          31 |
| Esben   | SQL    |    43 |          74 |
| Kaolin  | SQL    |    56 |          99 |
| Chun    | Tuning |    73 |         129 |
| Chun    | SQL    |    75 |         148 |
| Tatiana | SQL    |    87 |         162 |
| Kaolin  | Tuning |    88 |         175 |
+---------+--------+-------+-------------+

The current row and the following row:

SELECT name, test, score, SUM(score) 
 OVER (ORDER BY score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS
total_score 
 FROM student_test ORDER BY score;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |          74 |
| Esben   | SQL    |    43 |         130 |
| Kaolin  | SQL    |    56 |         172 |
| Chun    | Tuning |    73 |         204 |
| Chun    | SQL    |    75 |         235 |
| Tatiana | SQL    |    87 |         250 |
| Kaolin  | Tuning |    88 |         175 |
+---------+--------+-------+-------------+

URL: https://mariadb.com/kb/en/window-frames/