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.