구름 아카이브

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

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

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

SELECT 
    penguins.species,
    ROUND(
        SUM((penguins.flipper_length_mm - avg_values.avg_fl) * (penguins.body_mass_g - avg_values.avg_bm)) /
        SQRT(SUM(POWER(penguins.flipper_length_mm - avg_values.avg_fl, 2)) * 
             SUM(POWER(penguins.body_mass_g - avg_values.avg_bm, 2))),
    3) AS corr
FROM penguins,
    (SELECT 
         species,
         AVG(flipper_length_mm) AS avg_fl, 
         AVG(body_mass_g) AS avg_bm
     FROM penguins
     GROUP BY species) AS avg_values
WHERE penguins.species = avg_values.species
GROUP BY penguins.species