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

문제

제출

with
  pm_week as (
    SELECT
      *,
      strftime ('%w', measured_at) weekday
    FROM
      measurements
  )
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 '금요일'
    WHEN weekday = '6' THEN '토요일'
  end weekday,
  round(avg(no2), 4) no2,
  round(avg(o3), 4) o3,
  round(avg(co), 4) co,
  round(avg(so2), 4) so2,
  round(avg(pm10), 4) pm10,
  round(avg(pm2_5), 4) pm2_5
FROM
  pm_week
GROUP BY
  weekday
ORDER BY CASE 
    WHEN weekday = '0' THEN 7
    WHEN weekday = '1' THEN 1
    WHEN weekday = '2' THEN 2
    WHEN weekday = '3' THEN 3
    WHEN weekday = '4' THEN 4
    WHEN weekday = '5' THEN 5
    WHEN weekday = '6' THEN 6 end

📌 새로 배운 내용

✅ strftime('%w')

%w : day of week 0-6 with Sunday==0

또 다른 답

with
  pm_week as (
    SELECT
      *,
      strftime ('%u', measured_at) weekday
    FROM
      measurements
  )
SELECT
  CASE
    WHEN weekday = '1' THEN '월요일'
    WHEN weekday = '2' THEN '화요일'
    WHEN weekday = '3' THEN '수요일'
    WHEN weekday = '4' THEN '목요일'
    WHEN weekday = '5' THEN '금요일'
    WHEN weekday = '6' THEN '토요일'
    WHEN weekday = '7' THEN '일요일'
  end weekday,
  round(avg(no2), 4) no2,
  round(avg(o3), 4) o3,
  round(avg(co), 4) co,
  round(avg(so2), 4) so2,
  round(avg(pm10), 4) pm10,
  round(avg(pm2_5), 4) pm2_5
FROM
  pm_week
GROUP BY
  weekday

strftime ('%u', measured_at) 로 하면

%u : day of week 1-7 with Monday==1

이므로 order by 절에서 따로 순서를 정해주지 않아도 된다.