带有空值的求和“不起作用”在 SQL Server/Azure 中

Question

我试图通过在AVG（）函数中使用案例语句来平均一组列，并将其从分母中排除为零。我读到AVG（）不包括无效结果，因此正在使用案例语句用null替换0。但是，这不是我在实践中发现的 - 请参见下面的代码。有人可以解释为什么会发生这种情况吗？如果您可以提出一项代码调整以实现我之后的目标，那也将是晶圆厂。谢谢。

with a as
(
select 0 t1, 3 t2 
)
, b as
(
select 6 t1, 0 t2
)
, c as -- building a small table containing test data.
(
select * from a
union all
select * from b
)
select sum(case when t2 = 0 then null else t2 end + case when t1 = 0 then null else t1 end) r1 
  , avg(case when t2 = 0 then null else t2 end + case when t1 = 0 then null else t1 end) r2
  , avg(t1) r3
from c

什么子查询C包含：

T1	T2
0	3
6	0

我的查询的实际结果：

R1	R2	R3
NULL	3	列

R2是我希望我的查询结果为：avg （3 + NULL，NULL + 6）= AVG（3，6）= 4.5：

R1	R2	R3
9	4.5	3

Answer 1

您可以将它们过滤掉，而不是将 0 值设置为 null：

with a as
(
select cast(0 as float) t1, cast(3 as float) t2 
)
, b as
(
select cast(6 as float) t1, cast(0 as float) t2
)
, c as -- building a small table containing test data.
(
select * from a where t1 > 0 or t2 > 0
union all
select * from b where t1 > 0 or t2 > 0
)
select sum(t2+t1) r1 
  , avg(t2+t1) r2
  , avg(t1) r3
from c;

输出：

| r1 | r2 | r3 |
|----+----+----|
| 9  | 4.5| 3  |

*了解如何将数字转换为 float 类型，以便捕获 r2 上的浮点。