The role of MySQL 8's new feature window functions

New features in MySQL 8.0 include:

• Full out-of-the-box support for Unicode 9.0
• Supports window functions and recursive SQL syntax, which was impossible or difficult to write such queries in the past
• Enhanced support for native JSON data and document storage capabilities
• The release of MySQL 8.0 skipped several version numbers (starting from 5.5). Since 6.0 was modified and 7.0 was used to retain the cluster version of MySQL, the version number 8.0 was used.

1. Problem

MySQL 8.0.2 adds a new major feature - window function

What specific problem does this feature solve? Let's first look at a SQL query scenario to see how we usually do it, and then see how to use window functions to solve it more conveniently.

(1) Prepare test tables and data

Create a simple movie information table with the following fields:

ID
release_year
category_id (category ID)
rating
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);

The overall form is as follows

(2) Query requirements

Query the average score for each year, and require that the average score for that year be displayed after each record

For example, in 2015, there are 3 records with scores of 8.00, 8.50, and 9.00, and the average score is 8.5. In 2016, there are 2 records with an average score of 8.3. In 2017, there is 1 record with an average score of 7.00.

The final result is of the following form:

We can use a subquery to calculate the average score for each year, and then use join to connect the results back together.

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

Is it a bit complicated? Let's take a look at how window functions are handled.

2. Solution with window functions

What are window functions

Window functions are used to calculate a set of data. Unlike group by, they do not output a single row of results, but are associated with each record.

Syntax example:

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

Window_definition is the set of records to be calculated, like a small window that displays a part of the overall data set.

function_name specifies what calculation to perform on the data set in the window

Looking back at the query above, we need to calculate the average rating of all movies in each year. We use a window function to handle this.

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 window_definition part uses the PARTITION BY clause, which tells the database to split the resulting data set into smaller parts, putting the same release_year together. The function AVG(rating) will be calculated for each window data and then put the result into each row.

Query Example 1

Calculate the rating ranking position of each movie in its year

Query Statement

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 window_definition part uses PARTITION BY to partition the window by release_year and uses ORDER BY to sort within the window.

The RANK() function returns the position of a row of data in this window.

Query results

Query Example 2

See where each movie ranks in the overall rankings

Query Statement

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 clause in the main statement ensures that the entire data set is sorted.

If PARTITION BY is not used in window_definition, the entire result set is treated as a window, and ORDER BY sorts the data in the window in descending order according to rating, putting the highest-scoring data at the front.

The RANK() function obtains the position of each record in the window

Query results

3. Summary

Window functions are an advanced feature in MySQL 8.0.2 that can easily perform aggregate calculations without actually aggregating the result set, greatly increasing flexibility, readability, and making maintenance easier.