postgresql Window Functions column values vs dense_rank vs rank vs row_number
Example
here you can find the functions.
With the table wf_example created in previous example, run:
select i
, dense_rank() over (order by i)
, row_number() over ()
, rank() over (order by i)
from wf_example
The result is:
i | dense_rank | row_number | rank
---+------------+------------+------
1 | 1 | 1 | 1
1 | 1 | 2 | 1
1 | 1 | 3 | 1
2 | 2 | 4 | 4
2 | 2 | 5 | 4
3 | 3 | 6 | 6
4 | 4 | 7 | 7
5 | 5 | 8 | 8
-
dense_rank orders VALUES of i by appearance in window.
i=1appears, so first row has dense_rank, next and third i value does not change, so it isdense_rankshows 1 - FIRST value not changed. fourth rowi=2, it is second value of i met, sodense_rankshows 2, andso for the next row. Then it meets valuei=3at 6th row, so it show 3. Same for the rest two values of i. So the last value ofdense_rankis the number of distinct values of i. -
row_number orders ROWS as they are listed.
-
rank Not to confuse with
dense_rankthis function orders ROW NUMBER of i values. So it starts same with three ones, but has next value 4, which meansi=2(new value) was met at row 4. Samei=3was met at row 6. Etc..
