postgresql - 如何在 PostgreSQL sum() 中检测 NULL 行

Question

我想聚合列的总和，同时跟踪表示错误情况的 NULL 值的存在。例如，取表号:

# select * from numbers;
  n   |  l  
------+-----
    1 | foo
    2 | foo
 NULL | bar
    4 | bar

给定标签 l，我想计算带有该标签的数字 n 的总和，前提是没有 NULL 值。理想情况下，对于没有任何行的标签，总和将为 0。所以我正在寻找一些查询 q 这样q('foo') = 3、q('baz') = 0 和 q('bar') 以某种方式发出错误信号，例如通过返回 NULL。

我从 sum() aggregate function 开始，但这会将 NULL 行转换为 0。一种解决方案是返回 NULL 的变体，前提是存在任何 NULL 值。

sum() 给出

# select sum(n) from numbers where l = 'bar';
 sum 
-----
   4

但我宁愿用sumnull()

# select sumnull(n) from numbers where l = 'bar';
 sumnull 
---------
  NULL

到目前为止我发现的最佳解决方案是也计算非 NULL 行并与总计数进行比较:

# select sum(n), count(*), count(n) as notnull from numbers;
 sum | count | notnull 
-----+-------+---------
   7 |     4 |       3

如果 count 不等于 notnull，我知道结果无效。

Answer 1

空集是否足够好？

create table numbers (n int);
insert into numbers values (1),(2),(null),(4);

select sum(n)
from numbers
having bool_and(n is not null);
 sum 
-----
(0 rows)

如果你真的需要一个空值，那就有点复杂了:

with sum_null as (
    select sum(n) as sum_n
    from numbers
    having bool_and(n is not null)
)
select case
    when not exists (select 1 from sum_null) then null
    else (select sum_n from sum_null) end
;
 sum_n 
-------

(1 row)

替换 having 行:

having not bool_or(n is null)

可读性较差但可能更快，因为它可以在找到第一个 null 时停止搜索。

https://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE