A must-know for **Data Scientists**, **Data Engineers**, and **Data analysts**.

Hi folks, I hope you are doing well. These **MySQL Window Functions** are going to be very important and useful because in this tutorial you will learn everything about MySQL window functions with the help of the proper examples as well as the explanation so that you don’t have any confusion regarding window functions in MySQL.

Window functions in MySQL come under the advanced topics which are most used by data scientists and data analysts to perform large calculations on a set of rows. Throughout this article, I will explain all about MySQL window functions with the help of real-life examples so that you can understand window functions easily.

Headings of Contents

- 1 What are MySQL Window Functions
- 2 Why should we need to use the window function in MySQL?
- 3 MySQL window function syntax
- 4 Types of MySQL window functions
- 5 Data for applying the MySQL Window Functions
- 6 MySQL Aggregate Window Functions
- 7 MySQL Ranking Window function
- 8 MySQL Value Window Function
- 9 Summary

## What are MySQL Window Functions

**Window function in MySQL** comes under the advanced concept in MySQL. Window functions were first introduced in MySQL version **8.0** and MySQL window functions are used to perform operations across a set of table rows that are related to the current row. Here the meaning of the current row is to that row for which function evaluation occurs. The calculation of the window function is the same as the calculation done by using the aggregate function used with the **GROUP BY** clause.

The only difference between the windows functions and aggregate functions with **GROUP BY** clause is that **GROUP BY** clause applies aggregates function on the rows that have identical values and returns only a single value same as the windows function performs a calculation on a set of rows that have identical values but it retains all the rows and their identity and return value for each of the row.

Here I have shared a good visualization of the Windows function and **GROUP BY** clause.

Now, we have to notice in the above image, we have a total of two Figures, **Figure 1** represents the aggregate function, and **Figure 2** represents the window function.

In **Figure 1**, How **GROUP BY** clause grouped rows with the same values ( Here same values representing the same color) into a single value, and in **Figure 2**, the windows function performs calculations on a set of rows that have identical values same as in Figure 1 but window functions retain all the rows as well along with a calculated value.

I hope you will have understood the meaning of MySQL window functions. If not. Wait just continue after this article, and you will understand.

Let’s take an example to understand the difference between a window function and an aggregate function because an initial understanding of these two concepts is most important.

If you understand the difference between these two concepts aggregate functions and Window functions, Then way ahead will be easier.

I have created an **employee** table having some records and I want to create a new column average_salary which will be the average salary of employees for each department.

**Please see carefully!**

Below are the records of the **employee** table:

+----+------------+-----------+-----------+---------------------+ | id | first_name | last_name | salary | department | +----+------------+-----------+-----------+---------------------+ | 1 | Vishvajit | Rao | 25000.00 | IT Department | | 2 | Vinay | Kumar | 35000.00 | IT Department | | 3 | Minakshi | Kumary | 30000.00 | Digital Marketing | | 4 | John | Doe | 80000.00 | Data Department | | 5 | Akash | Saini | 25500.00 | Data Department | | 6 | Harshita | Singh | 20000.00 | Account and Finance | | 7 | Ayush | Shukla | 23500.00 | IT Department | | 8 | Abhishek | Mishra | 120000.00 | Data Department | | 9 | Vaishanvi | Mehta | 75250.00 | Digital Marketing | | 10 | Mahima | Chaudhari | 40300.00 | Data Department | | 11 | Arushi | Shulka | 46000.00 | Data Department | | 12 | Mahesh | Kumar | 90500.00 | IT Department | | 13 | Ajay | Gupta | 85340.00 | IT Department | | 14 | Manish | Das | 90000.00 | HR | | 15 | David | Russel | 105000.00 | Data Department | | 16 | Nishant | Chahal | 65000.00 | Account and Finance | | 17 | Darshan | Natrajan | 40000.00 | HR | +----+------------+-----------+-----------+---------------------+

Firstly, I have used the **MySQL GROUP BY clause** to calculate the average salary of all the employees within a department.

After applying the GROUP BY Clause on the **department** column, The result will be this.

