[SQL_ Q] 전국 매장 개수

문제

전국 도서관 데이터베이스는 2025년 3월 기준으로 서울에서 제주까지 전국 각지에 있는 도서관의 기본 정보를 담고 있습니다.

이 중 address 컬럼에는 주소 정보가 들어가 있으나 정제가 되어 있지 않아 활용이 어렵습니다. 이 데이터를 각각 시, 도 정보를 담는 sido 컬럼과 시, 군, 구 정보를 담는 sigungu 컬럼으로 정제하고, 각 행정구역별로 몇 개의 도서관이 있는지 집계하는 쿼리를 작성해주세요.

예를 들어,

'경기도 성남시 분당구 야탑로 22'라는 데이터는 sido 값이 '경기도', sigungu 값이 '성남시'로 정제되어야 합니다.

'대구광역시 수성구 신천동 123'라는 데이터는 sido 값이 '대구광역시', sigungu 값이 '수성구'로 정제되어야 합니다.

조건

sido: 시/도 정보 (예시: '대전광역시', '강원특별자치도', '충청남도')

sigungu: 시/군/구 정보 (예시: '성남시', '성동구')

cnt: 해당 행정구역에 포함된 도서관 수

| sido | sigungu | cnt |
| ---- | ------- | --- |
| 경기도  | 수원시     | 432 |
| 경상남도 | 창원시     | 378 |
| …    | …       | …   |

내 쿼리 1

SELECT DISTINCT IF(Regexp_like(address, "[가-힣]+[도]"), REGEXP_SUBSTR(address, "[가-힣]+[도]"), REGEXP_SUBSTR(address, "[가-힣]+(시|군)")) sido, IF(Regexp_like(address, "[가-힣]+[도]"), REGEXP_SUBSTR(address, "[가-힣]+(시|군)"), REGEXP_SUBSTR(address, "[가-힣]+[구]")) sigungu, count(cafe_id) as cnt from cafes group by 1,2 order by 3 DESC

결과가 이상하게 나왔다.

sido : 대구광역시
sigungu : 대구

쿼리 2

SELECT DISTINCT
-- 시도 추출
CASE
    WHEN REGEXP_LIKE(address, "[가-힣]+광역시") THEN REGEXP_SUBSTR(address, "[가-힣]+광역시")
    WHEN REGEXP_LIKE(address, "[가-힣]+도") THEN REGEXP_SUBSTR(address, "[가-힣]+도")
    ELSE REGEXP_SUBSTR(address, "[가-힣]+시")
END AS sido,

-- 시군구 추출
CASE
    WHEN REGEXP_LIKE(address, "[가-힣]+광역시[ ]+[가-힣]+구") THEN REGEXP_SUBSTR(address, "(?<=광역시 )[가-힣]+구")
    WHEN REGEXP_LIKE(address, "[가-힣]+도[ ]+[가-힣]+(시|군)") THEN REGEXP_SUBSTR(address, "(?<=도 )[가-힣]+(시|군)")
    ELSE REGEXP_SUBSTR(address, "[가-힣]+구")
END AS sigungu,

COUNT(cafe_id) AS cnt
FROM cafes
GROUP BY 1,2
ORDER BY cnt DESC;

sido 상도
sigungu 동작구
address 서울특별시 동작구 상도로 123-1
여기서 또 걸린다.

정답 쿼리

SELECT DISTINCT
-- 시도 추출
SUBSTRING_INDEX(address, ' ', 1) AS sido,
-- 시군구 추출

substring_index(SUBSTRING_INDEX(address,' ', 2)," ",-1) AS sigungu,
COUNT(cafe_id) AS cnt
FROM cafes
GROUP BY 1,2
ORDER BY cnt DESC;