创建加权平均数 - 删除空值的权重

Question

SQL Function AVG()可以计算一列中所有数值的平均值，忽略所有NULL值。如果需要进行加权平均，则可以使用SUM(value * weight)/SUM(weight)并带有Group By子句。

如果我想要进行后者，但是我的一些值是NULL，那么我该如何告诉SQL在SUM(weight)函数中忽略具有NULL值观测值的权重？

我的另一个问题是，我一次要对90个不同的列取平均值，因此我想避免为此计算90个新的权重变量。

请让我知道是否已经清楚明白了。

我正在使用SQL Server 2005。

- Jared

Answer 1

您需要使用条件求和作为分母：

select sum(value*weight) / sum(case when value is not null then weight else 0 end)

如果权重始终大于0，则无需担心除以0的问题。只有在所有值都为NULL时才会发生这种情况，此时分子也将为NULL。

你还可以这样表述：

select sum(value*weight) / sum(case when value is not null then weight end)

或者是：

select sum(case when value is not null then value*weight end) / sum(case when value is not null then weight end)

这种写法更冗长，但非常清晰地表明您在忽略分子和分母中的 NULL 值。

- Gordon Linoff

---

@Gordon...如果值为null，那么它不会因为除以零而失败吗？或者当值为null时会发生什么？ - MikeTWebb

@MikeTWebb……除了IS NULL和IS NOT NULL之外的任何操作中，NULL都会返回NULL。即使是除以0也是如此。但是，我包括第二个版本是因为有人可能会考虑到这一点。如果没有匹配项，它将更清楚地返回NULL。 - Gordon Linoff

这看起来很不错！我能在一个表中为85个不同的列执行此操作，每个列都有不同的NULL值吗？ - Jared

@Jared . . . 是的，你可以。我建议你将重量和价值变量名称复制到Excel中，并在那里生成SQL代码。在Excel中复制公式比手动输入要容易得多。 - Gordon Linoff