What is Window Function in MySQL 8: Simple Explanation and Example

A window function in MySQL 8 performs calculations across a set of table rows related to the current row without collapsing the result into a single output row. It allows you to compute running totals, ranks, or moving averages while keeping all original rows visible.

How It Works

Think of a window function like looking through a small window at a group of rows around the current row. Instead of combining rows into one result, it calculates values using the rows in that window but still shows each row separately.

For example, if you want to know the rank of each student in a class based on their score, a window function can assign ranks while keeping all students listed. It works by defining a window frame, which is a set of rows related to the current row, and then applying a function like ROW_NUMBER() or SUM() over that frame.

Example

This example shows how to use the ROW_NUMBER() window function to assign a rank to employees based on their salary within each department.

mysql

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees VALUES
(1, 'Alice', 'Sales', 5000),
(2, 'Bob', 'Sales', 6000),
(3, 'Charlie', 'HR', 5500),
(4, 'Diana', 'HR', 5200),
(5, 'Eve', 'Sales', 4800);

SELECT
  id,
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

Output

id | name    | department | salary | rank
---|---------|------------|--------|-----
2  | Bob     | Sales      | 6000   | 1
1  | Alice   | Sales      | 5000   | 2
5  | Eve     | Sales      | 4800   | 3
3  | Charlie | HR         | 5500   | 1
4  | Diana   | HR         | 5200   | 2

When to Use

Use window functions when you need to perform calculations across rows related to the current row without losing the individual row details. They are perfect for tasks like ranking items, calculating running totals, finding moving averages, or comparing values within groups.

For example, in sales reports, you might want to see each sale along with the total sales so far that day. Window functions let you do this easily without grouping and losing the individual sale records.

Key Points

• Window functions calculate values across a set of rows related to the current row.
• They do not reduce the number of rows returned.
• Common window functions include ROW_NUMBER(), RANK(), SUM(), and AVG().
• They use OVER() clause to define the window frame.
• Introduced in MySQL 8, they simplify complex queries involving ranking and running totals.

Key Takeaways

Window functions perform calculations across related rows without collapsing results.

They keep all original rows visible while adding computed columns like rank or running total.

Use the OVER() clause to specify how rows are grouped and ordered for the calculation.

MySQL 8 introduced window functions to simplify advanced data analysis tasks.

Common uses include ranking, running totals, and moving averages within groups.