top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-

Data Through SQL Windows

Today's world contains so much data in the applications that we need advanced SQL functions to analyze this data. Few of these advanced analytics functions are are grouped under Window Functions for SQL.


Lets analyze the different window functions available in SQL


Consider a dataset of students named Report with there marks and city where they live


1. PARTITION BY


To find average marks of students from a particular city we can write the query as below


select student, marks, city, avg(marks) over (partition by city order by student) from report order by student;


2. PRECEDING AND FOLLOWING


To find the marks and the minimum marks of each student and the students just before and after them we can write the below query


select

student, marks,

min(marks) over (order by student ROWS between 1 preceding and 1 following)

from report order by student;


3. ROW_NUMBER, RANK and DENSE_RANK


To find a unique number for each row even if they are equal we can use the row_number function.

To find the rank or dense_rank is the difference is if two values are equal then rank will skip one number and dense_rank will give the next number.


select student, marks,

row_number() over (order by marks),

rank() over (order by marks),

dense_rank() over (order by marks)

from report order by marks;

4. LAG and LEAD


To compare data of current row with previous and next row we can use lead and lag functions.

Consider a class report where we wanted to see the marks of the student in front of us and the number of marks we got more than the student behind us by


select student, marks,

lag(marks, 1) over (order by marks) as marks_of_student_infront_of_me,

lead(marks, 1) over (order by marks) - marks as how_much_i_was_ahead_of_the_student_behind_me

FROM report order by marks;


31 views0 comments

Recent Posts

See All

Comments

Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page