MySQL / 13. Статистические методы

Вычисление линейной регрессии и коэффициентов корреляции

Задача
Вы хотите вычислить для двух переменных регрессию методом наименьших квадратов или коэффициент корреляции, который выражает отношение между ними.

Решение
Используйте агрегирующие функции.

Обсуждение
Если значения данных для двух переменных X и Y хранятся в базе данных, для них можно без труда получить регрессию методом наименьших квадратов, используя агрегирующие функции. Это же касается и коэффициента корреляции. На самом деле эти два вычисления во многом похожи, и для их получения требуется предварительный расчет одних и тех же величин.

Предположим, вы хотите вычислить регрессию методом наименьших квадратов, используя значения возраста и количество баллов, полученных при тестировании, из таблицы testscore:

mysql> SELECT age, score FROM testscore;

+----+-------+
| age | score |
+----+-------+
| 5     | 5       |
| 5     | 4       |
| 5     | 6       |
| 5     | 7       |
| 6     | 8       |
| 6     | 9       |
| 6     | 4       |
| 6     | 6       |
| 7     | 8       |
| 7     | 6       |
| 7     | 9       |
| 7     | 7       |
| 8     | 9       |
| 8     | 6       |
| 8     | 7       |
| 8     | 10     |
| 9     | 9       |
| 9     | 7       |
| 9     | 10     |
| 9     | 9       |
+----+-------+

Линия регрессии задается уравнением, где a – отрезок, отсекаемый линией регрессии на оси Y, а b задает угол наклона линии:

Y = bX + a

Предположим, что возраст – это X, а результаты теста – Y, и начнем вычислять величины, необходимые для уравнения корреляции: количество тестов, средние значения, суммы и суммы квадратов для обеих переменных, а также сумму произведений для каждой переменной:

mysql> SELECT
-> @n := COUNT(score) AS N,-> @meanX := AVG(age) AS "X mean",
-> @sumX := SUM(age) AS "X sum",
-> @sumXX := SUM(age*age) "X sum of squares",
-> @meanY := AVG(score) AS "Y mean",
-> @sumY := SUM(score) AS "Y sum",
-> @sumYY := SUM(score*score) "Y sum of square",
-> @sumXY := SUM(age*score) AS "X*Y sum"
-> FROM testscore\G
*************************** 1. row ***************************
N: 20
X mean: 7.0000
X sum: 140
X sum of squares: 1020
Y mean: 7.3000
Y sum: 146
Y sum of square: 1130
X*Y sum: 1053

Теперь угловые коэффициенты корреляции можно получить так:

mysql> SELECT
-> @b := (@n*@sumXY - @sumX*@sumY) / (@n*@sumXX - @sumX*@sumX)
-> AS slope;

+------+
| slope |
+------+
| 0.775 |
+------+

mysql> SELECT @a :=
-> (@meanY - @b*@meanX)
-> AS intercept;

+----------+
| intercept |
+----------+
| 1.875      |
+----------+

А само уравнение корреляции будет таким:

mysql> SELECT CONCAT('Y = ',@b,'X + ',@a) AS 'least-squares regression';

+--------------------------+
| least-squares regression |
+--------------------------+
| Y = 0.775X + 1.875        |
+--------------------------+

Чтобы вычислить коэффициент корреляции, используем многие из уже полученных величин:

mysql> SELECT
-> (@n*@sumXY - @sumX*@sumY)
-> / SQRT((@n*@sumXX - @sumX*@sumX) * (@n*@sumYY - @sumY*@sumY))
-> AS correlation;

+------------------+
| correlation           |
+------------------+
| 0.61173620442199 |
+------------------+

Статьи по MySQL на эту тему:

Генерация случайных чисел
Присваивание рангов
Рандомизация набора строк
Случайный выбор из набора строк

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