+---------------------+----------------+ | department | average_salary | +---------------------+----------------+ | IT Department | 51868.000000 | | Digital Marketing | 52625.000000 | | Data Department | 69466.666667 | | Account and Finance | 42500.000000 | | HR | 65000.000000 | +---------------------+----------------+

Now, I will use the window function concept to calculate the average salary of each employee within a department, let’s see what happens.

After applying the MySQL window function, The result will be this.

+---------------------+----------------+ | department | average_salary | +---------------------+----------------+ | Account and Finance | 42500.000000 | | Account and Finance | 42500.000000 | | Data Department | 69466.666667 | | Data Department | 69466.666667 | | Data Department | 69466.666667 | | Data Department | 69466.666667 | | Data Department | 69466.666667 | | Data Department | 69466.666667 | | Digital Marketing | 52625.000000 | | Digital Marketing | 52625.000000 | | HR | 65000.000000 | | HR | 65000.000000 | | IT Department | 51868.000000 | | IT Department | 51868.000000 | | IT Department | 51868.000000 | | IT Department | 51868.000000 | | IT Department | 51868.000000 | +---------------------+----------------+

In the First table, data is returned by the **GROUP BY** clause, and in the second table, data is returned by the window function. Basically, The **GROUP BY** clause compresses the rows into just three rows but window functions retained all **17** rows along with new columns **averag_salary** which is the average salary of all the employees within a department.

I hope you will have the meaning of the window function and GROUP BY clause. Let’s understand why we need to learn window functions.

## Why should we need to use the window function in MySQL?

The main advantage of learning the window functions is, that it does not collapse rows together. It just retained all the rows along with new columns. Using window functions we can perform aggregate functions and not aggregate functions on the records at the same time. As you can see in the above two records table. In the First table, all 17 records collapsed but in the second table, it did not.

Window functions increase the efficiency and complexity of the SQL queries that analyze the data in the form of partitions or windows.

## MySQL window function syntax

Here, I have shared the MySQL window function syntax.

SELECT column1, column2, window_function(expression) OVER(PARTITION BY <partition_list> ORDER BY <order_list> ROWS frame_clause) FROM table_name;

**Explanation of the above MySQL window() function syntax:**

**window_function**represents the name of the window functions which we want to use for example**AVG()**,**SUM()**,**MAX(**),**MIN()**, etc are the window functions.- The
**expression**indicates the name of the column name on which the window will operate. You can use more than one column as an expression. **OVER**is nothing but it signifies that this is a window function.**PARTITION BY**divides the rows into partitions so that we can specify which row to use to compute the window function.**partition_list**is the name of the columns we want partitioned by.**ORDER BY**is used to order the rows within each partition. This is optional.**order_list**specifies the name of the columns we want to order by.**ROWS**is used when we want to limit the further rows within our partition.**frame_clause**defines how much we want to offset from the current row.

## Types of MySQL window functions

MySQL Window Functions divided the Window functions into three groups such as aggregate, ranking, and value. Don’t worry, We will see all these categories and their functions one by one through example. You can see all three window function categories and all the window functions within each category.

Let’s see the meaning of all the above categories like aggregate, ranking, and value window functions.

### aggregate window function

The aggregate window function is used to calculate the various aggregations and sets of rows like average, the sum of related rows, minimum values, and maximum value within each window or partition.

### Ranking window function

Ranking functions are used to provide the ranking within each partition or window.

### Value window function

This function allows us to compare values from previous values or rows within the partitions, or the first row or last row within the partition or window.

## Data for applying the MySQL Window Functions

To apply all the above window functions, I have created a table just for demonstrations in MySQL called **employee** having some records. As You can see below.

Throughout this tutorial guide, we will use **employee** table and their data to apply all the Window functions. This tutorial is going to be very interesting and informative.

We will explore all the categories and their function one by one. I hope you have remembered the MySQL window function syntax which we have seen earlier.

First of all, we will go with aggregate functions.

## MySQL Aggregate Window Functions

As we know aggregate functions are used to perform some calculations on the set of rows like total sum, maximum value, minimum value, and average value within a partition or window.

There are various types of aggregate functions available as you can see in the below list.

- AVG()
- SUM()
- MIN()
- MAX()
- COUNT()

