PostgreSQL 计数数组值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9143882/
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
PostgreSQL count array values
提问by TLP
What I would like is to count the array elements which corresponds to true (attendance), false (non-attendance) and NULL for any single event.
我想要的是计算与任何单个事件的 true（出席）、false（未出席）和 NULL 对应的数组元素。
EDIT:
编辑：
I just realized that arrays do not behave as I thought in pSQL, so a simple
我刚刚意识到数组的行为不像我在 pSQL 中想象的那样，所以一个简单的
userconfirm bool[]
Might suffice. However, I am still having the same problem counting true/false/null values. I will attempt to edit the question below to match this new constraint. I apologize for any errors.
可能就够了。但是，我在计算真/假/空值时仍然遇到同样的问题。我将尝试编辑下面的问题以匹配此新约束。对于任何错误，我深表歉意。
I have a column such as
我有一个专栏，例如
userconfirm bool[]
Where userconfirm[314] = truewould mean that user #314 will attend. (false = no attend, NULL = not read/etc).
哪里userconfirm[314] = true意味着用户#314 将参加。（false = 不参加，NULL = 未读/等）。
I'm not sure this is the best solution for this functionality (users announce their attendance to an event), but I am having trouble with an aggregate function on this column.
我不确定这是此功能的最佳解决方案（用户宣布他们参加了活动），但我在使用此列中的聚合函数时遇到了问题。
select count(*) from foo where id = 6 AND true = ANY (userconfirm);
This only returns 1, and trying to google "counting arrays" does not turn up anything useful.
这仅返回 1，并且尝试在 google 上搜索“计数数组”并没有发现任何有用的信息。
How would I go about counting the different values for a single event?
我将如何计算单个事件的不同值？
采纳答案by mu is too short
You can use unnestin your SELECT like this:
您可以unnest像这样在 SELECT 中使用：
select whatever,
(select sum(case b when 't' then 1 else 0 end) from unnest(userconfirm) as dt(b))
from your_table
-- ...
For example, given this:
例如，鉴于此：
=> select * from bools;
id | bits
----+--------------
1 | {t,t,f}
2 | {t,f}
3 | {f,f}
4 | {t,t,t}
5 | {f,t,t,NULL}
You'd get this:
你会得到这个：
=> select id, (select sum(case b when 't' then 1 else 0 end) from unnest(bits) as dt(b)) as trues from bools;
id | trues
----+-------
1 | 2
2 | 1
3 | 0
4 | 3
5 | 2
If that's too ugly, you could write a function:
如果这太难看，你可以写一个函数：
create function count_trues_in(boolean[]) returns bigint as $$
select sum(case b when 't' then 1 else 0 end)
from unnest() as dt(b)
$$ language sql;
and use it to pretty up your query:
并使用它来修饰您的查询：
=> select id, count_trues_in(bits) as trues from bools;
id | trues
----+-------
1 | 2
2 | 1
3 | 0
4 | 3
5 | 2
回答by aleroot
You could SUM the array_length function result :
你可以总结 array_length 函数结果：
SELECT SUM(array_length(userconfirm, 2)) WHERE id = 6;
回答by Alessandro Rossi
This one may do the trick(unnest).
这个可以解决问题（unnest）。
postgres=# with y(res) as (
postgres(# with x(a) as (
postgres(# values (ARRAY[true,true,false])
postgres(# union all
postgres(# values (ARRAY[true,null,false])
postgres(# )
postgres(# select unnest(a) as res
postgres(# from x
postgres(# )
postgres-# select count(*)
postgres-# from y
postgres-# where res;
count
-------
3
(1 row)
postgres=#
