Среди функций агрегации в SQL Server присутствуют STDEV, STDEVP, VAR и VARP, которые могут вызвать вопросы, что это такое, как и когда их применять. Для начала я приведу формулы, по которым идем расчет указанных функций. Функции взяты из математической статистики.

STDEV возвращает статистическое среднеквадратическое отклонение всех значений в указанном выражении.

 — дисперсия;  — i-й элемент выборки;  — объём выборки;  — среднее арифметическое выборки:

STDEVP возвращает статистическое среднеквадратичное отклонение совокупности всех значений в указанном выражении.

VAR возвращает статистическую дисперсию всех значений в указанном выражении. Значение равно (квадрат значения, возвращаемого функцией STDEV).

VARP Возвращает статистическую дисперсию для заполнения всех значений в указанном выражении (квадрат значения, возвращаемого функцией STDEVP).

Т.к. функции VAR и VARP фактически дублируют STDEV и STDEVP, то сосредоточимся только на последних. Перепишем формулы их вычисления в чуть более понятный на мой взгляд вид.

Stdevp = sqrt( ((x1-xmean)^2 + (x2-xmean)^2 + … + (xn-xmean)^2)/n )

Stdev = sqrt( ((x1-xmean)^2 + (x2-xmean)^2 + … + (xn-xmean)^2)/(n-1) )

По сути эти функции показывают насколько в среднем значения отличаются от арифметического среднего. Теперь давайте рассмотрим небольшой пример их применения. Возьмем простую таблицу со столбцом типа int, 3мя записями и рассчитаем для них значения функций.

USE [tempdb];
GO

IF OBJECT_ID('tempdb.dbo.test', 'U') IS NOT NULL
    DROP TABLE [dbo].[test];
GO

CREATE TABLE [dbo].[test] (
    [d] [int] NOT NULL
);
GO

INSERT INTO [dbo].[test] ([d]) VALUES (1), (2), (3);
GO

SELECT
    AVG([d]) AS [avg]
    ,STDEV([d]) AS [stdev]
    ,STDEVP([d]) AS [stdevp]
FROM [dbo].[test];
GO

Значение STDEV на небольших выборках, как мы видим может быть более точным, нежели STDEVP (по сути исключается из расчетов одно значение, которое либо само является средним, либо близко к среднему). Но на больших выборках они могут быть очень близки.

Т.е. эти функции удобно применять, когда требуется вычислить все значения в таблице, которые сильно отклоняются от среднего. Есть даже такое правило трех сигм (), которое утверждает, что практически все значения выборки будут лежать в диапазоне 3х сигм от среднего.

Приведу еще один пример, когда с помощью этих функций можно выводить значения в таблице, которые сильно отклоняются от среднего.

USE [tempdb];
GO

IF OBJECT_ID(‘tempdb.dbo.test’, ‘U’) IS NOT NULL
    DROP TABLE [dbo].[test];
GO

CREATE TABLE [dbo].[test] (
    [d] [int] NOT NULL
);
GO

INSERT INTO [dbo].[test] ([d]) VALUES (1), (2), (1), (1), (0), (1), (1), (2), (1), (1), (15);
GO

SELECT
    [d]
    ,CASE WHEN ABS([d] - AVG([d]) OVER() ) > 3 * STDEV([d]) OVER() THEN 1 ELSE 0 END IsLargeThan3Sigma
FROM [dbo].[test];
GO