如何使用 postgres 排除 array_agg 中的 null 值，如 string_agg 中的 null 值？

如果我使用 array_agg来收集名称，我会得到用逗号分隔的名称，但是如果有一个 null值，那么这个 null 也会被作为聚合中的名称。例如:

SELECT g.id,
array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END) canonical_users,
array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END) non_canonical_users
FROM groups g
GROUP BY g.id;

它返回的是 ,Larry,Phil，而不仅仅是 Larry,Phil(在我的9.1.2中，它显示的是 NULL,Larry,Phil)。

相反，如果我使用 string_agg()，它只显示名称(没有空逗号或空值)。

问题是我已经在服务器上安装了 Postgres 8.4，而且 string_agg()不能在那里工作

114091 次浏览
小开
最佳答案 
select
id,
(select array_agg(a) from unnest(canonical_users) a where a is not null) canonical_users,
(select array_agg(a) from unnest(non_canonical_users) a where a is not null) non_canonical_users
from (
SELECT g.id,
array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END) canonical_users,
array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END) non_canonical_users
FROM groups g
GROUP BY g.id
) s

或者，更简单，可能更便宜，使用 array_to_string消除空值:

SELECT
g.id,
array_to_string(
array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END)
, ','
) canonical_users,
array_to_string(
array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END)
, ','
) non_canonical_users
FROM groups g
GROUP BY g.id
小开

正如注释中提到的那样，你可以写一个函数来替换数组中的空值，但是注释中链接到的线程也指出，如果你必须创建一个聚合，拆分它然后再次聚合，这种做法会降低聚合函数的效率。

我认为在数组中保留 null 只是 Array _ Agg 的一个(也许是不需要的)特性。您可以使用子查询来避免这种情况:

SELECT  COALESCE(y.ID, n.ID) ID,
y.Users,
n.Users
FROM    (   SELECT  g.ID, ARRAY_AGG(g.Users) AS Users
FROM    Groups g
WHERE   g.Canonical = 'Y'
GROUP BY g.ID
) y
FULL JOIN
(   SELECT  g.ID, ARRAY_AGG(g.Users) AS Users
FROM    Groups g
WHERE   g.Canonical = 'N'
GROUP BY g.ID
) n
ON n.ID = y.ID

SQL FIDDLE

小开

在解决从数组聚合中移除 null 的一般问题时，有两种主要的解决方法: 执行 array _ agg (unnest (array _ agg (x))或创建自定义聚合。

第一个是表格 以上:

SELECT
array_agg(u)
FROM (
SELECT
unnest(
array_agg(v)
) as u
FROM
x
) un
WHERE
u IS NOT NULL;

第二:

/*
With reference to
http://ejrh.wordpress.com/2011/09/27/denormalisation-aggregate-function-for-postgresql/
*/
CREATE OR REPLACE FUNCTION fn_array_agg_notnull (
a anyarray
, b anyelement
) RETURNS ANYARRAY
AS $$
BEGIN


IF b IS NOT NULL THEN
a := array_append(a, b);
END IF;


RETURN a;


END;
$$ IMMUTABLE LANGUAGE 'plpgsql';


CREATE AGGREGATE array_agg_notnull(ANYELEMENT) (
SFUNC = fn_array_agg_notnull,
STYPE = ANYARRAY,
INITCOND = '{}'
);

把第二个称为第二个(自然)要比第一个好看一点:

从 x 中选择 array _ agg _ notnull (v) ;

小开

尽管这个线程已经很老了，我还是要添加它，但是我遇到了这个巧妙的技巧，它在小数组上运行得很好。它在 Postgres 8.4 + 上运行，不需要额外的库或函数。

string_to_array(array_to_string(array_agg(my_column)))::int[]

array_to_string()方法实际上去掉了 null。

小开

后期 ql-9.3可以做到这一点;

SELECT g.id,
array_remove(array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END), NULL) canonical_users,
array_remove(array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END), NULL) non_canonical_users
FROM groups g
GROUP BY g.id;

更新 : 用 postgreql-9.4;

SELECT g.id,
array_agg(g.users) FILTER (WHERE g.canonical = 'Y') canonical_users,
array_agg(g.users) FILTER (WHERE g.canonical = 'N') non_canonical_users
FROM groups g
GROUP BY g.id;

更新(2022-02-19) : 也带有 postgreql-9.4;

当一个数组中的所有值都为 null 而不是返回 null 时，这将导致一个空数组;

SELECT g.id,
coalesce( array_agg(g.users) FILTER (WHERE g.canonical = 'Y'), '{}' ) canonical_users,
coalesce( array_agg(g.users) FILTER (WHERE g.canonical = 'N'), '{}' ) non_canonical_users
FROM groups g
GROUP BY g.id;
小开

它非常简单，只是首先为 短信[]创建一个新的 - (减)操作符:

CREATE OR REPLACE FUNCTION diff_elements_text
(
text[], text[]
)
RETURNS text[] as
$$
SELECT array_agg(DISTINCT new_arr.elem)
FROM
unnest($1) as new_arr(elem)
LEFT OUTER JOIN
unnest($2) as old_arr(elem)
ON new_arr.elem = old_arr.elem
WHERE old_arr.elem IS NULL
$$ LANGUAGE SQL IMMUTABLE;


CREATE OPERATOR - (
PROCEDURE = diff_elements_text,
leftarg = text[],
rightarg = text[]
);

然后简单地减去数组[ null ] :

select
array_agg(x)-array['']
from
(   select 'Y' x union all
select null union all
select 'N' union all
select ''
) x;

仅此而已:

{ Y，N }

小开

如果你正在寻找一个 关于如何从数组中移除 NULL 的一般问题的现代答案，它是:

array_remove(your_array, NULL)

我对性能特别好奇，想将其与最佳可能的替代方案进行比较:

CREATE OR REPLACE FUNCTION strip_nulls(
IN array_in ANYARRAY
)
RETURNS anyarray AS
'
SELECT
array_agg(a)
FROM unnest(array_in) a
WHERE
a IS NOT NULL
;
'
LANGUAGE sql
;

进行 pgbench 测试证明(具有很高的信心) Array _ move ()的速度是其两倍多一点。我对双精度数字进行了测试，其中包括各种数组大小(10、100和1000个元素)和随机 NULL。

同样值得注意的是，这可以用来删除空格(“ ！= NULL).但是第二个参数接受 anyelement，而且由于它们很可能用字符串文字指示空白，所以请确保将其强制转换为所需的形式，通常是非数组形式。

例如:

select array_remove(array['abc', ''], ''::text);

如果你尝试:

select array_remove(array['abc', ''], '');

它将假设“是 TEXT [](array)并抛出这个错误:

错误: 畸形数组字面值: “”

小开

你应该用 Array _ remove包裹你的 array_agg。

SELECT g.id,
array_remove(array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END), NULL) canonical_users,
array_remove(array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END), NULL) non_canonical_users
FROM groups g
GROUP BY g.id;
小开

我做了一个 array_except，概念是 array_except(x,y)。

array_except(array_agg(x), array_agg(case when x is null then x end))

提交答案