Window Functions in SQL
FREE
Overview
In SQL, window functions, also known as analytical functions, operate similarly to aggregate functions, which compute all table rows in a set. However, window functions differ in that they do not aggregate all rows into a single output row. Instead, these functions return all rows in a table, with a computed column showing the specific function applied to each row. Common examples of window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), and LAG().
What are the Window Functions in SQL?
Window functions are used to perform some operation on a group of rows and provide a resultant value for each row in the table. The word window in the term "window function" refers to a set of rows in the table on which the function will run. By knowing the working of window functions in SQL, we can solve most of the complex SQL problems easily.
For instance, if you need to fetch the top earners from each department in your employee table or show the minimum and maximum salary in each department corresponding to each employee record, you'll likely need to use window functions. Without these functions, such queries could be difficult to write and execute efficiently. This article will explore various examples of SQL queries that benefit from the use of window functions.
Let's first look at the types of functions on which we can apply a window in SQL:
- Aggregate Window Functions in SQL
- SUM()
- MIN()
- MAX()
- COUNT()
- AVG()
- Ranking Window Functions in SQL
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
- Value Window Functions in SQL
- LEAD()
- LAG()
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
Syntax
While SQL queries using conventional methods, such as simple aggregate or ranking functions, may be straightforward to write, using window functions requires a slightly different syntax. In this section, we will examine the exact syntax for applying window functions to SQL queries.
Syntax:
Returns: A table with an extra column that is represented by the name <new_column_name> and contains resultant values as per the window function used.
Arguments
| Argument | Explanation |
|---|---|
| window_function_name | It specifies the window function's name. It is a mandatory argument. |
| column_name | It specifies the column name on which the computation should be performed. |
| OVER() | It is used with the window functions to specify the partitions and the order of rows in which the result should be obtained. It is also a mandatory argument to use the window function property. |
| PARTITION BY <field_name> | It is used to define the window for window functions, the group of rows on which the window function runs. If PARTITION BY is not given, data will be aggregated on the full table. field_name specifies the column on which we want to apply the partitioning. It is also a mandatory argument. |
| ORDER BY <field_name> | The rows inside each group/partition are sorted using ORDER BY. field_name specifies the column by which we want to sort the partitioning. It is an optional argument and by default, a random order will be followed. |
Example:
We are using a pre-created table for computational purposes (using window functions).
Employee table:
SQL Query:
Output:
Explanation: MAX() aggregate window function in the above SQL query is used to calculate the maximum salary for each department in a group from the employee table. The above query returned an extra column max_salary that represents the maximum salary within a particular department group. OVER(partition by dept_name) groups the department names and applies a window on the group/partition.
Examples
We are using the same employee table in all of the below examples.
1. Aggregate Window Functions
- SUM() function: This function returns the sum of a column containing numeric values.
Example:
Let's fetch the total salary of employees within a department group using the SUM() aggregate window function.
SQL Query:
Output:
- MIN() function: This function returns the minimum numeric value present in a particular column.
Let's fetch the minimum salary of an employee within a department group using the MIN() aggregate window function.
SQL Query:
Output:
- COUNT() function: This function returns the number of rows present in a column satisfying the conditions in the SQL query.
Let's fetch the total number of employees within a department group using the COUNT() aggregate window function.
SQL Query:
Output:
- AVG() function: This function returns an average of all the numeric values present in a column.
Let's fetch the average salary of employees within a department group using the AVG() aggregate window function.
SQL Query:
Output:
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
2. Ranking Window Functions
- ROW_NUMBER() function: This function is going to assign a unique sequential value to each of the records in the table.
Let's assign a unique row number to each employee within a department group using the ROW_NUMBER() ranking window function.
SQL Query:
Output:
- RANK() function: The RANK() function assigns a unique value to each record in a table, with the same value assigned to multiple records if they share the same values. If multiple records have the same values, the function skips ranks accordingly.
Let's fetch the top employees in each department earning the maximum salary using the RANK() ranking window function.
SQL Query:
Output:
- DENSE_RANK() function: This function assigns a unique rank to the records in the table. It is similar to the RANK() function but it doesn't skip ranks if two or more records have the same ranks.
Let's fetch the top employees in each department earning the maximum salary with no skip in the ranks using the DENSE_RANK() ranking window function.
SQL Query:
Output:
3. Value Window Functions
- LAG() function: This function allows us to retrieve data from the preceding row in the same result set, without using the SQL joins. As you can see in the example below, we are using the LAG function to find the prior employee's salary.
Let's fetch the previous employee's salary with respect to the emp_id using the LAG() value window function.
SQL Query:
Output:
- LEAD() function: This function allows us to retrieve data from the next row in the same result set, without using SQL joins. As you can see in the example below, we are using the LEAD() function to find the next employee's salary.
Let's fetch the next employee's salary with respect to the emp_id using the LEAD() value window function.
SQL Query:
Output:
Conclusion
- Window functions in SQL are commonly referred to as analytical functions.
- OVER() is used to specify a window clause for the window functions like the partition by clause and the order by clause.
- PARTITION BY clause is used to define the window for window functions, the group of rows on which the window function is applied. If PARTITION BY is not given, data will be aggregated on the full table.
- Window functions are a specific set of functions available in SQL that include aggregate functions (SUM(), COUNT(), MIN(), etc.), ranking functions (ROW_NUMBER(), RANK(), and DENSE_RANK()), and value functions (LEAD() and LAG()). Unlike conventional aggregate functions that return a single row output, window functions return each row of a table with a computed column that shows the specific function applied to each row.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more