SQL 或 SSRS 中组内的标准差（或者 - 计算 Sigma 或 Cpk）

Question

我在 SSRS/SQl 中计算子组内的标准偏差时遇到严重问题（以更方便的为准）。这背后的真正原因是我需要计算 Sigma 值（根据六西格码原则）或 Cpk 值（过程能力），但我的努力停留在标准差上。

我不擅长统计，但似乎SSRS（和SQL）中的StDev和StDevP函数不是在“子组内”计算的，而且我找不到一个函数可以做到这一点。下面是一个 Excel 屏幕截图，显示了我想在 SQL/SSRS 中实现的计算步骤 编辑：尚无法发布图像，请在下面查找

棘手的部分是计算每对值之间的绝对差之和。给出值的顺序很重要。我忘了提及 Rbar 分母（第 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 

我猜我需要的可以通过使用 RollingValue 或 SSRS 中的某些存储过程来实现。当然，如果有更智能的方法来计算 Sigma 或 Cpk，我会很高兴听到它。

我希望这个问题得到很好的阐述，如果没有，请发表评论。这对我来说非常重要，所以我将不胜感激任何帮助:) 谢谢！

Answer 1

我认为这应该能解决问题？不确定 27 的分母或 d2 值是如何计算的，因此现在已将它们硬编码...

在使用此查询方面，保留行顺序很重要，因此我已将这些值放入临时表中使用标识列 - 如果您的值来自表并且有固定的排序条件，请改用 row_number() OVER（按 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))

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