[TIL162] solvesql - 펭귄 날개와 몸무게의 상관계수

문제 설명

펭귄 종(species)별로

날개 길이(flipper_length_mm)
몸무게(body_mass_g)

사이의 피어슨 상관계수(Pearson Correlation Coefficient)를 구한다.

출력 컬럼

species
corr

상관계수는 소수점 셋째 자리까지 출력한다.

---

내가 생각한 접근

처음에는

상관관계 = y=ax+b 의 a

라고 생각했다.

하지만

기울기(a) ≠ 상관계수(corr)

이다.

---

기울기 : 

날개 길이 1mm 증가 시
몸무게 몇 g 증가하는가

 

---

상관계수 : 범위 (-1 ~ +1)

두 변수의 관계가
얼마나 강한가

 

---

내가 작성한 쿼리

SELECT
    species,
    flipper_length_mm,
    body_mass_g
FROM penguins
ORDER BY species, flipper_length_mm;

단순 조회만 했고 상관계수 계산은 하지 못했다.

---

정답 쿼리

WITH avgg AS (

    SELECT
        species,

        flipper_length_mm
        - AVG(flipper_length_mm)
          OVER(PARTITION BY species)
        AS dev_flipper,

        body_mass_g
        - AVG(body_mass_g)
          OVER(PARTITION BY species)
        AS dev_body

    FROM penguins

)

SELECT
    species,

    ROUND(
        SUM(dev_flipper * dev_body)
        /
        (
            SQRT(SUM(dev_flipper * dev_flipper))
            *
            SQRT(SUM(dev_body * dev_body))
        ),
        3
    ) AS corr

FROM avgg
GROUP BY species;

---

풀이 과정

Step 1. 종별 평균 (기준) 계산 

AVG(flipper_length_mm)
OVER(PARTITION BY species)

AVG(body_mass_g)
OVER(PARTITION BY species)

---

Step 2. 평균으로부터의 편차(Deviation) 계산

실제값 - 평균

---

날개 길이

flipper_length_mm
-
AVG(flipper_length_mm)
OVER(PARTITION BY species)

---

몸무게

body_mass_g
-
AVG(body_mass_g)
OVER(PARTITION BY species)

 

 

예시

180	190	-10
200	190	+10

 

의미

평균으로부터 얼마나 떨어져 있는가

---

Step 3. 공분산(Covariance) 계산

SUM(dev_f * dev_b)

 

 

의미

날개 길이와 몸무게가
같은 방향으로 움직이는 정도

 

---

예시

(-10) × (-200) = +

(+10) × (+200) = +

 

 

둘 다 평균보다 크거나

둘 다 평균보다 작으면 양수(+)가 됨

 

---

Step 4. 표준편차 계산

분산 : SQRT()

---

Step 5. 상관계수 계산

최종 공식

SUM(dev_f * dev_b)
/
(
 SQRT(SUM(dev_f * dev_f))
 *
 SQRT(SUM(dev_b * dev_b))
)

---

핵심 개념

편차 (Deviation)

값 - 평균

---

공분산 (Covariance)

SUM(편차 × 편차)

---

상관계수 (Correlation)

공분산

────────────

표준편차 × 표준편차

---

암기 포인트

편차

값 - AVG(값)

↓

평균에서 얼마나 벗어났는가

---

공분산

SUM(dev_x * dev_y)

↓

같이 움직이는 정도

---

표준편차

SQRT(SUM(dev_x * dev_x))

↓

퍼짐 정도

---

상관계수

SUM(dev_x * dev_y)

/

(
 SQRT(SUM(dev_x * dev_x))
 *
 SQRT(SUM(dev_y * dev_y))
)

↓

공분산 /(표준편차 × 표준편차)

---

한 줄 요약

이 문제는 종별 평균을 기준으로 편차를 계산한 뒤, 공분산 ÷ (표준편차 × 표준편차) 공식을 직접 구현하여 피어슨 상관계수를 구하는 문제이다. 상관계수 문제에서는 편차 → 공분산 → 표준편차 → 상관계수 순서로 접근하면 된다.