SQL: grouping records in a group based on row order

Question

I have a table with the following structure

| id | a | b |
| 1  | 1 | 1 |
| 2  | 1 | 5 |
| 3  | 2 | 2 |
| 4  | 2 | 3 |
| 5  | 2 | 5 |
| 6  | 1 | 3 |
| 7  | 1 | 7 |
| 8  | 3 | 1 |
| 9  | 2 | 0 |
| 10 | 4 | 8 |

and you need to calculate the sum in column "b" inside each group in column "a", but taking into account the order of the rows, that is, you should first add 1 + 5for a == 1( id1 and 2), then we will add 2 + 3 + 5for a = 2( id3, 4, 5), etc. d.

So, as a result, I need to have a table like this

| a | sum(b) |
| 1 |   6    |
| 2 |   10   |
| 1 |   10   |
| 3 |   1    |
| 2 |   0    |
| 4 |   8    |

Please help write an SQL query to do this.

Answer 1

select      a,sum(b)

from       (select   *
                    ,row_number () over (order by id)                as rn
                    ,row_number () over (partition by a order by id) as rnp

            from     t
            ) t

group by    a,rn - rnp    

order by    min(id)  
;          

Ps
I do not expect the ID to be continuous