I want to count cumulatively the number of previous repeating values

Question

The input data frame looks like this:

ID	Timestamp	Diff
1	2021-01-01 10:00:00	6
1	2021-01-01 10:00:06	6
1	2021-01-01 10:00:18	12
1	2021-01-01 10:00:24	6
1	2021-01-01 10:00:30	6
1	2021-01-01 10:00:36	6
2	2021-01-01 11:00:00	6
2	2021-01-01 11:00:06	6
2	2021-01-01 11:00:12	6
2	2021-01-01 11:00:24	12
2	2021-01-01 11:00:30	6
2	2021-01-01 11:00:36	6

Applying this to the diff column would result in a new column of values

ID	Timestamp	Diff	Result
1	2021-01-01 10:00:00	6	1
1	2021-01-01 10:00:06	6	2
1	2021-01-01 10:00:18	12	1
1	2021-01-01 10:00:24	6	1
1	2021-01-01 10:00:30	6	2
1	2021-01-01 10:00:36	6	3
2	2021-01-01 11:00:00	6	1
2	2021-01-01 11:00:06	6	2
2	2021-01-01 11:00:12	6	3
2	2021-01-01 11:00:24	12	1
2	2021-01-01 11:00:30	6	1
2	2021-01-01 11:00:36	6	2

score 1 · Accepted Answer · answered Jan 23 '22 at 13:40

select     t.id
          ,t.timestamp
          ,t.diff
          ,row_number() over (partition by id, rn_global - rn_diff order by timestamp) as Result

from      (select  t.id
                  ,t.timestamp
                  ,t.diff
                  ,row_number() over (partition by id       order by timestamp) rn_global
                  ,row_number() over (partition by id, diff order by timestamp) rn_diff
           from    t
           ) t
           
order by  id, timestamp

--

+----+------------------------------+------+--------+
| id |          timestamp           | diff | Result |
+----+------------------------------+------+--------+
|  1 | 2021-01-01T10:00:00.000+0000 |    6 |      1 |
|  1 | 2021-01-01T10:00:06.000+0000 |    6 |      2 |
|  1 | 2021-01-01T10:00:18.000+0000 |   12 |      1 |
|  1 | 2021-01-01T10:00:24.000+0000 |    6 |      1 |
|  1 | 2021-01-01T10:00:30.000+0000 |    6 |      2 |
|  1 | 2021-01-01T10:00:36.000+0000 |    6 |      3 |
|  2 | 2021-01-01T11:00:00.000+0000 |    6 |      1 |
|  2 | 2021-01-01T11:00:06.000+0000 |    6 |      2 |
|  2 | 2021-01-01T11:00:12.000+0000 |    6 |      3 |
|  2 | 2021-01-01T11:00:24.000+0000 |   12 |      1 |
|  2 | 2021-01-01T11:00:30.000+0000 |    6 |      1 |
|  2 | 2021-01-01T11:00:36.000+0000 |    6 |      2 |
+----+------------------------------+------+--------+

I want to count cumulatively the number of previous repeating values

1 Answers1