PadhaiTime Logo
Padhai Time

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:

undefined

 

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:

undefined

 

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:

undefined

 

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.

 

Bengaluru, India
contact.padhaitime@gmail.com
  • We collect cookies and may share with 3rd party vendors for analytics, advertising and to enhance your experience. You can read more about our cookie policy by clicking on the 'Learn More' Button. By Clicking 'Accept', you agree to use our cookie technology.
    Our Privacy policy can be found by clicking here