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 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.

When tuning this pattern, optimize the GROUP BY step first. Then check whether the window step still needs its own sort.

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:

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.

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:

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}