1

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

Ilovenoodles
  • 83
  • 1
  • 7

1 Answers1

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.

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • 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? – Ilovenoodles Jan 25 '21 at 05:38
  • 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. – Popeye Jan 25 '21 at 05:52
  • Lovely!! I understand now. Thank you – Ilovenoodles Jan 25 '21 at 06:28