Среди функций агрегации в 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 |
