Window Functions in SQL

Window functions in SQL helps in our analysis a lot. They help in assigning the calculated metric to each of the rows present in the table.

 

OVER() clause helps us in achieving this. It can be used with Aggregate or non-Aggregate functions.

 

1) Aggregate Functions where OVER() clause can be used:

• MIN()
• MAX()
• SUM()
• COUNT()
• STD()
• VARIANCE()

2) Non-Aggregate Functions where OVER() clause can be used:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

 

Let us look at the Data First:

 

A) Let us use Aggregate functions with Over clause to get minimum, maximum and sum of the marks and assign them to each row as separate columns.

 

Query:

select 

        name, 

        subject, 

        marks, 

        MIN(marks) OVER() as Overall_Min,

        MAX(marks) OVER() as Overall_Max,

        SUM(marks) OVER() as Overall_Sum,

        MIN(marks) OVER(PARTITION BY subject) AS Subject_Wise_Min,

        MAX(marks) OVER(PARTITION BY subject) AS Subject_Wise_Max,

         SUM(marks) OVER(PARTITION BY subject) AS Subject_Wise_Sum

From Student;

 

Output:

 

There are some scenarios where we need to assign the max score in each row or we need to assign max score category-wise to each row. Hence in these scenarios, Window functions are very useful.

 

B) Now let us look into Non-Aggregate functions with Over clause.

 

Query:

select  

        name, 

        subject, 

        marks, 

        ROW_NUMBER() OVER(ORDER BY marks desc) as ROW_NUMBER,

        RANK() OVER(ORDER BY marks desc) as RANK,

        DENSE_RANK() OVER(ORDER BY marks desc) as DENSE_RANK

From Student;

 

Output:

 

Row_NUMBER() simply assigns the row number to each row based on ascending or descending order of the attribute mentioned.

Whereas, Rank assigns the same row number to the rows when there is a tie.

 

Difference Between Rank and Dense Rank?

In the Rank function if there is a tie, the same row_number/rank is assigned and next rank is skipped.

As 1, 1 are assigned to first and second row, and row_number “2” is skipped and third row got “3”

 

However, if there is a tie in Dense Rank, the same row number/rank is assigned and next rank is not skipped.

Here row_number “2” is not skipped and the third row gets “2” as Rank.