[TIL163] solvesql - 전국 카페 주소 데이터 정제하기

문제 설명

카페 주소(address) 컬럼에서

시·도(sido)
시·군·구(sigungu)

를 분리한 뒤,

행정구역별 카페 개수를 집계하는 문제.

---

 

내가 작성한 쿼리

WITH a AS (

    SELECT
        name,
        SUBSTRING_INDEX(address,' ',1) AS sido,
        SUBSTRING_INDEX(address,' ',2) AS sigungu
    FROM cafes
)
SELECT
    sido,
    sigungu,
    COUNT(*) AS cnt
FROM a
GROUP BY
    sido,
    sigungu
ORDER BY cnt DESC;

---

왜 틀렸는가?

문제는

SUBSTRING_INDEX(address,' ',2)

부분.

---

예시

경기도 성남시 분당구 구미로 11

---

현재 결과

SUBSTRING_INDEX(address,' ',2)

↓

경기도 성남시

---

하지만 문제 요구사항은

성남시

만 추출해야 함.

---

 

-- 시도 sido, 시군구 sigungu 별로 몇 개의 카페가 있는지 cnt
-- 카페가 가장 많은 행정구역 순으로 출력 cnt desc
WITH a as (SELECT name,  
substring_index(address,' ',1) as sido,
substring_index(
  substring_index(address,' ',2), ' ', -1) as sigungu
from cafes
)
SELECT sido, sigungu, count(*) as cnt
from a 
group by sido, sigungu
order by cnt desc

---

핵심 함수

SUBSTRING_INDEX()

SUBSTRING_INDEX(문자열,구분자,개수)

 

- 첫 번째 단어 추출

SUBSTRING_INDEX(col,' ',1)

 

- 마지막 단어 추출

SUBSTRING_INDEX(col,' ',-1)

---

암기 포인트

주소 데이터 정제 문제에서

시도
시군구
동

를 분리해야 하면

가장 먼저 떠올릴 함수

SUBSTRING_INDEX()

---

자주 쓰는 패턴

첫 단어

SUBSTRING_INDEX(col,' ',1)

마지막 단어

SUBSTRING_INDEX(col,' ',-1)

두 번째 단어

SUBSTRING_INDEX(
    SUBSTRING_INDEX(col,' ',2),
    ' ',
    -1
)

---

한 줄 요약

이 문제는 주소 문자열에서 sido, sigungu를 분리한 뒤 집계하는 문자열 처리 문제이다. SUBSTRING_INDEX()를 중첩해서 사용하면 특정 위치의 단어를 추출할 수 있으며, 특히 "두 번째 단어 추출" 패턴은 실무 주소 정제에서 자주 사용된다.