无聚合错误的sql server中的加权标准偏差 [英] weighted standard deviation in sql server without aggregation error

问题描述

在计算加权和平方差时重做加权平均值(已在另一列中)导致错误无法对包含聚合或子查询的表达式执行聚合函数".

Redoing the weighted mean (which is already in another column) in working out the weighted-Sum-Of-Squared-Deviations, results in the error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

SQRT(SUM(wt.value*SQUARE(out1.value-(SUM(ISNULL(out1.value,0)*wt.value)/SUM(wt.value))))
    / (((COUNT(wt.value)-1)*SUM(wt.value))/(COUNT(wt.value)))) as wsdevInc0

您能建议如何在SQL中计算加权标准偏差，而不必在查询层次结构中添加另一级别的"SELECT"(可能会重复选择数据)吗?

Can you suggest how to calculate the WEIGHTED STANDARD DEVIATION in SQL, short of adding another level of 'SELECT' to the query hierarchy (and probably duplicating the selection of data)?

推荐答案

我刚刚遇到了同样的问题和错误消息.解决方法是重写加权标准差公式.

I just encountered this same problem and error message. The way to solve it is to rewrite the weighted standard deviation formula.

使用<>表示平均值的总体方差公式为:

The population variance formula, using <>'s to denote an average, which also applies to a weighted average, is:

variance = <(x - <x>)^2>

此表单包含嵌入在另一个聚合函数中的聚合函数，因此会显示SQL错误消息.

This form contains an aggregate function embedded in another aggregate function, hence the SQL error message.

解决方案是重写没有嵌套聚合的公式:

The solution is to rewrite the formula without nested aggregations:

variance = <x^2> - <x>^2

接下来直接乘以前面的公式，并注意<x<x>> = <x><x>.

This directly follow by multiplying out the previous formula, and noting that <x<x>> = <x><x>.

标准偏差只是方差的平方根，因此SQL变为:

The standard deviation is just the square root of the variance, so the SQL becomes:

SQRT( SUM(w*SQUARE(x))/SUM(w) - SQUARE(SUM(w*x)/SUM(w)) )

其中w是权重，x是统计变量.

where w is the weight, and x is the statistical variable.

以上公式适用于人口数据集.对于样本数据集，将上面的SQL值乘以

The above formulas are applicable to a population data set. For a sample data set, multiply the above SQL value by

SQRT( COUNT(*)/(COUNT(*)-1) )

这篇关于无聚合错误的sql server中的加权标准偏差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！