2

I am trying to get the distinct day number with a set of data that contains different dates that are not always consecutive. For example, if I have this data:

      date | id
2019-01-01 | 1
2019-01-02 | 2
2019-01-02 | 3
2019-01-04 | 4
2019-01-07 | 5
2019-01-07 | 6

I need to get the day number, where the day_num is the next unique data from the fist, and NOT the count of days SINCE the first .. e.g.

      date | id | day_num
2019-01-01 | 1  |   1
2019-01-02 | 2  |   2
2019-01-02 | 3  |   2
2019-01-04 | 4  |   3
2019-01-07 | 5  |   4
2019-01-07 | 6  |   4

Use case:
1) I need to know how many distinctive dates someone logged time .. so above I could see that the time was logged over 4 days (last day_num value).
2) I need to see which work day each separate date group is .. for example, above I can see that work done on 2019-01-04 was the 3rd time which this person worked.

for brevity, columns for user_id, hours etc, are not shown

In trying to figure this out, I got as far as

  COUNT(1) OVER (
     ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING
       AND CURRENT ROW
  ) day_num,

.. which of course is not grouping by the date. This is PG 9.6 so I dont have frame_exclusion available .. not sure if that would even have helped.

1 Answer 1

2

dense_rank() seems to be a perfect fit.

SELECT date,
       id,
       dense_rank() OVER (ORDER BY date) day_num
       FROM elbat;

db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.