为什么 SUM(null) 在 Oracle 中不为 0?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/17511567/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 01:46:59  来源:igfitidea点击:

Why SUM(null) is not 0 in Oracle?

sqldatabaseoracleplsql

提问by Mohsen Heydari

It would be appreciated explaining the internal functionality of SUM function in Oracle, when encountering null values:
The result of

将理解说明的Oracle SUM函数的内部功能,在遇到空值时:
结果

select sum(null) from dual;
is null

But when a null value is in a sequence of values (like sum of a null-able column), the calculated value of null value will be 0

但是当一个空值在一个值序列中时(比如一个可为空的列的总和),空值的计算值将为 0

select sum(value) from
(
select case when mod(level , 2) = 0 then null else level end as value from dual
connect by level <= 10
)
is 25

This will be more interesting when seeing the result of

当看到结果时,这会更有趣

select (1 + null) from dual
is null

As any operation with null will result null (except is nulloperator).

==========================
Some update due to comments:

因为任何带有 null 的操作都会导致 null(is null运算符除外)。

==========================
一些更新由于评论:

create table odd_table as select sum(null) as some_name from dual;

Will result:

将导致:

create table ODD_TABLE
(
  some_name NUMBER
)

Why some_namecolumn is of type number?

为什么some_namecolumn 是number类型?

采纳答案by dasblinkenlight

SQL does not treat NULLvalues as zeros when calculating SUM, it ignores them:

SQLNULL在计算时不会将值视为零SUM,它会忽略它们:

Returns the sum of all the values, or only the DISTINCTvalues, in the expression. Null values are ignored.

返回DISTINCT表达式中所有值的总和,或仅返回值的总和。空值被忽略。

This makes a difference only in one case - when the sequence being totalled up does not contain numeric items, only NULLs: if at least one number is present, the result is going to be numeric.

这仅在一种情况下有所不同 - 当被累加的序列不包含数字项时,只有NULLs:如果至少存在一个数字,则结果将为数字。

回答by David Aldridge

If you are looking for a rationale for this behaviour, then it is to be found in the ANSI SQL standards which dictate that aggregate operators ignore NULL values.

如果您正在寻找这种行为的基本原理,那么可以在 ANSI SQL 标准中找到它,该标准规定聚合运算符忽略 NULL 值。

If you wanted to override that behaviour then you're free to:

如果您想覆盖该行为,那么您可以自由地:

Sum(Coalesce(<expression>,0))

... although it would make more sense with Sum() to ...

...虽然使用 Sum() 更有意义...

Coalesce(Sum(<expression>),0)

You might more meaningfully:

你可能更有意义:

Avg(Coalesce(<expression>,0))

... or ...

... 或者 ...

Min(Coalesce(<expression,0))

Other ANSI aggregation quirks:

其他 ANSI 聚合怪癖:

  1. Count() never returns null (or negative, of course)
  2. Selecting only aggregation functions without a Group By will always return a single row, even if there is no data from which to select.
  1. Count() 永远不会返回 null(当然也不会返回负值)
  2. 仅选择没有 Group By 的聚合函数将始终返回单行,即使没有可供选择的数据。

So ...

所以 ...

Coalesce(Count(<expression>),0)

... is a waste of a good coalesce.

... 浪费了良好的凝聚力。

回答by Ben

You're looking at this the wrong way around. SUM() operates on a column, and ignores nulls.

你看错了。SUM() 对列进行运算,并忽略空值。

To quote from the documentation:

文档中引用:

This function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

此函数将任何数字数据类型或任何可以隐式转换为数字数据类型的非数字数据类型作为参数。该函数返回与参数的数值数据类型相同的数据类型。

A NULL has no data-type, and so your first example must return null; as a NULL is not numeric.

NULL 没有数据类型,因此您的第一个示例必须返回 null;因为 NULL 不是数字。

Your second example sums the numeric values in the column. The sum of 0 + null + 1 + 2 is 3; the NULL simply means that a number does not exist here.

您的第二个示例对列中的数值求和。0 + null + 1 + 2 之和为 3;NULL 仅表示此处不存在数字。

Your third example is not an operation on a column; remove the SUM() and the answer will be the same as nothingness + 1 is still nothingness. You can't cast a NULL to an empty number as you can with a string as there's no such thing as an empty number. It either exists or it doesn't.

您的第三个示例不是对列的操作;删除 SUM() ,答案将与虚无相同 + 1 仍然是虚无。您不能像使用字符串一样将 NULL 转换为空数字,因为没有空数字这样的东西。它要么存在,要么不存在。

回答by Bohemian

Arithmetic aggregate functions ignorenulls.

算术聚合函数忽略空值。

  • SUM()ignores them
  • AVG()calculates the average as if the null rows didn't exist (nulls don't count in the total or the divisor)
  • SUM()无视他们
  • AVG()计算平均值,就好像空行不存在一样(空行不计入总数或除数)

回答by Walter Mitty

As Bohemian has pointed out, both SUM and AVG exclude entries with NULL in them. Those entries do not go into the aggregate. If AVG treated NULL entries as zero, it would bias the result towards zero.

正如 Bohemian 所指出的,SUM 和 AVG 都排除了其中包含 NULL 的条目。这些条目不会进入聚合。如果 AVG 将 NULL 条目视为零,则会将结果偏向零。

It may appear to the casual observer as though SUM is treating NULL entries as zero. It's really excluding them. If all the entries are excluded, the result is no value at all, which is NULL. Your example illustrates this.

在不经意的观察者看来,好像 SUM 将 NULL 条目视为零。真的是把他们排除在外了。如果排除所有条目,则结果根本没有值,即 NULL。你的例子说明了这一点。

回答by Alex S

This is incorrect: The sum of 0 + null + 1 + 2 is 3; select 0 + null + 1 + 2 total from dual;

这是不正确的:0 + null + 1 + 2 的总和是 3;从双中选择 0 + null + 1 + 2 total;

Result is null! Similar statements give result null if any operand is null.

结果为空!如果任何操作数为空,类似的语句给出结果为空。