В части I было показано, как с помощью функций Oracle 11g получить статистические характеристики выборки, а именно:
- центральные тенденции: среднее арифметическое, медиану, моду и середину диапазона,
- характеристики разброса: дисперсию, среднеквадратичное отклонение, межквартильный диапазон и диапазон выборки.
Также с помощью запросов SQL были построены столбцовая диаграмма и гистограмма для номинальных и числовых данных.
Сегодня с помощью функций Oracle поиграем с нормальным распределением.
Нормальное распределение случайной величины встречается очень часто. Так, измерение некоторой стабильной характеристики, проводимое многократно, дает слегка отличающиеся результаты, которые нормально распределены. Результаты отличаются из-за действия различных независимых факторов, связанных с объектом измерения, процессом измерения, окружающими условиями, в которых проводятся измерения.
Гистограмма для такой серии измерений с увеличением объема выборки по форме приближается к "колоколу Гаусса", изображенному на следующем рисунке.
На рисунке представлена кривая плотности вероятности стандартного нормального распределения случайной величины. Как видим, плотность вероятности наибольшая в районе значения 0, отсюда, случайная величина наиболее вероятно примет значение, близкое к 0. Здесь 0 - это математическое ожидание случайной величины, подчиняющейся стандартному нормальному распределению. Практической оценкой мат. ожидания является среднее арифметическое выборки.
Всякое нормальное распределение характеризуется двумя параметрами: мат. ожиданием (далее мю) и среднеквадратичным отклонением (далее сигма).
По определению, у стандартного нормального распределения мю равно 0, а сигма равна 1. Поэтому получается, что значения случайной величины выражены в среднеквадратичных отклонениях (и они же отложены на горизонтальной оси графика).
Над горизонтальной осью на графике приведены проценты, характеризующие долю значений нормально распределенной случайной величины, попадающую в соответствующий интервал. Например, 34.13% значений случайной величины оказываются между -1 и 0, столько же - между 0 и 1. Иными словами, с вероятностью 0.3413 случайная величина примет значение между -1 и 0, с такой же вероятностью - между 0 и 1. А вероятность попадания в интервал от -1 до +1 равна 0.3413 + 0.3413 = 0.6826.
Отметим, что
- приблизительно 68% (34.13% + 34.13%) значений нормально распределенной случайной величины попадают в интервал от -1 до +1 сигмы,
- приблизительно 95% (34.13% + 34.13% + 13.59% + 13.59%) - в интервал от -2 до +2 сигмы,
- приблизительно 99.7% (34.13% + 34.13% + 13.59% + 13.59% + 2.14 + 2.14) - в интервал от -3 до +3 сигмы.
Любую нормально распределенную выборку можно привести к стандартно распределенной, отняв от каждого ее значения среднее арифметическое выборки и поделив на среднеквадратичное отклонение. (И наоборот - из стандартной нормальной выборки можно получить нестандартную нормальную, что мы проделаем ниже.)
Функция dbms_random.normal генерирует случайные значения, подчиняющиеся стандартному нормальному распределению:
SQL> select dbms_random.normal()
2 from dual
3 connect by level <= 100;
DBMS_RANDOM.NORMAL()
--------------------
0,154593023631174
1,02262223920391
-0,734122534869299
-0,863172348347507
1,45664782296563
-0,796529476603732
-0,867107600542802
-0,394780344173645
1,05769871760089
0,842462758098468
...
-0,0456642918696273
0,614956851766163
0,204468551347878
1,08128380697934
-0,989381640270147
1,46646798417473
-1,82846979569939
0,163847181182817
0,641707521929584
-1,95800793325413
100 rows selected
Проверим, что с ростом объема выборки среднее арифметическое стремится к нулю, а среднеквадратичное отклонение - к единице:
SQL> select avg(val), stddev(val)
2 from (
3 select dbms_random.normal() val
4 from dual
5 connect by level <= 100
6 );
AVG(VAL) STDDEV(VAL)
---------- -----------
-0,1636936 1,002839389
SQL> select avg(val), stddev(val)
2 from (
3 select dbms_random.normal() val
4 from dual
5 connect by level <= 1000
6 );
AVG(VAL) STDDEV(VAL)
---------- -----------
-0,0201765 1,026325964
SQL> select avg(val), stddev(val)
2 from (
3 select dbms_random.normal() val
4 from dual
5 connect by level <= 10000
6 );
AVG(VAL) STDDEV(VAL)
---------- -----------
-0,0033332 0,989012833
С помощью dbms_random.normal несложно получить нормально распределенную выборку с нужными нам мю и сигмой - то есть, перейти от стандартного нормального распределения к нормальному распределению с заданными мю и сигма.
Пусть мю будет 2.71, а сигма 3.14, тогда:
SQL> select dbms_random.normal()*3.14 + 2.71
2 from dual
3 connect by level <= 100;
DBMS_RANDOM.NORMAL()*3.14+2.71
------------------------------
6,75187826459014
1,86052717025083
-0,900891219094111
2,85496791999616
0,433334646731169
0,265590122983462
5,4512101466645
-1,25021864671862
-3,85845106614343
7,14423917638489
...
5,97610801535289
6,40672487648576
4,87697054970031
0,859017420951321
9,12860629346482
0,883519589468616
-1,15145423540538
4,24633726097418
8,72321075719189
1,10411678811017
100 rows selected
Получим среднее арифметическое и среднеквадратичное отклонение такой выборки:
SQL> select avg(val), stddev(val)
2 from (
3 select dbms_random.normal()*3.14 + 2.71 val
4 from dual
5 connect by level <= 10000
6 );
AVG(VAL) STDDEV(VAL)
---------- -----------
2,77654422 3,107572254
Среднее арифметическое и медиана нормально распределенных данных в случае большой выборки почти совпадают (а при бесконечном увеличении выборки они станут равны):
SQL> select avg(val), median(val)
2 from (
3 select dbms_random.normal()*3.14 + 2.71 val
4 from dual
5 connect by level <= 10000
6 );
AVG(VAL) MEDIAN(VAL)
---------- -----------
2,75573819 2,723043904
Из приведенного выше рисунка с "колоколом" видно, что значения мю плюс/минус 1 сигма, мю плюс/минус 2 сигма, мю плюс/минус 3 сигма можно выразить в процентах. Значит, можно получить процентили, соответствующие одной, двум и трем сигмам с каждой стороны от медианы:
SQL> select
2 percentile_cont(0.5 - 0.3413 - 0.1359 - 0.0214) within group (order by val) "-3sigma",
3 percentile_cont(0.5 - 0.3413 - 0.1359) within group (order by val) "-2sigma",
4 percentile_cont(0.5 - 0.3413) within group (order by val) "-1sigma",
5 percentile_cont(0.5) within group (order by val) "median",
6 percentile_cont(0.5 + 0.3413) within group (order by val) "+1sigma",
7 percentile_cont(0.5 + 0.3413 + 0.1359) within group (order by val) "+2sigma",
8 percentile_cont(0.5 + 0.3413 + 0.1359 + 0.0214) within group (order by val) "+3sigma"
9 from (select dbms_random.normal()*3.14 + 2.71 val from dual connect by level <= 10000)
10 ;
-3sigma -2sigma -1sigma median +1sigma +2sigma +3sigma
---------- ---------- ---------- ---------- ---------- ---------- ----------
-6,5676516 -3,6453956 -0,4723526 2,72038572 5,87491290 9,04764184 11,5276827
Эти процентили должны быть приближенно равны медиане плюc/минус среднеквадратичное отклонение с соответствующим множителем. Проверим это:
SQL> with nd as (
2 select dbms_random.normal()*3.14 + 2.71 val from dual connect by level <= 10000
3 ), ag as (
4 select median(val) median, stddev(val) stddev from nd
5 )
6 select 'perc',
7 percentile_cont(0.5 - 0.3413 - 0.1359 - 0.0214) within group (order by val) "-3sigma",
8 percentile_cont(0.5 - 0.3413 - 0.1359) within group (order by val) "-2sigma",
9 percentile_cont(0.5 - 0.3413) within group (order by val) "-1sigma",
10 percentile_cont(0.5) within group (order by val) "median",
11 percentile_cont(0.5 + 0.3413) within group (order by val) "+1sigma",
12 percentile_cont(0.5 + 0.3413 + 0.1359) within group (order by val) "+2sigma",
13 percentile_cont(0.5 + 0.3413 + 0.1359 + 0.0214) within group (order by val) "+3sigma"
14 from nd
15 union
16 select 'sigma',
17 ag.median - 3*ag.stddev,
18 ag.median - 2*ag.stddev,
19 ag.median - ag.stddev,
20 ag.median "MEDIAN",
21 ag.median + ag.stddev,
22 ag.median + 2*ag.stddev,
23 ag.median + 3*ag.stddev
24 from ag
25 ;
'PERC' -3sigma -2sigma -1sigma median +1sigma +2sigma +3sigma
------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
perc -6,8818008 -3,5456558 -0,5132840 2,68107497 5,88283178 8,97908017 11,8164862
sigma -6,8211555 -3,6537453 -0,4863352 2,68107497 5,84848516 9,01589535 12,1833055
Результат выглядит ожидаемо.
Для стандартного нормального распределения давно рассчитаны и сведены в таблицы вероятности того, что значение случайной величины окажется меньше или равно данному значению сигма. Есть несколько разновидностей таких таблиц (см., например, статью в Википедии), и одна из них содержит значения функции кумулятивного распределения вероятности (КФР, или CDF) для стандартного нормального распределения.
Вот график КФР для стандартного нормального распределения, соответствующий графику плотности вероятности, приведенному выше:
В Oracle 11g функция cume_dist строит кумулятивное распределение вероятности для произвольной выборки и возвращает вероятность того, что случайная величина меньше или равна переданному аргументу.
Для стандартного нормального распределения вероятность получить значение меньше или равное 0 равна, очевидно, 0.5. Это, кстати, видно и из графика КФР. А вероятность того, что значение будет меньше -3 сигма (или больше 3 сигма) равна примерно 0.5 - 0.3413 - 0.1359 - 0.0214 = 0.0014. (Числа для расчета взяты из графика плотности вероятности, приведенного ранее.)
Получим значения КФР для аргументов -3, 0 и 3:
SQL> select
2 cume_dist(-3) within group (order by val) "-3sigma",
3 cume_dist(0) within group (order by val) "mean",
4 cume_dist(3) within group (order by val) "+3sigma"
5 from (select dbms_random.normal() val from dual connect by level <= 10000)
6 ;
-3sigma mean +3sigma
---------- ---------- ----------
0,00149985 0,50574942 0,99870012
Судя по тому, что результат соответствует теоретическим предсказаниям, функция dbms_random.normal неплохо справляется с генерацией нормально распределенных значений.
Функция cume_dist может работать с различными распределениями. Вот примеры работы с константным и равномерным распределениями:
SQL> select
2 cume_dist(0.25) within group (order by val) "0.25",
3 cume_dist(0.5) within group (order by val) "0.5",
4 cume_dist(0.75) within group (order by val) "0.75"
5 from (select 3.14 val from dual connect by level <= 10000)
6 ;
0.25 0.5 0.75
---------- ---------- ----------
9,99900009 9,99900009 9,99900009
SQL> select
2 cume_dist(0.25) within group (order by val) "0.25",
3 cume_dist(0.5) within group (order by val) "0.5",
4 cume_dist(0.75) within group (order by val) "0.75"
5 from (select dbms_random.value val from dual connect by level <= 10000)
6 ;
0.25 0.5 0.75
---------- ---------- ----------
0,24487551 0,49955004 0,74582541
В последнем запросе с помощью функции dbms_random.value мы получили равномерное распределение случайной величины в диапазоне от 0 до 1. При этом вероятность получить значение, меньшее или равное некоторому значению из этого же диапазона, приблизительно равна самому этому значению, как и следовало ожидать.
Для получения равномерного распределения в произвольном диапазоне умножим значение dbms_random.value на ширину диапазона и прибавим нижнюю границу диапазона. При этом функция распределения вероятностей сохраняет свою форму. Вот пример для равномерного распределения в диапазоне от 100 до 200:
SQL> select
2 cume_dist(125) within group (order by val) "125",
3 cume_dist(150) within group (order by val) "150",
4 cume_dist(175) within group (order by val) "175"
5 from (select dbms_random.value*100 + 100 val from dual connect by level <= 10000)
6 ;
125 150 175
---------- ---------- ----------
0,24767523 0,49115088 0,74722527
Подведу итог сегодняшним упражнениям.
Итак, мы рассмотрели, как в Oracle 11g получить выборки значений случайной величины для стандартного нормального распределения, нормального распределения с заданными параметрами, константного и равномерного распределений. Полученные выборки были исследованы с помощью статистических функций Oracle, чтобы проверить свойства соответствующих распределений.
На этом тема статистических функций в Oracle 11g не исчерпана, - продолжение следует.
Комментариев нет:
Отправить комментарий