SQL或SSRS中组内的标准差(另一种方法是计算Sigma或Cpk)

Question

在SSRS/SQl中，我在计算子组内的标准差时遇到了严重的问题(以更方便的方式为准)。这背后的真正原因是我需要计算Sigma值(根据六西格玛原则)或者Cpk值(过程能力)，但是我的努力只限于标准差。

我不擅长统计数据，但似乎SSRS (和StDev )中的StDevP和SSRS函数不是在“子组内”计算的，而且我找不到能够做到这一点的函数。下面是一个excel屏幕截图，显示我想在SQL/SSRS编辑中实现的计算步骤:还不能发布图像，请在下面找到

​

​

棘手的部分是计算每一对值之间的绝对差之和。给出值的顺序是重要的。我忘了提到Rbar (Row 7)分母中的数字27是样本大小减去1 (27)。下面也是SQL中的一个表：

DECLARE @Measurements TABLE(Val FLOAT)
INSERT INTO @Measurements (Val)(
    SELECT 485
    UNION ALL SELECT 490.6
    UNION ALL SELECT 490.6
    UNION ALL SELECT 485
    UNION ALL SELECT 485
    UNION ALL SELECT 489
    UNION ALL SELECT 485
    UNION ALL SELECT 477
    UNION ALL SELECT 477
    UNION ALL SELECT 484.6
    UNION ALL SELECT 484.6
    UNION ALL SELECT 484.6
    UNION ALL SELECT 484.6
    UNION ALL SELECT 485
    UNION ALL SELECT 484.6
    UNION ALL SELECT 484.6
    UNION ALL SELECT 485
    UNION ALL SELECT 485
    UNION ALL SELECT 477
    UNION ALL SELECT 484.6
    UNION ALL SELECT 484.6
    UNION ALL SELECT 484.6
    UNION ALL SELECT 484.6
    UNION ALL SELECT 484.6
    UNION ALL SELECT 484.6
    UNION ALL SELECT 482
    UNION ALL SELECT 482
    UNION ALL SELECT 482
)
SELECT STDEV(Val) FROM @Measurements 

我猜想我所需要的可以通过在SSRS中使用RollingValue或某些存储过程来实现。当然，如果有一个更聪明的方法计算西格玛或Cpk，我将非常高兴听到它。

我希望这个问题能很好地表达出来，如果不是，请发表意见。这对我来说非常重要，所以我将非常感谢你的帮助:)谢谢！

Answer 1

我觉得这能行吗？不确定27的分母是如何计算的或d2值，所以现在对它们进行了硬编码.

就使用此查询而言，保持行顺序是很重要的，因此，我使用标识列将值清除到临时表中如果您的值来自表，并且有固定的排序条件，则使用row_number() OVER (order by criteria_column)代替。

IF OBJECT_ID('tempdb..#values') IS NOT NULL DROP TABLE #values
CREATE TABLE #values (row INT IDENTITY (1,1),val FLOAT)
INSERT INTO #values
    SELECT 485 as val
    UNION ALL SELECT 490.6 
    UNION ALL SELECT 490.6 
    UNION ALL SELECT 485 
    UNION ALL SELECT 485 
    UNION ALL SELECT 489 
    UNION ALL SELECT 485 
    UNION ALL SELECT 477 
    UNION ALL SELECT 477 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 485 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 485 
    UNION ALL SELECT 485 
    UNION ALL SELECT 477 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 484.6 
    UNION ALL SELECT 482 
    UNION ALL SELECT 482 
    UNION ALL SELECT 482 



;with stdevs AS
(
SELECT 
v1.val
,ABS(v1.val - v2.val) as abs_diff
,v2.val as value2
from #values v1
LEFT OUTER JOIN #values v2
ON v2.row = v1.row + 1
)
SELECT
avg(val) as average_value
,sum(abs_diff) as abs_sum
,sum(abs_diff) / 27 as Rbar
,1.13 as d2
,(sum(abs_diff) / 27) / 1.13 as std_dev
FROM stdevs

Answer 2

您可以根据SUM和COUNT聚合函数计算标准偏差：

STDEVP(x) := SQRT((SUM(x^2) - SUM(x)^2/COUNT(x)) / COUNT(x))
STDEV(x) := SQRT((SUM(x^2) - SUM(x)^2/COUNT(x)) / (COUNT(x) - 1))

不是最精确的方法，但很简单。