Window Functions in Mysql

Introduction

The Window functions in MySQL are special kinds of pre-defined methods that are used to perform computations on a set of rows rather than a particular column. The set of rows on the methods that are applied is referred to as a Window. The row on which the window function is applied is known as the current row. The current row is related to the set of rows on which the computations are applied. As discussed earlier, the window functions in MySQL work in a similar way as the aggregate functions do. But the catch is, the aggregate function is applied on a particular column or a whole table producing output in a single row whereas window functions in MySQL operate on every row from a selected set of rows (Window) and produce an output corresponding to each row.

It is mandatory to use an OVER clause with window functions in MySQL as it is used to define the set of rows (Window) on which the functions should be applied. Apart from that, the OVER clause is used to order the selected rows in groups.

Earlier, window functions were not supported in MySQL. It was introduced in version 8.0.

Syntax:

In the above syntax, the OVER clause is used after the window function to define the partitions, order, and range of the rows.

Here, the PARTITION BY clause is used to make divisions or partitions of rows so we can specify the rows to be used in the window function computation. The ORDER BY clause is used to sort the order of rows that exists in a partition. The ROW or RANGE clause is used to form a subset of rows in the current partition

Both, ORDER BY and ROW or RANGEclauses are optional for the window function.

Now, Let's discuss a bit the types of Window functions in MySQL.

The Window functions are divided into three categories:-

• Aggregate Window Functions
• Analytical Window Functions
• Ranking Window Functions

Aggregate Window Function

Aggregate window functions are the kind of functions that are used to perform mathematical calculations on the data stored in MySQL. They are used to aggregate the data. Some of the most commonly used aggregate functions are as follows:

• SUM()
• AVG()
• MIN()
• MAX()
• COUNT()

Analytical Window Function

Analytical window functions are the kind of functions that are used to carry out analysis of the data stored in a database. Some of the most commonly used analytical functions are as follows:

• NTILE()
• LAG()
• LAST_VALUE()
• FIRST_VALUE()
• LEAD()

Ranking Window Function

Ranking window functions are the kind of functions that are used to give rankings to each row of a partition. Some of the most commonly used ranking functions are as follows:

• RANK()
• DENSE_RANK()
• ROW_NUMBER()
• PERCENT_RANK()
• CUME_DIST()

ROW_NUMBER() Function

ROW_NUMBER is one of the most common ranking window functions in MySQL which provides a normal serial number to each row present within a partition. It is just a normal serial-wise numbering of rows in ascending order. Even the rows which have the same value when using the ORDER BY clause has been given a different row number.

Syntax

Let's understand the use of row_number with an example.

Example

In this example, let's take a table Car which will contain details of cars like the model and release year. We will apply the row_number() function on the table to assign incremental sequences to each row from the table.

Let's consider the following table:

Code

Output:

Explanation:

In the above example, we have used to table car which contains car_name and released_year columns. The ROW_NUMBER() window function is used here to assign numerical sequences to each row of the table. The rows are ordered by or sorted by the released_year using the ORDER BY clause as seen in the output table.

RANK() and DENSE_RANK() Functions

RANK()

RANK() is a kind of ranking window function in MySQL which is used to assign ranks to the rows within an ordered partition. The rows which contain the same values will be assigned the same rank, with the next ranking or rankings skipped. For example, if we have 4 items with the same value starting with ranking 1 then all 4 items will have rank 1, and the ranking from the 5th item would be 5 with 2,3,4 skipped.

Syntax:

Example

In this example, we will consider a table Car which will contain details of cars like the model and released year and price. We will apply the RANK() function on the table to assign rankings to each row from the table.

Let's consider the table:

Code

Output

Explanation

In the above example, we have used to table car which contains the car_name, released_year and price columns. The RANK() window function is used here to assign a ranking to each row of the table based on their prices. The rows are ordered by or sorted in descending order by the prices using the ORDER BY clause as seen in the output table.

DENSE_RANK()

The DENSE_RANK() function is a type of ranking window function in MySQL which is used to assign rankings to the rows within an ordered partition. DENSE_RANK() is almost as same as the RANK() window function but in DENSE_RANK() no rankings are skipped even if the rows have the same value.

Syntax

Example

Let's take the same example we have used in the RANK() function section. Here, we will apply the DENSE_RANK() method to assign rankings without skipping the ranks.

Code

Output

Explanation:

As seen in the previous example, the ranking is assigned based on price. Here, with the use of DENSE_RANK() the rankings are assigned and displayed with the car_name attribute. As seen in the output, the rankings are assigned without skipping the previous ranks even if the rows contain the same value.

LAG() and LEAD() Functions

LAG() and LEAD() both are types of analytical window functions in MySQL. As the name suggests, the LAG() function starts the value from the previous row (returns NULL if starting from the first row as no preceding row exists). It returns the value of the row before the current row of the partition. Whereas the LEAD() function returns the value of the row after the current row in a partition. It starts from the value of the next row (returns NULL if no row if no more rows are available).

Syntax (LAG)

Syntax (LEAD)

Example

Here, we will consider the same table car which contains different car details. We will apply both LAG() and LEAD window functions to demonstrate their practical usage.

Consider a table car:

Code (LAG)

Output

Code (LEAD)

Output

Explanation

As seen in the above example, we have used both the LAG() and LEAD()functions on the same table and set of rows. The LAG() function returns values from one preceding row i.e., here it returns NULL in the first row as there was no preceding row available. The LEAD() function returns the value from one subsequent row i.e., here it starts returning the value from the second row and the last row returns the NULL value as there are no subsequent rows available.

FIRST_VALUE() and LAST_VALUE() Functions

FIRST_VALUE() and LAST_VALUE() are one of the most commonly used analytics window functions in MySQL. The FIRST_VALUE() function shows the value from the first row of a window frame or within a partition. LAST_VALUE() will return the value of the last row of a window frame or within a partition.

Syntax(FIRST_VALUE)

Syntax(LAST_VALUE)

Example

In this example, we will consider a table Employee which contains details about the employee working in different departments with their salary. Here, we will use the FIRST_VALUE() and LAST_VALUE()methods to return the name of the employee having the least salary and highest salary from each department respectively.

Consider a table Employee:

Code(FIRST_VALUE)

Output

Code(LAST_VALUE)

Output

Explanation

In the above example, we have used both the FIRST_VALUE() and LAST_VALUE() methods on the same table. Here, using the FIRST_VALUE() method we've returned the name of the employee having least_salary for every partition (department). And using the LAST_VALUE() we've returned the name of the employee having max_salary for every partition (department).

NTILE() Function

NTILE() function is a kind of analytical window function in MySQL which is used to divide rows into groups within an ordered partition. Each of the divided groups will be assigned a number which will be the same for that group of rows. Every group is represented by a number.

Syntax

Example

Here the same table Employee is used which will be divided into groups using the NTILE() window function.

Code

Output

Explanation

Here, we have considered the same Employee table used in the previous examples. We've used the NTILE() to form a total of 3 groups from the rows of the Employee table. The first group contains 3 rows, the second group contains 3 rows and the last group contains 2 rows.

See Also

• Aggregate Functions in MySQL
• String Functions in MySQL