EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Window Functions
 

MySQL Window Functions

Priya Pedamkar
Article byPriya Pedamkar

Updated May 16, 2023

MySQL Window Function

 

 

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 broader note, both operate on a subset of the result set. But the significant difference between these two functions is that when the aggregate function reduces the number of rows in the result, the windows function returns the same number of rows.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax

General syntax:

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

Where,

  • 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 do Window Functions work in MySQL?

The window functions work closely 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:

Scenario

Here we used the aggregate function SUM () to get the total, and we saw the number of rows was 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:

Analyzing

A window function can be used only within a SELECT query, and an OVER() clause is mandatory. The OVER() clause can be left blank or 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 the OVER() clause mandatory.

Window Functions in MySQL

More window functions as below:

1. ROW NUMBER ( )

This function is used to insert row numbers into 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:

insert row numbers

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:

row with rank

3. DENSE RANK ()

This function is similar to the RANK() function except 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:

DENSE RANK

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:

PERCENT_RANK()

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:

MySQL Window Functions7

6. NTILE()

This helps to split the result set rows into a specified number of groups based on 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:

MySQL Window Functions8

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:

MySQL Window Functions9

8. LEAD()

This function does the exact opposite operation of the lag() function. The 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:

MySQL Window Functions10

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:

MySQL Window Functions11

10. LAST VALUE()

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

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:

MySQL Window Functions12

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:

MySQL Window Functions13

Conclusion

The window function operates 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 respective window functions by using the OVER() clause. The OVER() clause can have values or be left blank, and we also discussed the difference in outcomes.

Recommended Articles

We hope that this EDUCBA information on “MySQL Window Functions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW