[SQL]SELECT文で行(レコード)同士の相関係数を取得する

最終更新日

pear000000

例えば、上記のようなデータがあったとして、列同士の相関係数はcorr関数を使う事で簡単に取得することができます。
pear000001

しかし、時として行同士の相関係数を取得したい場合があります。
pear000002
上記のデータだと、id:1のレコードに対してid:3のレコードとの相関係数が欲しいのですが……求め方がわかりません orz
仕方がないので、SELECT文の中でピアソンの積率相関係数計算を行って、相関係数を求めることにします。(もっと簡単に取得できる方法がありましたら教えてください)

id:1のレコードに対して他のレコードのピアソン相関係数を取得するSELECT文は以下となります。(対象とするのは、a,b,c,d,e の5項目とします)

上記クエリを実行した結果が以下です。

pear000003

正の相関関係にあたる、自分自身のレコードには「1」が、負の相関関係にあたるid:2のレコードに大しては「-1」がちゃんと返ってきました。同じカラムに入っている値が近いほど、1に近い値が返ってきます。

5項目全て同じ値のレコードは相関を計ることができずNULLが返ります。
postgreSQLのcorr関数の仕様にあわせました。
あと、5項目の中にNULLが含まれていても上記の式では相関係数を計算できないのでNULLを返します。

ExcelのPEARSON関数を使って相関係数を求めた結果と比較してみます。

pear000004

ちょっと誤差が出ているのが気になりますが、まぁだいたい合ってますかね。

これで、行間の比較係数を取得することができました。
比較対象とする項目(カラム)が増える毎に、クエリを修正しなくてはならないので使い勝手はいまいちですが。

相関係数の計算式は下記書籍を参考にさせていただきました。


備考

上記コードでpow関数を使わずにベキ乗計算をおこなっている部分があります。
なぜか、ここでpow関数を使うと数値がずれるので回避策としてpow関数を使わずベキ乗計算をしています。

➡1

➡1.04166666666667

同じ計算をしているはずなのに……なぜ?