用于不匹配Excel STDEV()函数的小数的Server STDEV()函数

Question

只是想知道STDEV() (对于任何版本的sql服务器)背后的公式，或者为什么这些小数的标准差是不同的。

我的目标是向我们的QA部门解释这种计算上的差异，后者可以向我们的客户解释。

对于小数，SQL函数STDEV()似乎与excel不匹配，非十进制整数似乎没有匹配问题。

我试图计算样本的偏差，而不是人口。

示例值: 99.99991，99.99992

Excel STDEV (或任何在线标准差计算器)：7.07107E-06

SQL: 7.13664510111607E-06

我尝试过多个版本的SQL server，在SQL、Excel 2007和2019中，这个函数的所有变体都无法使它们与这些值匹配。

显然，与此网站上的计算相比，SQL所做的工作略有不同：

https://www.mathsisfun.com/data/standard-deviation-calculator.html

tSQL复制：

declare @table table (theNumber float)
insert into @table (theNumber) Values (99.99991), (99.99992)
select STDEV(theNumber) from @table

任何帮助都将不胜感激！

谢谢!

编辑:如果其他人遇到此问题，请使用以下函数：

CREATE FUNCTION[dbo].[StandardDevationSample](@Values as TVP_FLOAT READONLY) RETURNS DECIMAL(12,6)
AS

BEGIN

DECLARE @ItemCount                  INT,
        @Sum                        FLOAT,
        @Mean                       FLOAT,
        @SumOfDifferencesSquared    FLOAT,
        @Variance                   FLOAT

-------------------------------------------------------------------------------

DECLARE @Differences TABLE
(
    [Value] FLOAT
)

DECLARE @DifferencesSquared TABLE
(
    [Value] FLOAT
)

SELECT @ItemCount = (SELECT Count(ID) FROM @Values)

IF(@ItemCount <= 1)
RETURN 0

SELECT @Sum = (SELECT Sum(ID) FROM @Values)

SELECT @Mean = (@Sum / @ItemCount)

INSERT INTO @Differences ([value])
SELECT      (ID - @Mean)
FROM        @Values

INSERT INTO @DifferencesSquared ([value])
SELECT      Square([value])
FROM        @Differences

SELECT @SumOfDifferencesSquared = Sum([value])
FROM   @DifferencesSquared

SELECT @Variance = @SumOfDifferencesSquared / (@ItemCount - 1)

RETURN Convert(Decimal(18,9), Sqrt(@Variance)) 

RETURN 0

END
GO

Answer 1

如果输入只知道小数点5位，那么在标准偏差结果中使用任何小数都是有问题的。

考虑：

declare @table table (groupNo int, theNumber float)
insert into @table (groupNo, theNumber) Values
(1, 99.9999051), (1,99.9999249),
(2, 99.99991), (2,99.99992),
(3, 99.9999149), (3,99.9999151)
select groupNo,ROUND(theNumber,5) from @table
select groupNo,STDEV(theNumber) from @table group by groupNo

第一个结果集是：

groupNo (No column name)
1   99.99991
1   99.99992
2   99.99991
2   99.99992
3   99.99991
3   99.99992

第二个结果集是：

groupNo (No column name)
1   1.40160927359572E-05
2   7.13664510111607E-06
3   1.9073486328125E-06

因此，我建议您应该向用户提供的计算最多的是7E-06，即使是这样也是粗略的，而且在这一点上，SQL和Excel都是一致的。