Reputation: 3
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".
Upvotes: 0
Views: 552
Reputation: 2005
select T.*,
sum(case when action like 'promotion%' then 1 else 0 end)
over(partition by "group" order by sequence rows unbounded preceding) parameter
from Table T
Upvotes: 2
Reputation: 44941
filter
(Starting with PostgeSQL 9.4)
select *
,count (*) filter (where action like 'promotion%') over
(
partition by "group"
order by sequence
rows unbounded preceding
)
from mytable T
+-------+----------+-------------+-------+
| group | sequence | action | count |
+-------+----------+-------------+-------+
| 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 |
+-------+----------+-------------+-------+
Upvotes: 1