SQL Window Function

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.