서울숲 요일별 대기오염도 계산하기 - solvesql

서울숲 요일별 대기오염도 계산하기

출처 : 서울숲 요일별 대기오염도 계산하기 SolveSQL

내 답안📕

WITH weekday_table AS (
  SELECT WEEKDAY(measured_at) AS 'weekday'
      , ROUND(AVG(no2), 4) AS 'no2'
      , ROUND(AVG(o3), 4) AS 'o3'
      , ROUND(AVG(co), 4) AS 'co'
      , ROUND(AVG(so2), 4) AS 'so2'
      , ROUND(AVG(pm10), 4) AS 'pm10'
      , ROUND(AVG(pm2_5), 4) AS 'pm2_5'
  FROM measurements
  GROUP BY WEEKDAY(measured_at)
  ORDER BY weekday
)

SELECT CASE
           WHEN weekday = 0 THEN '월요일'
           WHEN weekday = 1 THEN '화요일'
           WHEN weekday = 2 THEN '수요일'
           WHEN weekday = 3 THEN '목요일'
           WHEN weekday = 4 THEN '금요일'
           WHEN weekday = 5 THEN '토요일'
           ELSE '일요일'
       END AS weekday
     , no2
     , o3
     , co
     , so2
     , pm10
     , pm2_5
FROM weekday_table;