Получение описательных статистических показателей

Задача
Вы хотите охарактеризовать набор данных, используя общие описательные или сводные статистические показатели.

Решение
Многие общие описательные статистические показатели множества, такие как среднее и стандартное отклонение, можно получить, примененяя к данным агрегирующие функции.

Обсуждение
Предположим, что у вас есть таблица testscore с результатами тестирования: идентификатор испытуемого (subject), возраст (age), пол (sex) и количество баллов (score):

mysql> SELECT subject, age, sex, score FROM testscore ORDER BY subject;

+--------+-----+-----+-------+
| subject | age | sex | score |
+--------+-----+-----+-------+
| 1 | 5 | M | 5 |
| 2 | 5 | M | 4 |
| 3 | 5 | F | 6 |
| 4 | 5 | F | 7 |
| 5 | 6 | M | 8 |
| 6 | 6 | M | 9 |
| 7 | 6 | F | 4 |
| 8 | 6 | F | 6 |
| 9 | 7 | M | 8 |
| 10 | 7 | M | 6 |
| 11 | 7 | F | 9 |
| 12 | 7 | F | 7 |
| 13 | 8 | M | 9 |
| 14 | 8 | M | 6 |
| 15 | 8 | F | 7 |
| 16 | 8 | F | 10 |
| 17 | 9 | M | 9 |
| 18 | 9 | M | 7 |
| 19 | 9 | F | 10 |
| 20 | 9 | F | 9 |
+--------+-----+-----+-------+

Анализ набора наблюдений разумно начать с получения некоторых описательных статистических показателей, обобщающих характеристики данных.


Подобные статистические показатели включают:

• Количество наблюдений, сумма баллов и диапазон (минимум и максимум).

• Параметры сдвига (central tendency), такие как среднее значение (mean), медиана и мода.

• Параметры вариации, такие как стандартная девиация или дисперсия (variance).

Все эти величины, за исключением медианы и моды, могут быть получены путем вызова агрегирующих функций:

mysql> SELECT COUNT(score) AS n,
-> SUM(score) AS sum,
-> MIN(score) AS minimum,
-> MAX(score) AS maximum,
-> AVG(score) AS mean,
-> STD(score) AS 'std. dev.'
-> FROM testscore;

+---+------+---------+---------+--------+-----------+
| n | sum | minimum | maximum | mean | std. dev. |
+----+------+---------+---------+--------+-----------+
| 20 | 146 | 4 | 10 | 7.3000 | 1.7916 |
+---+------+---------+---------+--------+-----------+

Агрегирующие функции в запросе обрабатывают только значения не-NULL наблюдений. Если вы используете NULL для представления отсутствующих значений, то можете захотеть получить дополнительно оценку того, как много значений отсутствует.

Запрос не затрагивает дисперсию, в MySQL нет функции для ее вычисления.


Но дисперсия – это просто квадрат стандартной девиации, так что ее легко получить так:

STD(score) * STD(score)
STDDEV() – это синоним STD().

Стандартную девиацию можно использовать для идентификации выбросов (outliers) – значений, которые расположены нехарактерно далеко от среднего.

Например, чтобы выбрать значения, которые отделены от среднего более чем тремя стандартными девиациями, сделаем что-то типа:

SELECT @mean := AVG(score), @std := STD(score) FROM testscore;
SELECT score FROM testscore WHERE ABS(score-@mean) > @std * 3;

Для множества из n значений стандартная девиация, выдаваемая STD(), вычисляется для n степеней свободы. Это эквивалентно такому вычислению стандартной девиации без использования агрегирующей функции (@ss представляет сумму квадратов):

mysql> SELECT
-> @n := COUNT(score),
-> @sum := SUM(score),
-> @ss := SUM(score*score)
-> FROM testscore;
mysql> SELECT @var := ((@n * @ss) - (@sum * @sum)) / (@n * @n);
mysql> SELECT SQRT(@var);

+---------------+
| SQRT(@var) |
+--------------+
| 1.791647 |
+--------------+

Чтобы получить стандартную девиацию для n–1 степеней свободы, сделайте следующее:

mysql> SELECT
-> @n := COUNT(score),
-> @sum := SUM(score),
-> @ss := SUM(score*score)
-> FROM testscore;
mysql> SELECT @var := ((@n * @ss) - (@sum * @sum)) / (@n * (@n - 1));
mysql> SELECT SQRT(@var);

