Statistical Techniques - MySQL - page 524

-> @sumXX := SUM(age*age) AS 'X sum of squares',
-> @meanY := AVG(score) AS 'Y mean',
-> @sumY := SUM(score) AS 'Y sum',
-> @sumYY := SUM(score*score) AS 'Y sum of squares',
-> @sumXY := SUM(age*score) AS 'X*Y sum'
-> FROM testscore\G
*************************** 1. row ***************************
N: 20
X mean: 7.000000000
X sum: 140
X sum of squares: 1020
Y mean: 7.300000000
Y sum: 146
Y sum of squares: 1130
X*Y sum: 1053
From those terms, calculate the regression slope and intercept as follows:
mysql> SET @b := (@n*@sumXY - @sumX*@sumY) / (@n*@sumXX - @sumX*@sumX);
mysql> SET @a := (@meanY - @b*@meanX);
mysql> SELECT @b AS slope, @a AS intercept;
+-------------+----------------------+
| slope | intercept |
+-------------+----------------------+
| 0.775000000 | 1.875000000000000000 |
+-------------+----------------------+
The regression equation then is:
mysql> SELECT CONCAT('Y = ',@b,'X + ',@a) AS 'least-squares regression';
+-----------------------------------------+
| least-squares regression |
+-----------------------------------------+
| Y = 0.775000000X + 1.875000000000000000 |
+-----------------------------------------+
To compute the correlation coefficient, use many of the same terms:
mysql> SELECT
-> (@n*@sumXY - @sumX*@sumY)
-> / SQRT((@n*@sumXX - @sumX*@sumX) * (@n*@sumYY - @sumY*@sumY))
-> AS correlation;
+--------------------+
| correlation |
+--------------------+
| 0.6117362044219903 |
+--------------------+