SQL | SOVESQL 2024 🎄: Day 18. 펭귄 날개와 몸무게의 상관 계수

 

 

 

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

https://solvesql.com/problems/correlation-penguin/

with t1 as (
  select species, flipper_length_mm, body_mass_g
    , avg(flipper_length_mm) over(PARTITION by species) as avg_f
    , avg(body_mass_g) over(PARTITION by species) as avg_b
  from penguins
)

select species
  , round(sum((flipper_length_mm - avg_f) * (body_mass_g - avg_b)) / (sqrt(sum(power((flipper_length_mm - avg_f),2))) * sqrt(sum(power((body_mass_g - avg_b),2)))),3) as corr
from t1
group by species

❗MYSQL은 CORR() 함수 지원이 안되니 상관계수 만들기

 

상관계수 : sum((X - MEAN_X) * (Y- MEAN_Y)) / (sqrt(sum(power((X - MEAN_X),2))) * sqrt(sum(power((Y- MEAN_Y),2))))

 

 

 

+) WITH 절 : T1

❗윈도우 함수 사용해서 종을 기준으로 날개, 무게 평균 만들기