SQL Window Function | Board Infinity

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

SELECT column_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 to apply the window function

column_name3= column on the basis of which to divide the rows

new_column= Name of new column
table_name= Name of table

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:

Name	Age	Department	Salary
Shruthi	22	Finance	30,000
Keshavinee	24	Sales	33,678
Varsha	76	Marketing	50,000
Smrithi	23	Sales	35,000
Pooja	25	Finance	32,786

Example

Find average salary of employees for each department.

SELECT Name, Age, Department, Salary,
AVERAGE(Salary) OVER( PARTITION BY Department) AS Average_Salary
FROM employee

Output:

Name	Age	Department	Salary	Average_salary
Shruthi	22	Finance	30,000	31,393
Keshavinee	24	Sales	33,678	34,339
Varsha	76	Marketing	50,000	50,000
Smrithi	23	Sales	35,000	34,339
Pooja	25	Finance	32,786	31,393

The average salary within each department is calculated and displayed in column Average_Salary.

write your code here: Coding Playground

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;

|------------|-----------------|--------|-------|
| d_id | emp_name | sales | rank |
|------------|-----------------|--------|-------|
| 1 | Rashita | 8227 | 1 |
| 3 | Mayrita | 9308 | 2 |
| 2 | Haviva | 9308 | 2 |
| 1 | Josephine | 9710 | 4 |
| 3 | Abellan | 12369 | 5 |
| 3 | Urmila | 15427 | 6 |
|------------|-----------------|--------|-------|
6 rows selected (0.174 seconds)a

Conclusion

Window functions and its types with examples were discussed in the article.