SQL 문제 풀이 챌린지(20/25) - 미세먼지 수치의 계절간 차이

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

 

 

 

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

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

WITH preprocessed_table AS (

    SELECT
      measured_at,
      season,
      pm10,
      ROW_NUMBER() OVER(PARTITION BY season ORDER BY pm10) AS row_num,
      COUNT(*) OVER(PARTITION BY season) AS cnt

    FROM (
          SELECT
            measured_at,
            CASE
                WHEN measured_at BETWEEN '2022-03-01' AND '2022-05-31' THEN 'spring'
                WHEN measured_at BETWEEN '2022-06-01' AND '2022-08-31' THEN 'summer'
                WHEN measured_at BETWEEN '2022-09-01' AND '2022-11-30' THEN 'autumn'
                ELSE 'winter'
            END AS season,
            pm10
          FROM measurements
    ) sub
)

, average_table AS(

    SELECT 
      season,
      ROUND(AVG(pm10),2) AS pm10_average
    FROM preprocessed_table
    GROUP BY season
)



, median_table AS (

    SELECT 
      season,
      AVG(pm10) AS pm10_median
    FROM preprocessed_table
    WHERE row_num IN (FLOOR((cnt+1) /2), FLOOR((cnt+2) /2))
    GROUP BY season

)




-- 테이블 통합 = avg + median

SELECT
  average_table.season AS season,
  pm10_median,
  pm10_average

FROM average_table
    INNER JOIN median_table ON average_table.season = median_table.season
ORDER BY FIELD(season, 'spring', 'summer', 'autumn', 'winter')