# Window Functions Overview

## Window Functions

Window functions calculate across related rows without collapsing them. Unlike `GROUP BY`, they return one result for each input row.

## When to Use Window Functions

Use window functions when you need to:

* Rank rows inside a group.
* Calculate running totals or moving averages.
* Compare a row with earlier or later rows.
* Return the top *N* rows per group.

## Basic Syntax

```sql
window_function(expr) OVER (
  [PARTITION BY expr [, ...]]
  [ORDER BY expr [ASC | DESC] [, ...]]
  [{ROWS | RANGE} frame_clause]
)

frame_clause:
  {frame_border | BETWEEN frame_border AND frame_border}

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

You can also define a named window and reuse it:

```sql
SELECT
  SUM(score) OVER w AS running_total,
  AVG(score) OVER w AS running_avg
FROM student
WINDOW w AS (
  ORDER BY score
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);
```

## Window Functions vs `GROUP BY`

Use `GROUP BY` when you want one output row per group.

Use a window function when you want to keep the original rows and add per-group or per-sequence calculations beside them.

```sql
SELECT test, AVG(score)
FROM student
GROUP BY test;
```

This returns one row per `test`.

```sql
SELECT
  name,
  test,
  score,
  AVG(score) OVER (PARTITION BY test) AS avg_by_test
FROM student;
```

This returns every row, plus the average for that row's `test`.

## How `OVER` Works

### `PARTITION BY`

`PARTITION BY` starts a new calculation for each group.

### `ORDER BY`

`ORDER BY` defines the row sequence inside each partition.

### Frame

The frame controls which rows contribute to the current result. Aggregate window functions use frames. Ranking functions such as `ROW_NUMBER()` and `RANK()` do not.

{% hint style="warning" %}
`OVER ()` uses the whole result set.

For aggregate window functions, `OVER (ORDER BY ...)` uses a running frame by default. In MariaDB, that default is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.
{% endhint %}

## `ROWS` vs `RANGE`

`ROWS` counts physical rows. `RANGE` groups peer rows that share the same `ORDER BY` value.

Use `ROWS` when you need strict row-by-row stepping. Use `RANGE` when ties should be treated as one peer group.

```sql
CREATE TABLE t (score INT);
INSERT INTO t VALUES (10), (10), (20);