### AVG() window function

The AVG() function is used to calculate the average value of the numeric column or the average value of the numeric column within a partition or window.

Here, I am going to calculate the average **salary** column for all employees within each department.

**Example**

SELECT first_name, last_name, age, salary, designation, department, AVG(salary) OVER (PARTITION BY department) as "average" FROM employee;

**Explanation of the above code**:

Let’s match the above SQL code with the Window function syntax which we have seen above.

- The columns
**first_name**,**last_name**,**age**,**salary**,**designation**, and**department**are the columns that we want to select. - The second,
**AVG()**function is the window function which takes a number column that is**salary**. Here salary indicates the expression in**window_function()**in the above syntax, that’s which is an expression that we want to use to calculate the average. **OVER**is used to signify that this function with treated as a window function.- As we know the
**PARTITION BY**clause can take one or multiple expressions that represent the column names, I have used the department column in**PARTITION BY**because I want to calculate the average employee**salary**department-wise. - Used
**as**a keyword to provide the name of the new column. - The
**employee**is representing the name of the table.

### MySQL SUM() Window Function

Similarly, We can calculate the sum of all the employee salaries within a department. We will have to just replace the **AVG()** window function with the **SUM()** window function.

SELECT first_name, salary, designation, department, SUM(salary) OVER (PARTITION BY department) AS "sum_of_salary" FROM employee;

In the above image, I have separated all the windows or partitions with different-different colors.

### MySQL MAX() Window Function

Same as We can find the maximum salary of an employee within a department. To find the maximum salary, we will have to use the **MAX()** function.

SELECT first_name, salary, designation, department, MAX(salary) OVER (PARTITION BY department) AS "maximum salary" FROM employee;

### MySQL MIN() Function

Similarly, we can find the lowest salary of an employee within a department using the **MIN()** function.

SELECT first_name, salary, designation, department, MIN(salary) OVER (PARTITION BY department) AS "minimum salary" FROM employee;

### MySQL COUNT() Window Function

The **COUNT()** window function in SQL is used to find the total number of records within a window or partition. Here, I am going to find the total number of employees exists on each department.

SELECT first_name, salary, designation, department, COUNT(*) OVER (PARTITION BY department) AS "total employees" FROM employee;

## MySQL Ranking Window function

The ranking is another category of the window function in MySQL. The ranking window function is used to provide the ranking of the records within each window or partition. You have remembered one thing about ranking functions, You don’t need to pass parameters in ranking functions like aggregate functions.

There are various types of Ranking functions available as follows.

**ROW_NUMBER()****RANK()****DENSE_RANK()****PERCENT_RANK()****NTILE()**

### MySQL ROW_NUMBER() window function

**ROW_NUMBER()** function is a window function that comes under the ranking functions, It does not take any expression as a parameter and it is used to provide the rank of records within a partition or window.

Suppose we want to provide a ranking of each employee in the employee table based on their salary within a department. For example, the Rank of the highest salaried employee within an organization is 1, and so on.

Let’s see how can we do that.

SELECT first_name, salary, designation, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS "rank" FROM employee;

As you can see, we have successfully applied the **ROW_NUMBER()** function to assign the rank of each employee within a department. Ranking will always start from 1 to a number of records in each window. As you can see in each department how does ranking get assigned? So this is how you can use MySQL **ROW_NUMBER()** window function.

### MySQL RANK() window function

The MySQL **RANK()** function works the almost same as the **ROW_NUMBER()** function. The only difference between the **ROW_NUMBER()** and **RANK()** window functions is **ROW_NUMBER()** function will assign different ranks even if the multiple values have the exact value but the **RANK()** function will assign the same rank if the multiple values have the same or exact value. Maybe you will be confused here. All the stuff will clear when we will see an example.

Let me change the salary of some employees within a department with the same value. After changing the record data will look like this.

