SQL | SOVESQL 2024 🎄: Day 20. 미세먼지 수치의 계절간 차이

 

Day 20. 미세먼지 수치의 계절간 차이

https://solvesql.com/problems/finedust-seasonal-summary/

with t1 as (
  select * 
    , case 
      when measured_at between '2022-03-01' and '2022-05-31' then 1
      when measured_at between '2022-06-01' and '2022-08-31' then 2
      when measured_at between '2022-09-01' and '2022-11-30' then 3
      else 4
    end as season_n
  from measurements
), t2 as (
  select season_n
    , pm10
    , PERCENT_RANK() OVER (PARTITION by season_n ORDER BY pm10 asc) as percent
    , ROW_NUMBER() OVER (PARTITION by season_n ORDER BY pm10 asc) as rnk
    , round(avg(pm10) over(PARTITION by season_n),2) as pm10_average
  from t1
)

select 
  case 
    when season_n = 1 then 'spring'
    when season_n = 2 then 'summer'
    when season_n = 3 then 'autumn'
    else 'winter'
  end as season
  , pm10 as pm10_median
  , pm10_average
from t2
where (season_n, rnk) in (select season_n
                        , ceil(max(rnk) / 2) as ceil_n
                      from t2
                      group by season_n)

❗CASE WHEN 사용해서 계절 넘버를 계절로 만들기
❗WHERE 절에 서브쿼리 사용해서 해당하는 값 가져오기

 

 

+) WHERE 절 : 서브쿼리

❗계절 넘버로 그룹 하이 한 것 들 중에 최댓값 2로 나눈 것

 

 

 

+) WITH 절 : T1

❗CASE WHEN 사용해서 봄, 여름, 가을, 겨울 기준으로 숫자 넣기 (나중에 정렬을 위해 숫자로 지정)

 

 

 

+) WITH 절 : T2

❗PERCENT_RANK(), ROW_NUMBER(), AVG() 와 윈도우 함수 사용해서 계절 넘버 기준으로 값 나오게 하기