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()

Can you give an example of what you are looking at as far as documentation on window functions in postrges? — Ross Bush
– Ross Bush, Commented Jan 25, 2021 at 3:28
an example of OVER and RANK clauses has been uploaded @RossBush — Ilovenoodles
– Ilovenoodles, Commented Jan 25, 2021 at 3:51

Popeye · Accepted Answer · 2021-01-25 04:06:34Z

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.

answered Jan 25, 2021 at 4:06

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ilovenoodles Over a year ago

Hi Popeye thank you for the explanation. I am also wondering that can I use GROUP BY instead of PARTITION BY to answer this question (by slightly altering the format of query) since I find they similar?

Popeye Over a year ago

Group by will group the result and will operat on aggregate functions which results in less than or equal to number of rows in your table based on which columns you did group by. If you group by class only then you will get one row per class. You will not get each student data in that case.

Ilovenoodles Over a year ago

Lovely!! I understand now. Thank you

Collectives™ on Stack Overflow

When and how to use Window Functions in PostgreSQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related