SQL 如何使用postgres排除array_agg中的空值，如string_agg中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13122912/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to exclude null values in array_agg like in string_agg using postgres?
提问by Daud
If I use array_aggto collect names, I get my names separated by commas, but in case there is a nullvalue, that null is also taken as a name in the aggregate. For example :
如果我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;
it returns ,Larry,Philinstead of just Larry,Phil(in my 9.1.2, it shows NULL,Larry,Phil). as in thisfiddle
它返回,Larry,Phil而不仅仅是Larry,Phil（在我的 9.1.2 中，它显示NULL,Larry,Phil）。就像在这个小提琴中一样
Instead, if I use string_agg(), it shows me only the names (without empty commas or nulls) like here
相反，如果我使用string_agg()，它只显示我的名字（没有空逗号或空值），就像这里
The problem is that I have Postgres 8.4installed on the server, and string_agg()doesn't work there. Is there any way to make array_agg work similar to string_agg() ?
问题是我已经Postgres 8.4安装在服务器上，string_agg()但在那里不起作用。有没有办法让 array_agg 工作类似于 string_agg() ？
采纳答案by Clodoaldo Neto
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
Or, simpler and may be cheaper, using array_to_stringwhich eliminates nulls:
或者，更简单且可能更便宜，使用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
回答by Dale O'Brien
With postgresql-9.3 one can do this;
使用 postgresql-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;
Update: with postgresql-9.4;
更新：使用 postgresql-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;
回答by rorycl
In solving the general question of removing nulls from array aggregates there are two main ways of attacking the problem: either doing array_agg(unnest(array_agg(x)) or creating a custom aggregate.
在解决从数组聚合中删除空值的一般问题时，有两种主要的解决方法：要么执行 array_agg(unnest(array_agg(x))，要么创建自定义聚合。
The first is of the form shown above:
第一个是如上所示的形式：
SELECT
array_agg(u)
FROM (
SELECT
unnest(
array_agg(v)
) as u
FROM
x
) un
WHERE
u IS NOT NULL;
The second:
第二：
/*
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 = '{}'
);
Calling the second is (naturally) a little nicer looking than the first:
调用第二个（自然）比第一个好看一点：
select array_agg_notnull(v) from x;
从 x 中选择 array_agg_notnull(v)；
回答by ced-b
I am adding this even though this this thread is quite old, but I ran into this neat trick that works quite well on small arrays. It runs on Postgres 8.4+ without additional libraries or functions.
尽管这个线程已经很老了，但我还是要添加这个，但是我遇到了这个巧妙的技巧，它在小数组上运行得很好。它在 Postgres 8.4+ 上运行，无需额外的库或函数。
string_to_array(array_to_string(array_agg(my_column)))::int[]
The array_to_string()method actually gets rid of the nulls.
该array_to_string()方法实际上摆脱了空值。
回答by GarethD
As has been suggested in the comments you can write a function to replace nulls in an array, however as also pointed out in the thread linked to in the comments, this kind of defeats the efficiency of the aggregate function if you have to create an aggregate, split it then aggregate it again.
正如评论中所建议的那样，您可以编写一个函数来替换数组中的空值，但是正如评论中链接到的线程中所指出的那样，如果您必须创建聚合，则这种方法会降低聚合函数的效率，将其拆分然后再次聚合。
I think keeping nulls in the array is just a (perhaps unwanted) feature of Array_Agg. You could use subqueries to avoid this:
我认为在数组中保留空值只是 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
回答by Alexi Theodore
If you are looking for a modern answer to the general question of how to remove a NULL from an array, it is:
如果您正在寻找有关如何从数组中删除 NULL 的一般问题的现代答案，它是：
array_remove(your_array, NULL)
I was specifically curious about performance and wanted to compare this to the best possible alternative:
我对性能特别好奇，并想将其与最佳替代方案进行比较：
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
;
Doing a pgbench test proved (with high confidence) that array_remove() is a little more than twice as fast. I did my test on double precision numbers with a variety of array sizes (10, 100 and 1000 elements) and random NULLs in between.
进行 pgbench 测试证明（以高可信度）array_remove() 的速度快了两倍多一点。我对具有各种数组大小（10、100 和 1000 个元素）和介于两者之间的随机 NULL 的双精度数字进行了测试。
回答by Miklos
It is very simple, just first of all create a new - (minus)operator for text[]:
这很简单，首先为text[]创建一个新的-（减号）运算符：
CREATE OR REPLACE FUNCTION diff_elements_text
(
text[], text[]
)
RETURNS text[] as
$$
SELECT array_agg(DISTINCT new_arr.elem)
FROM
unnest() as new_arr(elem)
LEFT OUTER JOIN
unnest() 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[]
);
And simply subtract the array[null]:
并简单地减去数组[null]：
select
array_agg(x)-array['']
from
( select 'Y' x union all
select null union all
select 'N' union all
select ''
) x;
That's all:
就这样：
{Y, N}
{Y, N}
回答by Michael
A bigger question is why pull all user/group combos at once. Guaranteed your UI cant handle all that data. Adding paging to oversized data is also a bad idea. Get your users to filter the set before they see data. Make sure your JOIN option set is in the list so they can filter for performance if they want to. Sometimes 2 queries make users happier if they are both fast.
一个更大的问题是为什么一次拉出所有用户/组组合。保证您的 UI 无法处理所有这些数据。为过大的数据添加分页也是一个坏主意。让您的用户在看到数据之前过滤该集合。确保您的 JOIN 选项集在列表中，以便他们可以根据需要过滤性能。有时，如果 2 个查询都快的话，用户会更开心。
