了解 T-SQL stdev、stdevp、var 和 varp [英] Understanding T-SQL stdev, stdevp, var, and varp

问题描述

我很难理解这些统计函数的作用及其工作原理.我更难理解 stdev 与 stdevp 和 var equivelant 的工作原理.有人可以帮我把这些分解成哑巴吗?

I'm having a difficult time understand what these statistics functions do and how they work. I'm having an even more difficult time understanding how stdev works vs stdevp and the var equivelant. Can someone please break these down into dumb for me?

推荐答案

在统计学中，标准偏差和方差是衡量总体中某个指标偏离平均值(通常是平均值)多少的度量.标准差定义为方差的平方根，方差定义为与均值的平方差的平均值，即:

In statistics Standard Deviation and Variance are measures of how much a metric in a population deviate from the mean (usually the average.) The Standard Deviation is defined as the square root of the Variance and the Variance is defined as the average of the squared difference from the mean, i.e.:

对于大小为 n 的种群:x1, x2, ..., xn均值:xmean

For a population of size n: x1, x2, ..., xn with mean: xmean

Stdevp = sqrt( ((x1-xmean)^2 + (x2-xmean)^2 + ... + (xn-xmean)^2)/n )

Stdevp = sqrt( ((x1-xmean)^2 + (x2-xmean)^2 + ... + (xn-xmean)^2)/n )

当整个总体的值不可用时(大多数情况下)，通常会应用贝塞尔校正来更好地估计整个总体的实际标准偏差.在计算方差时，贝塞尔的校正只是除以 n-1 而不是除以 n，即:

When values for the whole population are not available (most of the time) it is customary to apply Bessel's correction to get a better estimate of the actual standard deviation for the whole population. Bessel's correction is merely dividing by n-1 instead of by n when computing the variance, i.e:

标准差 = sqrt( ((x1-xmean)^2 + (x2-xmean)^2 + ... + (xn-xmean)^2)/(n-1) )

Stdev = sqrt( ((x1-xmean)^2 + (x2-xmean)^2 + ... + (xn-xmean)^2)/(n-1) )

请注意，对于足够大的数据集，使用哪个函数并不重要.

Note that for large enough data-sets it won't really matter which function is used.

您可以通过运行以下 T-SQL 脚本来验证我的回答:

You can verify my answer by running the following T-SQL script:

-- temporary data set with values 2, 3, 4
declare @t table([val] int);

insert into @t values
    (2),(3),(4);

select avg(val) as [avg], -- equals to 3.0
   -- Estimation of the population standard devisation using a sample and Bessel's Correction:
   -- ((x1 - xmean)^2 + (x2 - xmean)^2 + ... + (xn-xmean)^2)/(n-1)  
   stdev(val) as [stdev],
   sqrt( (square(2-3.0) + square(3-3) + square(4-3))/2) as [stdev calculated], -- calculated with value 2, 3, 4

   -- Population standard deviation: 
   -- ((x1 - xmean)^2 + (x2 - xmean)^2 + ... + (xn-xmean)^2)/n
   stdevp(val) as [stdevp],
   sqrt( (square(2-3.0) + square(3-3) + square(4-3))/3) as [stdevp calculated] -- calculated with value 2, 3, 4
from @t;

进一步阅读维基百科文章:标准偏差n 和 Bessel's Correction.

Further reading wikipedia articles for: standard deviation and Bessel's Correction.

这篇关于了解 T-SQL stdev、stdevp、var 和 varp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！