mysql> SELECT id, first_name, salary, designation, department FROM employee; +----+------------+-----------+--------------------------+---------------------+ | id | first_name | salary | designation | department | +----+------------+-----------+--------------------------+---------------------+ | 1 | Vishvajit | 25000.00 | Python Developer | IT Department | | 2 | Vinay | 35000.00 | Angular Developer | IT Department | | 3 | Minakshi | 30000.00 | SEO Specialist | Digital Marketing | | 4 | John | 80000.00 | Data Engineer | Data Department | | 5 | Akash | 25500.00 | Junior Data Engineer | Data Department | | 6 | Harshita | 20000.00 | Junior Accountant | Account and Finance | | 7 | Ayush | 23500.00 | Angular Developer | IT Department | | 8 | Abhishek | 120000.00 | Senior Data Engineer | Data Department | | 9 | Vaishanvi | 75250.00 | Senior SEO Specialist | Digital Marketing | | 10 | Mahima | 40300.00 | Junior Data Scientist | Data Department | | 11 | Arushi | 40300.00 | Junior Data Analyst | Data Department | | 12 | Mahesh | 85340.00 | Senior Python Developer | IT Department | | 13 | Ajay | 85340.00 | Senior Angular Developer | IT Department | | 14 | Manish | 90000.00 | Senior HR Manager | HR | | 15 | David | 105000.00 | Senior Data Analyst | Data Department | | 16 | Nishant | 65000.00 | Senior Accountant | Account and Finance | | 17 | Darshan | 40000.00 | Hiring HR | HR | +----+------------+-----------+--------------------------+---------------------+ 17 rows in set (0.00 sec)

Let’s use the RANK() function.

SELECT id, first_name, salary, designation, department, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS "rank" FROM employee;

As you can see in the above result set, The salary of an employee is the same whose ID is equal to 12 and 13, In this case, the RANK() window function assigned the same rank 1 and the same thing happened with employees whose ID is **10** and **11**.

Please, Notice that for rows **6** and **7** the salary is** 40300.00, **therefore**,** both rows have been assigned the same rank **2** by **RANK()**, and rows **13** and **14** also have the same salary of **85340.00** That’s why both rows have assigned the same ranking **1** by **RANK() **function.

One most important things about the **RANK()** function is that it skips a ranking number if the multiple values have exact values. For example, you can see in rows **13** and **14**, that rank **1 **has been assigned but 2 has been skipped and started from **3** and the same thing happened in rows **6** and **7**, The ranking assigned **4** and **5** have been skipped.

Well, Most of the time we don’t want to skip the ranking function, In that case, we can go with **DESNE_RANK()** window function.

### MySQL DESNE_RANK() function

MySQL **DESNE_RANK()** window function works almost the same as the RANK() function, It does not skip the ranking number like the above example. Here we will go with the same example that we have seen above.

SELECT id, first_name, salary, designation, department, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS "rank" FROM employee;

As you can see in the above result, the **DENSE_RANK()** function did not skip the ranking number like the **RANK()** window function.

### MySQL PERCENT_RANK() Window Function

**PERCENT_RANK()** function is also part of the Ranking window function that is used to assign the percent rank to each of the rows within a window or partition but quite different from other ranking functions that we have seen above.

Let’s see how it is diffrent ?

**In PERCENT_RANK() window function:**

- The lowest rank always starts from
**0**. - The last rank always ends with
**1**. - The rank of each row is evaluated by the
**( current_row_number -1 ) / total_number_of_rows -1 )**formula. Where current_row_number represents the row number of itself and total_number_of_rows indicates the number of records that exist in the table.

Let’s demonstrate **PERCENT_RANK()** through an example.

SELECT id, first_name, salary, designation, department, PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS "rank" FROM employee;

As you can in the above result set, the How **PERCENT_RANK()** window function is implemented, and within a window or partition, each rank is started from **0** and ends at **1**. Each rank is calculated from the **( current_row_number -1 ) / ( number of rows in window or partition -1 **) formula even to verify the rank, you can calculate it yourself. let’s try to calculate the rank of the IT Department.

In the **IT** Department, There are 5 employees and their rows numbers are 1, 2, 3, 4, 5. and the total number of records in the employee table is 17

**Formula:-** **( current_row_number -1 ) / ( number of rows in window or partition**

**-1**).

**Rank for row 1:**

- rank = (1 – 1)/(5 – 1 ) = 0

