Master MySQL 8.0 Window Functions: Yearly Averages and Rankings Made Easy

1. Problem

MySQL 8.0.2 added a major feature – window functions .

This feature solves what problem? First look at a typical SQL query scenario, see how we normally do it, then see how window functions make it easier.

(1) Prepare test table and data

Create a simple movies table with fields:

ID

release_year (release year)

category_id (category ID)

rating (score)

CREATE TABLE films (
  id int(11),
  release_year int(11),
  category_id int(11),
  rating decimal(3,2)
)

Insert test data

insert into films2 values
(1,2015,1,8.00),
(2,2015,2,8.50),
(3,2015,3,9.00),
(4,2016,2,8.20),
(5,2016,1,8.40),
(6,2017,2,7.00);

Overall layout:

(2) Query requirement

Query the average rating for each year, showing the yearly average on every record.

For example, in 2015 there are three records with ratings 8.00, 8.50, 9.00, average 8.5; 2016 has two records average 8.3; 2017 has one record average 7.00.

Desired result format:

We can use a subquery to calculate yearly averages and join back:

SELECT
 f.id, f.release_year,
 f.rating, years.year_avg
FROM films f
LEFT JOIN (
 SELECT f.release_year,
        AVG(rating) AS year_avg
 FROM films f
 GROUP BY f.release_year
) years
ON f.release_year = years.release_year

This is a bit complex; now see the window function solution.

2. Window functions solution

What are window functions

Window functions compute over a set of rows, unlike GROUP BY which returns a single aggregated row; the result is associated with each row.

Syntax example:

SELECT
function_name OVER ( window_definition )
FROM (...)

window_definition

defines the set of rows (the window) to compute over. function_name specifies the calculation to perform on the window.

Returning to the earlier query, we need to compute the average rating for each movie's year using a window function:

SELECT
 f.id, f.release_year,
 f.category_id, f.rating,
 AVG(rating) OVER (PARTITION BY release_year) AS year_avg
FROM films f

The PARTITION BY clause splits the data set into smaller windows where release_year is the same; AVG(rating) computes the average for each window and places the result on every row.

Query example 1

Calculate each movie's ranking within its year.

Query:

SELECT
 f.id, f.release_year,
 f.category_id, f.rating,
 RANK() OVER (PARTITION BY release_year 
               ORDER BY rating DESC) AS year_rank
FROM films f

The PARTITION BY creates windows per release_year, and ORDER BY sorts within the window. RANK() returns the position of each row in its window.

Result:

Query example 2

View each movie's position in the overall ranking.

Query:

SELECT
 f.id, f.release_year,
 f.category_id, f.rating,
 RANK() OVER (ORDER BY rating DESC) AS general_rank
FROM films f order by id

The ORDER BY in the main statement ensures the dataset is sorted. Since the window definition does not use PARTITION BY, the whole result set is a single window; ORDER BY rating DESC orders by rating descending, and RANK() gives each record's position.

Result:

3. Summary

Window functions are an advanced feature in MySQL 8.0.2 that allow convenient aggregate calculations without collapsing the result set, greatly improving flexibility, readability, and maintainability.

Interested readers can try the MySQL 8.0.2 Docker image for easy setup.

References:

http://mysqlserverteam.com/mysql-8-0-2-introducing-window-functions/

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html