[SQL] solvesql Advent of SQL 2024 20일차

# 링크

• solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/

https://solvesql.com/collections/advent-of-sql-2024/

• 20일차 - 미세먼지 수치의 계절간 차이: https://solvesql.com/problems/finedust-seasonal-summary/

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

 

 

# 풀이

WITH
  seasonal_data AS (
    SELECT
      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
  ),
  ranked_data AS (
    SELECT
      season,
      pm10,
      ROW_NUMBER() OVER (PARTITION BY season ORDER BY pm10) AS rn,
      COUNT(*) OVER (PARTITION BY season) AS cnt,
      ROUND(AVG(pm10) OVER (PARTITION BY season), 2) AS pm10_average
    FROM
      seasonal_data
  ),
  median_calculations AS (
    SELECT
      season,
      pm10_average,
      CASE
        WHEN cnt % 2 = 1 THEN 
          MAX(CASE WHEN rn = (cnt + 1) / 2 THEN pm10 END)
        ELSE
          AVG(CASE WHEN rn IN (cnt / 2, (cnt / 2) + 1) THEN pm10 END)
      END AS pm10_median
    FROM
      ranked_data
    GROUP BY
      season, pm10_average
  )
SELECT
  season,
  pm10_median,
  pm10_average
FROM
  median_calculations;

• seasonal_data CTE
  • SELECT
    • CASE
      
      • WHEN measured_at BETWEEN '2022-03-01' AND '2022-05-31' THEN 'spring': measured_at의 값이 '2022-03-01'과 '2022-05-31' 사이인 경우 'spring'으로 분류한다.
      • WHEN measured_at BETWEEN '2022-06-01' AND '2022-08-31' THEN 'summer' : measured_at의 값이 '2022-06-01'과 '2022-08-31' 사이인 경우 ' summer'로 분류한다.
      • WHEN measured_at BETWEEN '2022-09-01' AND '2022-11-30' THEN 'autumn' : measured_at의 값이 '2022-09-01'과 '2022-11-30' 사이인 경우 ' autumn'으로 분류한다.
      • ELSE 'winter': 나머지 값은 'winter'로 분류한다.
      • END AS season: 해당 값을 season으로 저장한다.
    • pm10: pm10 컬럼을 선택한다.
  • FROM measurements: measurements 테이블에서 데이터를 가져온다.
• ranked_data CTE
  • SELECT
    
    • season: season 컬럼을 선택한다.
    • pm10: pm10 컬럼을 선택한다.
    • ROW_NUMBER() OVER (PARTITION BY season ORDER BY pm10) AS rn: 데이터를 season별로 pm10을 기준으로 오름차순 정렬하고 순위를 매긴 후, 그 값을 rn으로 저장한다.
    • COUNT(*) OVER (PARTITION BY season) AS cnt: season별 데이터의 개수를 계산하여 cnt로 저장한다.
    • ROUND(AVG(pm10) OVER (PARTITION BY season), 2) AS pm10_average: season별 pm10의 평균값을 소수점 둘째 자리까지 반올림한 후, 그 값을 pm10_average로 저장한다.
  • FROM seasonal_data: seasonal_data CTE에서 데이터를 가져온다.
• median_calculations CTE
  
  • SELECT
    
    • season: season 컬럼을 선택한다.
    • pm10_average: pm10_average 컬럼을 선택한다.
    • CASE
      
      • WHEN cnt % 2 = 1 THEN MAX(CASE WHEN rn = (cnt + 1) / 2 THEN pm10 END): cnt를 2로 나눈 나머지가 1일 때(데이터의 개수가 홀수), 'rn = (cnt + 1) / 2' 조건에 해당하는 pm10 값을 MAX로 반환한다.
        
        1. 데이터의 개수가 홀수일 때, 조건에 해당하지 않는 경우는 NULL이 반환되어 조건에 해당하는 pm10 값은 단일값이며, MAX로 그 값을 선택한다.
      • ELSE AVG(CASE WHEN rn IN (cnt / 2, (cnt / 2) + 1) THEN pm10 END): cnt를 2로 나눈 나머지가 0일 때(데이터의 개수가 짝수), rn이 'cnt / 2', 'cnt /2 + 1' 조건에 해당하는 pm10 값의 평균을 반환한다.
      • END AS pm10_median: 해당 값을 pm10_median으로 저장한다.
  • FROM ranked_data: ranked_data CTE에서 데이터를 가져온다.
  • GROUP BY season, pm10_average: season과 pm10_average 컬럼을 기준으로 그룹화한다.
• SELECT
  • season: season 컬럼을 선택한다.
  • pm10_median: pm10_median 컬럼을 선택한다.
  • pm10_average: pm10_average 컬럼을 선택한다.
• FROM median_calculations: median_calculations CTE에서 데이터를 가져온다.