**Rank for row 2:**

- rank = (2 – 1)/(5 – 1 ) = 0

So this is how a PERCENT_RANK rank is calculated for a row.

### MySQL NTILE() Window Function

NTILE() is the window function used to distribute the rows of order partitions in a pre-defined number of groups.

It takes a number value as a parameter which represents the number of groups to be created. The **NTILE()** function divides the total number of rows within a partition by the number of groups specified in the **NTILE()** function.

- If the number of rows is divisible by a number of groups then each partition will have the same number of rows.
- If the number of rows is not divisible by a number of groups Then each partition will have different rows and large rows always be ahead of a smaller number of rows.

SELECT first_name, designation, department, salary, NTILE(3) OVER (PARTITION BY department order by salary DESC) AS "Ntile" FROM employee;

## MySQL Value Window Function

In MySQL, The value window functions are used to compare the values. There is various type of value window function available in MySQL. All those functions are listed below. All the value window functions have great value in extracting power from other rows.

Value window functions are mostly used to create reports. All the values window functions are listed below.

**LAG()****LEAD()****FIRST_VALUE()****LAST_VALUE()****NTH_VALUE()**

### MySQL LAG Window Function

The LAG window function is a kind of value window function that has the power to extract value from previous rows. It is the best value function in order to compare the value of the current row to previous records.

The LAG() window function takes three parameters.

**column_name**:- Required, This argument indicates the column name on which you want to perform the calculation.**offset**:- Optional, Number of rows back from the current row which to access the data. By default, it is 1.**default**:- Optional, default value to be returned if the offset goes beyond the scope of the partition.

Let’s understand this by an example.

Now this time I am not going to use the **PARTITION BY** clause. When we will not use **PARTITION BY** clause it will treat whole records as a single window or partition.

Let’s see.

mysql> SELECT -> first_name, -> department, -> salary, -> LAG(salary) OVER (ORDER BY salary DESC) AS "lag" -> FROM employee; +------------+---------------------+-----------+-----------+ | first_name | department | salary | lag | +------------+---------------------+-----------+-----------+ | Abhishek | Data Department | 120000.00 | NULL | | David | Data Department | 105000.00 | 120000.00 | | Manish | HR | 90000.00 | 105000.00 | | Mahesh | IT Department | 85340.00 | 90000.00 | | Ajay | IT Department | 85340.00 | 85340.00 | | John | Data Department | 80000.00 | 85340.00 | | Vaishanvi | Digital Marketing | 75250.00 | 80000.00 | | Nishant | Account and Finance | 65000.00 | 75250.00 | | Mahima | Data Department | 40300.00 | 65000.00 | | Arushi | Data Department | 40300.00 | 40300.00 | | Darshan | HR | 40000.00 | 40300.00 | | Vinay | IT Department | 35000.00 | 40000.00 | | Minakshi | Digital Marketing | 30000.00 | 35000.00 | | Akash | Data Department | 25500.00 | 30000.00 | | Vishvajit | IT Department | 25000.00 | 25500.00 | | Ayush | IT Department | 23500.00 | 25000.00 | | Harshita | Account and Finance | 20000.00 | 23500.00 | +------------+---------------------+-----------+-----------+ 17 rows in set (0.00 sec)

In the above result set, As you can see. The **lag** column contained the salary of their previous employee salary. This could be very useful in order to compare current employee salaries with their previous ones to get the differences between salaries.

Notice that in the first row, The lag value is** NULL** because there is no previous record for the first row, That’s why it is happening.

To avoid the **NULL** value in the first row, You can pass an additional two parameters to the **LAG()** function. As you can see below example.

