MySQL Window Functions

Introduction to MySQL Window Functions

Windows function in MySQL helps to solve a query problem. The operation is performed on a set of query rows while keeping the number of rows intact. Windows function is easily understood when explained in comparison with aggregate functions. On a wider note, both perform an operation on a subset of the result set. But the major difference between these two functions is that, when aggregate function reduces the number of rows in the result, the windows function returns the same number of rows.

Syntax

General syntax:

window_function (expression)
OVER ( [partition_definition] [order_defenition] )

• window_funtion -the name of the window function
• expression – field on which function is performed
• partition_definition – constraint for partition
• order_definition – order of result set

The part detailed within the OVER() clause is not mandatory, though, the use of it will help in easier results.

How does Window Functions work in MySQL?

The window functions work close to aggregate functions. Let’s compare the operations of both functions.

Scenario: segregate the total marks of each student in the class

Code:

SELECT student_name, SUM (marks)
from students
GROUP BY student_name;

Output:

Here we used aggregate function SUM () to get total and we saw the number of rows is reduced.

Code:

using window function:

SELECT student_name, subject, marks,
SUM (marks) OVER () AS Total_marks,
SUM (marks) OVER (PARTITION BY student_name) AS perhead_total
From students
ORDER BY subject;

Analyzing the query: The total_marks column is derived as a sum of marks of all students in all subjects, whereas the column perhead_total is, to sum up, the marks per student for all three subjects.

Output:

A window function can be used only within a SELECT query, and an OVER() clause is mandatory. The OVER() clause can either be left blank or can hold clauses like PARTITION BY, ORDER BY, etc.Almost all aggregate functions that allow an OVER() clause and certain non-aggressive functions can be used as window functions, with OVER() clause mandatory.

Window Functions in MySQL

More window functions as below:

1. ROW NUMBER ( )

This function is used to insert row numbers to each row. As a window function, it will add rows to the small groups of result sets and will have the following syntax:

Code:

SELECT
ROW_NUMBER() OVER (PARTITION BY student_name) AS row_num,
id,
student_name,
subject,
marks
FROM students
ORDER BY student_name;

Output:

2. RANK()

This function will provide every row with rank but it is not always a consecutive number like Row_number().

Code:

SELECT
ROW_NUMBER() OVER (order by subject) as row_num,
RANK () OVER (ORDER BY subject) as ranks,
student_name,
subject,
marks
FROM students;

Output:

3. DENSE RANK ()

This function is similar to the RANK()  function except for the fact that even when divided into smaller groups of the total result set, the DENSE_RANK() function will assign consecutive rank numbers for each group.

Code:

SELECT
ROW_NUMBER() OVER (order by subject) as row_num,
RANK () OVER (ORDER BY subject) as ranks,
DENSE_RANK () OVER (ORDER BY subject) as D_ranks,
student_name,
subject,
marks
FROM students;

Output:

4. PERCENT_RANK()

This function calculates the percentile of a row within the small result set and will return a value ranging from 0 to 1 for every row. Also, note that the function will return 0 for the first-row set.

Code:

SELECT
ROW_NUMBER() OVER (order by subject) as row_num,
PERCENT_RANK () over (order by subject) as percentile_rank,
RANK () OVER (ORDER BY subject) as ranks,
DENSE_RANK () OVER (ORDER BY subject) as D_ranks,
student_name,
subject,
marks
FROM students;

Output:

5. CUME_DIST()

This will return the Cumulative Distribution of a value over a set of values in the result set. The value returned will be within 0 to 1 and any repetition in the column value will end up in the same cumulative distribution.

Code:

SELECT
ROW_NUMBER() OVER (order by marks) as row_num,
RANK () OVER (ORDER BY marks) as ranks,
CUME_DIST () over (order by marks) as cume_distribution,
student_name,
marks
FROM marklist;

Output:

6. NTILE()

This helps to split the result set rows into a specified number of groups based upon the partition_by and order_by clauses provided.

Code:

SELECT
ROW_NUMBER() OVER (order by marks) as row_num,
NTILE (4) over (order by marks) as group_number,
student_name,
marks
FROM marklist;

Output:

7. LAG()

LAG() is a window function that returns the value to the previous rows in a sorted and partitioned result set.

Code:

SELECT
employee,
year,
sales,
LAG (sales, 1) OVER (PARTITION BY employee ORDER BY year) prev_year_sales
FROM sales;

Output:

8. LEAD()

This function does the exact opposite operation of lag() function. Lead() function will return the values ahead, in the partitioned and sorted result set.

Code:

SELECT
employee,
year,
sales,
LAG (sales, 1) OVER (PARTITION BY employee ORDER BY year) prev_year_sales,
LEAD (sales, 1) OVER (PARTITION BY employee ORDER BY year) next_year_sales
FROM sales;

Output:

9. FIRST VALUE()

This window function returns the first row among a partitioned sorted result set.

Code:

SELECT
employee,
department,
hours,
FIRST_VALUE (employee) OVER (PARTITION BY department ORDER BY hours) AS least_logged_hours
FROMloggedhours;

Output:

10. LAST VALUE()

This window function returns the first row among a partitioned sorted result set. The syntax is as follow:

Code:

SELECT
employee,
department,
hours,
FIRST_VALUE (employee) OVER (PARTITION BY department ORDER BY hours) AS least_logged_hours,
LAST_VALUE (employee) OVER (PARTITION BY department ORDER BY hours
RANGE BETWEEN UNBOUNDED PRECEEDING AND
UNBOUNDED FOLLOWING) AS highest_logged_hours
FROM loggedhours;

Output:

11. NTH VALUE ()

The window function NTH_VALUE returns the Nth row among a partitioned sorted result set.

Code:

SELECT
employee,
department,
hours,
NTH_VALUE (employee, 2)
FROM FIRST
OVER ( PARTITION BY department ORDER BY hours) as NTH_val
FROM loggedhours;

Output:

Conclusion

The window function is a function operated upon a window of a table from the database. The operation of a window function is very much close to an aggregate function. Many aggregate and non-aggregate functions can be termed as respective window functions by proper use of the OVER() clause. The OVER() clause can have values or left blank and we discussed the difference in outcomes as well.

Recommended Articles

This is a guide to MySQL Window Functions. Here we discuss an introduction to MySQL Window Functions with syntax, working, functions with examples. You can also go through our other related articles to learn more –

1. MySQL MAX() Function
2. MIN() in MySQL
3. MySQL avg()
4. ORDER BY in MySQL