sql - 如果数组重叠，则折叠多行数组

Question

我在 PostgreSQL 9.3 中有一个包含单个列的表每行包含一个数组。我在努力寻找崩溃的方法共享相同元素的数组行。

例子

简单重叠

给定以下两行数组:

{ 1, 2, 3 }
{ -5, 3, 6, 9 }

结果将是一行包含:

{ -5, 1, 2, 3, 6, 9 }

这是因为“3”存在于两个数组中。注意“3”没有重复。

多重重叠

相同的重叠概念也可以应用于表格中任意位置的多行:

{ 1, 2, 3 }
{ 100, 200, 300 }
{ 3, 4, 5 }
{ 5, 6, 7 }

所需的输出将是两行:

{ 1, 2, 3, 4, 5, 6, 7}
{ 100, 200, 300 }

从结果返回的数组应该是唯一的并且彼此之间没有共享元素。

我尝试过的

我对 array union 使用了“递归”查询函数，但找不到正确的查询。

提供了一个示例表 here on SQL fiddle (它模仿第二个例子)，或者它可以用:

create table test ( 
  arr integer[] 
); 

insert into test (arr) values ('{ 1, 2, 3 }');
insert into test (arr) values ('{ 100, 200, 300 }');
insert into test (arr) values ('{ 3, 4, 5 }');
insert into test (arr) values ('{ 5, 6, 7 }');

Answer 1

好吧，这很艰难。请查看此查询:

;with recursive minelem AS(
select arr, MIN(unnest) minel from (select arr, unnest(arr) from test) a group by arr),
testwithrn as(
select arr, row_number() over (order by minel) rn from minelem
),
cte(arr, rn, counter, grp) as(
  select arr, rn, 1, 1 from testwithrn where rn = 1
union all 
  select 
    case when array_length(a.arr & b.arr, 1) > 0 then a.arr | b.arr else b.arr end, 
    b.rn, 
    case when array_length(a.arr & b.arr, 1) > 0 then a.counter + 1 else 1 end,
    case when array_length(a.arr & b.arr, 1) > 0 then a.grp else a.grp + 1 end
    from cte a inner join testwithrn b 
    on b.rn > a.rn
),
grouped as(
  SELECT arr, counter, grp,
  row_number() over (partition by grp order by counter desc) rn from cte)
select distinct arr from grouped where rn = 1

SQL Fiddle

您可以在上面的查询中测试不同的 CTE，以了解我是如何得出解决方案的。这里的关键是使用运算符 |合并数组，如 a.arr | b.arr

有一个名为 cte 的递归查询，它计算每个集合在不同集合组中的出现次数。您可以将最后一行替换为 select * from cte order by grp, counter 以查看 counter 和 grp 在设置时如何更改递归构建