Suppose I have a table like this:
id | part | value
----+-------+-------
1 | 0 | 8
2 | 0 | 3
3 | 0 | 4
4 | 1 | 6
5 | 0 | 13
6 | 0 | 4
7 | 1 | 2
8 | 0 | 11
9 | 0 | 15
10 | 0 | 3
11 | 0 | 2
I would like to enumerate groups between rows that have part atribute 1.
So I would like to get this:
id | part | value | number
----+-------+-----------------
1 | 0 | 8 | 1
2 | 0 | 3 | 1
3 | 0 | 4 | 1
4 | 1 | 6 | 0
5 | 0 | 13 | 2
6 | 0 | 4 | 2
7 | 1 | 2 | 0
8 | 0 | 11 | 3
9 | 0 | 15 | 3
10 | 0 | 3 | 3
11 | 0 | 2 | 3
Is it possible to achieve this with Postgres window functions or is there any other way?
You seem to want something like 1 more than the cumulative sum of the parts. The simplest method is:
select t.*,
(case when part = 1 then 0 -- the easy case
else 1 + sum(part) over (order by id)
end) as number
from t;
If part
can take on values other than 0 and 1:
select t.*,
(case when part = 1 then 0 -- the easy case
else 1 + sum( (part = 1)::int ) over (order by id)
end) as number
from t;