SQL Select语句存在NULL值时计算平均值返回NULL问题咨询

问题原因 #

1. SQL中NULL参与任意算术运算的结果都为NULL：客户987654321的Total InStore Orders为NULL，a.[Total Online Orders] + b.[Total InStore Orders]的计算结果直接为NULL，后续运算全部失效。
2. coalesce函数使用错误：该函数需要传入多个候选值，返回第一个非NULL的结果，你仅传入了相加后得到的NULL值，没有实现空值替换的效果。
3. 额外隐患：full outer join后客户编号可能来自a表或b表，仅取a.[customer number]会导致仅在b表存在的客户编号显示为NULL。

修正后的SQL代码 #

完全符合你预期（仅在单来源时返回对应值，双来源时取两者平均）的代码如下：

select
  coalesce(a.[customer number], b.[customer number]) as [customer number],
  a.[Total Online Orders],
  b.[Total InStore Orders],
  (coalesce(a.[Total Online Orders], 0) + coalesce(b.[Total InStore Orders], 0))
  /
  case
    when a.[Total Online Orders] is not null and b.[Total InStore Orders] is not null then 2
    else 1
  end as [Average Order Size]
from table_a a
full outer join table_b b on a.[customer number] = b.[customer number]

如果你需要固定除以2（仅需解决空值问题），可使用简化版本：

select
  coalesce(a.[customer number], b.[customer number]) as [customer number],
  a.[Total Online Orders],
  b.[Total InStore Orders],
  (coalesce(a.[Total Online Orders], 0) + coalesce(b.[Total InStore Orders], 0)) / 2 as [Average Order Size]
from table_a a
full outer join table_b b on a.[customer number] = b.[customer number]

内容的提问来源于stack exchange，提问作者st87_top