[PostgreSQL, Greenplum] 상관관계 분석 (Correlation Coefficients using SQL, MADlib)

지난번 포스팅에서는 PostgreSQL, Greenplum database에서 SQL, MADlib 을 사용하여 연수형 데이터의 집계/ 요약통계량(aggregation/ summary statistics)을 구하는 방법(https://rfriend.tistory.com/580)을 소개하였습니다.

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 SQL, Apache MADlib 을 사용하여 여러개의 연속형 변수들 간의 상관관계를 구하는 방법을 소개하겠습니다.

(1) SQL 로 두개 연속형 변수간 상관계수 구하기

(2) SQL 로 다수의 연속형 변수간 상관계수 행렬(Correlation Matrix) 구하기

(3) MADlib으로 다수의 연속형 변수간 상관계수 행렬 구하기

(4) MADlib으로 다수의 연속형 변수간 그룹별로 상관계수 행렬(Correlation Matrix by Group) 구하기

예제로 사용하도록 연속형 데이터 4개 칼럼과 범주형 데이터 1개를 가지고 iris 샘플 데이터를 테이블로 만들어보겠습니다.

-- Iris data table DROP TABLE IF EXISTS iris; CREATE TABLE iris (id INT, sepal_length FLOAT, sepal_width FLOAT,                     petal_length FLOAT, petal_width FLOAT,                    class_name TEXT); INSERT INTO iris VALUES (1,5.1,3.5,1.4,0.2,'Iris-setosa'), (2,4.9,3.0,1.4,0.2,'Iris-setosa'), (3,4.7,3.2,1.3,0.2,'Iris-setosa'), (4,4.6,3.1,1.5,0.2,'Iris-setosa'), (5,5.0,3.6,1.4,0.2,'Iris-setosa'), (6,5.4,3.9,1.7,0.4,'Iris-setosa'), (7,4.6,3.4,1.4,0.3,'Iris-setosa'), (8,5.0,3.4,1.5,0.2,'Iris-setosa'), (9,4.4,2.9,1.4,0.2,'Iris-setosa'), (10,4.9,3.1,1.5,0.1,'Iris-setosa'), (11,7.0,3.2,4.7,1.4,'Iris-versicolor'), (12,6.4,3.2,4.5,1.5,'Iris-versicolor'), (13,6.9,3.1,4.9,1.5,'Iris-versicolor'), (14,5.5,2.3,4.0,1.3,'Iris-versicolor'), (15,6.5,2.8,4.6,1.5,'Iris-versicolor'), (16,5.7,2.8,4.5,1.3,'Iris-versicolor'), (17,6.3,3.3,4.7,1.6,'Iris-versicolor'), (18,4.9,2.4,3.3,1.0,'Iris-versicolor'), (19,6.6,2.9,4.6,1.3,'Iris-versicolor'), (20,5.2,2.7,3.9,1.4,'Iris-versicolor'), (21,6.3,3.3,6.0,2.5,'Iris-virginica'), (22,5.8,2.7,5.1,1.9,'Iris-virginica'), (23,7.1,3.0,5.9,2.1,'Iris-virginica'), (24,6.3,2.9,5.6,1.8,'Iris-virginica'), (25,6.5,3.0,5.8,2.2,'Iris-virginica'), (26,7.6,3.0,6.6,2.1,'Iris-virginica'), (27,4.9,2.5,4.5,1.7,'Iris-virginica'), (28,7.3,2.9,6.3,1.8,'Iris-virginica'), (29,6.7,2.5,5.8,1.8,'Iris-virginica'), (30,7.2,3.6,6.1,2.5,'Iris-virginica'); SELECT * FROM iris ORDER BY id LIMIT 5;

(1) SQL 로 두개 연속형 변수간 상관계수 구하기

공분산(Covariance)은 두 변량이 각각의 평균으로부터 변화하는 방향 및 양에 대한 기대값입니다. PostgreSQL, Greenplum DB에서 모집단의 공분산(Population Covariance)는 COVAR_POP(Y, X) 함수를 사용하며, 샘플 공분산(Sample Covariance)은 COVAR_SAMP(Y, X) 함수를 사용합니다.

(* 공분산, 상관계수 참고: https://rfriend.tistory.com/126)

피어슨 상관계수(Pearson's Correlation Coefficient)는 공분산을 표준편차로 나누어서 표준화해준 값으로서, -1~1 사이의 값을 가집니다. 1에 가까울수록 두 변수간에 양의 선형관계가 있고, -1에 가까울수록 음의 선형관계가 있으며, 0이면 선형관계가 없다고 해석합니다. PostgreSQL, Greenplum DB에서는 CORR(Y, X) 함수를 사용합니다.

----- b/w Y and X -- Population Covariance using SQL: covar_pop(Y, X) SELECT COVAR_POP(sepal_length, sepal_width) FROM iris; -- Sample Covariance using SQL: covar_samp(Y, X) SELECT COVAR_SAMP(sepal_width, sepal_length) FROM iris; -- Pearson's Correlation Coefficients using SQL: corr(Y, X) SELECT CORR(sepal_width, sepal_length) FROM iris;

(2) SQL 로 다수의 연속형 변수간 상관계수 행렬(Correlation Matrix) 구하기

먼저, 이해를 돕기 위해서 2단계로 나누어서 과정을 설명하자면요, 긴 형태(long format)로 두 연속형 변수 간의 상관계수를 구해보겠습니다. (두번째 단계에서 Pivoting 하는데 사용하는, sub query 의 내용임)

------ Correlation Coefficients among multiple variables -- Correlation Matrix -- (a) Calculating pairwise correlation using corr(Y, X) function SELECT     1 AS column_position     , 'sepal_length' AS row     , 'sepal_width' AS col     , CORR(sepal_length, sepal_width) AS corr_coef FROM iris UNION SELECT     1 AS column_position     , 'sepal_length' AS row     , 'petal_length' AS col     , CORR(sepal_length, petal_length) AS corr_coef FROM iris UNION SELECT     1 AS column_position     , 'sepal_length' AS row     , 'petal_width' AS col     , CORR(sepal_length, petal_width) AS corr_coef FROM iris UNION SELECT     2 AS column_position     , 'sepal_width' AS row     , 'petal_length' AS col     , CORR(sepal_width, petal_length) AS corr_coef FROM iris UNION SELECT     2 AS column_position     , 'sepal_width' AS row     , 'petal_width' AS col     , CORR(sepal_width, petal_width) AS corr_coef FROM iris UNION SELECT     3 AS column_position     , 'petal_length' AS row     , 'petal_width' AS col     , CORR(petal_length, petal_width) AS corr_coef FROM iris;

위의 SQL query를 sub query 로 넣고, manual 하게 테이블을 Pivot 하여 우리가 원하는 상관계수 행렬 (correlation matrix)를 만들어보겠습니다. 대각행렬에서 우상단에만 상관계수를 나타내고 좌하단에는 0으로 채워넣기를 하였습니다.

-- (b) Pivoting the table to get a correlation matrix SELECT     column_position     , row     , SUM(CASE WHEN col='sepal_length' THEN corr_coef ELSE 0 END) AS sepal_length     , SUM(CASE WHEN col='sepal_width' THEN corr_coef ELSE 0 END) AS sepal_width     , SUM(CASE WHEN col='petal_length' THEN corr_coef ELSE 0 END) AS petal_length     , SUM(CASE WHEN col='petal_width' THEN corr_coef ELSE 0 END) AS petal_width FROM (     SELECT         1 AS column_position         , 'sepal_length' AS row         , 'sepal_width' AS col         , CORR(sepal_length, sepal_width) AS corr_coef     FROM iris     UNION     SELECT         1 AS column_position         , 'sepal_length' AS row         , 'petal_length' AS col         , CORR(sepal_length, petal_length) AS corr_coef     FROM iris     UNION     SELECT         1 AS column_position         , 'sepal_length' AS row         , 'petal_width' AS col         , CORR(sepal_length, petal_width) AS corr_coef     FROM iris     UNION     SELECT         2 AS column_position         , 'sepal_width' AS row         , 'petal_length' AS col         , CORR(sepal_width, petal_length) AS corr_coef     FROM iris     UNION     SELECT         2 AS column_position         , 'sepal_width' AS row         , 'petal_width' AS col         , CORR(sepal_width, petal_width) AS corr_coef     FROM iris     UNION     SELECT         3 AS column_position         , 'petal_length' AS row         , 'petal_width' AS col         , CORR(petal_length, petal_width) AS corr_coef     FROM iris ) a GROUP BY column_position, row ORDER BY column_position;

원하는 상관계수행렬을 구하기는 했는데요, SQL query가 엄청 길어지고 또 복잡해졌습니다. 분석의 대상이 되는 연속형 데이터 칼럼 수가 늘어날수록 query가 더 길어지고 복잡해지는 구조여서 더 좋은 방법이 없을까 고민하게 됩니다. 짜잔~! 이럴때 PostgreSQL, Greenplum DB에서 쉽고 편하고 또 빠르게 분석하는데 쓸 수 있는 SQL 기반의 오픈소스 분석툴인 Apache MADlib이 있습니다!

(3) MADlib으로 다수의 연속형 변수간 상관계수 행렬 구하기

위의 (2)번에서 pure SQL로 했던 상관계수 행렬 구하기를 Apache MADlib의 madlib.correlation() 함수를 사용하면 아래처럼 단 3줄이면 끝납니다! 비교할 수 없을 정도로 SQL query가 간소해졌습니다!

----- Correlation Matrix using MADlib DROP TABLE IF EXISTS iris_corr_output, iris_corr_output_summary; SELECT madlib.correlation( 'iris',             -- source table                            'iris_corr_output',       -- output table                            'sepal_length, sepal_width, petal_length, petal_width' -- target columns                          ); /*                          SELECT madlib.correlation( 'iris',        -- source table                            'iris_corr_output',  -- output table                            '*'                     -- for all columns                          );                         */                         SELECT * FROM iris_corr_output ORDER BY column_position;

(4) MADlib으로 다수의 연속형 변수간 그룹별로 상관계수 행렬구하기        (Correlation Matrix by Group using Apache MADlib)

PostgreSQL, Greenplum DB에서 Apache MADlib을 사용했을 때 코드가 간소해지는점 말고 또 좋은 점 중의 하나는 그룹별 연산을 하기에도 굉장히 편리하다는 점입니다.

아래 예제는 class_name ('iris-setosa', 'iris-versicolor', 'iris-virginica') 의 그룹별로 상관계수 행렬을 구하기(correlation matrix by groups using MADlib) 입니다. 위의 (3)번 예제어서 'grouping column' 위치에 "class_name" 칼럼 이름만 추가하면 끝입니다. MADlib이 이처럼 "simple & powerful" 합니다. (만약 (2)번 방법으로 SQL로 일일이 group by 넣어서 query 짠다고 상상을 해보면 끔찍하지 않나요? ^^;)

-- Correlation Matrix by Groups using MADlib DROP TABLE IF EXISTS iris_corr_grp_output, iris_corr_grp_output_summary; SELECT madlib.correlation( 'iris'             -- source table                            , 'iris_corr_grp_output' -- output table                            , '*' -- target columns                            , False -- verbose                            , 'class_name' -- grouping column                          );                           SELECT * FROM iris_corr_grp_output ORDER BY class_name, column_position;

[Reference]

* PostgreSQL 9.4: https://www.postgresql.org/docs/9.4/functions-aggregate.html
* Apache MADlib: https://madlib.apache.org/docs/v1.0/group__grp__correlation.html

이번 포스팅이 많은 도움이 되었기를 바랍니다 .

행복한 데이터 과학자 되세요!  :-)