Задача
Вы хотите вычислить для двух переменных регрессию методом наименьших квадратов или коэффициент корреляции, который выражает отношение между ними.
Решение
Используйте агрегирующие функции.
Обсуждение
Если значения данных для двух переменных 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 |
+------------------+