DAY 8 / [solvesql] Advent of SQL 2024 2문제 (전국 카페 주소 데이터 정제하기, 미세먼지 수치의 계절간 차이)

전국 카페 주소 데이터 정제하기

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', 1) AS sido
     , SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', -1) AS sigungu
     , COUNT(DISTINCT cafe_id) AS cnt
FROM cafes
GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', 1)
       , SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', -1)
ORDER BY cnt DESC

 

 

예를 들어 주소(address)에 이런 데이터가 있다고 하자.

'서울특별시 성북구 얼마나2길 77' 

 

작성 순서

1. SUBSTRING_INDEX 를 이용하여 주소의 앞 부분 두 블럭만 추출한다.

SUBSTRING_INDEX(address, ' ', 2) 👉 '서울특별시 성북구'

 

2. 1번에서 추출한 텍스트를 SUBSTRING_INDEX 를 이용하여 각각 분리 & 추출한다. 앞 부분이 sido가 되고, 뒷 부분이 sigungu가 된다.

SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', 1) 👉 '서울특별시' 

SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', -1) 👉 '성북구'

 

3. sido와 sigungu를 기준으로 그룹핑하여 카페 수를 카운트한다.

 

 

[복습노트] 문자열 다루기 (SUBSTRING, SUBSTRING_INDEX)

https://limhm4907.tistory.com/142

[MySQL] String Functions and Operators (1)

 

 

 

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

WITH season_tab 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
       , measured_at
 FROM measurements
)

SELECT season
     , AVG(pm10) AS pm10_median
     , ROUND(MAX(avg_pm10), 2) AS pm10_average
FROM (
      SELECT *
          , ROW_NUMBER() OVER(PARTITION BY season ORDER BY pm10) AS rn
          , COUNT(*) OVER(PARTITION BY season) AS total_rows
          , AVG(pm10) OVER(PARTITION BY season) AS avg_pm10
      FROM season_tab
      ) AS tmp
WHERE rn IN (FLOOR((total_rows+1)/2), CEIL((total_rows+1)/2))
GROUP BY season

 

중앙값의 경우 DBMS에 내장되어 있는 MEDIAN() 과 같은 함수가 있다면 바로 사용하면 되지만, 내장 함수가 없는 경우 직접 계산하는 과정이 필요하다. 나는 MySQL에서 쿼리를 작성했다. 

 

작성 순서

1. CASE 구문을 활용하여 season 컬럼 생성한 후, WITH문으로 CTE 만들어준다. 

-- 3월 1일부터 5월 31일까지를 ‘spring’
-- 6월 1일부터 8월 31일까지를 ‘summer’
-- 9월 1일부터 11월 30일까지를 ‘autumn’
-- 나머지를 ‘winter’

 

2. 1번에서 만든 테이블에 윈도우 함수를 이용해 아래 3개의 컬럼을 생성한다.

-- (1) season별로 pm10 오름차순으로 랭크 매기기 (윈도우 함수 ROW_NUMBER 이용)

-- (2) season별로 총 행 갯수를 카운트하기 (윈도우 함수 COUNT 집계 이용)

-- (3) season별로 평균 구하기 (윈도우 함수 AVG 집계 이용) -> 여기서 평균을 먼저 구해놓는 이유는 이따 중앙값을 구하기 위해 데이터 필터링을 해야하기 때문에! 

 

3. 2번에서 만든 결과를 FROM 서브쿼리로 넣고 최종 결과를 작성한다.

-- 평균은 2번에서 만든 결과를 그대로 가져온다. 단, GROUP BY로 그룹핑해야 하기 때문에 MAX() 함수를 씌워줌

-- 중앙값은 총 행 갯수가 짝수/홀수인지 관계없이 계산한다. (자세한 내용은 아래 복습 노트 참고)

 

 

[복습노트]

중앙값 구하기

https://limhm4907.tistory.com/260

[SQL] MEDIAN() 없이 중앙값 구하기

 

윈도우 함수 

https://limhm4907.tistory.com/151

[MySQL] 윈도우 함수(Window Functions) - 1