Window Functions in SQL

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()

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

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:

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: