[SQL_Q] 계절 별 기온의 평균값, 중앙값

문제

문제 설명:
강남역 주변 일별 온도 데이터베이스에는 강남역 기상 관측소에서 2022년 1년 동안 측정한 기온 정보가 들어있습니다.

이 데이터를 활용해 봄, 여름, 가을, 겨울 계절 별로 평균 기온의 차이가 있는지 알아보고 싶습니다.
3월 1일부터 5월 31일까지를 ‘spring’, 6월 1일부터 8월 31일까지를 ‘summer’, 9월 1일부터 11월 30일까지를 ‘autumn’, 나머지를 ‘winter’라고 이름 붙이고 각 계절별로 일별 평균 기온(temperature)의 중앙값과 평균을 계산하는 쿼리를 작성해주세요.

쿼리 결과는 아래 세 컬럼을 포함해야 하며, 평균값은 소수점 셋째 자리에서 반올림해 소수점 둘째 자리까지 표시되어야 합니다.

season: 계절 (’spring’, ‘summer’, ‘autumn’, ‘winter’)

temp_median: 일별 평균 기온의 중앙값

temp_average: 일별 평균 기온의 평균

조건:

테이블 이름은 gangnam_temperature

날짜 컬럼은 measured_at

기온 컬럼은 temperature

내 쿼리

WITH numbered AS (
  SELECT season, temperature, measured_at,
         ROW_NUMBER() OVER(PARTITION BY season ORDER BY temperature) AS rn,
         COUNT(*) OVER(PARTITION BY season) AS cnt
  FROM (
    SELECT *,
           CASE 
             WHEN measured_at >= "2022-03-01" AND measured_at < "2022-06-01" THEN "spring"
             WHEN measured_at >= "2022-06-01" AND measured_at < "2022-09-01" THEN "summer"
             WHEN measured_at >= "2022-09-01" AND measured_at < "2022-12-01" THEN "autumn"
             ELSE "winter"
           END AS season
    FROM gangnam_temperature
  ) AS tmp
),
temp_mid AS (
  SELECT season, AVG(temperature) AS temp_median, ROW_NUMBER() OVER(ORDER BY rn) row_n
  FROM numbered
  WHERE rn IN (FLOOR((cnt + 1)/2), CEIL((cnt+1)/2))
  GROUP BY season
),
temp_avg AS (
  SELECT season, ROUND(AVG(temperature),2) AS temp_average
  FROM numbered
  GROUP BY season
)
SELECT tm.season, temp_median, temp_average
FROM temp_mid tm
JOIN temp_avg ta
ON tm.season = ta.season
ORDER BY ((row_n+1)%4);

1. 중첩 CTE

   • gangnam_temperature → numbered_temp → temp_mid + temp_avg → 최종 JOIN
   • CTE가 여러 번 순차적으로 계산됨 → 대용량에서는 임시 결과 테이블 생성 비용 증가 가능

2. 중복 연산:

   • 평균(temp_avg) 계산과 median 후보(temp_mid)를 구하기 위해 이미 계산된 numbered_temp CTE를 재사용.

3. ROW_NUMBER() + COUNT():

   • median 후보 추출 시 rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
   • cnt를 계산하기 위해 다시 COUNT(*) OVER → 대용량에서는 윈도우 함수 비용 높음.

4. ORDER BY가 복잡함:

   • ORDER BY ((row_n+1)%4) → 의미상 순서를 위해 계산 필요, 불필요한 연산 가능.

GPT 쿼리

WITH seasoned AS (
    SELECT 
        CASE 
            WHEN measured_at >= '2022-03-01' AND measured_at < '2022-06-01' THEN 'spring'
            WHEN measured_at >= '2022-06-01' AND measured_at < '2022-09-01' THEN 'summer'
            WHEN measured_at >= '2022-09-01' AND measured_at < '2022-12-01' THEN 'autumn'
            ELSE 'winter'
        END AS season,
        temperature
    FROM gangnam_temperature
),
numbered AS (
    SELECT 
        season,
        temperature,
        ROW_NUMBER() OVER(PARTITION BY season ORDER BY temperature) AS rn,
        COUNT(*) OVER(PARTITION BY season) AS cnt
    FROM seasoned
),
temp_stats AS (
    SELECT
        season,
        MAX(CASE WHEN rn = CEIL(cnt/2.0) THEN temperature END) AS temp_median,
        ROUND(AVG(temperature), 2) AS temp_average
    FROM numbered
    GROUP BY season
)
SELECT season, temp_median, temp_average
FROM temp_stats
ORDER BY FIELD(season,'spring','summer','autumn','winter');

성능 비교 관점

항목	사용자 쿼리	GPT 쿼리	분석
CTE 깊이	3단계	2단계	GPT 쿼리가 임시 테이블 생성과 재계산 비용 절감
median 계산	rn IN (FLOOR(...), CEIL(...)) → AVG	MAX(CASE WHEN rn = CEIL(...) END)	GPT 쿼리가 단일 집계로 median 계산 → 불필요한 row 필터링/집계 제거
평균 계산	별도 CTE에서 AVG	median 계산과 동일 CTE에서 AVG	GPT 쿼리가 읽기 한 번으로 평균/median 계산
ORDER BY	수식 기반	FIELD()	GPT 쿼리가 단순하고 인덱스 활용 가능
윈도우 함수 사용	ROW_NUMBER + COUNT	ROW_NUMBER + COUNT	유사하지만, 사용자 쿼리는 median 후보 추출 후 추가 ROW_NUMBER → 중복 연산
대용량 데이터 적합성	낮음	높음	GPT 쿼리는 중복 연산 최소화, CTE 단순화, 집계 연산 통합