时间戳

首页 > 解决方案 > 在不同 SQL Server 上计算的标准差差异

sql-server - 在不同 SQL Server 上计算的标准差差异

问题描述

我在两个不同的地方运行两个查询SQL Server instances，一个是 a "13.0.4474.0"，另一个是 a "13.0.4411.0"。

他们计算了STDEV同一组的近似8K bigint numbers。STDEV出来的不一样！

我确信数字是相同的，因为在应用之前STDEV我选择了所有数字并比较它们。他们。是。这。相同的。（我把这两组放在 Excel 中，对它们进行排序并取不同行的差异。它总是出来 0，所以它们是相同的）

STDEV略有不同：

2880.01921436887 and 2880,01956854958

如果我计算STDEV相同数字中的数字，Excel我会得到第三个数字，但在这种情况下，谁在乎呢，这一定是由于两个程序的实现中的某些东西。

但是为什么在同一个 SQL Server 中呢？

我正在使用的查询没有OVER nor ORDER BY（这使得函数不确定）

这是第一台机器上的代码：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT 
    COUNT_BIG(*) AS C, 
    SUM(CAST(MyBigintField AS BIGINT)) AS S, 
    STDEV(MyBigintField) AS D 
FROM 
    dbo.myTable 
WHERE 
    MyBigintField >= 108000000 
    AND MyBigintField < 108010000

这些数字不会被任何进程更改。选择不断吐出相同的两个不同的数字。

在第二台机器上，查询完全相同，除了字段和表的名称，因为我在另一台机器上。再一次，如果我只是用相同的查询收集数字（只是使用 * 而不是 COUNT、SUM、STDEV），我会得到相同的数字。

我做了很多尝试，我发现如果我将 1 加到一台机器中最大的 bigint 数字上，那么我会得到与第二台机器相同的 STDEV。如果我只在任何其他数字上加 1，则 ST.DEV 不会改变，这是更自然的事情，因为这些数字约为 1 亿，它们是 8 千，因此只需将 1 加到其中一个上不应更改前 11 位小数的 st.dev。

我想这是由于四舍五入造成的，但我无法完全理解。

[编辑]

过了一会儿，我发现了这个非常奇怪的事情：

DECLARE @min as BIGINT
DECLARE @max as BIGINT
DECLARE @step as BIGINT
SET @min = 1900000001
SET @max = 1900000050
SET @step = 1
;WITH mycte AS (
    SELECT @min AS i
        UNION ALL
    SELECT i + @step
    FROM mycte
    WHERE i <= @max - @step
)
SELECT COUNT(*) myCount, stdev(i) myStDev
FROM mycte
OPTION (MAXRECURSION 0)

这给了我 myCount = 50（正确）和 myStDev = 0（在两台机器上），我检查了，CTE 正确地给了我 1900000001 和 1900000050（包括两者）的 50 个数字。

这怎么可能？

如果我再试一次，但范围上升到 1900000150，则 STDev 与 0 不同，但它似乎与使用 Excel 计算的不同（我尝试了 ST.DEV.P 和 ST.DEV.S）

也许这与我正在使用值非常接近 maxint 的 bigint 数字这一事实有关？

标签： sql-serverstandard-deviation

解决方案

这是一个用于检查任何一组值的 CTE - 如果您更改 'd' 中的 SQL 以返回任何别名为 y 的数字列表，那么您应该得到 'theirSD' 等于 'mySD' - 如果两个不同的服务器给出不同的值，那么我希望 sigma、sigma2 或 n 在它们之间有所不同

我根据 sd 的定义进行了自己的计算，即 (x - mu) 的平方和，然后除以 n-1（在这种情况下）

with d as (select  cast(x as float) y from tempt)
    ,calcs as (select  stdev(y) theirSD, 
                        avg(y) theirAV,
                        sum(y) Sigma,
                        sum(y *y) sigma2,
                        sum(y) / count(0) myAV,
                        count(0) n
                        from d) 
    select *, sqrt((sigma2 - sigma/n * sigma ) / (n-1)) mysd     from calcs

我的公式需要转换为浮点数 - 如果留在 bigint，由于四舍五入，它不会完全正常，但他们的数字仍然看起来与 bigint

我认为您需要检查数据中的 sum(x * x) 因子，然后如果 COUNT 和 SUM(x) 看起来相同，则可能是 Jeroem 假设的数字舍入效应

推荐阅读