A window function in SQL is a type of function that performs a specific calculation across a specific set of rows or subset of the data (“window”). It is always identified by the OVER clause, which defines the "window" or subset of data the function looks at.
The syntax for window functions is as follows:
where ,
- The SELECT clause defines the columns you want to select from the table table_name.
- function() is the window function you want to use.
- The OVER clause defines the partitioning and ordering of rows in the window.or subset of the data.
- The PARTITION BY clause divides rows into partitions based on the specified partition_expression; if the partition_expression is not specified, the result set will be treated as a single partition. i.e., the entire dataset
- The ORDER BY clause uses the specified order_expression to define the order in which rows will be processed within each partition; if the order_expression is not specified, rows will be processed in an undefined order.
- output_column_name is the name of your output column.
Types of Window Functions :
Example : Let us take a simple table to understand window functions.
Suppose, the table is as below :
Aggregate Window Functions :
- Avg() : Average
Using AVG(), we will calculate the average salary within each department for the above table
The SQL code is as below :
SELECT name, department, salary,
AVG(salary) OVER (
PARTITION BY department
) AS avg_salary
FROM employee;
Here , the partition by clause divides the employees into separate groups based on department, and calculate the average separately for each group.
SQL create the temporary groups as :
- IT : (50000+70000+70000)/3 = 63333.3333
- HR : (45000+60000)/2 = 52500
In this case , all individual employees are still displayed.
If we calculate the average salary of each department using GROUP BY :
The SQL code is as below :
SELECT department, AVG(salary) FROM employee
GROUP BY department;
The output will be as follows :
Here , the individual employee rows are gone.
Thus , we understand that the partition by does not collapse the rows.
Similarly , the other aggregate functions perform their respective tasks
- MAX() returns the maximum value in the expression.
- MIN() returns the minimum value in the expression.
- SUM() returns the sum of all the values, or only the DISTINCT values, in the expression.
- COUNT() returns the number of items found in a group.
Value Window Functions :
Suppose you want to arrange the employees in descending order of their salaries.
- Row_number() : It assigns a unique sequential integer to rows within a partition of a result set.
The SQL code Is as below :
SELECT name, department, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employee;
In this case , SQL will first order the salary in descending order as it is given in the OVER clause, then it will assign each row a unique integer which will be displayed in a separate column named “row_num”.
The output will as below :
Thus , the row_number() function has assigned unique number to each row and ordered the salary in descending order. But what if we want to rank them . For example: the employee with highest salary should be given rank 1 , then the second highest salary should be given rank 2 and so on. In such cases, we need to use rank() and dense_rank() window functions .
- Rank() : It gives rank to each row based on the condition (ascending or descending) , but skips the next integer when there is a tie .
For example : Rank the employees in descening order of their salary.
The SQL code is as below :
SELECT name, department, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employee;
The output as below :
Here , Priya and Amit have same salary and it is highest , so they are given rank 1. But Riya who is having the second highest salary is given rank 3 because RANK() has skip the number 2 as there was a tie.
To overcome this problem , we use DENSE_RANK . It basically means give rank but “do not skip”
- DENSE_RANK()
The SQL code is as below :
SELECT name, department, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employee;
The output as below :
IMPORTANT : Find the second highest salary using dense_rank.
The SQL code is as follows :
SELECT name, salary FROM
( SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employee ) AS ranked_employees
WHERE salary_rank = 2;
- PERCENT_RANK() : It tell us the position of the row with respect to other rows by giving a number between 0 and 1
Example : Find where does each employee stand with respect to salary compared with the other employees.
The SQL code is as follows :
SELECT name, salary,
PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank
FROM employee;
The output is as follows :
- NTILE() : It divides the rows into specific number of groups .
SELECT name, salary,
NTILE(2) OVER (ORDER BY salary DESC) AS bucket
FROM employee;
The output is as below :
RANKING WINDOW FUNCTIONS :
- LAG() : IT gives the previous row value of the corresponding row.
Example : Compare previous salary with current salary .
The SQL code is as below :
SELECT name, salary,
LAG(salary) OVER (ORDER BY salary) AS previous_salary
FROM employee;
The output is :
- LEAD() : It gives the next row value to a corresponding row
Example :
SELECT name, salary,
LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM employee;
The output is :
Related Links :
Author:
Chaitali Kate
Do visit our channel to know more: SevenMentor
Chaitali Kate
Expert trainer and consultant at SevenMentor with years of industry experience. Passionate about sharing knowledge and empowering the next generation of tech leaders.