PostgreApply在一个非常大的表上跨多个列的组by

Question

我正在处理一个非常大的表(约4.25亿行，约800列，约750GB的磁盘)，它看起来像(这里包括4列):

id  | c1 |   c2   |   c3   | c4
-----+----+--------+--------+----
101 | t  | green  | small  | 10
102 | t  | green  | small  | 11
103 | f  | red    | medium | 12
104 | f  | yellow | medium | 13
105 | t  | blue   | medium | 14
106 | t  | green  | large  | 14

我要做的是通过每列的每个唯一值将id分组在一起。输出如下所示:

colname | value  | ids
----------------------
c1      | t      | 101, 102, 105, 106
c1      | f      | 103, 104
c2      | green  | 101, 102, 106
c2      | red    | 103
c2      | yellow | 104
c2      | blue   | 105
c3      | small  | 101, 102
...

每个列将有2-15个不同的值，因此在每个分组中会有非常多的id，因为总共有大约4.25亿个id。我决定做的是将id分成特定大小的组，然后跟踪这些块，看起来像这样(只显示c1列):

colname | value  | chunk | ids
------------------------------
c1      | t      | 1     | {101, 102, ...}
c1      | t      | 2     | {205, 206, ...}
c1      | t      | 3     | {331, 332, ...}
c1      | f      | 1     | {103, 104, ...}
...

我面临的问题是，我不知道如何一次跨多个列检索这些结果。对于单个列，此SQL将提供上面的表:

select 'c1' as colname, tmp.c1 as value, tmp.chunk, array_agg(tmp.id) as ids
from (
select id, c1, (row_number() over (partition by c1) - 1) / ARRAY_SIZE + 1 as chunk
from my_large_table
) as tmp
group by (tmp.c2, tmp.chunk)

我担心的是必须运行这个查询约800次(无论有多少列)，每次读取约4.25亿行。我知道分组集提供了一种同时对多个不同的集合进行分组的方式，这意味着我们可以一次读取表并获得我们正在寻找的所有结果，但我不知道如何以一种方式做到这一点:1)合并分块行为，2)在结果集中保留相关的colname，这将是我们正在考虑的所有分组集中的每个分组集所需要的。

附加上下文:在期望的最终结果集中，对id、列、值和块排序是不重要的。

任何帮助都将不胜感激。

Answer 1

可以通过使用横向连接在一个查询中表示:

select colname, colval, 
ceil(seqnum::numeric / array_size) as chunk,
array_agg(id)
from (select v.colname, v.colval, t.id,
row_number() over (partition by v.colname, v.colval) as seqnum
from my_large_table t cross join lateral
(values ('c1', c1::text),
('c2', c2::text),
. . .
) v(colname, colval)
) c
group by colname, colval, chunk;

也就是说，我不确定与为每列运行一组单独的查询相比，这将如何执行。