In this tutorial, we will learn what a MySQL window function is and the different types of window functions. This is an introductory article on window functions where you will learn what is it, why they exist, what are the uses of these window functions and how to use them.
Also read: MySQL Stored Function with Example – A Complete Reference
Introduction to Window Function
Window functions are a part of SQL. So, if you understand what a window function means in SQL, you will understand the MySQL window function as well very easily.
Window functions are a special category of pre-built functions that return a value for each row from a group of rows. Although it can appear complicated at first, it is not. To put it simply, a window function determines the value from several records for each row in the context.
For example, if a table contains multiple records of orders where records can contain orders of the same customer. We can find the maximum or minimum amount of the purchase that customer made or an average number of purchase that customer make in each month of the year or similar kind of examples.
Take another example. The following table contains the records of students. Using the aggregate window functions, we will find the sum, average and min-max values.
SELECT name,dept,subject,marks,
count(subject)over(partition by name)as subjects,
sum(marks)over(partition by name)as sum,
avg(marks) over(partition by name)as average
from students order by average;Code language: SQL (Structured Query Language) (sql)
You can see that each row consists of a new column result based on the window of multiple rows.
Types of Window Functions
There are mainly two types of window functions-Aggregate window functions and Analytical window functions
Aggregate Window Functions
Aggregate window functions are the same as normal aggregate functions which calculate the aggregated values of a group of rows from the table. MySQL Functions such as SUM, MAX, MIN, AVG, and COUNT are some of the aggregate functions. For finding the result, you use the GROUP BY clause or the PARTITION BY clause for grouping together the rows.
Analytical/Non-Aggregate Window Functions
analytical window functions are those that determine a window based on the current row and then compute the outcomes based on that window of records. In SQL, the result is frequently returned as a collection of records. RANK, DENSE RANK, CUME DIST, RANK, LEAD, LAG, etc. are typical examples of Analytical window functions.
Aggregate Window Functions
Let’s look at some aggregate window functions now.
SUM Function
The SUM window function determines the sum of a specified window’s number of columns. In the event that there are no columns to be grouped by, this produces a scalar result. A GROUP BY clause may be used to obtain the totals for each row if we need to get the sums for each group.
AVG Function
The AVG function is used to find the average of the numerical field. It is similar to the SUM functions.
MIN and MAX Functions
These functions are used to calculate the minimum and maximum values from the table records. The value will only be returned from that row specifically if there is only one row in the window.
Analytical Window Functions
ROW_NUMBER Function
This function assigns the incremental row number to each row in the table or the selected window of the table. Read our detailed guide on the ROW_NUMBER function from here.
RANK and DENSE RANK Functions
Because ties are addressed in ranking but not in the row number function, the Ranking is almost identical to the Row Number function.
A tie occurs when two or more records that the function has been applied to have the same numerical value. The ties in the RANK function will receive the same number. For instance, the next reported rank will be 15 rather than 6 if there are 10 ties at rank 5.
This is the result of RANK skipping the rows that are tied. We have a feature called the DENSE RANK function that allows us to get around this. Ties will be handled using the DENSE RANK without skipping the row counts.
You can read our detailed guide on the MySQL dense_rank from here.
Other Analytical Window Functions
There are some other window functions as well that are provided by MySQL such as LEAD(), LAG(), NTILE(), RANK(), FIRST_VALUE() etc. We will see each of them in a separate tutorial.
Summary
So, this is an introduction to MySQL window functions where we saw what is window functions and what they do. You all are aware of the aggregate functions so we will cover only non-aggregate window functions in the upcoming tutorials. Till then, stay tuned!
References
MySQL Official Documentation on Windows Functions.