Рассмотрим на примерах возможности статистического анализа и статистические функции в Oracle 11g.
Статистика имеет дело с наборами измерений некоторой величины. Например,
- возраст (рост, зарплата, цвет глаз) работников компании,
- суммы, потраченные вами в последние 20 визитов в супермаркет,
- времена отклика, полученные сотней
ping'ов адреса google.com с вашего компьютера.
В первом случае объем выборки равен числу работников компании, во втором равен 20-ти, в третьем - 100. Для дальнейших экспериментов я буду использовать две выборки: цвет глаз и время сетевого отклика, в миллисекундах. Цвет глаз является номинальной дискретной величиной, тогда как время отклика есть числовая непрерывная величина.
Я поместил выборки в таблицы sts_eyec и sts_ping. Вот они:
SQL> select val from sts_eyec;
VAL
----------------------------------------
grey
brown
blue
amber
brown
blue
amber
amber
green
amber
green
brown
amber
green
grey
brown
amber
amber
grey
green
20 rows selected
SQL> select val from sts_ping;
VAL
----------
120
125
120
124
125
121
128
121
124
124
120
124
121
120
129
124
124
121
125
129
20 rows selected
Прежде всего, исследуем центральные тенденции выборок. Наиболее известные из центральных тенденций
- среднее арифметическое,
- медиана,
- мода.
Поскольку цвет глаз есть величина номинальная, неупорядоченная, то нельзя получить для нее среднее арифметическое или медиану. Хотя можно попытаться:
SQL> select avg(val) "AVG" from sts_eyec;
select avg(val) "AVG" from sts_eyec
ORA-01722: invalid number
SQL> select median(val) "MEDIAN" from sts_eyec;
select median(val) "MEDIAN" from sts_eyec
ORA-30495: The argument should be of numeric or date/datetime type.
Обратите внимание на последнее сообщение об ошибке: для выборки дат Oracle найдет медиану, ведь даты, в отличие от цвета глаз, можно упорядочить и отыскать середину упорядоченной последовательности.
Из центральных тенденций для номинальной величины можно получить моду - значение, которое встречается в выборке чаще других:
SQL> select stats_mode(val) "MODE"
2 from sts_eyec;
MODE
----------------------------------------
amber
Для числовой выборки в sts_ping найдем все три характеристики:
SQL> select avg(val) "AVG", median(val) "MEDIAN", stats_mode(val) "MODE"
2 from sts_ping;
AVG MEDIAN MODE
---------- ---------- ----------
123,45 124 124
Проверю, что функция avg действительно возвращает среднее арифметическое выборки:
SQL> select sum(val)/count(val) from sts_ping;
SUM(VAL)/COUNT(VAL)
-------------------
123,45
Проверю, что функция median действительно возвращает середину упорядоченной выборки:
SQL> select rownum, val
2 from (select val from sts_ping order by 1);
ROWNUM VAL
---------- ----------
1 120
2 120
3 120
4 120
5 121
6 121
7 121
8 121
9 124
10 124
11 124
12 124
13 124
14 124
15 125
16 125
17 125
18 128
19 129
20 129
20 rows selected
Поскольку количество элементов в выборке четное, то медиана равна среднему арифметическому двух центральных элементов последовательности, а именно элементов с rownum 10 и 11: 124 и 124.
Проверю, что функция stats_mode действительно возвращает наиболее часто встречающееся значение:
SQL> select val, count(*) from sts_ping group by val order by 2 desc;
VAL COUNT(*)
---------- ----------
124 6
120 4
121 4
125 3
129 2
128 1
6 rows selected
И для цвета глаз:
SQL> select val, count(*) from sts_eyec group by val order by 2 desc;
VAL COUNT(*)
---------------------------------------- ----------
amber 7
green 4
brown 4
grey 3
blue 2
В нашей выборке янтарные глаза встречаются в три с половиной раза чаще, чем голубые.
Получим еще одну меру центральной тенденции - середину диапазона (midrange) - и парную ей характеристику разброса - диапазон выборки (range):
SQL> select min(val) "MIN",
2 max(val) "MAX",
3 max(val) - min(val) "RANGE",
4 (max(val) + min(val))/2 "MIDRANGE"
5 from sts_ping;
MIN MAX RANGE MIDRANGE
---------- ---------- ---------- ----------
120 129 9 124,5
Кроме диапазона, наиболее известные из характеристик разброса выборки следующие:
- межквартильный диапазон (interquartile range) - диапазон между 1-м и 3-м квартилями, в который попадает 50% всех значений выборки,
- дисперсия (variance) - среднее значение квадратов разностей элементов выборки и среднего арифметического выборки,
- среднеквадратичное отклонение (standard deviation) - квадратный корень из дисперсии.
Межквартильный диапазон работает в паре с медианой, дисперсия и среднеквадратичное отклонение - со средним арифметическим.
Перечисленные характеристики применимы только к числовым выборкам. А для исследования распределения значений в номинальной выборке нужно построить таблицу частотности (или столбцовую диаграмму, чтобы представить информацию о частоте встречаемости каждого значения графически). Отложим это на потом, а пока будем работать с характеристиками разброса числовой выборки.
Чтобы подступиться к межквартильному диапазону, вначале получим квартили нашей выборки с помощью функции percentile_cont:
SQL> select
2 min(val) "MIN",
3 percentile_cont(0) within group (order by val) "Q0",
4 percentile_cont(0.25) within group (order by val) "Q1",
5 percentile_cont(0.5) within group (order by val) "Q2",
6 median(val) "MEDIAN",
7 percentile_cont(0.75) within group (order by val) "Q3",
8 percentile_cont(1) within group (order by val) "Q4",
9 max(val) "MAX"
10 from sts_ping
11 ;
MIN Q0 Q1 Q2 MEDIAN Q3 Q4 MAX
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
120 120 121 124 124 125 129 129
1-й, 2-й, 3-й и 4-й квартили есть 25-й, 50-й, 75-й и 100-й процентили, соответственно. Запрос демонстрирует, что медиана ожидаемо равна 50-му процентилю (или 2-му квартилю), а минимальное и максимальное значения - 0-му и 100-му процентилям, соответственно.
Получим межквартильный диапазон как разность 3-го и 1-го квартилей:
SQL> select percentile_cont(0.75) within group (order by val) - percentile_cont(0.25) within group (order by val) "IQR"
2 from sts_ping;
IQR
----------
4
Функция percentile_cont интерполирует результат, исходя из того, что выборка непрерывна, поэтому ее результатом может быть значение, отсутствующее в выборке. В отличие от этого, функция percentile_disc всегда возвращает значение, присутствующее в выборке, не прибегая к линейной регрессии. Сравните результаты:
SQL> select 'cont' type,
2 percentile_cont(0) within group (order by val) p0,
3 percentile_cont(0.1) within group (order by val) p10,
4 percentile_cont(0.2) within group (order by val) p20,
5 percentile_cont(0.3) within group (order by val) p30,
6 percentile_cont(0.4) within group (order by val) p40,
7 percentile_cont(0.5) within group (order by val) p50,
8 percentile_cont(0.6) within group (order by val) p60,
9 percentile_cont(0.7) within group (order by val) p70,
10 percentile_cont(0.8) within group (order by val) p80,
11 percentile_cont(0.9) within group (order by val) p90,
12 percentile_cont(1.0) within group (order by val) p100
13 from sts_ping
14 union all
15 select 'disc',
16 percentile_disc(0) within group (order by val),
17 percentile_disc(0.1) within group (order by val),
18 percentile_disc(0.2) within group (order by val),
19 percentile_disc(0.3) within group (order by val),
20 percentile_disc(0.4) within group (order by val),
21 percentile_disc(0.5) within group (order by val),
22 percentile_disc(0.6) within group (order by val),
23 percentile_disc(0.7) within group (order by val),
24 percentile_disc(0.8) within group (order by val),
25 percentile_disc(0.9) within group (order by val),
26 percentile_disc(1.0) within group (order by val)
27 from sts_ping
28 ;
TYPE P0 P10 P20 P30 P40 P50 P60 P70 P80 P90 P100
---- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
cont 120 120 120,8 121 122,8 124 124 124,3 125 128,1 129
disc 120 120 120 121 121 124 124 124 125 128 129
Теперь получим дисперсию и среднеквадратичное отклонение. В Oracle имеются следующие функции для этой цели:
var_pop | дисперсия генеральной совокупности (population) |
var_samp | дисперсия выборки (sample) |
variance | дисперсия выборки, то же, что var_samp |
stddev_pop | среднеквадратичное отклонение генеральной совокупности |
stddev_samp | среднеквадратичное отклонение выборки |
stddev | среднеквадратичное отклонение выборки, то же, что stddev_samp |
Используем их все в следующем запросе:
SQL> select var_pop(val) var_pop,
2 var_samp(val) var_samp,
3 variance(val) variance,
4 stddev_pop(val) stddev_pop,
5 stddev_samp(val) stddev_samp,
6 stddev(val) stddev
7 from sts_ping;
VAR_POP VAR_SAMP VARIANCE STDDEV_POP STDDEV_SAMP STDDEV
---------- ---------- ---------- ---------- ----------- ----------
8,1475 8,57631578 8,57631578 2,85438259 2,928534751 2,92853475
Рассчитаю дисперсию и среднеквадратичное отклонение по известным формулам. Сравните результат с результатом предыдущего запроса:
SQL> select sum(power((val - agg.mean), 2)) / count(*) var_pop,
2 sum(power((val - agg.mean), 2)) / (count(*) - 1) var_samp,
3 sqrt(sum(power((val - agg.mean), 2)) / count(*)) stddev_pop,
4 sqrt(sum(power((val - agg.mean), 2)) / (count(*) - 1)) stddev_samp
5 from sts_ping,
6 (select avg(val) mean from sts_ping) agg;
VAR_POP VAR_SAMP STDDEV_POP STDDEV_SAMP
---------- ---------- ---------- -----------
8,1475 8,57631578 2,85438259 2,928534751
Проверю, что среднеквадратичное отклонение равно квадратному корню из дисперсии:
SQL> select stddev(val) stddev,
2 sqrt(variance(val)) sqrt_var
3 from sts_ping;
STDDEV SQRT_VAR
---------- ----------
2,92853475 2,92853475
Выше я упомянул таблицу частотности и столбцовую диаграмму в связи с анализом дискретной выборки. Чтобы получить таблицу частотности, достаточно подсчитать количество каждого из дискретных значений в выборке:
SQL> select val "Eye Color",
2 count(*) "Count"
3 from sts_eyec
4 group by val;
Eye Color Count
---------------------------------------- ----------
green 4
grey 3
brown 4
blue 2
amber 7
Несложно получить подобие столбцовой диаграммы (лежащей на боку):
SQL> select val "Eye Color",
2 rpad('#', count(val), '#') "Count"
3 from sts_eyec
4 group by val;
Eye Color Count
---------------------------------------- --------------------------------------------------------------------------------
green ####
grey ###
brown ####
blue ##
amber #######
Если считать выборку репрезентативной, то интересно узнать вероятность для каждого цвета глаз в популяции:
SQL> select val "Eye Color",
2 count(*) / (select count(*) from sts_eyec) "Probability"
3 from sts_eyec
4 group by rollup(val);
Eye Color Probability
---------------------------------------- -----------
amber 0,35
blue 0,1
brown 0,2
green 0,2
grey 0,15
1
6 rows selected
Как видим, вероятность встретить янтарные глаза наибольшая, а голубые - наименьшая. Сумма вероятностей ожидаемо равна 1.
Теперь попробуем "нарисовать" гистограмму для числовой выборки из sts_ping (лежащую на боку, как и столбцовая диаграмма раньше). Разобьем диапазон значений в выборке на 5 интервалов. Сначала определим границы (b1 и b2) и середины интервалов (mid):
SQL> select
2 minval + (lvl - 1)*width b1,
3 minval + lvl*width b2,
4 (minval + (lvl - 1)*width + minval + lvl*width)/2 mid
5 from
6 (select min(val) minval, (max(val) - min(val)) / 5 width from sts_ping) agg,
7 (select level lvl from dual connect by level < 6) five
8 ;
B1 B2 MID
---------- ---------- ----------
120 121,8 120,9
121,8 123,6 122,7
123,6 125,4 124,5
125,4 127,2 126,3
127,2 129 128,1
Теперь соединим выборку с рассчитанными интервалами и сгруппируем значения выборки по интервалам:
SQL> with intervals as (
2 select
3 minval + (lvl - 1)*width b1,
4 minval + lvl*width b2,
5 (minval + (lvl - 1)*width + minval + lvl*width)/2 mid
6 from
7 (select min(val) minval, (max(val) - min(val)) / 5 width from sts_ping) agg,
8 (select level lvl from dual connect by level < 6) five
9 )
10 select b1, b2, mid,
11 rpad('#', count(val), '#') "Count"
12 from sts_ping right outer join intervals on val between b1 and b2
13 group by b1, b2, mid
14 order by mid
15 ;
B1 B2 MID Count
---------- ---------- ---------- --------------------------------------------------------------------------------
120 121,8 120,9 ########
121,8 123,6 122,7
123,6 125,4 124,5 #########
125,4 127,2 126,3
127,2 129 128,1 ###
Приведенные примеры не исчерпывают тему статистических функций в Oracle 11g. В СУБД имеются функции для исследования корреляции, выполнения регрессии, статистических тестов. Расскажу о них позднее.
Комментариев нет:
Отправить комментарий