Window Functions in SQL
A comprehensive article on basic window functions along with examples.
Window functions are similar to aggregate functions but they help us to concentrate on a window, not all the rows.
Window functions apply to aggregate and ranking functions over a particular window (set of rows)
Basic Syntax
SELECT coulmn_name1,
window_function(column_name2),
OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column
FROM table_name;window_function= any aggregate or ranking function
column_name1= column to be selected
coulmn_name2= column on which window function is to be applied
column_name3= column on whose basis partition of rows is to be done
new_column= Name of new column
table_name= Name of table
Note
- Partitions rows into form set of rows. (PARTITION BY clause is used)
- Orders rows within those partitions into a particular order. (ORDER BY clause is used)
- If partitions aren’t done, then ORDER BY orders all rows of the table.
Using Aggregate Functions as Window Functions
Aggregate functions such as SUM(), COUNT(), AVERAGE(), MAX(), MIN() applied over a particular window (set of rows) are called aggregate window functions.
Example
To find the average salary of employees for each department and order employees within a department by age.
SELECT Name, Age, Department, Salary,
AVERAGE(Salary) OVER( PARTITION BY Department ORDER BY Age) AS Avg_Salary
FROM employee
To Find Maximum Salary in each department
select e.*, max(salary) over(partition by dept_name) as max_salary
from employee e;
row_number() window function
It assigns consecutive integers to all the rows within a partition. Within a partition, no two rows can have the same row number.
Examples
Assign different row numbers to every row
select e.*,
row_number() over(partition by dept_name) as rn
from employee e;
To Fetch the first 2 employees from each department to join the company
select * from (
select e.*,
row_number() over(partition by dept_name order by emp_id) as rn
from employee e) x
where x.rn < 3;
RANK() window function
the rank function assigns a rank to all the rows within every partition. Rank is assigned such that rank 1 is given to the first row and rows having the same value are assigned the same rank.
For the next rank after two same rank values, one rank value will be skipped.
Example
To Fetch the top 3 employees in each department earning the max salary.
select * from (
select e.*,
rank() over(partition by dept_name order by salary desc) as rnk
from employee e) x
where x.rnk < 4;DESNE_RANK() Window function
Assigns rank to each row within a partition. Just like rank function first row is assigned rank 1 and rows having the same value have the same rank.
The difference between RANK() and DENSE_RANK() is that in DENSE_RANK(), for the next rank after two same ranks, the consecutive integer is used, no rank is skipped.
select * from (
select e.*,
dense_rank() over(partition by dept_name order by salary desc) as rnk
from employee e) x
where x.rnk < 4;
Note
row_number(),rank(),dense_rank() are rankfunctions.ORDER BY() should be specified compulsorily while using rank window functions.
In ROW_NUMBER() the row numbers are consecutive integers within each partition.
The difference between rank and dense rank is that in dense rank there is no gap between rank values while there is a gap in rank values after repeated rank.
lead() and lag() window functions
Lag() function is used to access previous rows data as per defined offset value.
Lead() function to access subsequent rows data as per defined offset value.
Example
To fetch a query to display if the salary of an employee is higher, lower, or equal to the previous employee.
select e.*,
lag(salary) over(partition by dept_name order by emp_id) as prev_empl_sal,
lead(salary) over(partition by dept_name order by emp_id) as next_empl_sal
from employee e;There are a few other window functions like FIRST_VALUE, LAST_VALUE, etc. These will be covered sometime in the future. You can also check the reference links down below for the same.
If you found this article helpful. Consider clapping and follow me on medium.
Let's connect via Linkedin or Twitter for any further discussions on big data and ML
References
