Understand Window Functions for Advanced Data Analysis - AnalyticDB for MySQL

Window functions compute values across a set of rows related to the current row without collapsing those rows into a single output row. Use them for group rankings, moving averages, cumulative sums, and row-to-row comparisons. Window functions run after the HAVING clause and before the ORDER BY clause. A window function is triggered by using an OVER clause to specify a window.

AnalyticDB for MySQL supports three categories of window functions: aggregate functions, sorting functions, and value functions. For aggregate functions used as window functions, see Aggregate functions.

Supported functions

Function	Category	Return type	Description
All aggregate functions	Aggregate	Varies	Aggregate functions work as window functions when combined with an `OVER` clause
`CUME_DIST()`	Sorting	DOUBLE	Cumulative distribution of each value within the partition
`RANK()`	Sorting	BIGINT	Rank with gaps for tied values
`DENSE_RANK()`	Sorting	BIGINT	Rank without gaps for tied values
`NTILE(n)`	Sorting	BIGINT	Distributes rows into `n` buckets
`ROW_NUMBER()`	Sorting	BIGINT	Sequential row number within the partition, starting from 1
`PERCENT_RANK()`	Sorting	DOUBLE	Rank as a percentage: `(r - 1) / (n - 1)`
`FIRST_VALUE(x)`	Value	Same as input	Value of the first row in the window partition
`LAST_VALUE(x)`	Value	Same as input	Value of the last row in the window frame
`LAG(x[, offset[, default]])`	Value	Same as input	Value from `offset` rows before the current row
`LEAD(x[, offset[, default]])`	Value	Same as input	Value from `offset` rows after the current row
`NTH_VALUE(x, offset)`	Value	Same as input	Value of the row at position `offset` within the window frame

Syntax

function_name OVER ([PARTITION BY expr] ORDER BY expr [RANGE|ROWS BETWEEN start AND end])

A window function call has three parts:

Partition rule (optional): Divides input rows into independent partitions, similar to GROUP BY. Each partition is processed separately.
Sorting rule: Determines the order in which rows are processed within each partition.
Window frame: Defines the subset of rows within the partition on which the function operates. The frame is anchored to the current row.

Window frame modes

The window frame can be defined in two modes:

Mode	Defines	Example use
`ROWS`	A fixed number of rows relative to the current row	Running totals, moving averages
`RANGE`	A range of values relative to the current row's value	Value-based sliding windows

Use BETWEEN start AND end to set the frame boundaries:

Boundary	Meaning
`CURRENT ROW`	The current row
`N PRECEDING`	`N` rows before the current row
`UNBOUNDED PRECEDING`	From the first row of the partition to the current row
`N FOLLOWING`	`N` rows after the current row
`UNBOUNDED FOLLOWING`	From the current row to the last row of the partition

The following diagram shows how boundaries relate to the current row within a partition:

      PARTITION
+─────────────────+  <- UNBOUNDED PRECEDING (start of partition)
|                 |
|=================|  <- N PRECEDING  -+
|   rows before   |                   |
|   current row   |                   |  FRAME
|~~~~~~~~~~~~~~~~~|  <- CURRENT ROW   |
|   rows after    |                   |
|   current row   |                   |
|=================|  <- N FOLLOWING  -+
|                 |
+─────────────────+  <- UNBOUNDED FOLLOWING (end of partition)

Default window frame behavior

LAST_VALUE: The default frame ends at CURRENT ROW, so by default LAST_VALUE returns the current row's value — not the last row in the partition. To get the actual last value, explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Always specify the window frame explicitly to avoid unexpected results.

Set up the example table

The examples in this topic use a testwindow table. Run the following statements to create and populate it:

CREATE TABLE testwindow (
  year    INT,
  country VARCHAR(20),
  product VARCHAR(20),
  profit  INT
) DISTRIBUTED BY HASH(year);

