When and how to use Window Functions in PostgreSQL?

Question

I've been learning Window Functions (e.g., OVER, RANK, PARTITION BY clauses) in PostgreSQL but am still confused when and how to use them and what would the resulted outputs mean?

Does anyone have some summarised explanations on them?

EXAMPLE: OVER()andRANK()

Answer 1

I can give you one simple example where you can use rank.

Lets say you have the student_marks table and you have one test for each class. (Class 1, class 2, .. test with marks for each student in single table, for simplicity all data is considered in one table)

If you now want to give the result to the student for each class (highest marks yields the 1st number and so on) then you can use rank as follows:

Rank() over (partition by class order by marks desc) -- student_rank_in_class

Here,

• Partition by class - means rank should be given per class.

• Order by marks desc - means in each class, highest mark student (marks desc) should be given 1st rank, then second highest should be given 2nd rank and so on.

Hope, This explanation will give you some glimplse about rank window function.