I have a table like below:
group sequence action
1 1 promotion_1
1 2 promotion_1
1 3 promotion_2
1 4 act1
1 5 act1
1 6 act2
1 7 promotion_1
1 8 act1
1 9 act2
1 10 promotion_1
1 11 act1
2 1 promotion_2
2 2 act1
I would like to create a ranking, which will show range of promotion. So I need to have the same number for promotion and every action after this promotion:
group sequence action parameter
1 1 promotion_1 1
1 2 promotion_1 2
1 3 promotion_2 3
1 4 act1 3
1 5 act1 3
1 6 act2 3
1 7 promotion_1 4
1 8 act1 4
1 9 act2 4
1 10 promotion_1 5
1 11 act1 5
2 1 promotion_2 1
2 2 act1 1
I read about window functions and their possibilities (for example over()), but I can create only ranking with group by "action".