MySQLのクエリで相関係数を計算する方法

はい、MySQLのクエリ内でも相関（相関係数）を計算することは可能です。OracleやPostgreSQLにはCORR()という組み込み関数がありますが、MySQLには用意されていないため、AVG()やSTDDEV_SAMP()といった集計関数とユーザー変数を組み合わせて、ピアソンの相関係数を手動で算出します。

ここでは、実際にテーブルを作成して相関を計算する手順を順番に見ていきましょう。まず、テーブルを作成するためのクエリは以下の通りです。

mysql> create table correlationDemo
   - > (
   - > value float not null,
   - > value2 float not null
   - > );
Query OK, 0 rows affected (0.62 sec)

次に、INSERTコマンドを使ってテーブルにレコードを挿入します。レコード挿入用のクエリは以下の通りです。

mysql> insert into correlationDemo values(1,10);
Query OK, 1 row affected (0.19 sec)
mysql> insert into correlationDemo values(2,4);
Query OK, 1 row affected (0.16 sec)
mysql> insert into correlationDemo values(3,5);
Query OK, 1 row affected (0.14 sec)
mysql> insert into correlationDemo values(6,17);
Query OK, 1 row affected (0.16 sec)

SELECT文を使って、テーブル内のすべてのレコードを表示して確認します。クエリは以下の通りです。

mysql> select *from correlationDemo;

以下が出力結果です。

+-------+--------+
| value | value2 |
+-------+--------+
|     1 | 10     |
|     2 | 4      |
|     3 | 5      |
|     6 | 17     |
+-------+--------+
4 rows in set (0.03 sec)

ステップ1：平均値と標準偏差の積を取得する

それでは、シンプルな相関計算のクエリを見てみましょう。まず、各列の平均値と、標本標準偏差の積をユーザー変数に格納します。

mysql> select @firstValue:=avg(value),
   - > @secondValue:=avg(value2),
   - > @division:=(stddev_samp(value) * stddev_samp(value2)) from correlationDemo;

以下が出力結果です。

+-------------------------+---------------------------+-------------------------------------------------------+
| @firstValue:=avg(value) | @secondValue:=avg(value2) | @division:=(stddev_samp(value) *stddev_samp(value2))  |
+-------------------------+---------------------------+-------------------------------------------------------+
| 3                       | 9                         | 12.84090685617215                                     |
+-------------------------+---------------------------+-------------------------------------------------------+
1 row in set (0.00 sec)

この時点で、value列の平均値は「3」、value2列の平均値は「9」、そして両者の標準偏差の積は約「12.84」ということがわかります。

ステップ2：共分散を標準偏差の積で割る

続いて、先ほどのユーザー変数を使って相関係数を計算します。各データ点から平均値を引いた値同士を掛け合わせて合計し、それを「（データ数 − 1）× 標準偏差の積」で割るのがポイントです。

mysql> select sum( ( value - @firstValue ) * (value2 - @secondValue) ) / ((count(value) -1) *
@division) from
   - > correlationDemo;

以下が出力結果です。

+--------------------------------------------------------------------------------------------+
| sum( ( value - @firstValue ) * (value2 - @secondValue) ) / ((count(value) -1) * @division) |
+--------------------------------------------------------------------------------------------+
| 0.7008850777290727                                                                         |
+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

結果の読み方

計算結果である「0.7008…」が、value列とvalue2列の間のピアソン相関係数です。相関係数は−1から1までの範囲の値を取り、1に近いほど強い正の相関、−1に近いほど強い負の相関を示します。今回の例では約0.70という値が出ているため、2つの列の間には比較的強い正の相関があると言えます。

このように、MySQLでは組み込み関数がなくても、平均・標準偏差・共分散の関係を利用したクエリを組み合わせることで、相関係数を簡単に求めることができます。