How to enumerate groups of partitions in my Postgres table with window functions?

Question

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?

i do not understand the numbering and "wierd" order within the expected results off the number column.. Can you elaborate? — Raymond Nijland
– Raymond Nijland, Commented Aug 23, 2018 at 14:40
It is different because in previous question I asked for enumerating inside partitions and here I'm asking about enumerating from the outside. — anzoman
– anzoman, Commented Aug 23, 2018 at 15:02
We don't understand that "enumerating from the outside". You'll have to explain it in more detail. — Laurenz Albe
– Laurenz Albe, Commented Aug 23, 2018 at 15:04
Yes i see your other question ( stackoverflow.com/questions/51984611/… ) which makes more sense.. Because the number column follows a very direct algorithm.. The number in this question does not seam to follows a very direct algorithm.. — Raymond Nijland
– Raymond Nijland, Commented Aug 23, 2018 at 15:04
I just want to divide table in certain parts and these parts need to have some number that defines them and also separates them from other parts of the table. The part of the table starts when part column is 1 and where the zeros are this is the whole part which end with part column again being 1. — anzoman
– anzoman, Commented Aug 23, 2018 at 15:05

Gordon Linoff · Accepted Answer · 2018-08-23 16:17:32Z

2

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;

answered Aug 23, 2018 at 16:17

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oto Shavadze Over a year ago

very nice solution

Oto Shavadze · Accepted Answer · 2018-08-23 16:10:41Z

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, case when  part = 1 then 0 else dense_rank() over(order by grp) end as result
from (
    select *,
    row_number() over(order  by id)   -
    row_number() over(partition by part order  by id) as grp
    from t
    order by id
) tt
order by id

answered Aug 23, 2018 at 16:10

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Collectives™ on Stack Overflow

How to enumerate groups of partitions in my Postgres table with window functions?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related