PostgreSQL reduce rows if values match previous rows

user_3817017 0
Viewed 222

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.

postgresql sql database
1 Answers

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
user_34318690
Related