[Greenplum, PostgreSQL] SQL과 MADlib을 활용한 대용량데이터 선형 회귀분석 (Linear Regression using SQL, MADlib)
Greenplum and PostgreSQL Database 2020. 12. 1. 23:47먼저, 예제로 사용할 데이터로 4개의 연속형 데이터('sepal_length', 'sepal_width', 'petal_length', 'petal_width')와 1개의 범주형 데이터('class_name')를 가진 iris 데이터셋으로 테이블을 만들어보겠습니다.
-- Iris data table |
(1) SQL로 선형 회귀모형 적합하고 모수 확인하기 |
PostgreSQL 에서 설명변수(독립변수) X 1개와 목표변수(종속변수) Y 와의 선형 회귀모형을 적합할 수 있습니다. 대신에 하나의 함수로 한번에 선형 회귀모형을 적합하는 것은 아니구요, REGR_SLOPE(Y, X) 함수로 기울기(slope)를 구하고, REGR_INTERCEPT(Y, X)로 Y절편을 구할 수 있습니다.
-- Python으로 산점도와 선형회귀선을 겹쳐서 그래보면 아래와 같습니다.
## Scatter Plot using Python seaborn package import pandas as pd iris = sns.load_dataset('iris') sns.regplot(x=iris['petal_length'],
|
REGR_COUNT(Y, X)는 관측치의 개수, REGR_AVGY(Y, X) 는 Y의 평균 값, REGR_AVGX(Y, X) 는 X의 평균 값을 구해줍니다.
그리고 REGR_R2(Y, X)는 적합된 선형회귀모형의 설명력을 보여주는 결정계수(coefficient of determination)를 구해줍니다.
----------------------------
|
(2) SQL로 그룹별로 선형 회귀모형 적합하고 예측하기 |
다음으로 'class_name' 범주('iris_setosa', 'iris_versicolor', 'iris_virginica') 그룹별로 1개 설명변수 'petal_length'와 종속변수 'petal_width'의 관계를 모형화하는 선형 회귀모형을 적합해보겠습니다.
위의 (1)번 SQL query에 SELECT 문에 그룹 칼럼('class_name')을 넣어주고, FROM 절 다음에 GROUP BY 그룹 칼럼('class_name') 을 넣어주면 됩니다. 모델 3개가 잘 적합되었습니다.
-- Regression by Groups
|
-- Prediction |
(3) Apache MADlib으로 다중 선형 회귀모형(Multiple Linear Regression) 적합하기 |
SQL 기반의 오픈소스 Apache MADlib의 madlib.linregr_train() 함수를 사용하여 PostgreSQL, Greenplum database에서 여러개의 설명변수를 사용하는 다중 선형회귀모형 (multiple linear regression)을 적합할 수 있습니다.
위의 (1)번 PostgreSQL 의 기울기, Y절편 함수에서는 설명변수 X로 1개의 칼럼만을 사용하는 한계가 있었습니다. 그리고 기울기와 Y절편, R^2 등을 구하기 위해 개별 함수를 사용해야 하는 불편함이 있었습니다.
반면에, MADlib의 madlib.linregr_train() 함수는 source table, output table, dependent variable, ARRAY[1, independent variables] 의 순서대로 칼럼 이름을 넣어주면 됩니다. 그러면 회귀계수(coef), 결정계수(r2), 설명변수별 표준화오차(std_err)와 t통계량(t_stats), p값(p_values), condition_no, 관측치 개수(num_rows_processed), 결측치 개수(num_missing_rows_skipped), 분산공분산(variance_covariance) 의 결과를 반환합니다.
---------------- |
위의 선형 회귀모형 적합 결과를 좀더 보기 좋도록 UNNEST() 를 사용해서 설명변수별로 구분해서 풀어서 제시해보겠습니다.
SELECT |
(4) Apache MADlib으로 그룹별로 다중 선형 회귀모형 적합하고 예측하기 |
이번에는 class_name 범주의 그룹별로 다중 선형회귀모형을 적합해 보겠습니다.
madlib.linregr_tarin() 함수의 5번째 인자에 Grouping Column으로서 'class_name' 을 넣어주면 됩니다.
-- Multiple Regression by Group using MADlib |
위에서 3개 범주 그룹별로 적합한 모델을 사용해서 madlib.linregr_predict() 함수로 예측을 해보겠습니다. 이때 WHERE 조건절에 input dataset의 class_name 범주와 모델 테이블의 class_name 이 같아야 한다는 조건을 추가해줍니다.
-- Prediction |
* Apache MADlib : https://madlib.apache.org/docs/latest/group__grp__linreg.html