MySQL Window Functions

Since MySQL is one of the most popular database systems in the software industry, a huge number of web applications use MySQL as their on-site or cloud-based database management system. In order to query databases and produce relevant results, it is crucial for developers and database users to expand their querying capabilities. This is where MySQL window functions come into play.

What is a MySQL window function?

A window function performs a calculation across a set of table rows that are somehow related to the current row. No window function can be defined without an OVER clause; and in the OVER clause, there are the following possibilities:

The PARTITION BY clause is used to arrange rows together
The ORDER BY clause is used to sort result sets in ascending or descending order

Also note that the majority of MySQL window functions only apply to MySQL version 8 and later.

Different types of window functions

The primary types of window functions in SQL are aggregate window functions and analytical window functions.

Aggregate window functions, as their name suggests, calculate average values for a group of table rows. These functions include SUM, COUNT, AVG, MIN, and MAX, and typically return a scalar value.

Analytical window functions, which can be further divided into Ranking and Value functions, establish a window of records depending on the current row and then compute the results using that window. The output is typically given as a set of records. Examples include RANK, DENSE_RANK, ROW_NUMBER, CUME_DIST, LAG, LEAD, etc.

Now let's take a closer look at these types.

MySQL aggregate window functions

We'll start with the simplest, yet very useful aggregate functions. The five basic types of those are as follows:

SUM

The SUM function calculates the total of a numeric column in a selected window.

AVG

The average value of a numeric column can be obtained using the AVG function.

MIN and MAX

The smallest and largest values can be retrieved with the MIN and MAX window functions.

COUNT

Finally, the number of records in a specified window is calculated using the COUNT function. And if you need to count the distinct values inside your specified window, combine COUNT with a DISTINCT clause.

Example

Let's illustrate all aggregate functions with a single example using the payment table of the Sakila sample database.

We'll take each payment conducted by a client and retrieve it along with the following data about the said client: the sum of all payments, their quantity, as well as the average, lowest, and highest payment values. We'll do it for all clients and form a data pool that can be analyzed further. Without these functions, the retrieval of this information would require writing lots of JOINs and subqueries, not to mention the toll it would take on server performance.

SELECT c. first_name, c.last_name, c.email, p.customer_id, p.amount,
SUM (p.amount) OVER (PARTITION BY p.staff_id, p.customer_id) AS total ,
COUNT (p.amount) OVER (PARTITION BY p.staff_id, p.customer_id) AS count_payment ,
AVG (p.amount) OVER (PARTITION BY p.staff_id, p.customer_id) AS AVG_payment ,
MIN (p.amount) OVER (PARTITION BY p.staff_id, p.customer_id) AS MIN_payment ,
MAX (p.amount) OVER (PARTITION BY p.staff_id, p.customer_id) AS MAX_payment
FROM payment p
JOIN customer c ON p.customer_id = c.customer_id;

Now if we run this query, we will get the following output.

MySQL analytical window functions

Analytical window functions work on a window of rows inside the context of a single row. The most widely used examples of analytical functions are ROW_NUMBER< LEAD, LAG, RANK, and DENSE_RANK.

ROW_NUMBER

This is one of the most basic analytical window functions in MySQL. It assigns a unique sequential number to each row, starting with 1, according to the ordering of rows within the window partition.

Let's illustrate it with an example from the same Sakila sample database and write a query that searches duplicate customer records that contain the same email address.

WITH cte_src AS (
SELECT * , ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS ROWNUM FROM customer c
)
SELECT * FROM cte_src WHERE ROWNUM > 1;

The output of this query will be as follows.

LEAD and LAG

The LEAD and LAG functions are applied to retrieve the preceding and succeeding values of any row within its partition.

Here's a good practical example: a query that returns information about the customer if their previous payment was smaller than the current one.

WITH prev_payment AS
(SELECT p.payment_id,
p.customer_id,
p.payment_date,
p.amount,
LAG(p.amount, 1) OVER (ORDER BY p.customer_id, p.payment_date ) prev_amount_payment
FROM payment p)
SELECT payment_id,
payment_date,
customer_id,
amount,
prev_amount_payment
FROM prev_payment
WHERE amount < prev_amount_payment;

The output of this query will be as follows.

RANK and DENSE_RANK

The RANK function returns the rank of the current row within its partition. Peers are treated as ties and thus receive the same rank, producing gaps in the sequence.

The DENSE_RANK function is overall similar to RANK. It also returns the rank of the current row within its partition, but this time there will be no gaps in the sequence of ranked values.

Restrictions

Window functions are subject to restrictions imposed by SQL. They cannot be used in UPDATE or DELETE statements to update rows. However, it is acceptable to use these functions in subqueries alongside these statements to select rows.

Additionally, MySQL does not support the following:

The DISTINCT syntax in aggregate window functions
Nested window functions
Dynamic frame endpoints based on the value of the current row

Also note that these window constructs, albeit recognized by the parser, are not supported:

The GROUPS frame units specifier is parsed, but produces an error (however, ROWS and RANGE are supported)
The EXCLUDE clause for frame specification is parsed, but produces an error
IGNORE is parsed, but produces an error (however, RESPECT NULLS is supported)
FROM LAST is parsed, but produces an error (however, FROM FIRST is supported)

As of MySQL 8.0.28, the number of SELECT clauses that can be used in a single query is unlimited, whereas each given SELECT can support up to 127 windows. Additionally, you should be aware that queries employing a lot of windows may require you to increase the default thread stack size.

Using window functions in dbForge Studio for MySQL

You can make your routine management of window functions effortless with dbForge Studio for MySQL, one of the best IDEs for MySQL development, management, and administration. After you establish a connection to the required MySQL server, simply click New SQL to open a new document, enter your query with the window function of your choice, and click Execute to get the results. Simple as that.

The Studio offers quite a few capabilities that can further enhance your work with MySQL databases:

Intelligent code completion, formatting, refactoring, and debugging
Comparison and synchronization of database schemas and table data
Visual database design and query building
Query optimization
Data import and export supporting 10+ most popular data formats
Generation of meaningful test data
Management of users and privileges
Database migration, backup and recovery
Pivot tables for data analysis
Generation of full database documentation and data reports
...and much more!

Conclusion

Now you know how to apply various MySQL window functions in MySQL, and you can try the best IDE on the market that will help you handle them with ease. Give it a go - download dbForge Studio for MySQL for a free 30-day trial and see how effectively it can streamline your daily database development and management activities.