如果数组重叠，则折叠多行数组 [英] Collapse multiple rows of arrays if the arrays overlap

问题描述

我在PostgreSQL 9.3中有一个表，其中包含一个单列
，每行包含一个数组。我试图找到一种方法来折叠共享相同元素的
数组行。

I have a table in PostgreSQL 9.3 that contains a single column containing an array in each row. I am trying to find a way to collapse rows of arrays that share the same elements.

简单重叠

给出以下两行数组：

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

结果将是包含以下内容的行：

The result would be a row containing:

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

这是因为两个数组中都存在 3。注意，不重复 3。

This is because the "3" exists in both arrays. Note that the "3" is not repeated.

多个重叠

相同的重叠概念也可以应用于任何地方的多行在表中：

The same overlap concept can also apply to multiple rows anywhere in the table:

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

输出将是两行：

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

从结果返回的数组应该是唯一的，并且彼此不共享任何元素其他。

The arrays returned from the result should be unique and share no elements with each other.

我在数组联合函数，但找不到正确的查询。

I used a "with recursive" query with array union function, but couldn't figure out the right query.

在SQL上提供了此处提供的示例表小提琴（它模仿第二个示例），或者可以使用以下命令构建它：

A sample table to work with is provided here on SQL fiddle (it mimics the second example), or it can be built with:

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

推荐答案

好的，这很艰难。请看一下这个查询：

OK, it was tough. Please have a look at this query:

;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提琴

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

You can test different CTEs in the query above to understand how I have come up with the solution. The key here is to use operator | to merge arrays, as in a.arr | b.arr

有一个名为 cte 的递归查询，用于计算每个组都在不同的组中。您可以将最后一行替换为grp，计数器从cte订单中的 select *，以查看计数器和<$递归构建集时，将更改c $ c> grp

There is a recursive query called cte that counts the occurrence of each set within different groups of sets. You can replace the last line to select * from cte order by grp, counter to see how the counter and grp are changed when the sets are recursively built

这篇关于如果数组重叠，则折叠多行数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！