khajlk
This is a follow up question of my previous [post](https://topanswers.xyz/databases?q=880).
For each group grp, I need to merge/combine the values of `low`, `high` based on `avg` using `axis_1` and `axis_2` as controls.
For each grp, if the difference between `avg` values of the **preceding** and the **succeeding** `ids` (where the `low` of previous row matches with the `high` of next row) is zero (same value), then, merge all such rows (with `low` of the first row to the `high` of the last merged row).
The merging/combining is not allowed across the axes values (`axis_1` and `axis_2`), that is, rows should not be merged crossing over the axes values.
The merging is also not allowed if the `low` of the previous row doesn't match with the `high` of the succeeding row OR there is a significant gap in `id` numbers/non-adjacent `ids` (e.g 7, 12) (see expected output of grp 19 below).
The sample data is given as follows:
```
CREATE TABLE my_tbl2 (grp int, id int, axis_1 int, axis_2 int, low int, high int, avg decimal(3,1));
```
```
insert into my_tbl2 values
(1, 7, 112, 292, 292, 322, 18.8),
(1, 8, 112, 292, 322, 352, 18.8),
(1, 9, 112, 292, 352, 22, 18.8),
(1, 10, 112, 292, 22, 52, 18.8),
(1, 11, 112, 292, 52, 82, 18.8),
(1, 12, 112, 292, 82, 112, 18.8),
(6, 6, 32, 212, 182, 212, 0.0),
(6, 7, 32, 212, 212, 242, 0.0),
(6, 8, 32, 212, 242, 272, 0.0),
(6, 9, 32, 212, 272, 302, 21.4),
(6, 10, 32, 212, 302, 332, 21.4),
(6, 11, 32, 212, 332, 2, 0.0),
(6, 12, 32, 212, 2, 32, 0.0),
(7, 5, 155, 335, 275, 305, 0.0),
(7, 6, 155, 335, 305, 335, 0.0),
(7, 7, 155, 335, 335, 5, 0.0),
(7, 8, 155, 335, 5, 35, 0.0),
(7, 9, 155, 335, 35, 65, 21.2),
(7, 10, 155, 335, 65, 95, 21.2),
(7, 11, 155, 335, 95, 125, 21.2),
(7, 12, 155, 335, 125, 155, 21.2),
(19, 1, 161, 341, 161, 191, 0.0),
(19, 2, 161, 341, 191, 221, 13.2),
(19, 3, 161, 341, 221, 251, 13.2),
(19, 5, 161, 341, 281, 311, 11.1),
(19, 6, 161, 341, 311, 341, 0.0),
(19, 7, 161, 341, 341, 11, 0.0),
(19, 12, 161, 341, 131, 161, 0.0),
(20, 7, 151, 331, 331, 1, 16.8),
(20, 8, 151, 331, 1, 31, 0.0),
(20, 9, 151, 331, 31, 61, 16.8),
(20, 10, 151, 331, 61, 91, 16.8),
(20, 11, 151, 331, 91, 121, 16.8),
(20, 12, 151, 331, 121, 151, 0.0);
```
The following code does the merging as follows:
```
with m as (select *, sum(avg_chg::integer) over (partition by grp order by id) subgrp
from (select *, (lag(avg) over (partition by grp order by id))
is distinct from avg
or low = axis_1 or low = axis_2 avg_chg
from my_tbl2) z )
, a as (select grp,subgrp,array[sum(low),sum(high),avg] vals
from(select *
from(select distinct on (grp,subgrp,avg) grp,subgrp,avg,low,null::integer high
from m
order by grp,subgrp,avg,id) z
union all
select *
from(select distinct on (grp,subgrp,avg) grp,subgrp,avg,null::integer,high
from m
order by grp,subgrp,avg,id desc) z) z
group by grp,subgrp,avg
order by grp,subgrp)
select grp, array_agg(val)
from (select grp, subgrp, unnest(vals) val from a) z
group by grp;`
```
The output of the above code is:
```
grp array_agg
1 {292,112,18.8}
6 {182,212,0.0,212,272,0.0,272,332,21.4,332,32,0.0}
7 {275,335,0.0,335,35,0.0,35,155,21.2}
19 {161,191,0.0,191,251,13.2,251,311,11.1,311,341,0.0,341,161,0.0}
20 {331,1,16.8,1,31,0.0,31,121,16.8,121,151,0.0}
```
However, the **expected output** needs to be like this:
```
grp array_agg
1 {292,112,18.8}
6 {182,212,0.0,212,272,0.0,272,332,21.4,332,32,0.0}
7 {275,335,0.0,335,35,0.0,35,155,21.2}
19 {161,191,0.0,191,251,13.2,251,311,11.1,311,341,0.0,341,11,0.0,131,161,0.0}
20 {331,1,16.8,1,31,0.0,31,121,16.8,121,151,0.0}`
```
Since, for the case of `grp 19`, the `high` of **id 7** and `low` of **id 12** do not match (alternatively, the ids are not adjacent/there is a significant gap).
How the above code could be modified to get the expected output?