PostgreSQL réduit les rangs si les valeurs correspondent aux rangs précédents

Question

J'ai un tableau comme ci-dessous et je voudrais regrouper les lignes par row_group (1), ordonner les rangées selon row_order au sein de chaque groupe (2) et enfin réduire le nombre de lignes dans la sortie (3) de sorte que toutes les lignes adjacentes ayant la même valeur soient regroupées.

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);

J'ai réussi à faire les deux premières étapes en utilisant les fonctions de la fenêtre plus ou moins comme indiqué dans la requête ci-dessous, mais j'ai du mal à réduire le nombre de lignes.

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

Le résultat devrait ressembler à ceci :

+-----------+---------------+-------+
| 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     |
+-----------+---------------+-------+

Avez-vous des suggestions sur la façon dont je peux faire cette réduction ? Je pense que j'aurai probablement besoin d'une requête parent avec une fonction GROUP BY et HAVING en quelque sorte, mais j'ai du mal à le faire fonctionner.

Answer 1

Vous devez définir des groupes pour des valeurs consécutives égales par groupe. Pour ce faire, vous pouvez utiliser la méthode de la différence des numéros de ligne. Une fois que cela est fait, il suffit de group by pour obtenir le résultat final. ( Exécutez la requête interne pour voir comment les groupes sont assignés. )

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