时间戳

首页 > 解决方案 > PostgreSQL 和 array_agg：删除导致多个数组的空值

sql - PostgreSQL 和 array_agg：删除导致多个数组的空值

问题描述

我有一个包含两列的表：k 是键，a 可能包含空值。一个例子如下：

drop table if exists test;
create table test(k, a) as
select * from ( values
(1, 1),
(2, 2),
(3, 3),
(4, NULL),
(5, NULL),
(6, 6),
(7, 7),
(8, NULL),
(9, 9),
(10, 10)
) t;

我需要将按列 k 排序的列 a 的值聚合到几个没有空值的数组中。使用 array_agg 和过滤器不是我需要的

select array_agg(a order by k)  from test
-- "{1,2,3,NULL,NULL,6,7,NULL,9,10}"

select array_agg(a order by k) filter (where a is not null) from test
-- "{1,2,3,6,7,9,10}"

我需要获得的内容如下

"{1,2,3}"
"{6,7}"
"{9,10}"

知道如何实现这一目标吗？

标签： sqlarrayspostgresqlaggregatesql-null

解决方案

You can define the groups by counting the number of NULL values up-to-each row. The rest is then just filtering and aggregation:

select array_agg(k order by a)
from (select t.*,
             count(*) filter (where a is null) over (order by k) as grp
      from test t
     ) t
where a is not null
group by grp;

Here is a db<>fiddle.

推荐阅读