txqz memo

MySQLで相関係数を求めtai!

「MySQL+相関係数+関数」などで検索したらSQLのリファレンス本 の紹介サイトくらいしかめぼしいサイトが引っかからなかったので、自分で何とかすることにした。

まず、以下のようなテストデータを作成した。

X13141516151413121110
Y34293316264031333545

Excelのcorrel()関数によると、このデータの相関係数は-0.74987である。SQLを操作した結果、これと同じ値が出てくればよい。今回、以下のような操作を行った。

CREATE TABLE correl(x int, y int);
INSERT INTO correl VALUES(13,34);
(中略)
INSERT INTO correl VALUES(10,45);
CREATE TEMPORARY TABLE avg SELECT AVG(x) AS xavg, AVG(y) AS yavg FROM correl;
CREATE TEMPORARY TABLE sd SELECT POW(x - xavg, 2) AS xsd, POW(y - yavg, 2) AS ysd, (x - xavg) * (y - yavg) AS covariance FROM correl CROSS JOIN avg;
SELECT SUM(covariance) / (POW(SUM(xsd), 0.5) * POW(SUM(ysd), 0.5)) AS correlation FROM sd;

実行したら -0.74986565067481 が出た。成功。