Introduction of SQL Window Functions

SQL is one of the most widely-used languages for working with relational databases, and understanding how to use it effectively is crucial for data professionals. One of the most powerful features of SQL is its support for window functions, which allow you to perform calculations and aggregations across multiple rows of data in a way that would otherwise be difficult or impossible.

Why Do We Learn SQL Window Functions?

Window functions are incredibly versatile and can be used to solve a wide range of problems. They allow you to perform calculations and aggregations across multiple rows of data in a way that would otherwise be difficult or impossible. Some of the most common use cases for window functions include:

• Ranking rows based on the values in one or more columns
• Assigning unique numbers to each row in a result set
• Calculating running totals or subtotals
• Comparing values in different rows

In this post, we’ll introduce the most common 5 window functions in SQL and provide examples of how to use them effectively.

1. ROW_NUMBER() – assigns a unique number to each row within a result set, based on the order specified in the ORDER BY clause. This function can be used to implement pagination, to find a specific row in a result set, or to determine the ranking of a row based on specific criteria.

SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) as "Row Number"
FROM employees;

2. RANK() – assigns a unique rank to each row within a result set, based on the order specified in the ORDER BY clause. If there are ties, the same rank is assigned to multiple rows. This function is useful for ranking items based on specific criteria, such as sales or scores.

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as "Rank"
FROM employees;

3. DENSE_RANK() – assigns a unique rank to each row within a result set, based on the order specified in the ORDER BY clause. If there are ties, the next rank is not skipped. This function is useful for ranking items based on specific criteria, such as sales or scores, and avoiding gaps in the rankings when there are ties.

SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as "Dense Rank"
FROM employees;

4. NTILE(n) – assigns a “bucket” number to each row within a result set, based on the number of buckets specified (n). This function can be used to divide a set of data into equal groups, such as quartiles or deciles, for statistical analysis or visualization.

SELECT name, salary, NTILE(4) OVER (ORDER BY salary DESC) as "Quartile"
FROM employees;

5. LAG() and LEAD() – access data from a previous or next row within a result set, respectively. These functions can be used to compare values between rows, such as calculating the difference in salary between two employees or determining the change in stock prices over time.ult set, respectively.

SELECT name, salary, LAG(salary, 1) OVER (ORDER BY salary DESC) as "Previous Salary"
FROM employees;

If you want to get more details and references regarding to window functions usage, please check the following documentation:

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html