Description\n-----------\n\nWindow Functions\n\nWindow functions calculate across related rows without collapsing them. Unlike GROUP BY, they return one result for each input row.\n\nWhen to Use Window Functions\n\nUse window functions when you need to:\n\n Rank rows inside a group.\n Calculate running totals or moving averages.\n Compare a row with earlier or later rows.\n Return the top _N_ rows per group.\n\nBasic Syntax\n\n``sql\nwindow_function(expr) OVER (\n  [PARTITION BY expr [, ...]]\n  [ORDER BY expr [ASC | DESC] [, ...]]\n  [{ROWS | RANGE} frame_clause]\n)\n\nframe_clause:\n  {frame_border | BETWEEN frame_border AND frame_border}\n\nframe_border:\n  UNBOUNDED PRECEDING\n  | UNBOUNDED FOLLOWING\n  | CURRENT ROW\n  | expr PRECEDING\n  | expr FOLLOWING\n`\n\nYou can also define a named window and reuse it:\n\n`sql\nSELECT\n  SUM(score) OVER w AS running_total,\n  AVG(score) OVER w AS running_avg\nFROM student\nWINDOW w AS (\n  ORDER BY score\n  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n);\n`\n\nWindow Functions vs GROUP BY\n\nUse GROUP BY when you want one output row per group.\n\nUse a window function when you want to keep the original rows and add per-group or per-sequence calculations beside them.\n\n`sql\nSELECT test, AVG(score)\nFROM student\nGROUP BY test;\n`\n\nThis returns one row per test.\n\n`sql\nSELECT\n  name,\n  test,\n  score,\n  AVG(score) OVER (PARTITION BY test) AS avg_by_test\nFROM student;\n`\n\nThis returns every row, plus the average for that row's test.\n\nHow OVER Works\n\nPARTITION BY\n\nPARTITION BY starts a new calculation for each group.\n\nORDER BY\n\nORDER BY defines the row sequence inside each partition.\n\nFrame\n\nThe frame controls which rows contribute to the current result. Aggregate window functions use frames. Ranking functions such as ROW_NUMBER() and RANK() do not.\n\nOVER () uses the whole result set.\n\nFor aggregate window functions, OVER (ORDER BY ...) uses a running frame by default. In MariaDB, that default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.\n\nROWS vs RANGE\n\nROWS counts physical rows. RANGE groups peer rows that share the same ORDER BY value.\n\nUse ROWS when you need strict row-by-row stepping. Use RANGE when ties should be treated as one peer group.\n\n`sql\nCREATE TABLE t (score INT);\nINSERT INTO t VALUES (10), (10), (20);\n\nSELECT\n  score,\n  SUM(score) OVER (\n    ORDER BY score\n    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n  ) AS rows_sum,\n  SUM(score) OVER (\n    ORDER BY score\n    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n  ) AS range_sum\nFROM t;\n`\n\nThis produces different results for the duplicate 10 values:\n\n`\n+-------+----------+-----------+\n| score | rows_sum | range_sum |\n+-------+----------+-----------+\n|    10 |       10 |        20 |\n|    10 |       20 |        20 |\n|    20 |       40 |        40 |\n+-------+----------+-----------+\n`\n\nSupported Functions\n\nDedicated 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.\n\nAggregate 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.\n\nAggregate window functions do not support DISTINCT.\n\nAggregate Functions as Window Functions\n\nIt 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:\n\n`sql\nSELECT COUNT() OVER (ORDER BY column) FROM table;\n`\n\nCommon Pitfalls\n\n You cannot reference a window function in WHERE.\n Compute the window result in a subquery or CTE first.\n MariaDB does not support GROUPS frames.\n MariaDB does not support frame exclusion.\n MariaDB does not support explicit NULLS FIRST or NULLS LAST.\n RANGE frames do not support DATE or DATETIME arithmetic.\n\nWindow functions are evaluated after WHERE, GROUP BY, and HAVING. Filter the computed result in an outer query or CTE.\n\nOptimization\n\nWindow functions often need sorted input. Query shape can decide whether MariaDB can reuse an existing order or must sort again.\n\nGROUP BY Comes First\n\nIf a query uses both GROUP BY and window functions, MariaDB executes the grouping step first. The window step runs on the grouped result.\n\nThis affects index usage. An index that helps the base table scan does not automatically avoid later sorting for the window stage.\n\nWhen tuning this pattern, optimize the GROUP BY step first. Then check whether the window step still needs its own sort.\n\nSort Reuse Depends on Sort Keys\n\nIf 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.\n\nFor example, this shape can require an extra sort:\n\n`sql\nSELECT\n  dept,\n  month,\n  SUM(sales) AS monthly_sales,\n  ROW_NUMBER() OVER (\n    PARTITION BY dept\n    ORDER BY month\n  ) AS row_num\nFROM revenue\nGROUP BY month, dept;\n`\n\nThe grouped result is ordered by month, dept. The window step needs dept, month. Those orders do not match.\n\nMultiple Window Functions Can Share One Sort\n\nMultiple window functions can share the same sort pass when they use the same PARTITION BY and ORDER BY clause.\n\n`sql\nSELECT\n  dept,\n  month,\n  sales,\n  ROW_NUMBER() OVER (\n    PARTITION BY dept\n    ORDER BY month\n  ) AS row_num,\n  SUM(sales) OVER (\n    PARTITION BY dept\n    ORDER BY month\n    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n  ) AS running_sales\nFROM revenue;\n`\n\nBoth window functions use the same partitioning and ordering. MariaDB can reuse the same sorted stream for both.\n\nPractical Tuning Tips\n\n Expect GROUP BY to shape the input seen by window functions.\n Align GROUP BY keys with the window sort keys when possible.\n Reuse the same PARTITION BY and ORDER BY` across multiple window functions.\n* Check the execution plan to see whether an extra sort is still present.\n\nExamples\n--------\n\nCREATE TABLE student (\n  name CHAR(10),\n  test CHAR(10),\n  score TINYINT\n);\n\nINSERT INTO student VALUES\n  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),\n  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),\n  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),\n  ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);\n\nURL: https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/window-functions-overview