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

문제

정답

WITH a AS (
    SELECT
      species,
      flipper_length_mm,
      body_mass_g,
      avg(flipper_length_mm) OVER (PARTITION BY species) length_avg,
      avg(body_mass_g) OVER (PARTITION BY species) mass_avg
    FROM penguins
)

SELECT species,
       round(sum((flipper_length_mm - length_avg)*(body_mass_g - mass_avg))
       /sqrt(sum(power(flipper_length_mm - length_avg,2)))
       /sqrt(sum(power(body_mass_g - mass_avg,2))),3) corr
FROM a
GROUP BY species

🚨 error

첫번째 문제

SELECT
  species,
  round(
    (
      sum(
        (
          flipper_length_mm - (SELECT avg(flipper_length_mm) OVER(PARTITION BY species) FROM penguins)
        ) *
        (
          body_mass_g - (SELECT avg(body_mass_g) OVER(PARTITION BY species) FROM penguins)
        )
      )
    ) / (
      sqrt(sum((flipper_length_mm - (SELECT avg(flipper_length_mm) OVER(PARTITION BY species) FROM penguins))^2))
      *
      sqrt(sum((body_mass_g - (SELECT avg(body_mass_g) OVER(PARTITION BY species) FROM penguins))^2))
    ),
    3
  ) AS corr
FROM penguins
GROUP BY species

SELECT avg(...) OVER (PARTITION BY species) 그 자체를 컬럼으로 쓰고,
빼기 계산을 할 때 현재 행 기준으로 바로 빼야 한다.

서브쿼리로 SELECT avg(…) FROM penguins 쓰는 건 틀린 방법이다.
?
다시 수정하기

두번째 문제

WITH a AS (
    SELECT
      species,
      flipper_length_mm,
      body_mass_g,
      avg(flipper_length_mm) OVER (PARTITION BY species) length_avg,
      avg(body_mass_g) OVER (PARTITION BY species) mass_avg
    FROM penguins
    GROUP BY species
)
SELECT species,
       round(sum((flipper_length_mm - length_avg)*(body_mass_g - mass_avg))
       /sqrt(sum(power(flipper_length_mm - length_avg,2)))
       /sqrt(sum(power(body_mass_g - mass_avg,2))),3) corr
FROM a

그룹바이 cte 안에 쓰면 그룹당 한 행씩만 남는다. 따라서 본 쿼리에서 계산이 제대로 되지 않는다.

참고 블로그