Window functions

How it works

A window function operates over a "window" of rows defined by the OVER clause. For each row in the result, the function looks at the rows in its window, computes a value, and attaches that value to the current row.

This is the key difference from GROUP BY:

-- GROUP BY collapses rows: one output row per country
SELECT country, SUM(profit) AS total
FROM test_window
GROUP BY country;
-- Returns 3 rows (one per country)

-- Window function preserves rows: one output row per input row
SELECT country, SUM(profit) OVER (PARTITION BY country) AS total
FROM test_window;
-- Returns 7 rows (one per input row, with the country total attached)

Supported functions

Window functions fall into two categories.

Aggregate functions used as window functions (require the OVER clause):

Dedicated window functions (can only be used with OVER):

For full parameter signatures of each dedicated function, see Window function descriptions.

Syntax

FUNCTION OVER ([[PARTITION BY column1] [ORDER BY column2] [RANGE|ROWS BETWEEN start AND end]])

Window frames

The BETWEEN start AND end clause defines the boundaries of the window frame. Use ROWS to define the frame by physical row positions, or RANGE to define it by value range relative to the current row's sort value.

Valid values for `start`:

Valid values for `end`:

Limitations

• Window functions can only appear in the SELECT list.

• An aggregate function and a window function cannot coexist in the same SELECT list unless the aggregate function itself uses an OVER clause. To combine them, wrap the window function in a subquery:

  -- Invalid: SUM(NAME) has no OVER clause
  SELECT SUM(NAME), COUNT() OVER(...) FROM <table_name>
  
  -- Valid: move the window function into a subquery
  SELECT SUM(NAME), WIN1
  FROM (SELECT NAME, COUNT() OVER(...) AS WIN1 FROM <table_name>) alias

• The PARTITION BY and ORDER BY clauses inside an OVER clause do not support complex expressions. Reference columns directly.

Examples

The following examples use this table:

CREATE TABLE test_window (
    year    INT          NOT NULL,
    country VARCHAR(50)  NOT NULL,
    product VARCHAR(100) NOT NULL,
    profit  INT          NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test_window (year, country, product, profit) VALUES
(2001, 'Finland',   'Phone',      10),
(2000, 'Finland',   'Computer',   1500),
(2001, 'USA',       'Calculator', 50),
(2001, 'USA',       'Computer',   1500),
(2000, 'Singapore', 'Calculator', 75),
(2000, 'Singapore', 'Calculator', 75),
(2001, 'Singapore', 'Calculator', 79);

SELECT
    country,
    SUM(profit) OVER (PARTITION BY country) AS sum_profit
FROM test_window;

+-----------+------------+
| country   | sum_profit |
+-----------+------------+
| Finland   |       1510 |
| Finland   |       1510 |
| USA       |       1550 |
| USA       |       1550 |
| Singapore |        229 |
| Singapore |        229 |
| Singapore |        229 |
+-----------+------------+

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

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

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

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