CODEWITHSUNDEEP
postgresqlrankingrow-number
phil
phil

Reputation: 263

Postgres - Rank based on consecutive values

How can I create a rank that restarts at 1 when the consecutive sequence is broken?

(Click on the Image below)

The table below represent the flow of a User. His total journey consists of 8 steps, the page indicates which page he is on during each step. I would like to create a rank, that resets when the page changes. The tricky part is getting the rank in (step 6, page 1) to reset to 1 and not continue at 4. Partitioning by Page is not enough, as I want the rank to restart when the user changes back to Page 1 and continue. The table contains the results I would like to achieve, I just do not know how to do this in Postgres.

Table Rank

Upvotes: 3

Views: 1339

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can assign a group using the difference of row numbers approach and then use row_number() to assign the ranking:

select step, page,
       row_number() over (partition by page, seqnum - seqnum_p order by step) as ranking
from (select t.*,
             row_number() over (order by step) as seqnum,
             row_number() over (partition by page order by step) as seqnum_p
      from t
     ) t;

To understand why this works, look at the results of the subquery. You will see how the difference between the two seqnum values identifies consecutive rows with the same value.

Upvotes: 5

Related Questions