Postgresql 数组唯一聚合

Question

我有一张带有结构的大 table

CREATE TABLE t (
  id SERIAL primary key ,
  a_list int[] not null,
  b_list int[] not null,
  c_list int[] not null,
  d_list int[] not null,
  type int not null 
)

我想查询 a_list、b_list、c_list、d_list 中 type 的所有唯一值 像这样

    select 
        some_array_unique_agg_function(a_list), 
        some_array_unique_agg_function(b_list), 
        some_array_unique_agg_function(c_list), 
        some_array_unique_agg_function(d_list),
        count(1) 
    where type = 30

例如，对于此数据

+----+---------+--------+--------+---------+------+
| id | a_list  | b_list | c_list | d_list  | type |
+----+---------+--------+--------+---------+------+  
| 1  | {1,3,4} | {2,4}  | {1,1}  | {2,4,5} | 30   |
| 1  | {1,2,4} | {2,4}  | {4,1}  | {2,4,5} | 30   |
| 1  | {1,3,5} | {2}    | {}     | {2,4,5} | 30   |
+----+---------+--------+--------+---------+------+

我想要下一个结果

+-------------+--------+--------+-----------+-------+
| a_list      | b_list | c_list | d_list    | count |
+-------------+--------+--------+-----------+-------+  
| {1,2,3,4,5} | {2,4}  | {1,4}  | {2,4,5}   | 3     |
+-------------+--------+--------+-----------+-------+

有some_array_unique_agg_function适合我的目的吗？

Answer 1

试试这个

with cte as (select 
        unnest( a_list::text[] )::integer as a_list, 
        unnest( b_list::text[] )::integer as b_list, 
        unnest( c_list::text[] )::integer as c_list, 
        unnest( d_list::text[] )::integer as d_list,
        (select count(type) from t) as type
    from t 
    where type = 30
)
select array_agg(distinct a_list),array_agg(distinct b_list)
,array_agg(distinct c_list),array_agg(distinct d_list),type from cte group by type ;

结果:

"{1,2,3,4,5}";"{2,4,NULL}";"{1,4,NULL}";"{2,4,5}";3