INSERT INTO testwindow VALUES (2000, 'Finland', 'Computer',   1500);
INSERT INTO testwindow VALUES (2001, 'Finland', 'Phone',        10);
INSERT INTO testwindow VALUES (2000, 'Germany', 'Calculator',   75);
INSERT INTO testwindow VALUES (2000, 'Germany', 'Calculator',   75);
INSERT INTO testwindow VALUES (2001, 'Germany', 'Calculator',   79);
INSERT INTO testwindow VALUES (2001, 'USA',     'Calculator',   50);
INSERT INTO testwindow VALUES (2001, 'USA',     'Computer',   1500);

Verify the data:

SELECT * FROM testwindow;

+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2000 | Germany | Calculator |     75 |
| 2000 | Germany | Calculator |     75 |
| 2001 | Germany | Calculator |     79 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
+------+---------+------------+--------+

Usage notes

General frame constraints

The following BETWEEN start AND end combinations are invalid:

Invalid combination	Error message
`start = UNBOUNDED FOLLOWING`	`Window frame start cannot be UNBOUNDED FOLLOWING`
`end = UNBOUNDED PRECEDING`	`Window frame end cannot be UNBOUNDED PRECEDING`
`start = CURRENT ROW`, `end = N PRECEDING`	`Window frame starting from CURRENT ROW cannot end with PRECEDING`
`start = N FOLLOWING`, `end = N PRECEDING`	`Window frame starting from FOLLOWING cannot end with PRECEDING`
`start = N FOLLOWING`, `end = CURRENT ROW`	`Window frame starting from FOLLOWING cannot end with CURRENT ROW`

RANGE mode constraints

In RANGE mode, only UNBOUNDED boundaries are supported:

Invalid combination	Error message
`start` or `end = N PRECEDING`	`Window frame RANGE PRECEDING is only supported with UNBOUNDED`
`start` or `end = N FOLLOWING`	`Window frame RANGE FOLLOWING is only supported with UNBOUNDED`

Aggregate functions

All aggregate functions become window functions when you add an OVER clause. The function computes its result over the rows in the current sliding window rather than collapsing all rows into one.

The following example computes a rolling sum of order prices by date for each clerk:

SELECT
  clerk,
  orderdate,
  orderkey,
  totalprice,
  SUM(totalprice) OVER (PARTITION BY clerk ORDER BY orderdate) AS rolling_sum
FROM orders
ORDER BY clerk, orderdate, orderkey;

The following example computes a running cumulative sum of profit within each country:

