SQL | SOVESQL 2024 🎄: Day 11. 서울숲 요일별 대기오염도 계산하기

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

https://solvesql.com/problems/weekday-stats-airpollution/

WITH T1 AS (
  SELECT * 
    , WEEKDAY(measured_at) AS week
    , CASE 
        WHEN WEEKDAY(measured_at) = 0 THEN '월요일'
        WHEN WEEKDAY(measured_at) = 1 THEN '화요일'
        WHEN WEEKDAY(measured_at) = 2 THEN '수요일'
        WHEN WEEKDAY(measured_at) = 3 THEN '목요일'
        WHEN WEEKDAY(measured_at) = 4 THEN '금요일'
        WHEN WEEKDAY(measured_at) = 5 THEN '토요일'
        ELSE '일요일'
      END AS weekday
  FROM measurements
)

SELECT 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 T1
GROUP BY week, weekday
ORDER BY week ASC

❗ROUND(),AVG() 사용해서 출력 포맷 맞추기
❗생성한 요일 변수(한글)로 GROUP BY

 

 

 

+) WITH 절 : T1

❗WEEKDAY() 사용해서 요일을 숫자로 나타내는 변수 생성. 이후에 정렬을 요일 숫자로 사용
❗CASE WHEN, WEEKDAY() 사용해서 요일에 따라 값이 달라지는 칼럼 생성