mysql> SELECT -> first_name, -> department, -> salary, -> LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS "lag" -> FROM employee; +------------+---------------------+-----------+-----------+ | first_name | department | salary | lag | +------------+---------------------+-----------+-----------+ | Abhishek | Data Department | 120000.00 | 0.00 | | David | Data Department | 105000.00 | 120000.00 | | Manish | HR | 90000.00 | 105000.00 | | Mahesh | IT Department | 85340.00 | 90000.00 | | Ajay | IT Department | 85340.00 | 85340.00 | | John | Data Department | 80000.00 | 85340.00 | | Vaishanvi | Digital Marketing | 75250.00 | 80000.00 | | Nishant | Account and Finance | 65000.00 | 75250.00 | | Mahima | Data Department | 40300.00 | 65000.00 | | Arushi | Data Department | 40300.00 | 40300.00 | | Darshan | HR | 40000.00 | 40300.00 | | Vinay | IT Department | 35000.00 | 40000.00 | | Minakshi | Digital Marketing | 30000.00 | 35000.00 | | Akash | Data Department | 25500.00 | 30000.00 | | Vishvajit | IT Department | 25000.00 | 25500.00 | | Ayush | IT Department | 23500.00 | 25000.00 | | Harshita | Account and Finance | 20000.00 | 23500.00 | +------------+---------------------+-----------+-----------+ 17 rows in set (0.00 sec)

**Note:-** You can also use the **PARTITION BY** clause to divide the rows into disparate partitions.

### MySQL LEAD() window function

The MySQL **LEAD() **window function works the same as the **LAG()** function, Unlike the LAG() function, The LEAD() function uses the value of the next row.

It accepts a total of three parameters.

**column_name:-**Required, This argument indicates the column name on which you want to perform the calculation.**offset:-**Optional, Number of rows next to the current row which to access the data. By default, it is 1.**default:-**Optional, default value to be returned if the offset goes beyond the scope of the partition.

SELECT id, first_name, designation, department, salary, LEAD(salary, 1, 0) OVER (ORDER BY salary DESC) AS "lead" FROM employee;

**Note:-** You can also use the **PARTITION BY** clause to divide the rows into disparate partitions.

### MySQL FIRST_VALUE() Window Function

The **FIRST_VALUE()** window function returns the first value of a specified column for every row within a partition or window.

let’s see through an example.

**Using FIRST_VALUE() without PARTITION BY clause**:

If we do not define the PARTITION BY clause, then it will treat whole rows as a single window or partition. As you can see below screenshot.

SELECT first_name, designation, department, salary, FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS "FirstValue" FROM employee;

**Using FIRST_VALUE() with PARTITION BY clause**:

If we do not define the **PARTITION BY** clause, then it will treat whole rows as a single window or partition. As you can see below screenshot.

SELECT first_name, designation, department, salary, FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS "FirstValue" FROM employee;

### MySQL LAST_VALUE() Window Function

The **LAST_VALUE()** window function works the same as **FIRST_VALUE()** function. Instead of getting the first value of the specified column, it uses the last value of the specified column.

SELECT first_name, designation, department, salary, LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS "LastValue" FROM employee;

### MySQL NTH_VALUE() Window Function

The **NTH_VALUE()** also work same as **FIRST_VALUE()** and **LAST_VALUE()** window function.Instead of getting value from the first row and last row, it takes value from the **Nth** row where **N** is the row number in a set of order rows.

It takes two parameters, the first is an expression that indicates the column name is the same as previous functions and the second is the row number to get the value.

**Example:**

In this example, I have created a column called **NthValue** which contains the name of the employee of the 3rd row whose salary is the 3rd highest salary in the result set because I have ordered the result set based on the salary.

ðŸ‘‰MySQL Window Functions Reference:-Click Here

**Related Articles:-**

**How to get the last day of the month in MySQL****How to extract month from date in MySQL****How to extract the year from the month in MySQL****How to handle Null values in MySQL**

## Summary

So, In this amazing guide, We have seen everything about **MySQL window functions with examples**. Window functions in MySQL or any other database engine like **SQL Server**, **PostgreSQL**, etc are one of the most important concepts, especially for **Data Engineers, Data analysts**, and **Data Scientists**.

As Data Engineers, Data analysts, and Data Scientists you must have knowledge of MySQL window functions. The main advantage of using the Window function in MySQL is that it does not collapse the result set together like the **GROUP BY** clause, It just retains all the rows with their unique id and returns a single value for every row in the result set. You can use any window function as per your requirement.

I hope you found this article, Please keep visiting and share this kind of amazing tutorial.

**Thanks for your valuable time…**

**Have a nice day.**