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

pear000000

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

select corr(a, c) from example_tb

1	select corr(a, c) from example_tb

しかし、時として行同士の相関係数を取得したい場合があります。

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

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

SELECT
x.id, x.a, x.b, x.c, x.d, x.e,
(
    (
        (x.a*y.a)+(x.b*y.b)+(x.c*y.c)+(x.d*y.d)+(x.e*y.e) 
    )
    -
    (
        (x.a+x.b+x.c+x.d+x.e)*(y.a+y.b+y.c+y.d+y.e)/5
    )
)
/
NULLIF(
	(
	    sqrt(
	            (
	                (pow(x.a,2)+pow(x.b,2)+pow(x.c,2)+pow(x.d,2)+pow(x.e,2)) 
	                - 
	                (x.a+x.b+x.c+x.d+x.e)*(x.a+x.b+x.c+x.d+x.e)/5
	            )
	            *
	            (
	                (pow(y.a,2)+pow(y.b,2)+pow(y.c,2)+pow(y.d,2)+pow(y.e,2)) 
	                - 
	                (y.a+y.b+y.c+y.d+y.e)*(y.a+y.b+y.c+y.d+y.e)/5 
	            )
	        )
	 
	)
,0)
as pearson
from 
example_tb x,
example_tb y
where y.id = 1 
order by pearson DESC

SELECT

x.id, x.a, x.b, x.c, x.d, x.e,

(

(x.a*y.a)+(x.b*y.b)+(x.c*y.c)+(x.d*y.d)+(x.e*y.e)

)

(

(x.a+x.b+x.c+x.d+x.e)*(y.a+y.b+y.c+y.d+y.e)/5

)

NULLIF(

(

sqrt(

(

(pow(x.a,2)+pow(x.b,2)+pow(x.c,2)+pow(x.d,2)+pow(x.e,2))

(x.a+x.b+x.c+x.d+x.e)*(x.a+x.b+x.c+x.d+x.e)/5

)

(

(pow(y.a,2)+pow(y.b,2)+pow(y.c,2)+pow(y.d,2)+pow(y.e,2))

(y.a+y.b+y.c+y.d+y.e)*(y.a+y.b+y.c+y.d+y.e)/5

)

,0)

as pearson

from

example_tb x,

example_tb y

where y.id = 1

order by pearson DESC

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

pear000003

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

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

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

pear000004

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

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

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

備考

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

select
(
	(
		(5*5)+(3*3)+(5*5)+(3*3)+(5*5)
	)
	-
	(
		(5+3+5+3+5)*(5+3+5+3+5)/5
	)
)
/
(
	sqrt(
			(
				(pow(5,2)+pow(3,2)+pow(5,2)+pow(3,2)+pow(5,2)) 
				- 
				((5+3+5+3+5)*(5+3+5+3+5))/5  
			)
			*
			(
				(pow(5,2)+pow(3,2)+pow(5,2)+pow(3,2)+pow(5,2)) 
				- 
				((5+3+5+3+5)*(5+3+5+3+5))/5  
			)
		)
)

select

(

(5*5)+(3*3)+(5*5)+(3*3)+(5*5)

)

(

(5+3+5+3+5)*(5+3+5+3+5)/5

)

(

sqrt(

(

(pow(5,2)+pow(3,2)+pow(5,2)+pow(3,2)+pow(5,2))

((5+3+5+3+5)*(5+3+5+3+5))/5

)

(

(pow(5,2)+pow(3,2)+pow(5,2)+pow(3,2)+pow(5,2))

((5+3+5+3+5)*(5+3+5+3+5))/5

)

➡1

select
(
	(
		(5*5)+(3*3)+(5*5)+(3*3)+(5*5)
	)
	-
	(
		(5+3+5+3+5)*(5+3+5+3+5)/5
	)
)
/
(
	sqrt(
			(
				(pow(5,2)+pow(3,2)+pow(5,2)+pow(3,2)+pow(5,2)) 
				- 
				pow((5+3+5+3+5),2)/5  
			)
			*
			(
				(pow(5,2)+pow(3,2)+pow(5,2)+pow(3,2)+pow(5,2)) 
				- 
				pow((5+3+5+3+5),2)/5  
			)
		)
)

select

(

(5*5)+(3*3)+(5*5)+(3*3)+(5*5)

)

(

(5+3+5+3+5)*(5+3+5+3+5)/5

)

(

sqrt(

(

(pow(5,2)+pow(3,2)+pow(5,2)+pow(3,2)+pow(5,2))

pow((5+3+5+3+5),2)/5

)

(

(pow(5,2)+pow(3,2)+pow(5,2)+pow(3,2)+pow(5,2))

pow((5+3+5+3+5),2)/5

)

➡1.04166666666667

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

備考

投稿者: shimizu