Window Functions Overview

Understand window function concepts. These functions operate on a set of rows (window) defined by an OVER clause, returning a value for each row without collapsing results.

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

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:

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.

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

This returns one row per test.

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.

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.

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.

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, DENSE_RANK, FIRST_VALUE, LAG, LAST_VALUE, LEAD, MEDIAN, NTH_VALUE, NTILE, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, RANK, and ROW_NUMBER.

Aggregate functions that also work with OVER (...) include AVG, BIT_AND, BIT_OR, BIT_XOR, COUNT, MAX, MIN, STD, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, and VARIANCE.

Aggregate window functions do not support DISTINCT.

Aggregate Functions as Window Functions

It is possible to use aggregate functions as window functions. An aggregate function used as a window function must have the OVER clause. For example, here's COUNT() used as a window function:

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.

Window functions are evaluated after WHERE, GROUP BY, and HAVING. Filter the computed result in an outer query or CTE.

Optimization

Window function evaluation invokes a sort pass per group of compatible windows. Each such pass appears as a filesort node in the execution plan. Query shape decides how many passes are needed and how cheap each one is to perform.

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.

When tuning this pattern, optimize the GROUP BY step first. Then check EXPLAIN FORMAT=JSON to confirm the window step's sort cost is low.

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, the window's sort pass cannot reuse the grouped output's order — it has to re-sort the rows. Aligning the keys keeps the sort cheap; misaligning them forces a full sort.

For example, this shape can require an extra sort:

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 their PARTITION BY and ORDER BY clauses are compatible. Two specs are compatible if they are identical, or if one is a prefix of the other — the longer specification then becomes the canonical sort order.

Identical specs:

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 reuses the same sorted stream for both — one filesort pass.

Prefix-compatible specs:

SELECT
  dept,
  month,
  day,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY month)      AS rn,
  SUM(sales)   OVER (PARTITION BY dept ORDER BY month, day) AS running_sales
FROM revenue;

The first window's order key (dept, month) is a prefix of the second's (dept, month, day). MariaDB sorts once by the longer key (dept, month, day) and both window functions consume the result.

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 — the sort still runs, but it is cheap when the data is already in the right order.

• Reuse the same PARTITION BY and ORDER BY across multiple window functions, or make one a prefix of another, so MariaDB can collapse them into a single sort pass.

• Use EXPLAIN FORMAT=JSON (or ANALYZE FORMAT=JSON for real timings) to count the window-function sort passes — each pass appears as a filesort node under the aggregation step.

Examples

Given the following data:

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

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

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

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

• Aggregate Functions as Window Functions

• ROW_NUMBER

• RANK

• AVG

• SUM

• Introduction to Window Functions in MariaDB Server 10.2

_{This page is licensed: CC BY-SA / Gnu FDL}