TSQL – Понимание агрегатных функций: STDEV STDEVP VAR VARP
Толчком к написанию данной статьи послужила статья
«Variance and Standard Deviation» http://www.bullsbetting.com/index.php/variance-and-standard-deviation
Для того что бы
начать понадобится ввести термин «популяция».
Лучше всего понимаешь его на примере:
Предположим мы
занимаемся фермерским делом и уже сняли урожай помидоров. К сожалению, за весь
год мы сумели вырастить только три помидора. Первый имеет диаметр 11
сантиметров, второй 12 и последний, наша гордость, целых 16 сантиметров.
Далее мы
положили все помидоры в банку и оформляем этикетку, закон требует указать на
этикетке средний диаметр помидора. Путем простых калькуляций, мы вычисляем что
это (11+12+16)/3 = 13 сантиметров. Но по факту у нас нет ни одного помидора с
таким диаметром, зато теперь мы можем рассчитать популяцию для всего нашего
урожая. Рассчитывается разница между каждым помидором и средним помидором, эта
последовательность разниц и есть популяция. В нашем случае это -2, -1, 3.
Теперь рассмотрим функцию VARP, создадим в тестовой базе данных
таблицу Yield и
заполним ее:
CREATE
TABLE Yield
(
Name
nvarchar(50),
Diameter
int
)
GO
INSERT
Yield VALUES ('Tomato',11);
INSERT
Yield VALUES ('Tomato',12);
INSERT
Yield VALUES ('Tomato',16);
Расчет популяции этих трех значений будет: -2,-1,3
Как, используя
популяцию, можно рассчитать среднюю разницу между помидорами? В начале,
напрашивается ответ, сложить все числа популяции и разделить на количество
чисел в популяции. Но особенностью популяции является то, что при сложении всех
чисел результатом будет ноль. Математики поступают следующим образом - возводят в квадрат каждое число в популяции,
и таким образом значение не будет нулем 4+1+9 = 14, затем делим на три 4,666…
но этот результат до сих пор в квадрате, выводим корень из 4,6666 …. = 2,160.. То
есть среднее изменение диаметра от помидора к помидору около 2,160 сантиметра.
Проверяем, соответствуют ли наши вычисления агрегатной функции VARP (Variant) SQL Server:
SELECT Name, VARP(Diameter) as VARP, SQRT(VARP(Diameter)) as Sqrt_From_VARP
FROM Yield
GROUP BY Name
Name VARP Sqrt_From_VARP
--------------------- ---------------------- ----------------------
Tomato 4.66666666666667 2.16024689946929
(1 row(s) affected)
Как видите, результат оказался ожидаемым.
Чем же отличается VARP от VAR? Дело в том, что VAR использует
не всю популяцию для расчетов, а лишь часть, в итоге результат будет менее
точным, но расчет произойдет быстрее.
Разница видна даже на такой маленькой таблице (у вас
могут получиться другие результаты):
SELECT Name, VAR(Diameter) as VAR, SQRT(VAR(Diameter)) as Sqrt_From_VAR
FROM Yield
GROUP BY Name
Name VAR Sqrt_From_VAR
------------------------ ----------------------
----------------------
Tomato 7 2.64575131106459
(1 row(s) affected)
Как видите,
данные примерны, но не далеки от оригинала. VAR быстрее обработает большую популяцию
чем VARP, к тому же,
при работе с большей популяцией у VAR возрастет точность, и погрешность будет не сильно велика.
Теперь фраза из MSDN описывающая агрегатную функцию VARP:
«Возвращает
статистическую дисперсию для заполнения всех значений в указанном выражении»
Становится понятной, и не нагоняет тоску.
:)
Перейдем к агрегатной функции STDEVP, выполним
следующий запрос:
SELECT Name, STDEVP(Diameter) as STDEVP
FROM Yield
GROUP BY Name
Name STDEVP
------------------------
----------------------
Tomato 2.16024689946929
(1
row(s) affected)
Ничего не напоминает? Правильно – результат STDEVP это SQRT(VARP(Diameter))
Несложно догадаться что:
SELECT Name, STDEV(Diameter) as STDEV
FROM Yield
GROUP BY Name
Name STDEV
---------------------
----------------------
Tomato 2.64575131106459
(1
row(s) affected)
STDEV это SQRT(VAR(Diameter))
На этом все,
удачи вам и спасибо что прочитали мою статью. Буду рад оставленным комментариям
и новым постоянным читателям.
Александр Кобелев.