SELECT
  year,
  country,
  profit,
  SUM(profit) OVER (
    PARTITION BY country
    ORDER BY year
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM testwindow;

+------+---------+--------+---------------+
| year | country | profit | running_total |
+------+---------+--------+---------------+
| 2001 | USA     |     50 |            50 |
| 2001 | USA     |   1500 |          1550 |
| 2000 | Germany |     75 |            75 |
| 2000 | Germany |     75 |           150 |
| 2001 | Germany |     79 |           229 |
| 2000 | Finland |   1500 |          1500 |
| 2001 | Finland |     10 |          1510 |
+------+---------+--------+---------------+

Without ORDER BY and a frame clause, the aggregate covers the entire partition:

SELECT country, SUM(profit) OVER (PARTITION BY country) AS total_profit
FROM testwindow;

+---------+--------------+
| country | total_profit |
+---------+--------------+
| Germany |          229 |
| Germany |          229 |
| Germany |          229 |
| USA     |         1550 |
| USA     |         1550 |
| Finland |         1510 |
| Finland |         1510 |
+---------+--------------+

CUME_DIST

CUME_DIST()

Returns the cumulative distribution of each value within a partition: the fraction of rows with values less than or equal to the current row's value.

Return type: DOUBLE
Tied values receive the same distribution value.

Example:

SELECT
  year,
  country,
  product,
  profit,
  CUME_DIST() OVER (PARTITION BY country ORDER BY profit) AS cume_dist
FROM testwindow;

+------+---------+------------+--------+--------------------+
| year | country | product    | profit | cume_dist          |
+------+---------+------------+--------+--------------------+
| 2001 | USA     | Calculator |     50 |                0.5 |
| 2001 | USA     | Computer   |   1500 |                1.0 |
| 2001 | Finland | Phone      |     10 |                0.5 |
| 2000 | Finland | Computer   |   1500 |                1.0 |
| 2000 | Germany | Calculator |     75 | 0.6666666666666666 |
| 2000 | Germany | Calculator |     75 | 0.6666666666666666 |
| 2001 | Germany | Calculator |     79 |                1.0 |
+------+---------+------------+--------+--------------------+

RANK

RANK()

Returns the rank of each row within its partition, ordered by the ORDER BY expression. The rank equals the number of rows preceding the current row plus one. Tied values receive the same rank, and the next rank skips accordingly — producing gaps in the sequence.

Return type: BIGINT

Example:

SELECT
  year,
  country,
  product,
  profit,
  RANK() OVER (PARTITION BY country ORDER BY profit) AS rank
FROM testwindow;

+------+---------+------------+--------+------+
| year | country | product    | profit | rank |
+------+---------+------------+--------+------+
| 2001 | Finland | Phone      |     10 |    1 |
| 2000 | Finland | Computer   |   1500 |    2 |
| 2001 | USA     | Calculator |     50 |    1 |
| 2001 | USA     | Computer   |   1500 |    2 |
| 2000 | Germany | Calculator |     75 |    1 |
| 2000 | Germany | Calculator |     75 |    1 |
| 2001 | Germany | Calculator |     79 |    3 |
+------+---------+------------+--------+------+

In the Germany partition, the two rows with profit = 75 both receive rank 1, and the next row jumps to rank 3.

DENSE_RANK

DENSE_RANK()

Returns the rank of each row within its partition. Unlike RANK(), tied values do not produce gaps — the next rank always follows consecutively.

Return type: BIGINT

Example:

SELECT
  year,
  country,
  product,
  profit,
  DENSE_RANK() OVER (PARTITION BY country ORDER BY profit) AS dense_rank
FROM testwindow;

+------+---------+------------+--------+------------+
| year | country | product    | profit | dense_rank |
+------+---------+------------+--------+------------+
| 2001 | Finland | Phone      |     10 |          1 |
| 2000 | Finland | Computer   |   1500 |          2 |
| 2001 | USA     | Calculator |     50 |          1 |
| 2001 | USA     | Computer   |   1500 |          2 |
| 2000 | Germany | Calculator |     75 |          1 |
| 2000 | Germany | Calculator |     75 |          1 |
| 2001 | Germany | Calculator |     79 |          2 |
+------+---------+------------+--------+------------+

In the Germany partition, the two rows with profit = 75 both receive rank 1, and the next row receives rank 2 (no gap).

NTILE

NTILE(n)

Divides rows within each partition into n buckets, numbered 1 through n. If the rows do not divide evenly, the extra rows are distributed one per bucket starting from bucket 1.

For example, 6 rows into 4 buckets: 1, 1, 2, 2, 3, 4.

Return type: BIGINT

Example (2 buckets per country):

SELECT
  year,
  country,
  product,
  profit,
  NTILE(2) OVER (PARTITION BY country ORDER BY profit) AS bucket
FROM testwindow;

+------+---------+------------+--------+--------+
| year | country | product    | profit | bucket |
+------+---------+------------+--------+--------+
| 2001 | USA     | Calculator |     50 |      1 |
| 2001 | USA     | Computer   |   1500 |      2 |
| 2001 | Finland | Phone      |     10 |      1 |
| 2000 | Finland | Computer   |   1500 |      2 |
| 2000 | Germany | Calculator |     75 |      1 |
| 2000 | Germany | Calculator |     75 |      1 |
| 2001 | Germany | Calculator |     79 |      2 |
+------+---------+------------+--------+--------+

ROW_NUMBER

ROW_NUMBER()

Assigns a unique sequential integer to each row within its partition, starting from 1. Unlike RANK(), no two rows share the same number.

Return type: BIGINT

Example:

SELECT
  year,
  country,
  product,
  profit,
  ROW_NUMBER() OVER (PARTITION BY country) AS row_num
FROM testwindow;

+------+---------+------------+--------+---------+
| year | country | product    | profit | row_num |
+------+---------+------------+--------+---------+
| 2001 | USA     | Calculator |     50 |       1 |
| 2001 | USA     | Computer   |   1500 |       2 |
| 2000 | Germany | Calculator |     75 |       1 |
| 2000 | Germany | Calculator |     75 |       2 |
| 2001 | Germany | Calculator |     79 |       3 |
| 2000 | Finland | Computer   |   1500 |       1 |
| 2001 | Finland | Phone      |     10 |       2 |
+------+---------+------------+--------+---------+

PERCENT_RANK

PERCENT_RANK()

Returns the relative rank of each row as a value between 0 and 1, using the formula (r - 1) / (n - 1), where r is the RANK() of the current row and n is the total number of rows in the partition.

Return type: DOUBLE

Example:

SELECT
  year,
  country,
  product,
  profit,
  PERCENT_RANK() OVER (PARTITION BY country ORDER BY profit) AS pct_rank
FROM testwindow;

+------+---------+------------+--------+----------+
| year | country | product    | profit | pct_rank |
+------+---------+------------+--------+----------+
| 2001 | Finland | Phone      |     10 |      0.0 |
| 2000 | Finland | Computer   |   1500 |      1.0 |
| 2001 | USA     | Calculator |     50 |      0.0 |
| 2001 | USA     | Computer   |   1500 |      1.0 |
| 2000 | Germany | Calculator |     75 |      0.0 |
| 2000 | Germany | Calculator |     75 |      0.0 |
| 2001 | Germany | Calculator |     79 |      1.0 |
+------+---------+------------+--------+----------+

FIRST_VALUE

FIRST_VALUE(x)

Returns the value of the first row within the window partition.

Return type: Same as the input argument type

Example:

SELECT
  year,
  country,
  product,
  profit,
  FIRST_VALUE(profit) OVER (PARTITION BY country ORDER BY profit) AS first_profit
FROM testwindow;

+------+---------+------------+--------+--------------+
| year | country | product    | profit | first_profit |
+------+---------+------------+--------+--------------+
| 2000 | Germany | Calculator |     75 |           75 |
| 2000 | Germany | Calculator |     75 |           75 |
| 2001 | Germany | Calculator |     79 |           75 |
| 2001 | USA     | Calculator |     50 |           50 |
| 2001 | USA     | Computer   |   1500 |           50 |
| 2001 | Finland | Phone      |     10 |           10 |
| 2000 | Finland | Computer   |   1500 |           10 |
+------+---------+------------+--------+--------------+

LAST_VALUE

LAST_VALUE(x)

Returns the value of the last row within the window frame. The default window frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so by default LAST_VALUE returns the current row's value, not the last row in the partition.

To return the actual last value in the partition, add an explicit frame clause:

LAST_VALUE(x) OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Return type: Same as the input argument type

Example 1 — default frame (returns current row's value):

SELECT
  year,
  country,
  product,
  profit,
  LAST_VALUE(profit) OVER (PARTITION BY country ORDER BY profit) AS last_val
FROM testwindow;

+------+---------+------------+--------+----------+
| year | country | product    | profit | last_val |
+------+---------+------------+--------+----------+
| 2001 | USA     | Calculator |     50 |       50 |
| 2001 | USA     | Computer   |   1500 |     1500 |
| 2001 | Finland | Phone      |     10 |       10 |
| 2000 | Finland | Computer   |   1500 |     1500 |
| 2000 | Germany | Calculator |     75 |       75 |
| 2000 | Germany | Calculator |     75 |       75 |
| 2001 | Germany | Calculator |     79 |       79 |
+------+---------+------------+--------+----------+

Example 2 — explicit full-partition frame (returns last row's value):

SELECT
  year,
  country,
  product,
  profit,
  LAST_VALUE(profit) OVER (
    PARTITION BY country
    ORDER BY profit
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_val
FROM testwindow;

+------+---------+------------+--------+----------+
| year | country | product    | profit | last_val |
+------+---------+------------+--------+----------+
| 2001 | Finland | Phone      |     10 |     1500 |
| 2000 | Finland | Computer   |   1500 |     1500 |
| 2000 | Germany | Calculator |     75 |       79 |
| 2000 | Germany | Calculator |     75 |       79 |
| 2001 | Germany | Calculator |     79 |       79 |
| 2001 | USA     | Calculator |     50 |     1500 |
| 2001 | USA     | Computer   |   1500 |     1500 |
+------+---------+------------+--------+----------+

LAG

LAG(x[, offset[, default_value]])

Returns the value from the row that is offset rows before the current row within the partition.

Parameter	Description	Default
`x`	The column or expression to evaluate	—
`offset`	Number of rows to look back; `0` refers to the current row	`1`
`default_value`	Value returned when `offset` is `NULL` or exceeds the partition size	`NULL`

Return type: Same as the input argument type

Example (look back 1 row):

SELECT
  year,
  country,
  product,
  profit,
  LAG(profit) OVER (PARTITION BY country ORDER BY profit) AS prev_profit
FROM testwindow;

+------+---------+------------+--------+-------------+
| year | country | product    | profit | prev_profit |
+------+---------+------------+--------+-------------+
| 2001 | USA     | Calculator |     50 |        NULL |
| 2001 | USA     | Computer   |   1500 |          50 |
| 2000 | Germany | Calculator |     75 |        NULL |
| 2000 | Germany | Calculator |     75 |          75 |
| 2001 | Germany | Calculator |     79 |          75 |
| 2001 | Finland | Phone      |     10 |        NULL |
| 2000 | Finland | Computer   |   1500 |          10 |
+------+---------+------------+--------+-------------+

LEAD

LEAD(x[, offset[, default_value]])

Returns the value from the row that is offset rows after the current row within the partition.

Parameter	Description	Default
`x`	The column or expression to evaluate	—
`offset`	Number of rows to look ahead; `0` refers to the current row	`1`
`default_value`	Value returned when `offset` is `NULL` or exceeds the partition size	`NULL`

Return type: Same as the input argument type

Example (look ahead 1 row):

SELECT
  year,
  country,
  product,
  profit,
  LEAD(profit) OVER (PARTITION BY country ORDER BY profit) AS next_profit
FROM testwindow;

+------+---------+------------+--------+-------------+
| year | country | product    | profit | next_profit |
+------+---------+------------+--------+-------------+
| 2000 | Germany | Calculator |     75 |          75 |
| 2000 | Germany | Calculator |     75 |          79 |
| 2001 | Germany | Calculator |     79 |        NULL |
| 2001 | Finland | Phone      |     10 |        1500 |
| 2000 | Finland | Computer   |   1500 |        NULL |
| 2001 | USA     | Calculator |     50 |        1500 |
| 2001 | USA     | Computer   |   1500 |        NULL |
+------+---------+------------+--------+-------------+

NTH_VALUE

NTH_VALUE(x, offset)

Returns the value of the row at position offset within the window frame. The offset starts from 1.

If offset is NULL or exceeds the number of rows in the frame, NULL is returned.
If offset is 0 or negative, an error is returned.
Return type: Same as the input argument type

Example (first row in each partition, equivalent to FIRST_VALUE):

SELECT
  year,
  country,
  product,
  profit,
  NTH_VALUE(profit, 1) OVER (PARTITION BY country ORDER BY profit) AS nth_val
FROM testwindow;

+------+---------+------------+--------+---------+
| year | country | product    | profit | nth_val |
+------+---------+------------+--------+---------+
| 2001 | Finland | Phone      |     10 |      10 |
| 2000 | Finland | Computer   |   1500 |      10 |
| 2001 | USA     | Calculator |     50 |      50 |
| 2001 | USA     | Computer   |   1500 |      50 |
| 2000 | Germany | Calculator |     75 |      75 |
| 2000 | Germany | Calculator |     75 |      75 |
| 2001 | Germany | Calculator |     79 |      75 |
+------+---------+------------+--------+---------+

What's next

Aggregate functions