[Day 18] solvesql | Advent of SQL 2024 | 펭귄 날개와 몸무게의 상관 계수

펭귄 날개와 몸무게의 상관 계수

 

✔️0213 1118 복습

문제 조건

1. 펭귄 종에 따라 날개 길이와 몸무게의 피어슨 상관 계수를 구하기

2. 상관 계수는 반올림 해 셋째 자리까지 출력

 

Solution

WITH statistics AS (
  SELECT species -- 종별로 상관계수 구하기
       , SUM(flipper_length_mm * body_mass_g) AS sum_xy
       , AVG(flipper_length_mm) AS mean_x
       , AVG(body_mass_g) AS mean_y
       , SUM(flipper_length_mm * flipper_length_mm) AS sum_xx
       , SUM(body_mass_g * body_mass_g) AS sum_yy
       , COUNT(*) AS n   
  FROM penguins
  WHERE flipper_length_mm IS NOT NULL
    AND body_mass_g IS NOT NULL
  GROUP BY species
)
SELECT species
     , ROUND(( sum_xy - n * mean_x * mean_y ) / ( sqrt(sum_xx - n * POW(mean_x,2) ) * sqrt(sum_yy - n * POW(mean_y,2)) ), 3) AS corr
FROM statistics
GROUP BY species

 

✅ MySQL에는 기본적으로 피어슨 상관계수(Pearson Correlation Coefficient)를 계산하는 내장 함수가 없기 때문에 피어슨 상관계수의 정의, 즉 공식대로 구해야 한다.

 

상관계수 공식

 

피드백

💡공식을 알고 계산하기 쉬운 수식으로 변환하는 것도 중요하지만, 내가 놓친 부분은 상관 계수를 구하고자 하는 두 변수(날개 길이, 몸무게)의 null 값 존재 여부이다. 

 

WITH 절에서 null 값을 필터링하지 않았을 때의 메인 쿼리 결과는 다음과 같다.

 

그래서 실제 확인해보니 Chinstrap 종 이외의 나머지 2종에서 두 변수가 null 인 행이 존재했다.

 

WITH 절 내에서는 구하고자 하는 통계량들이 잘 구해졌지만 null 값이 연산에 포함되면 메인 쿼리의 계산식 결과가 null이 된다. 따라서 이 점을 반영한 결과, 최종 결과가 잘 도출되었다.

 

강사님

WITH stats AS (
  SELECT species
       , AVG(flipper_length_mm) AS avg_flipper -- Xbar
       , AVG(body_mass_g) AS avg_body_mass -- Ybar
  FROM penguins
  GROUP BY species
)
SELECT species
     , ROUND(xy / SQRT(x*y), 3) AS corr
FROM (
  SELECT p.species
       , SUM(POWER(p.flipper_length_mm - s.avg_flipper, 2)) AS x -- V(x)
       , SUM(POWER(p.body_mass_g - s.avg_body_mass, 2)) AS y -- V(y)
       , SUM((p.flipper_length_mm - s.avg_flipper)*(p.body_mass_g - s.avg_body_mass)) AS xy
  FROM penguins p
    JOIN stats s ON p.species = s.species
  WHERE flipper_length_mm IS NOT NULL
    AND body_mass_g IS NOT NULL
  GROUP BY p.species
) AS t