[오늘의 코드 147] [SOLVESQL] 서울숲 요일별 대기오염도 계산하기

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

[문제]

• 서울숲 일별 평균 대기오염도 데이터베이스에는 서울숲 대기 관측소에서 2022년 1년 동안 측정한 대기 오염 정보가 들어있습니다. 서울숲과 그 주변 일대의 유동 인구가 늘어남에 따라 서울숲 주변의 대기 오염 패턴도 유동 인구의 방문에 따라 달라지는 부분이 있을 것으로 예상되는데 이를 데이터를 통해 확인해보고자 합니다. 특히, 주말에 유동 인구가 늘어나고 월요일에 유동인구가 감소하는 방문 패턴이 대기 오염에도 영향을 미치는지 확인하려고 합니다.
  • weekday: 요일 (월요일-일요일)
  • no2: 평균 이산화질소(NO2) 농도(ppm)
  • o3: 평균 오존(O3) 농도(ppm)
  • co: 평균 일산화탄소(CO) 농도(ppm)
  • so2: 평균 아황산가스 농도(ppm)
  • pm10: 평균 미세먼지(PM10) 농도(㎍/㎥)
  • pm2_5: 평균 초미세먼지(PM2.5) 농도(㎍/㎥)
• 이를 위해, measurements 테이블의 데이터를 조회하여 요일별 대기 오염도 평균을 계산하는 쿼리를 작성해주세요. 쿼리 결과에는 아래 7개의 컬럼이 존재해야하며, 대기 오염도 값은 모두 소수점 다섯째 자리에서 반올림 해 넷째 자리까지 표현되어야 합니다. 또한, 쿼리 결과는 월요일부터 일요일 순으로 출력되어야 합니다.

[💡 정답]

--요일별 대기오염 평균 구하기
SELECT
  CASE
    WHEN strftime ('%w', measured_at) = '1' THEN '월요일'
    WHEN strftime ('%w', measured_at) = '2' THEN '화요일'
    WHEN strftime ('%w', measured_at) = '3' THEN '수요일'
    WHEN strftime ('%w', measured_at) = '4' THEN '목요일'
    WHEN strftime ('%w', measured_at) = '5' THEN '금요일'
    WHEN strftime ('%w', measured_at) = '6' THEN '토요일'
    WHEN strftime ('%w', measured_at) = '0' THEN '일요일'
  END 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
  strftime ('%w', measured_at)
ORDER BY
  CASE
    WHEN strftime ('%w', measured_at) = '1' THEN 1
    WHEN strftime ('%w', measured_at) = '2' THEN 2
    WHEN strftime ('%w', measured_at) = '3' THEN 3
    WHEN strftime ('%w', measured_at) = '4' THEN 4
    WHEN strftime ('%w', measured_at) = '5' THEN 5
    WHEN strftime ('%w', measured_at) = '6' THEN 6
    WHEN strftime ('%w', measured_at) = '0' THEN 7
  END;

✔️알게된 것

- strftime()은 기본적으로 0부터 시작하여 CASE WHEN 문으로 월요일을 1로 지정해야함.

- 요일별 그룹화하여 대기오염 평균 계산
- 요일 순서를 월-일요일로 정렬
- 각 평균값 소수점 넷째자리까지 반올림 ROUND( , 4)