+---------------+
| SQRT(@var) |
+--------------+
| 1.838191 |
+--------------+

Или, что проще:

mysql> SELECT @n := COUNT(score) FROM testscore;
mysql> SELECT STD(score)*SQRT(@n/(@n-1)) FROM testscore;

+------------------------------------+
| STD(score)*SQRT(@n/(@n-1)) |
+------------------------------------+
| 1.838191 |
+------------------------------------+

В MySQL нет встроенных функций для вычисления моды и медианы набора данных, но вы можете получить их самостоятельно.


Мода – это наиболее часто встречающееся значение. Чтобы выявить его, подсчитаем количество вхождений в множество каждого значения и посмотрим, какое из них самое большое:

mysql> SELECT score, COUNT(score) AS count
-> FROM testscore GROUP BY score ORDER BY count DESC;

+------+-------+
| score | count |
+------+-------+
| 9 | 5 |
| 6 | 4 |
| 7 | 4 |
| 4 | 2 |
| 8 | 2 |
| 10 | 2 |
| 5 | 1 |
+------+-------+

В данном случае модальным значением является 9.

Медиана множества упорядоченных значения вычисляется так:

• Если значений нечетное количество, то медиана – это центральное значение.

• Если значений четное количество, то медиана – это среднее двух центральных значений множества.

На основе этого определения вычислим медиану множества наблюдений, хранящихся в базе данных:

• Создаем запрос для подсчета количества наблюдений. По полученному счетчику определяем, одно или два значения будут использованы для вычисления медианы, и каковы их индексы в упорядоченном множестве наблюдений.

• Создаем запрос с инструкцией ORDER BY для упорядочивания наблюдений и инструкцией LIMIT для извлечения центрального значения (значений).

• Вычисляем среднее для выбранного значения или значений.

Например, если таблица t содержит столбец score с 37 значениями (нечетное количество), необходимо выбрать одно значение, используя такой запрос:

SELECT score FROM t ORDER BY 1 LIMIT 18,1

Если столбец содержит 38 значений (четное количество), запрос будет таким:

SELECT score FROM t ORDER BY 1 LIMIT 18,2

Теперь выбираем значение или значения, возвращенные запросом, и вычисляем медиану как их среднее.

Приведем сценарий на Perl, реализующий вычисление медианы множества.


Он принимает дескриптор базы данных и имена таблицы и столбца, содержащих наблюдения, затем генерирует запрос, извлекающий соответствующие значения, и возвращает их среднее:

sub median
{
my ($dbh, $tbl_name, $col_name) = @_;
my ($count, $limit);
$count = $dbh->selectrow_array ("SELECT COUNT($col_name) FROM $tbl_name");
return undef unless $count > 0;
if ($count % 2 == 1) # нечетное количество значений,
# вернуть центральное значение
{
$limit = sprintf ("LIMIT %d,1", ($count-1)/2);
}
else # четное количество значений, вернуть два центральных значения
{$limit = sprintf ("LIMIT %d,2", $count/2 - 1);
}
my $sth = $dbh->prepare (
"SELECT $col_name FROM $tbl_name ORDER BY 1 $limit");
$sth->execute ();
my ($n, $sum) = (0, 0);
while (my $ref = $sth->fetchrow_arrayref ())
{
++$n;
$sum += $ref->[0];
}
return ($sum / $n);
}

Этот прием работает для множества значений, хранящихся в базе данных.

Если же вы уже выбрали упорядоченное множество данных в массив @val, то медиану можно вычислить по-другому:

if (@val == 0) # если массив пуст, то медиана не определена
{
$median = undef;
}
elsif (@val % 2 == 1) # если количество элементов массива нечетное,
# медиана – это его центральный элемент
{
$median = $val[(@val-1)/2];
}
else # если количество элементов массива четное, медиана – это
# среднее значение двух его центральных элементов
$median = ($val[@val/2 - 1] + $val[@val/2]) / 2;
}

Код написан для массивов, нумерация элементов в которых начинается с 0.


Если же вы используете язык с индексом массивов, начинающимся с 1, то подкорректируйте алгоритм соответствующим образом.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Вычисление линейной регрессии и коэффициентов корреляции
Генерация случайных чисел
Групповые описательные статистические показатели
Подсчет отсутствующих значений
Получение частотного распределения

Вернуться в раздел: MySQL / 13. Статистические методы