[Day 19] solvesql | Advent of SQL 2024 | 전국 카페 주소 데이터 정제하기💡

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

 

✔️0213 복습

문제 조건 

1. 주소 정보에서 (시, 도) 정보와 (시, 군, 구) 정보를 추출하여 각 행정구역 별로 카페의 개수를 집계

2. 카페 개수가 많은 행정구역 순으로 출력

 

Solution

-- 최종 : 익숙해지면?!
SELECT SUBSTR(address, 1, INSTR(address, ' ')-1) AS sido -- '서울특별시 종로구' -> '서울특별시'
     , SUBSTR(SUBSTR(address, INSTR(address, ' ')+1), 1, INSTR(SUBSTR(address, INSTR(address, ' ')+1), ' ')-1) AS sigungu -- '종로구 ~'
     , COUNT(cafe_id) AS cnt
FROM cafes
GROUP BY sido, sigungu
ORDER BY cnt DESC

WITH T1 AS (
  SELECT cafe_id,
         address,
         INSTR(address, ' ') as point
  FROM cafes
  ), T2 AS (
  SELECT cafe_id,
         address,
         SUBSTR(address, 1, point-1) as sido,
         SUBSTR(address, point+1) as rest,
         INSTR(SUBSTR(address, point+1), ' ') as point2
  FROM T1
  ), T3 AS (
  SELECT cafe_id,
         sido,
         SUBSTR(rest, 1, point2-1) as sigungu
  FROM T2
  )
  SELECT sido,
         sigungu,
         COUNT(cafe_id) as cnt 
  FROM T3
  GROUP BY 1, 2
  ORDER BY 3 DESC
  
  /*
  WITH step1 AS (
  SELECT cafe_id
       , address
      --  , INSTR(address, ' ')
       , LOCATE(' ', address) AS p1
      --  , LOCATE(' ', address, 9)
  FROM cafes

), step2 AS (
  SELECT cafe_id
       , address
       , SUBSTR(address, 1, p1-1) AS sido --  [SUBSTR|SUBSTRING](문자열, 시작 위치, 추출할 문자의 길이)
       , SUBSTRING(address, p1+1) AS rest
       , LOCATE(' ', SUBSTRING(address, p1+1)) AS p2
  FROM step1

), step3 AS (
  SELECT cafe_id
       , address
       , sido
       , SUBSTR(rest, 1, p2-1) AS sigungu
  FROM step2

)
SELECT sido
     , sigungu
     , COUNT(cafe_id) AS cnt
FROM step3
GROUP BY sido, sigungu
ORDER BY cnt DESC
  */

 

▶ T1 CTE:

주소 정보는 다음과 같이 저장되어있다.

 

공백을 기준으로 (시, 도) 정보와 (시, 군, 구) 정보를 추출할 수 있기 때문에 instr, substr 함수를 적절히 활용하여 접근한다. instr 함수는 내가 찾고자 하는 특정 문자열의 첫 번째 발생 위치를 반환한다.

 

▶ T2 CTE:

- substr(문자열, 시작 위치, 추출할 문자의 길이)를 적절히 활용한다.

- "첫 공백 위치 - 1" 만큼을 추출할 문자의 길이로 입력해야 (시, 도) 정보를 정확히 추출할 수 있다. 

- instr 함수는 특정 문자열의 첫 번째 발생 위치를 반환하기 때문에, 이 다음으로 (시, 군, 구) 정보를 추출하기 위해 (시, 도) 정보를 제외한 주소를 별도로 저장하는 단계를 거친다.

- (시, 도) 정보를 제외한 주소 정보의 문자열에서 다시 등장하는 첫 공백 위치를 저장한다.

 

▶ T3 CTE:

point2를 이용하여 (시, 군, 구) 정보를 추출한다.

 

▶ 최종 SELECT:

각 행정구역 별로 카페의 개수를 집계하여 조건에 맞게 정렬한다.

 

 

INSTR() vs LOCATE()

 

💡INSTR(str, substr) /  LOCATE(substr, str)

인수 2개일 때는 str내에서 찾고자 하는 substr이 처음 등장하는 인덱스를 반환하여 동작하는 원리가 같다. 다만, 인수의 위치가 반대라는 점이 다르다.

SELECT address
     , INSTR(address, ' ')
     , LOCATE(' ', address)
FROM cafes
LIMIT 10

 

✅LOCATE(substr, str, pos)

추가적으로 LOCATE 함수는 pos 인수 하나를 더 추가하여, pos 이후부터 원하는 문자열을 찾을 수 있다.

SELECT address
     , INSTR(address, ' ')
     , LOCATE(' ', address)
     , LOCATE(' ', address, 9)
FROM cafes
LIMIT 10