Window functions in SQL allows us to perform calculations across a group of related rows without combining them into a single result. They allow us to calculate values such as totals, averages, rankings, and running totals while keeping individual rows in the output.
Syntax of Window Functions in SQL
It has the following syntax.
SELECT column_name, window_function(column_name) OVER ( [PARTITION BY column_name] [ORDER BY column_name] ) AS result_column FROM table_name;
Explanation of the syntax:
- SELECT: Specifies the columns to retrieve from the table.
- window_function(): Defines the function used for calculation or ranking.
- OVER(): Defines the window of rows used by the function.
- PARTITION BY: Divides rows into separate groups.
- ORDER BY: Specifies the order of rows within each group.
- AS: Assigns a name to the calculated result.
- FROM: Specifies the source table.
Important Clauses Used in Window Functions
There are three important clauses commonly used with SQL window functions.
1. OVER Clause
The OVER() clause defines the set of rows on which a window function performs its calculation.
Example:
SELECT name, salary, AVG(salary) OVER() AS avg_salary FROM employee;
This query calculates the average salary of all employees and displays the same average value alongside every employee.
2. PARTITION BY Clause
The PARTITION BY clause divides the result set into separate groups. The window function performs its calculation independently within each group.
Example:
SELECT name, department, salary, AVG(salary) OVER( PARTITION BY department ) AS avg_salary FROM employee;
This query calculates the average salary separately for each department.
3. ORDER BY Clause
The ORDER BY clause specifies the order in which rows are processed within a window.
Example:
SELECT name, salary, ROW_NUMBER() OVER( ORDER BY salary DESC ) AS row_num FROM employee;
This query assigns row numbers based on salary, starting with the highest salary.
Types of Window Functions in SQL
SQL window functions can be broadly classified into two main categories:
- Aggregate Window Functions
- Ranking Window Functions
Let's understand each type with examples.
1. Aggregate Window Functions
Aggregate window functions perform calculations on a group of rows and return the result for each individual row. Some commonly used aggregate window functions are:
| Function | Description |
|---|---|
| SUM() | It calculates the total of values. |
| AVG() | It calculates the average of values. |
| COUNT() | It counts the number of rows. |
| MAX() | It returns the maximum value. |
| MIN() | It returns the minimum value. |
Example of Aggregate Window Functions
First, let's create an Employee table to understand aggregate window functions.
CREATE TABLE employee ( id INT, name VARCHAR(50), department VARCHAR(50), salary INT );
Insert some sample records:
INSERT INTO employee VALUES (1, 'Andrew', 'Finance', 50000), (2, 'Brian', 'Finance', 50000), (3, 'Charles', 'Finance', 20000), (4, 'Daniel', 'Sales', 30000), (5, 'Ethan', 'Sales', 20000);
The Employee table contains the following data:
| id | name | department | salary |
|---|---|---|---|
| 1 | Andrew | Finance | 50000 |
| 2 | Brian | Finance | 50000 |
| 3 | Charles | Finance | 20000 |
| 4 | Daniel | Sales | 30000 |
| 5 | Ethan | Sales | 20000 |
AVG() Function
The AVG() function calculates the average value of a specified column within a window.
Example: Calculate the average salary of employees in each department.
SELECT name, department, salary, AVG(salary) OVER( PARTITION BY department ) AS avg_salary FROM employee;
Output:
| name | department | salary | avg_salary |
|---|---|---|---|
| Andrew | Finance | 50000 | 40000 |
| Brian | Finance | 50000 | 40000 |
| Charles | Finance | 20000 | 40000 |
| Daniel | Sales | 30000 | 25000 |
| Ethan | Sales | 20000 | 25000 |
SUM() Function
The SUM() function calculates the total of values within a specified window.
Example: Calculate the total salary of employees in each department.
SELECT name, department, salary, SUM(salary) OVER( PARTITION BY department ) AS total_salary FROM employee;
Output:
| name | department | salary | total_salary |
|---|---|---|---|
| Andrew | Finance | 50000 | 120000 |
| Brian | Finance | 50000 | 120000 |
| Charles | Finance | 20000 | 120000 |
| Daniel | Sales | 30000 | 50000 |
| Ethan | Sales | 20000 | 50000 |
COUNT() Function
The COUNT() function counts the number of rows within a specified window.
Example: Count the total employees in each department.
SELECT name, department, COUNT(*) OVER( PARTITION BY department ) AS total_employees FROM employee;
Output:
| name | department | total_employees |
|---|---|---|
| Andrew | Finance | 3 |
| Brian | Finance | 3 |
| Charles | Finance | 3 |
| Daniel | Sales | 2 |
| Ethan | Sales | 2 |
MAX() Function
The MAX() function returns the highest value within a specified window.
Example: Find the highest salary in each department.
SELECT name, department, salary, MAX(salary) OVER( PARTITION BY department ) AS highest_salary FROM employee;
Output:
| name | department | salary | highest_salary |
|---|---|---|---|
| Andrew | Finance | 50000 | 50000 |
| Brian | Finance | 50000 | 50000 |
| Charles | Finance | 20000 | 50000 |
| Daniel | Sales | 30000 | 30000 |
| Ethan | Sales | 20000 | 30000 |
MIN() Function
The MIN() function returns the lowest value within a specified window.
Example: Find the lowest salary in each department.
SELECT name, department, salary, MIN(salary) OVER( PARTITION BY department ) AS lowest_salary FROM employee;
Output:
| name | department | salary | lowest_salary |
|---|---|---|---|
| Andrew | Finance | 50000 | 20000 |
| Brian | Finance | 50000 | 20000 |
| Charles | Finance | 20000 | 20000 |
| Daniel | Sales | 30000 | 20000 |
| Ethan | Sales | 20000 | 20000 |
2. Ranking Window Functions
Ranking window functions assign numbers or ranks to rows based on a specified order.
They are useful for finding top-performing employees, ranking students, comparing sales performance, and analyzing records.
Common ranking window functions include:
| Function | Description |
|---|---|
| ROW_NUMBER() | Assigns a unique number to each row. |
| RANK() | Assigns the same rank to equal values and skips subsequent ranks. |
| DENSE_RANK() | Assigns the same rank to equal values without skipping ranks. |
| PERCENT_RANK() | Calculates the relative rank of a row between 0 and 1. |
Let's understand these functions with examples.
1. RANK() Function
The RANK() function assigns a rank to each row based on the specified order. If two or more rows have the same value, they receive the same rank, and the next rank is skipped.
Example: Rank employees based on their salaries within each department.
SELECT name, department, salary, RANK() OVER( PARTITION BY department ORDER BY salary DESC ) AS emp_rank FROM employee;
Output:
| name | department | salary | emp_rank |
|---|---|---|---|
| Andrew | Finance | 50000 | 1 |
| Brian | Finance | 50000 | 1 |
| Charles | Finance | 20000 | 3 |
| Daniel | Sales | 30000 | 1 |
| Ethan | Sales | 20000 | 2 |
2. DENSE_RANK() Function
The DENSE_RANK() function assigns ranks to rows based on their values. If multiple rows have the same value, they receive the same rank. However, it does not skip the next rank.
Example: Rank employees based on salary without skipping rank numbers.
SELECT name, department, salary, DENSE_RANK() OVER( PARTITION BY department ORDER BY salary DESC ) AS emp_dense_rank FROM employee;
Output:
| name | department | salary | emp_dense_rank |
|---|---|---|---|
| Andrew | Finance | 50000 | 1 |
| Brian | Finance | 50000 | 1 |
| Charles | Finance | 20000 | 2 |
| Daniel | Sales | 30000 | 1 |
| Ethan | Sales | 20000 | 2 |
3. ROW_NUMBER() Function
The ROW_NUMBER() function assigns a unique sequential number to each row based on the specified ordering.
Unlike RANK() and DENSE_RANK(), it does not assign the same number to duplicate values.
Example: Assign a unique row number to employees based on their salaries.
SELECT name, department, salary, ROW_NUMBER() OVER( PARTITION BY department ORDER BY salary DESC, id ) AS emp_row_no FROM employee;
Output:
| name | department | salary | emp_row_no |
|---|---|---|---|
| Andrew | Finance | 50000 | 1 |
| Brian | Finance | 50000 | 2 |
| Charles | Finance | 20000 | 3 |
| Daniel | Sales | 30000 | 1 |
| Ethan | Sales | 20000 | 2 |
4. PERCENT_RANK() Function
The PERCENT_RANK() function calculates the relative ranking of a row within a partition. Its result ranges from 0 to 1.
The formula is:
PERCENT_RANK() = (RANK - 1) / (Total Rows - 1)
Example: Calculate the relative salary ranking of employees within each department.
SELECT name, department, salary, PERCENT_RANK() OVER( PARTITION BY department ORDER BY salary DESC ) AS emp_percent_rank FROM employee;
Output:
| name | department | salary | emp_percent_rank |
|---|---|---|---|
| Andrew | Finance | 50000 | 0.00 |
| Brian | Finance | 50000 | 0.00 |
| Charles | Finance | 20000 | 1.00 |
| Daniel | Sales | 30000 | 0.00 |
| Ethan | Sales | 20000 | 1.00 |
Difference Between RANK(), DENSE_RANK(), and ROW_NUMBER()
These three functions are commonly used for ranking records in SQL. However, their behavior is different when duplicate values are present.
Consider the following salary values:
| Employee | Salary |
|---|---|
| Andrew | 50000 |
| Brian | 50000 |
| Charles | 20000 |
| Daniel | 10000 |
The ranking results are:
| Employee | RANK() | DENSE_RANK() | ROW_NUMBER() |
|---|---|---|---|
| Andrew | 1 | 1 | 1 |
| Brian | 1 | 1 | 2 |
| Charles | 3 | 2 | 3 |
| Daniel | 4 | 3 | 4 |
Main Differences:
- RANK(): Assigns the same rank to duplicate values and skips the next rank.
- DENSE_RANK(): Assigns the same rank to duplicate values without skipping ranks.
- ROW_NUMBER(): Assigns a unique sequential number to every row.
Difference Between Window Functions and GROUP BY
Window functions and GROUP BY are both used to perform calculations on data, but they produce different results.
| Window Functions | GROUP BY |
|---|---|
| Retains individual rows. | Combines rows into groups. |
| Uses the OVER() clause. | Uses the GROUP BY clause. |
| Can calculate rankings and running totals. | Commonly calculates group-level aggregates. |
| Displays calculated values alongside original rows. | Returns one result row per group. |
Example using GROUP BY:
SELECT department, AVG(salary) AS avg_salary FROM employee GROUP BY department;
This query returns one row for each department.
Example using a Window Function:
SELECT name, department, salary, AVG(salary) OVER( PARTITION BY department ) AS avg_salary FROM employee;
This query displays every employee along with their department's average salary.
Common Mistakes When Using Window Functions
While working with SQL window functions, beginners may encounter some common mistakes.
1. Incorrect PARTITION BY Clause
Using the wrong partition column can produce incorrect calculations. Always select the appropriate column for dividing records into groups.
2. Missing ORDER BY Clause
Ranking functions usually require an ORDER BY clause to define the ranking order. Without it, the ranking may not represent the intended result.
3. Confusing RANK() and DENSE_RANK()
Both functions assign the same rank to duplicate values, but RANK() skips subsequent ranks whereas DENSE_RANK() does not.
4. Using Window Functions Directly in WHERE
In many SQL databases, window functions cannot be used directly in the WHERE clause. Use a CTE or subquery to filter their results.
Example:
WITH EmployeeRank AS ( SELECT name, department, salary, RANK() OVER( PARTITION BY department ORDER BY salary DESC ) AS emp_rank FROM employee ) SELECT * FROM EmployeeRank WHERE emp_rank = 1;
This query returns the highest-paid employee or employees in each department, including ties.
5. Ignoring Performance
Window functions may require sorting and processing large sets of rows. For better performance, use appropriate indexes, filter unnecessary records, and review the query execution plan.