Блог

У меня серьезная проблема с вычислением стандартного отклонения внутри подгруппы в SSRS / SQl (в зависимости от того, что удобнее). Реальная причина этого в том, что мне нужно вычислить значение сигмы (в соответствии с принципами шести сигм) или, альтернативно, значение Cpk (возможности процесса), но мои усилия останавливаются на стандартном отклонении.

Я не силен в статистике, но кажется, что функции StDev и StDevP в SSRS (и SQL) не вычисляются «внутри подгруппы», и я не могу найти функцию, которая могла бы это сделать. Ниже приведен снимок экрана Excel, показывающий шаги вычисления, которые я хотел бы выполнить в SQL / SSRS Редактировать: Пока не удается опубликовать изображения, см. Ниже

Сложная часть — вычисление суммы абсолютных различий между каждой парой значений. Порядок, в котором задаются значения, имеет значение. Я забыл упомянуть, что число 27 в знаменателе Rbar (строка 7) — это размер выборки минус 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, я буду очень рад услышать об этом.

Я надеюсь, что вопрос хорошо сформулирован, если нет, пожалуйста, прокомментируйте. Это очень важно для меня, поэтому я буду благодарен за любую помощь 🙂 Спасибо!

Я думаю, это должно сработать? Не уверен, как был вычислен знаменатель 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
 

Вы можете рассчитать стандартное отклонение в терминах 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))
 

Не самый точный способ, но это просто.