In SQL, a window function is a function that performs a calculation across a set of rows that are related to the current row. These functions operate on a set of rows called a window and return a single value for each row in the result set.
Window functions can be used in the SELECT, FROM, and WHERE clauses of a SELECT statement. They are often used to calculate a running total or moving average or to rank rows within a result set.
Employee Table
| emp_id | emp_name | salary | dept_id | manager_id |
|---|---|---|---|---|
| E1 | Rahul | 15000 | D1 | M1 |
| E2 | Manoj | 15000 | D1 | M1 |
| E3 | James | 55000 | D2 | M2 |
| E4 | Michael | 25000 | D2 | M2 |
| E5 | Ali | 20000 | D10 | M3 |
| E6 | Robin | 35000 | D10 | M3 |
Here are some examples of how you could use SQL window functions on the above dataset:
- RANK() function:
You can use the RANK() function to assign a rank to each employee based on their salary, with the highest salary receiving a rank of 1. For example:
SELECT emp_id, emp_name, salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM Employee;
Output
| emp_id | emp_name | salary | salary_rank |
|---|---|---|---|
| E3 | James | 55000 | 1 |
| E6 | Robin | 35000 | 2 |
| E4 | Michael | 25000 | 3 |
| E5 | Ali | 20000 | 4 |
| E1 | Rahul | 15000 | 5 |
| E2 | Manoj | 15000 | 5 |
- LAG() function:
You can use the LAG() function to retrieve the salary of the previous employee in the list. For example:
SELECT emp_id, emp_name, salary,
LAG(salary, 1) OVER (ORDER BY salary) as prev_salary
FROM Employee;
Output
| emp_id | emp_name | salary | prev_salary |
|---|---|---|---|
| E1 | Rahul | 15000 | |
| E2 | Manoj | 15000 | 15000 |
| E5 | Ali | 20000 | 15000 |
| E4 | Michael | 25000 | 20000 |
| E6 | Robin | 35000 | 25000 |
| E3 | James | 55000 | 35000 |
- SUM() function:
You can use the SUM() function to calculate the total salary of all employees in each department. For example:
SELECT distinct dept_id, SUM(salary) OVER (PARTITION BY dept_id) as dept_total_salary
FROM Employee;
Output
| dept_id | dept_total_salary |
|---|---|
| D1 | 30000 |
| D10 | 55000 |
| D2 | 80000 |
In this example, the SUM() function is used as a window function to calculate the running total of the value column. The OVER clause specifies the window over which the function operates, in this case, the set of rows ordered by the id column. The result of the query would be a list of rows with the id that are distinct in the table, value, and running_total columns. The running_total column would contain the sum of all the value rows up to and including the current row, ordered by id.
There are many other window functions available in SQL, including AVG(), COUNT(), MAX(), MIN(), and RANK(). You can find more information about window functions in the documentation for your specific database management system.
Code Runner
Copy the below queries in the code runner to see the result
SELECT emp_id, emp_name, salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM Employee;
SELECT emp_id, emp_name, salary,
LAG(salary, 1) OVER (ORDER BY salary) as prev_salary
FROM Employee;
SELECT distinct dept_id, SUM(salary) OVER (PARTITION BY dept_id) as dept_total_salary
FROM Employee;