피어슨 상관계수 SQL

WITH t AS ( 

SELECT 17 x, 28 y FROM dual 

UNION ALL SELECT 19, 30 FROM dual 

UNION ALL SELECT 20, 24 FROM dual 

UNION ALL SELECT 21, 36 FROM dual

UNION ALL SELECT 23, 32 FROM dual

) 

-- 피어슨 상관계수(Pearson correlation coefficient) -- 

SELECT ( n*xy - x*y ) / SQRT( (n*x2-x*x) * (n*y2-y*y) ) AS r , ( xy - x*y/n ) / SQRT( (x2-x*x/n) * (y2-y*y/n) ) AS r FROM (SELECT SUM(x) x , SUM(y) y , SUM(x*x) x2 , SUM(y*y) y2 , SUM(x*y) xy , COUNT(*) n FROM t);