воскресенье, 17 марта 2013 г.

TSQL – Понимание агрегатных функций: STDEV STDEVP VAR VARP



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))

На этом все, удачи вам и спасибо что прочитали мою статью. Буду рад оставленным комментариям и новым постоянным читателям.
Александр Кобелев.