Enumerating table partitions in Postgres table

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 that have part atribute 0.

Ultimately I want to get this:

id  | part  | value | number
----+-------+-----------------
 1  | 0     | 8     |   1
 2  | 0     | 3     |   2
 3  | 0     | 4     |   3
 4  | 1     | 6     |   0
 5  | 0     | 13    |   1
 6  | 0     | 4     |   2
 7  | 1     | 2     |   0
 8  | 0     | 11    |   1
 9  | 0     | 15    |   2
 10 | 0     | 3     |   3
 11 | 0     | 2     |   4

Is it possible to solve this with Postgres window functions or is there another way?

Laurenz Albe · Accepted Answer · 2018-08-23 11:45:23Z

3

Yes, that is simple:

SELECT id, part, value,
       row_number() OVER (PARTITION BY grp ORDER BY id) - 1 AS number
FROM (SELECT id, part, value,
             sum(part) OVER (ORDER BY id) AS grp
      FROM mytable
     ) AS q;

 id | part | value | number 
----+------+-------+--------
  1 |    0 |     8 |      0
  2 |    0 |     3 |      1
  3 |    0 |     4 |      2
  4 |    1 |     6 |      0
  5 |    0 |    13 |      1
  6 |    0 |     4 |      2
  7 |    1 |     2 |      0
  8 |    0 |    11 |      1
  9 |    0 |    15 |      2
 10 |    0 |     3 |      3
 11 |    0 |     2 |      4
(11 rows)

answered Aug 23, 2018 at 11:45

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

anzoman Over a year ago

I edited my question. Do you know how would I get the second table?

Laurenz Albe Over a year ago

Don't edit the question. Rather, ask a new question including a description of the desired data ( I didn't understand it).

anzoman Over a year ago

Ok, here is my new question stackoverflow.com/questions/51988259/…

Collectives™ on Stack Overflow

Enumerating table partitions in Postgres table

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related