PostgreSQL reduce rows if values match previous rows

Question

I have a table as below and I would like to group rows by row_group (1), order rows according to row_order within each group (2) and finally reduce the number of rows in the output (3) so any adjacent rows with the same value are collapsed.

CREATE TABLE rowgroups (row_group varchar(1), row_order varchar(2), value integer);

INSERT INTO rowgroups (row_group, row_order, value) VALUES 
('A', '1', 0),
('A', '2', 1),
('A', '3', 1),
('B', '1', 0),
('B', '2', 0),
('B', '3', 0),
('C', '1', 1),
('C', '2', 0),
('C', '3', 1),
('C', '4', 1),
('C', '5', 0),
('D', '1', 1),
('D', '2', 0);

I managed to do the first two steps using window functions more or less as shown in query below, but I am struggling to reduce the number of rows.

SELECT *, lag(value) OVER w = value AS value_change
FROM rowgroups rg
WINDOW w AS (PARTITION BY row_group ORDER BY row_order)

The output should look like this:

+-----------+---------------+-------+
| row_group | row_order_agg | value |
+-----------+---------------+-------+
| A         | 1             | 0     |
+-----------+---------------+-------+
| A         | 2,3           | 1     |
+-----------+---------------+-------+
| B         | 1,2,3         | 0     |
+-----------+---------------+-------+
| C         | 1             | 1     |
+-----------+---------------+-------+
| C         | 2             | 0     |
+-----------+---------------+-------+
| C         | 3,4           | 1     |
+-----------+---------------+-------+
| C         | 5             | 0     |
+-----------+---------------+-------+
| D         | 1             | 1     |
+-----------+---------------+-------+
| D         | 2             | 0     |
+-----------+---------------+-------+

Any suggestions how I can make the reduction? I'm thinking I'll probably need a parent query with a GROUP BY and HAVING of sorts but I'm struggling to get it to work.

Vamsi Prabhala · Accepted Answer · 2019-01-15 16:09:04Z

2

You need to define groups for equal consecutive values per group. This can be done with a difference of row numbers approach. Once that is done, it is just a group by operation to get the final result. (Run the inner query to see how groups get assigned)

select row_group,string_agg(row_order,',') as row_order_all,value
from (SELECT *, row_number() OVER w - row_number() over(partition by row_group,value order by row_order) as grp
      FROM rowgroups rg
      WINDOW w AS (PARTITION BY row_group ORDER BY row_order)
     ) t
group by row_group,grp,value
order by 1,2

answered Jan 15, 2019 at 16:09

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL reduce rows if values match previous rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related