SELECT
  score,
  SUM(score) OVER (
    ORDER BY score
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rows_sum,
  SUM(score) OVER (
    ORDER BY score
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS range_sum
FROM t;
```

This produces different results for the duplicate `10` values:

```
+-------+----------+-----------+
| score | rows_sum | range_sum |
+-------+----------+-----------+
|    10 |       10 |        20 |
|    10 |       20 |        20 |
|    20 |       40 |        40 |
+-------+----------+-----------+
```

## Supported Functions

Dedicated window functions include [CUME\_DIST](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/cume_dist), [DENSE\_RANK](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/dense_rank), [FIRST\_VALUE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/first_value), [LAG](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/lag), [LAST\_VALUE](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/last_value), [LEAD](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/lead), [MEDIAN](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/median), [NTH\_VALUE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/nth_value), [NTILE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/ntile), [PERCENTILE\_CONT](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/percentile_cont), [PERCENTILE\_DISC](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/percentile_disc), [PERCENT\_RANK](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/percent_rank), [RANK](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/rank), and [ROW\_NUMBER](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/row_number).

Aggregate functions that also work with `OVER (...)` include [AVG](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/avg), [BIT\_AND](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/bit_and), [BIT\_OR](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/bit_or), [BIT\_XOR](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/bit_xor), [COUNT](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/count), [MAX](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/max), [MIN](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/min), [STD](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/std), [STDDEV](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/stddev), [STDDEV\_POP](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/stddev_pop), [STDDEV\_SAMP](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/stddev_samp), [SUM](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/sum), [VAR\_POP](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/var_pop), [VAR\_SAMP](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/var_samp), and [VARIANCE](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/variance).

Aggregate window functions do not support `DISTINCT`.

### Aggregate Functions as Window Functions

It is possible to use [aggregate functions](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions) as window functions. An aggregate function used as a window function must have the `OVER` clause. For example, here's [COUNT()](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/count) used as a window function:

```sql
SELECT COUNT(*) OVER (ORDER BY column) FROM table;
```

## Common Pitfalls

* You cannot reference a window function in `WHERE`.
* Compute the window result in a subquery or CTE first.
* MariaDB does not support `GROUPS` frames.
* MariaDB does not support frame exclusion.
* MariaDB does not support explicit `NULLS FIRST` or `NULLS LAST`.
* `RANGE` frames do not support `DATE` or `DATETIME` arithmetic.

{% hint style="info" %}
Window functions are evaluated after `WHERE`, `GROUP BY`, and `HAVING`. Filter the computed result in an outer query or CTE.
{% endhint %}

## Optimization

Window functions often need sorted input. Query shape can decide whether MariaDB can reuse an existing order or must sort again.

### `GROUP BY` Comes First

If a query uses both `GROUP BY` and window functions, MariaDB executes the grouping step first. The window step runs on the grouped result.

This affects index usage. An index that helps the base table scan does not automatically avoid later sorting for the window stage.

{% hint style="info" %}
When tuning this pattern, optimize the `GROUP BY` step first. Then check whether the window step still needs its own sort.
{% endhint %}

### Sort Reuse Depends on Sort Keys

If the `GROUP BY` definition and the window's `PARTITION BY` and `ORDER BY` definition use different sort keys, MariaDB usually needs another sort pass before evaluating the window functions.

For example, this shape can require an extra sort:

```sql
SELECT
  dept,
  month,
  SUM(sales) AS monthly_sales,
  ROW_NUMBER() OVER (
    PARTITION BY dept
    ORDER BY month
  ) AS row_num
FROM revenue
GROUP BY month, dept;
```

The grouped result is ordered by `month, dept`. The window step needs `dept, month`. Those orders do not match.

### Multiple Window Functions Can Share One Sort

Multiple window functions can share the same sort pass when they use the same `PARTITION BY` and `ORDER BY` clause.

```sql
SELECT
  dept,
  month,
  sales,
  ROW_NUMBER() OVER (
    PARTITION BY dept
    ORDER BY month
  ) AS row_num,
  SUM(sales) OVER (
    PARTITION BY dept
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sales
FROM revenue;
```

Both window functions use the same partitioning and ordering. MariaDB can reuse the same sorted stream for both.

### Practical Tuning Tips

* Expect `GROUP BY` to shape the input seen by window functions.
* Align `GROUP BY` keys with the window sort keys when possible.
* Reuse the same `PARTITION BY` and `ORDER BY` across multiple window functions.
* Check the execution plan to see whether an extra sort is still present.

## Examples

Given the following data:

```sql
CREATE TABLE student (
  name CHAR(10),
  test CHAR(10),
  score TINYINT
);

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

### Average by Test

```sql
SELECT
  name,
  test,
  score,
  AVG(score) OVER (PARTITION BY test) AS avg_by_test
FROM student
ORDER BY test, name;
```

```
+---------+--------+-------+-------------+
| name    | test   | score | avg_by_test |
+---------+--------+-------+-------------+
| Chun    | SQL    |    75 |     65.2500 |
| Esben   | SQL    |    43 |     65.2500 |
| Kaolin  | SQL    |    56 |     65.2500 |
| Tatiana | SQL    |    87 |     65.2500 |
| Chun    | Tuning |    73 |     68.7500 |
| Esben   | Tuning |    31 |     68.7500 |
| Kaolin  | Tuning |    88 |     68.7500 |
| Tatiana | Tuning |    83 |     68.7500 |
+---------+--------+-------+-------------+
```

### Running Total by Score

```sql
SELECT
  name,
  score,
  SUM(score) OVER (
    ORDER BY score
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM student
ORDER BY score;
```

```
+---------+-------+---------------+
| name    | score | running_total |
+---------+-------+---------------+
| Esben   |    31 |            31 |
| Esben   |    43 |            74 |
| Kaolin  |    56 |           130 |
| Chun    |    73 |           203 |
| Chun    |    75 |           278 |
| Tatiana |    83 |           361 |
| Tatiana |    87 |           448 |
| Kaolin  |    88 |           536 |
+---------+-------+---------------+
```

### Top 2 Scores per Test

```sql
WITH ranked AS (
  SELECT
    name,
    test,
    score,
    RANK() OVER (PARTITION BY test ORDER BY score DESC) AS rnk
  FROM student
)
SELECT
  name,
  test,
  score,
  rnk
FROM ranked
WHERE rnk <= 2
ORDER BY test, rnk, name;
```

```
+---------+--------+-------+-----+
| name    | test   | score | rnk |
+---------+--------+-------+-----+
| Tatiana | SQL    |    87 |   1 |
| Chun    | SQL    |    75 |   2 |
| Kaolin  | Tuning |    88 |   1 |
| Tatiana | Tuning |    83 |   2 |
+---------+--------+-------+-----+
```

## MariaDB Support and Limitations

MariaDB:

* Supports `ROWS` and `RANGE` frames.
* Supports all frame bounds, including `RANGE n PRECEDING` and `RANGE n FOLLOWING`.
* Does not support `GROUPS` frames.
* Does not support frame exclusion.
* Does not support explicit `NULLS FIRST` or `NULLS LAST`.
* Does not support `DATE` or `DATETIME` arithmetic for `RANGE` frames.
* Does not support `DISTINCT` for aggregate window functions.

## See Also

* [Window Frames](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/window-frames)
* [Aggregate Functions as Window Functions](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/broken-reference)
* [ROW\_NUMBER](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/row_number)
* [RANK](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/rank)
* [AVG](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/avg)
* [SUM](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/sum)
* [Introduction to Window Functions in MariaDB Server 10.2](https://mariadb.com/resources/blog/introduction-window-functions-mariadb-server-102)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}