Merge rows (same values) in PostgreSQL based on row difference

Question

I have a table my_tbl in my PostgreSQL 9.5 (x64 Windows) database, which contains data as shown below.

grp    id   low   high  avg
1      7    292   322   18.8
1      8    322   352   18.8
1      9    352   22    18.8
1      10   22    52    18.8
1      11   52    82    18.8
1      12   82    112   18.8
4      1    97    127   19.0
4      2    127   157   11.4
4      3    157   187   11.4
4      4    187   217   19.6
4      5    217   247   19.6
4      6    247   277   19.6
4      10   7     37    19.5
4      11   37    67    19.5
4      12   67    97    19.5
6      6    182   212   0.0
6      7    212   242   0.0
6      8    242   272   0.0
6      9    272   302   21.4
6      10   302   332   21.4
6      11   332   2     0.0
6      12   2     32    0.0
7      5    275   305   0.0
7      6    305   335   0.0
7      7    335   5     0.0
7      8    5     35    0.0
7      9    35    65    21.2
7      10   65    95    21.2
7      11   95    125   21.2
7      12   125   155   21.2

Now I would like to merge rows in the above data in the following way. For each grp, if the difference between avg values (the preceding and the succeeding ones) is zero (same value), then all such rows should be merged with the low value of the first row to high value of the last row (where merging should stop).

My expected output is:

grp   id                low     high     avg
1     {7,8,9,10,11,12}  292     112      18.8
4     {1}               97      127      19.0
4     {2,3}             127     187      11.4
4     {4,5,6}           187     277      19.6
4     {10,11,12}        7       97       19.5
6     {6,7,8}           182     272      0.0
6     {9,10}            272     332      21.4
6     {11,12}           332     32       0.0
7     {5,6,7,8}         275     35       0.0
7     {9,10,11,12}      35      155      21.2

Does someone care to help or suggest on how this could be achieved using SQL/PLPGSQL?

Answer 1

You can use ARRAY_AGG function and FIRST_VALUE function:

SELECT
    grp, ARRAY_AGG(id) AS id, low, high, avg
FROM (
  SELECT
    grp,
    id,
    FIRST_VALUE(low) OVER (PARTITION BY grp, avg ORDER BY id) AS low,
    FIRST_VALUE(high) OVER (PARTITION BY grp, avg ORDER BY id DESC) AS high,
    avg
  FROM my_tbl ORDER BY id
) t
GROUP BY grp, avg , low, high
ORDER BY grp;

DB Fiddle