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 FOLLOWINGYou can also define a named window and reuse it:
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.
This returns one row per test.
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.
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.
This produces different results for the duplicate 10 values:
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:
Common Pitfalls
You cannot reference a window function in
WHERE.Compute the window result in a subquery or CTE first.
MariaDB does not support
GROUPSframes.MariaDB does not support frame exclusion.
MariaDB does not support explicit
NULLS FIRSTorNULLS LAST.RANGEframes do not supportDATEorDATETIMEarithmetic.
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.
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:
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:
Both window functions use the same partitioning and ordering. MariaDB reuses the same sorted stream for both — one filesort pass.
Prefix-compatible specs:
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 BYto shape the input seen by window functions.Align
GROUP BYkeys 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 BYandORDER BYacross multiple window functions, or make one a prefix of another, so MariaDB can collapse them into a single sort pass.Use
EXPLAIN FORMAT=JSON(orANALYZE FORMAT=JSONfor real timings) to count the window-function sort passes — each pass appears as afilesortnode under the aggregation step.
Examples
Given the following data:
Average by Test
Running Total by Score
Top 2 Scores per Test
MariaDB Support and Limitations
MariaDB:
Supports
ROWSandRANGEframes.Supports all frame bounds, including
RANGE n PRECEDINGandRANGE n FOLLOWING.Does not support
GROUPSframes.Does not support frame exclusion.
Does not support explicit
NULLS FIRSTorNULLS LAST.Does not support
DATEorDATETIMEarithmetic forRANGEframes.Does not support
DISTINCTfor aggregate window functions.
See Also
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?

