PostgreSQL计算数组值数量

Question

我希望的是能够计算与单个事件对应的数组元素中真实（出勤）、假实（未出勤）和 NULL 的数量。

编辑：

我刚意识到在 pSQL 中数组的行为与我所想的不同，所以一个简单的

userconfirm bool[]

也许可以解决问题。但我仍然无法统计 true/false/null 值。我将尝试编辑以下问题以符合这个新限制。对任何错误我深感抱歉。

---

我有一列数据如下：

userconfirm bool[]

如果 userconfirm[314] = true，则表示用户＃314将出席。（false = 不出席，NULL = 未读/等等）。

我不确定这是否是此功能的最佳解决方案（用户宣布参加活动），但我在此列上使用聚合函数时遇到了问题。

select count(*) from foo where id = 6 AND true = ANY (userconfirm);

这只返回1，尝试谷歌搜索“计数数组”并没有找到有用的信息。

我该如何计算单个事件的不同值的数量？

- TLP

Answer 1

您可以在SELECT语句中使用unnest来操作数组，如下所示：

select whatever,
       (select sum(case b when 't' then 1 else 0 end) from unnest(userconfirm) as dt(b))
from your_table
-- ...

例如，给出以下内容：

=> select * from bools;
 id |     bits     
----+--------------
  1 | {t,t,f}
  2 | {t,f}
  3 | {f,f}
  4 | {t,t,t}
  5 | {f,t,t,NULL}

你会得到这个：

=> 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

如果这样太丑陋的话，你可以编写一个函数：

create function count_trues_in(boolean[]) returns bigint as $$
    select sum(case b when 't' then 1 else 0 end)
    from unnest($1) as dt(b)
$$ language sql;

并使用它来美化您的查询：

=> select id, count_trues_in(bits) as trues from bools;
 id | trues 
----+-------
  1 |     2
  2 |     1
  3 |     0
  4 |     3
  5 |     2

- mu is too short

---

那似乎可以工作。我可以使用 'f' 来反转它以获得相反的结果。不知道你是否知道如何获取一个索引列表，这些索引是真实的？例如，让它返回 1,4,314。 - TLP

@TLP：我认为你可以通过unnest和generate_series的组合来实现；使用unnest打开数组并使用generate_series提供索引号。然后，也许可以使用array_agg将它们重新组合在一起。 - mu is too short

@TLP：或者使用 rank() over 来提供索引。 - mu is too short

SQL让我头疼。也许最简单的方法是尝试将一些数组导出到PHP并在那里进行处理。如果我能使用Perl，这甚至不会成为一个问题。 - TLP

Answer 2

您可以对array_length函数的结果进行求和：

SELECT SUM(array_length(userconfirm, 2)) WHERE id = 6;

- aleroot

---

很抱歉，我刚意识到我的问题已经改变了。数组并不像我以前从Perl中认为的那样工作。我现在使用bool[]代替。对数组长度求和似乎不是正确的解决方案，因为我得到了293作为一个有两个非空值的数组的总和。 - TLP

Answer 3

这个（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=#

- Alessandro Rossi

---

这似乎非常复杂。我该如何将其应用到我的表格上？我不是在比较两个数组，我想要将一个数组与一个值进行比较。 - TLP

unnest函数将数组展开为表结构（y子查询）。在将数组转换为单列表的情况下，您可以在SQL查询中使用count(*)来评估条件。检查res = true与检查res相同。 - Alessandro Rossi