네, MySQL 쿼리만으로도 두 변수 간의 상관관계(correlation)를 충분히 계산할 수 있습니다. 별도의 애플리케이션 로직 없이 avg(), stddev_samp() 같은 집계 함수와 사용자 정의 변수를 조합하면 피어슨 상관계수(Pearson correlation coefficient)를 직접 구할 수 있습니다.
이 글에서는 예제 테이블을 만들고 데이터를 삽입한 뒤, 실제 상관계수를 계산하는 전체 과정을 단계별로 살펴보겠습니다.
1단계: 예제 테이블 생성
먼저 상관관계를 분석할 두 개의 숫자 컬럼을 가진 테이블을 생성합니다.
mysql> create table correlationDemo
-> (
-> value float not null,
-> value2 float not null
-> );
Query OK, 0 rows affected (0.62 sec)
2단계: 샘플 데이터 삽입
INSERT 문을 이용해 테이블에 분석용 데이터를 입력합니다.
mysql> insert into correlationDemo values(1,10);
Query OK, 1 row affected (0.19 sec)
mysql> insert into correlationDemo values(2,4);
Query OK, 1 row affected (0.16 sec)
mysql> insert into correlationDemo values(3,5);
Query OK, 1 row affected (0.14 sec)
mysql> insert into correlationDemo values(6,17);
Query OK, 1 row affected (0.16 sec)
3단계: 저장된 데이터 확인
SELECT 문으로 테이블에 들어간 전체 레코드를 조회해 보겠습니다.
mysql> select * from correlationDemo;
실행 결과는 다음과 같습니다.
+-------+--------+
| value | value2 |
+-------+--------+
| 1 | 10 |
| 2 | 4 |
| 3 | 5 |
| 6 | 17 |
+-------+--------+
4 rows in set (0.03 sec)
4단계: 평균과 표준편차 미리 구하기
피어슨 상관계수 공식에는 각 변수의 평균과 표본 표준편차가 필요합니다. MySQL의 사용자 정의 변수(@)에 이 값들을 저장해 두면 이후 쿼리에서 재사용할 수 있습니다.
mysql> select @firstValue:=avg(value),
-> @secondValue:=avg(value2),
-> @division:=(stddev_samp(value) * stddev_samp(value2)) from correlationDemo;
실행 결과입니다.
+-------------------------+---------------------------+------------------------------------------------------+
| @firstValue:=avg(value) | @secondValue:=avg(value2) | @division:=(stddev_samp(value)*stddev_samp(value2)) |
+-------------------------+---------------------------+------------------------------------------------------+
| 3 | 9 | 12.84090685617215 |
+-------------------------+---------------------------+------------------------------------------------------+
1 row in set (0.00 sec)
여기서 각 값의 의미는 다음과 같습니다.
- @firstValue = 3: value 컬럼의 평균
- @secondValue = 9: value2 컬럼의 평균
- @division ≒ 12.84: 두 변수의 표본 표준편차를 곱한 값
5단계: 상관계수 최종 계산
피어슨 상관계수는 다음 공식으로 구합니다.
r = Σ((x - x̄) × (y - ȳ)) / ((n - 1) × sx × sy)
이 공식을 그대로 SQL로 옮기면 아래와 같습니다.
mysql> select sum( (value - @firstValue) * (value2 - @secondValue) ) / ((count(value) - 1) *
-> @division) from
-> correlationDemo;
실행 결과입니다.
+--------------------------------------------------------------------------------------------+
| sum( ( value - @firstValue ) * (value2 - @secondValue) ) / ((count(value) -1) * @division) |
+--------------------------------------------------------------------------------------------+
| 0.7008850777290727 |
+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
결과 해석
계산된 상관계수는 약 0.7009입니다. 상관계수는 -1부터 1 사이의 값을 가지며, 0.7 정도면 두 변수 사이에 비교적 강한 양의 상관관계가 있다고 해석할 수 있습니다. 즉, value 값이 증가할 때 value2 값도 함께 증가하는 경향이 있다는 의미입니다.
참고로 MySQL 8.0 이상에서는 윈도우 함수인 CORR()를 지원하지 않지만, 위처럼 기본 집계 함수만으로도 동일한 결과를 얻을 수 있어 하위 버전에서도 유용하게 활용됩니다.