SQL 创建加权平均值 - 降低 NULL 值的权重
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15164707/
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
Creating a Weighted Average - Dropping Weights for NULL values
提问by Jared
So there's the SQL Function AVG(), which takes the average of all values in a column, ignoring all NULL values. If one needs to make a weighted average, then they'd just use SUM(value * weight)/SUM(weight) with a Group By clause.
所以有 SQL 函数 AVG()，它取列中所有值的平均值，忽略所有 NULL 值。如果需要进行加权平均，那么他们只需将 SUM(value * weight)/SUM(weight) 与 Group By 子句一起使用。
If I would want to do the latter, but some of my values are NULL, then how would I go about telling SQL to ignore weights with NULL value observations in the SUM(weight) function?
如果我想做后者，但我的一些值是 NULL，那么我将如何告诉 SQL 在 SUM(weight) 函数中忽略带有 NULL 值观察的权重？
My other issue is that I'm taking an average of 90 different columns at once, so I'd like to avoid making 90 new weight variables for this calculation.
我的另一个问题是我一次平均取 90 个不同的列，所以我想避免为这个计算创建 90 个新的权重变量。
Let me know if I've made this clear or not.
让我知道我是否清楚地说明了这一点。
I'm using SQL Server 2005
我正在使用 SQL Server 2005
回答by Gordon Linoff
You would use conditional summing as the denominator:
您将使用条件求和作为分母：
select sum(value*weight) / sum(case when value is not null then weight else 0 end)
If the weights are always bigger than 0, then you don't have to worry about divide by 0. That would only occur when all the values are NULL. And, in that case the numerator would be NULL.
如果权重总是大于 0，那么您不必担心除以 0。只有当所有值都为 NULL 时才会发生这种情况。而且，在这种情况下，分子将为 NULL。
You could also phrase it as:
您也可以将其表述为：
select sum(value*weight) / sum(case when value is not null then weight end)
or as:
或作为：
select sum(case when value is not null then value*weight end) / sum(case when value is not null then weight end)
This is more verbose, but makes it very clear that you are ignoring NULL values in both the numerator and denominator.
这更冗长，但很清楚地表明您忽略了分子和分母中的 NULL 值。
