postgresql 空结果集的聚合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6524352/
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
aggregate of an empty result set
提问by milan
I would like the aggregates of an empty result set to be 0. I have tried the following:
我希望空结果集的聚合为 0。我尝试了以下操作：
SELECT SUM(COALESCE(capacity, 0))
FROM objects
WHERE null IS NOT NULL;
Result:
结果：
sum
-----
(1 row)
Subquestion: wouldn't the above work in Oracle, using SUM(NVL(capacity, 0))?
子问题：使用SUM(NVL(capacity, 0))?
采纳答案by Andriy M
From the documentation pageabout aggregate functions:
从关于聚合函数的文档页面：
It should be noted that except for
count, these functions return a null value when no rows are selected. In particular,sumof no rows returns null, not zero as one might expect. Thecoalescefunction may be used to substitute zero for null when necessary.
应该注意的是，除了当没有选择任何行时
count，这些函数返回空值。特别是，sum没有行返回空值，而不是人们所期望的零。coalesce必要时，该函数可用于将零替换为空值。
So, if you want to guarantee a value returned, apply COALESCEto the resultof SUM, not to its argument:
所以，如果你想保证返回的值，适用COALESCE于结果的SUM，而不是它的参数：
SELECT COALESCE(SUM(capacity), 0) …
As for the Oracle 'subquestion', well, I couldn't find any notion of NULLs at the official doc page (the one for 10.2, in particular), but two other sources are unambiguous:
至于 Oracle 的“子问题”，好吧，我在官方文档页面（尤其是10.2 的页面）中找不到任何关于 NULL 的概念，但另外两个来源是明确的：
SUM([DISTINCT] n)Sum of values of n, ignoring NULLssum aggregate function [Oracle SQL]:
…if a sum() is created over some numbers, nulls are disregarded, as the following example shows…
SUM([DISTINCT] n)n 值的总和，忽略 NULL...如果在某些数字上创建 sum()，则忽略空值，如以下示例所示...
That is, you needn't apply NVL to capacity. (But, like with COALESCEin PostgreSQL, you might want to apply it to SUM.)
也就是说，您无需将 NVL 应用于capacity. （但是，就像COALESCE在 PostgreSQL 中一样，您可能希望将其应用于SUM.）
回答by Denis de Bernardy
The thing is, the aggregate always returns a row, even if no rows were aggregated (as is the case in your query). You summed an expression over no rows. Hence the null value you're getting.
问题是，聚合总是返回一行，即使没有聚合行（如您的查询中的情况）。您对没有行的表达式求和。因此你得到的空值。
Try this instead:
试试这个：
select coalesce(sum(capacity),0)
from objects
where false;
回答by Michael Buen
Just do this:
只需这样做：
SELECT COALESCE( SUM(capacity), 0)
FROM objects
WHERE null IS NOT NULL;
By the way, COALESCE inside of SUM is redundant, even if capacity is NULL, it won't make the summary null.
顺便说一句，SUM 里面的 COALESCE 是多余的，即使容量为 NULL，也不会使摘要为空。
To wit:
以机智：
create table objects
(
capacity int null
);
insert into objects(capacity) values (1),(2),(NULL),(3);
select sum(capacity) from objects;
That will return a value of 6, not null.
这将返回值 6，而不是 null。
And a coalesce inside an aggregate function is a performance killer too, as your RDBMS engine cannot just rip throughall the rows, it has to evaluate each row's column if its value is null. I've seen a bit OCD query where all the aggregate queries has a coalesce inside, I think the original dev has a symptom of Cargo Cult Programming, the query is way very sloooowww. I removed the coalesce inside of SUM, then the query become fast.
聚合函数内的合并也是一个性能杀手，因为您的 RDBMS 引擎不能只是遍历所有行，如果每行的值为空，它必须评估每一行的列。我见过一些 OCD 查询，其中所有聚合查询内部都有合并，我认为原始开发人员有Cargo Cult Programming的症状，查询方式非常糟糕。我删除了 SUM 内部的合并，然后查询变得很快。
回答by Asad Nauman
Although this post is very old, but i would like to update what I use in such cases
虽然这篇文章很旧，但我想更新我在这种情况下使用的内容
SELECT NVL(SUM(NVL(capacity, 0)),0)
FROM objects
WHERE false;
Here external NVL avoids the cases when there is no row in the result set. Inner NVL is used for null column values, consider the case of (1 + null) and it will result in null. So inner NVL is also necessary other wise in alternate set default value 0 to the column.
这里外部 NVL 避免了结果集中没有行的情况。内部 NVL 用于空列值，考虑 (1 + null) 的情况，它将导致空值。因此，内部 NVL 也需要在其他方面为列设置默认值 0。
