[SQL 실습] URL 분석에 자주 사용되는 SQL 패턴 정리

 

1. 도메인 추출하기

-- example.com 부분만 추출
SELECT SUBSTRING(url FROM '://([^/]+)') as domain
FROM url_table;

-- www.example.com에서 example.com만 추출
SELECT SUBSTRING(url FROM '(?:https?://)?(?:www\.)?([^/]+)') as clean_domain
FROM url_table;

 

'://([^/]+)' 패턴 분석:

1. ://
   • URL에서 'http://' 또는 'https://' 다음에 오는 '://' 부분을 찾습니다
2. ( )
   • 괄호는 "캡처 그룹"을 만듭니다
   • 이 부분이 실제로 추출하고 싶은 부분입니다
3. [^/]
   • [ ]: 문자 집합을 정의
   • ^: NOT을 의미
   • [^/]는 "슬래시(/)가 아닌 모든 문자"를 의미
4. +
   • "하나 이상의" 반복을 의미
   • [^/]+는 "슬래시가 아닌 문자가 하나 이상" 있다는 뜻

---

2. 경로(path) 추출하기

-- /path/to/page 부분만 추출
SELECT SUBSTRING(url FROM '/[^?#]+') as path
FROM url_table;

 

URL: https://example.com/products/shoes?color=red#details → 추출되는 부분: /products/shoes

이유:
- '/' (첫 슬래시)부터 시작해서
- '?' 나 '#' 가 나오기 전까지의 모든 문자를 추출

---

3. 쿼리 파라미터 추출하기

-- ?id=123&page=2 부분 추출
SELECT SUBSTRING(url FROM '\?([^#]+)') as query_params
FROM url_table;

-- 특정 파라미터 값만 추출 (예: id값만)
SELECT SUBSTRING(url FROM 'id=([^&]+)') as id_param
FROM url_table;

 

4. URL 구성요소별 분리

SELECT 
    SUBSTRING(url FROM '.*://([^/]+)') as domain,
    SUBSTRING(url FROM '/[^?#]+') as path,
    SUBSTRING(url FROM '\?([^#]+)') as query,
    SUBSTRING(url FROM '#(.+)$') as fragment
FROM url_table;

 

정규표현식 패턴 설명:

• [^/]: '/'가 아닌 모든 문자
• [^?#]: '?'와 '#'이 아닌 모든 문자
• +: 앞의 패턴이 1회 이상 반복
• .*: 모든 문자가 0회 이상 반복
• (): 그룹화
• ?:: 비캡처링 그룹 (결과에 포함하지 않음)

 

예시 URL: https://www.example.com/products/shoes?color=red&size=9#details

    SUBSTRING(url FROM '.*://([^/]+)') as domain,
    SUBSTRING(url FROM '/[^?#]+') as path,
    SUBSTRING(url FROM '\?([^#]+)') as query,
    SUBSTRING(url FROM '#(.+)$') as fragment

domain: http://www.example.com
path: /products/shoes
query: color=red&size=9
fragment: details

 

---

split_part 함수 사용법

 

split_part(string, delimiter, position)

split_part(분리하려는 문자열, 구분자 (예: ',', '-', '/' 등) , 추출하려는 위치 (1부터 시작) )

 

SELECT split_part('2024-03-31', '-', 1) as year,  -- 결과: 2024
       split_part('2024-03-31', '-', 2) as month, -- 결과: 03
       split_part('2024-03-31', '-', 3) as day;   -- 결과: 31