postgreSQL连接基于字符串包含

Question

我有以下SQL表:

COL_A      || COL_B    ||
=========================
aa         ||    1     ||
aa         ||    2     ||
aa.bb      ||    3     ||  
aa.bb.cc   ||    4     ||  
aa.bb.cc   ||    5     ||
dd         ||    6     ||
dd.ee      ||    7     ||  

作为SELECT查询的一部分，我希望按Col_A的值分组，并根据Col_A中的值彼此的子集将Col_B中的值连接起来。也就是说，如果包含Col_A的值/等于Col_A的另一个值，则超集/相同Col_A值的对应Col_B应该连接在一起。

预期的结果:

COL_A      || COL_B                 ||
======================================
aa         ||   [1, 2, 3, 4, 5]     ||
aa.bb      ||   [3, 4, 5]           ||  
aa.bb.cc   ||   [4, 5]              ||  
dd         ||   [6, 7]              ||
dd.ee      ||   [7]                 ||  

Answer 1

您可以使用自join与array_agg:

select t1.col_a, array_agg(distinct t2.col_b) 
from vals t1 join vals t2 on t2.col_a ~ t1.col_a 
group by t1.col_a order by t1.col_a

Answer 2

您可以使用lateral join

select t.cola, Concat('[',x.colB,']') ColB
from t
left join lateral (
select string_agg(colb::character,',') colB
from t t2
where t2.cola ~ t.cola
)x on true
group by t.cola, x.colb;

工作小提琴