Introduction
Window functions apply ranking and aggregation functions over a certain window (set of rows). The window is defined by the OVER clause when used with window functions. the following two things:
- divides rows into groups called sets. (The clause "PARTITION BY" is utilised.)
- rows within such partitions are put in a certain sequence. (The clause ORDER BY is utilised.)
Syntax
Aggregate window function
Aggregate window functions are different aggregate functions, such as SUM(), COUNT(), AVERAGE(), MAX(), and MIN(), applied over a specific window (set of data).
Consider the employee table:
Example
Find average salary of employees for each department.
Output:
The average salary within each department is calculated and displayed in column Average_Salary.
Ranking Window Functions
RANK()
The rank function, as its name implies, ranks each row within a partition. The first row is given rank 1, and subsequent rows with the same value are given the same rank. One rank value will be skipped for the rank that follows two identical rank values.
DENSE RANK()
It gives each row in the partition a rank. Similar to a rank function, the first row is given rank 1, and subsequent rows with the same value have the same rank. The distinction between RANK() and DENSE RANK() is that no rank is skipped when using a consecutive integer for the next rank following two of the same rank in DENSE RANK().
ROW NUMBER()
This function assigns sequential integers to each row in the partition. No two rows can have the same row number within a division.
Note: ORDER BY() should be specified compulsorily while using rank window functions.
Example:
select d_id, emp_name, sales, rank() over(order by sales) as ‘rank’ from q1_sales;
Conclusion
Window functions and its types with examples were discussed in the article.