Как рассчитать стандартное отклонение в SQL без встроенной функции

Question

У меня есть столбец чисел в моей базе данных. Как я могу вычислить стандартное отклонение? Я не хочу использовать функцию stddev.

Answer 1

Просто потому, что мне было любопытно, я решил проверить фактический STDEV(). Теперь я не смог прибить встроенную функцию.

Я был близко... 0,000141009220002264 или 0,00748% скидка

Также, Total Average и Count должны быть преобразованы в float (дисперсия была больше с десятичной дробью)

Приведенный ниже пример приведен после моей таблицы ставок казначейства за 10-летнюю доходность (не то, чтобы это имело значение)

Select SQLFunction = Stdev([TR_Y10]) 
      ,ManualCalc  = Sqrt(Sum(Power(((cast([TR_Y10] as float)-B.TotalAvg)),2) / B.TotalCnt))
      ,Variance    = Stdev([TR_Y10]) - Sqrt(Sum(Power(((cast([TR_Y10] as float)-B.TotalAvg)),2) / B.TotalCnt))
 From [Chinrus-Shared].[dbo].[DS_Treasury_Rates]
 Join (Select TotalAvg=Avg(cast([TR_Y10] as float)),TotalCnt=count(*) From [Chinrus-Shared].[dbo].[DS_Treasury_Rates]) B on 1=1

Возвращает

SQLFunction         ManualCalc          Variance
1.88409468982299    1.88395368060299    0.000141009220002264

Answer 2

Стандартное отклонение - это квадратный корень дисперсии, деленный на n.

Дисперсия - это сумма квадратов разностей между средним и наблюдаемым значением.

Итак, в большинстве баз данных вы можете использовать оконные функции:

select sqrt(avg(var))
from (select square(t.x - avg(t.x) over ()) as var
      from t
     ) t;

Заметки:

• square() Функция может иметь другое имя (например, power()).
• sqrt() Функция может иметь другое имя.
• Это не очень хороший способ для расчета стандартного отклонения в целом. В частности, это численно нестабильный алгоритм (он будет отлично работать для конечного числа нормальных чисел).
• Подзапрос необходим, потому что оконные функции не могут быть аргументами функций агрегации.