MySQL Named Window: Define and Reuse Named Windows

When several window functions use the same OVER (...) specification, repeating it is noisy and error-prone. The WINDOW clause gives it a name.

query.sql

SQL

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    manager_id INT NULL,
    hired_on DATE NOT NULL
);

INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
    ('Asha Rao',     'management',  150000.00, NULL, '2020-01-15'),
    ('Ben Carter',   'engineering', 120000.00,    1, '2021-03-01'),
    ('Chitra Nair',  'engineering',  95000.00,    2, '2022-07-11'),
    ('Dev Malhotra', 'engineering',  88000.00,    2, '2023-01-09'),
    ('Esha Gupta',   'sales',        70000.00,    1, '2021-09-20'),
    ('Farid Khan',   'sales',        64000.00,    5, '2022-11-02'),
    ('Gita Menon',   'sales',        64000.00,    5, '2024-02-14'),
    ('Hari Iyer',    'support',      52000.00,    1, '2023-05-30');

Without a name: repetition

query.sql

SQL

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS position_in_department,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department,
       SUM(salary)  OVER (PARTITION BY department ORDER BY salary DESC) AS cumulative_salary
FROM employees
ORDER BY department, position_in_department;

Output

OUTPUT

+--------------+-------------+-----------+------------------------+--------------------+-------------------+
| name         | department  | salary    | position_in_department | rank_in_department | cumulative_salary |
+--------------+-------------+-----------+------------------------+--------------------+-------------------+
| Ben Carter   | engineering | 120000.00 |                      1 |                  1 |         120000.00 |
| Chitra Nair  | engineering |  95000.00 |                      2 |                  2 |         215000.00 |
| Dev Malhotra | engineering |  88000.00 |                      3 |                  3 |         303000.00 |
| Asha Rao     | management  | 150000.00 |                      1 |                  1 |         150000.00 |
| Esha Gupta   | sales       |  70000.00 |                      1 |                  1 |          70000.00 |
| Farid Khan   | sales       |  64000.00 |                      2 |                  2 |         198000.00 |
| Gita Menon   | sales       |  64000.00 |                      3 |                  2 |         198000.00 |
| Hari Iyer    | support     |  52000.00 |                      1 |                  1 |          52000.00 |
+--------------+-------------+-----------+------------------------+--------------------+-------------------+
8 rows in set

With a named window

query.sql

SQL

SELECT name, department, salary,
       ROW_NUMBER() OVER by_department AS position_in_department,
       RANK()       OVER by_department AS rank_in_department,
       SUM(salary)  OVER by_department AS cumulative_salary
FROM employees
WINDOW by_department AS (PARTITION BY department ORDER BY salary DESC)
ORDER BY department, position_in_department;

Output

OUTPUT

+--------------+-------------+-----------+------------------------+--------------------+-------------------+
| name         | department  | salary    | position_in_department | rank_in_department | cumulative_salary |
+--------------+-------------+-----------+------------------------+--------------------+-------------------+
| Ben Carter   | engineering | 120000.00 |                      1 |                  1 |         120000.00 |
| Chitra Nair  | engineering |  95000.00 |                      2 |                  2 |         215000.00 |
| Dev Malhotra | engineering |  88000.00 |                      3 |                  3 |         303000.00 |
| Asha Rao     | management  | 150000.00 |                      1 |                  1 |         150000.00 |
| Esha Gupta   | sales       |  70000.00 |                      1 |                  1 |          70000.00 |
| Farid Khan   | sales       |  64000.00 |                      2 |                  2 |         198000.00 |
| Gita Menon   | sales       |  64000.00 |                      3 |                  2 |         198000.00 |
| Hari Iyer    | support     |  52000.00 |                      1 |                  1 |          52000.00 |
+--------------+-------------+-----------+------------------------+--------------------+-------------------+
8 rows in set

The same result. The WINDOW clause comes after HAVING and before ORDER BY. Now the window is defined once.

Several windows

You can define more than one, separated by commas:

query.sql

SQL

SELECT name, department, salary,
       RANK() OVER company_wide   AS company_rank,
       RANK() OVER in_department  AS department_rank
FROM employees
WINDOW company_wide AS (ORDER BY salary DESC),
       in_department AS (PARTITION BY department ORDER BY salary DESC)
ORDER BY company_rank, name;

Output

OUTPUT

+--------------+-------------+-----------+--------------+-----------------+
| name         | department  | salary    | company_rank | department_rank |
+--------------+-------------+-----------+--------------+-----------------+
| Asha Rao     | management  | 150000.00 |            1 |               1 |
| Ben Carter   | engineering | 120000.00 |            2 |               1 |
| Chitra Nair  | engineering |  95000.00 |            3 |               2 |
| Dev Malhotra | engineering |  88000.00 |            4 |               3 |
| Esha Gupta   | sales       |  70000.00 |            5 |               1 |
| Farid Khan   | sales       |  64000.00 |            6 |               2 |
| Gita Menon   | sales       |  64000.00 |            6 |               2 |
| Hari Iyer    | support     |  52000.00 |            8 |               1 |
+--------------+-------------+-----------+--------------+-----------------+
8 rows in set

Building on a window (inheritance)

A window definition can start from another window and add to it. The base window gives the partition and order, and you may add a frame:

query.sql

SQL

SELECT name, hired_on, salary,
       SUM(salary) OVER (w)                                       AS running_payroll,
       AVG(salary) OVER (w ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS average_of_last_two
FROM employees
WINDOW w AS (ORDER BY hired_on, id)
ORDER BY hired_on, id;

Output

OUTPUT

+--------------+------------+-----------+-----------------+---------------------+
| name         | hired_on   | salary    | running_payroll | average_of_last_two |
+--------------+------------+-----------+-----------------+---------------------+
| Asha Rao     | 2020-01-15 | 150000.00 |       150000.00 |       150000.000000 |
| Ben Carter   | 2021-03-01 | 120000.00 |       270000.00 |       135000.000000 |
| Esha Gupta   | 2021-09-20 |  70000.00 |       340000.00 |        95000.000000 |
| Chitra Nair  | 2022-07-11 |  95000.00 |       435000.00 |        82500.000000 |
| Farid Khan   | 2022-11-02 |  64000.00 |       499000.00 |        79500.000000 |
| Dev Malhotra | 2023-01-09 |  88000.00 |       587000.00 |        76000.000000 |
| Hari Iyer    | 2023-05-30 |  52000.00 |       639000.00 |        70000.000000 |
| Gita Menon   | 2024-02-14 |  64000.00 |       703000.00 |        58000.000000 |
+--------------+------------+-----------+-----------------+---------------------+
8 rows in set

OVER (w) uses w as it is. OVER (w ROWS BETWEEN ...) adds a frame to it.

The rules for inheritance:

A window that extends another cannot define its own PARTITION BY.
It can add an ORDER BY only if the base window has none.
It cannot change a frame that the base already has.

Windows with the same definition are computed together

When different functions use the same window, MySQL sorts the rows once and calculates all of them in one pass. That is another reason to share windows: it is faster than several different windows, each of which needs its own sort.

Where names are visible

A window name is visible only within the query block where it is defined. A CTE or subquery needs its own WINDOW clause.

Style

Name windows by what they do: by_department, company_wide, monthly.
Use a named window as soon as you repeat the same OVER (...) twice.
Keep the WINDOW clause next to the select list when the query is short, and use a CTE for complicated ones.

lightbulb

Spot repetition

If you copy and paste PARTITION BY ... ORDER BY ... for the second time in one query, move it to a WINDOW clause.