sql - 如何计算 pgsql 中值大于 0 的逗号拆分值？

Question

我正在使用 PostgreSQL 并将数据库中的值存储为逗号拆分。现在我想计算所有逗号拆分值，其中单个值大于零。

我怎样才能实现它？

我的数据库列值看起来像

0,120,0,0,118,0,0,128,0,123,0,0,0,125,0
192,193,196,195
192,193,196,1950,128,0,123,0,0,

我尝试的是:

SELECT case when col='0' then 0 else array_length(regexp_split_to_array(replace(replace(col,'0,',''),',0',''), ','), 1) end 
FROM table

这里的问题是它会替换所有 0，即使它存在于任何其他值中也是如此

注意:我使用的是PostgreSQL 8.4.2

Answer 1

您需要 unnest() 数组中的值(本质上是将其转换为正确规范化的模型)，然后您才能正确计算它们:

我不清楚您是要计算表中所有行的非零值还是计算每一行的非零值。

计算所有行:

select count(*)
from the_table, 
     unnest(string_to_array(the_column, ',')) as x(v) 
where v::int > 0;

如果您需要为每一行计算它们，您可以假设您在表中有一个主键(或唯一)列:

select id, count(*)
from the_table, 
     unnest(string_to_array(the_column, ',')) as x(v) 
where v::int > 0
group by id;

上面假设有一列 id 是唯一的。

编辑

对于旧的和不受支持的 Postgres 版本，您需要将其更改为:

select count(*)
from (
  select unnest(string_to_array(the_column, ',')) v
  from the_table
) t  
where v::int > 0

或

select id, count(*)
from (
  select id, unnest(string_to_array(the_column, ',')) v
  from the_table
) t
where v::int > 0
group by id;