How To Enumerate Groups Of Partitions In My Postgres Table With Window Functions?

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

Solution 1:

You seem to want something like 1 more than the cumulative sum of the parts. The simplest method is:

select t.*,
       (casewhen part =1then0-- the easy caseelse1+sum(part) over (orderby id)
        end) as number
from t;

If part can take on values other than 0 and 1:

select t.*,
       (casewhen part =1then0-- the easy caseelse1+sum( (part =1)::int ) over (orderby id)
        end) as number
from t;

Solution 2:

If i correctly understand, you need something like:

with t(id  , part  , value) as(
values
(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)
)

select id, part, value, casewhen  part =1then0elsedense_rank() over(orderby grp) endasresultfrom (
    select*,
    row_number() over(orderby id)   -row_number() over(partitionby part orderby id) as grp
    from t
    orderby id
) tt
orderby id