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

문제

(생략)

코드

SELECT
  CONCAT(
    substr('월화수목금토일',
      CASE
        WHEN strftime('%w', measured_at) = '0' THEN 7
        ELSE CAST(strftime('%w', measured_at) as int)
      END, 
    1)
  , '요일') 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
ORDER BY
  CASE
    WHEN strftime('%w', measured_at) = '0' THEN 7
    ELSE CAST(strftime('%w', measured_at) as int)
  END
;

알게된 점

평소보다 알고리즘스럽게 푼 문제였기에 기억이 남았다

날짜에 해당하는 요일 구하기 : STRFTIME('%w', date)

오라클 / MYSQL 날짜별 요일 구하는 법
strftime 함수를 사용하여 날짜 및 시간 조회
날짜 관련 함수는 어떤 SQL툴을 사용하고 있냐에 따라 방식이 너무 달라서 할 때마다 헷갈리게 된다
오라클과 MySQL은 위에 참고용으로 링크를 달았으니 참고하고,
SQLite의 경우 STRFTIME 함수를 통해 요일을 구할 수 있다

STRFTIME은 str form time으로, time 데이터를 str으로 변환시켜주는 함수이다
이때 변수값으로 '%w'를 넣어주게 되면 해당하는 요일의 정수값을 반환해준다 (일=0, 토=6)

이번 문제의 경우 일-토가 아니라 월-일로 정렬해야 했기에 해당 값이 0인 경우에는 +7로 처리하여 1~7로 정렬되도록 설정했다

원하는 type으로 변환하기 : CAST

[MSSQL 숫자를 문자로, 문자를 숫자로] 형변환 CAST, CONVERT
다만 위의 단계에서 하나 문제가 있다면, strftime은 string으로 반환해준다는 점이다
그렇기에 그대로 order by weekday로 처리한다면 '7' -> 1 -> 2 -> 3 -> ... 순서로 정렬이 된다는 문제가 남는다
정수든 문자든 하나의 공통 type으로 통일할 필요가 있었으며,
추후 substr을 사용하기도 했기에 정수형으로 통일하기 위해 CASE(variable as INT)로 처리했다

요일(정수)를 요일(문자)로 변환하기 : SUBSTR

SQL 문자열 자르기 - SUBSTR / SUBSTRING / LEFT / RIGHT
위의 단계까지 하고 나면 아래와 같이 weekday를 숫자로 얻게 된다.

하지만 해당 문제는 '월요일'과 같이 한글로 반환하는 것을 요구하고 있기에 그에 맞춰서 변환을 해주어야 한다.

사실 가장 쉬운 방법은 CASE END 구문을 통해 일일이 값을 매칭시켜주는 것이겠으나,
그건 너무 무식한 방법으로 보이기 때문에, 조금이라도 귀찮음을 덜 수 있는 방법을 생각하다가 SUBSTR을 사용하게 되었다.

SUBSTR함수는 문자열, 시작지점, 문자길이의 3개의 값을 받는데,
여기서 앞서 구해놓은 1~7은 '시작 지점'으로 사용하기에 적절하다.
문자열은 순서대로 '월화수목금토일'로 넣고, 문자 길이는 1글자만 반환하도록 처리하면
아래 이미지와 같이 원하는 요일을 뽑을 수 있다.

다만, 이때 order by를 weekday로 쓰면 ㄱㄴㄷ 순으로 정렬이 되어서 금→목→ … 순서로 순서가 꼬여버리는 문제가 있으므로, order by에 기존 정수값을 반환하던 CASE END 문을 넣어주면 깔끔하게 해결된다.

마무리는 CONCAT을 통해 weekday에 '요일'을 붙여주기만 하면 끝!