[PostgreSQL] 3. 데이터 가공을 위한 SQL - substring, split_part

 

# 본 게시물은 PostgreSQL 문법으로 공부합니다

 

 

< 1. 코드 값을 레이블로 변경하기 >

 

5-1. 코드를 레이블로 변경하는 쿼리

 

SELECT user_id,
	   CASE WHEN register_device=1 THEN '테스크톱'
	   		WHEN register_device=2 THEN '스마트폰'
	   		WHEN register_device=3 THEN '에플리케이션'
	   		ELSE '' END AS device_name
FROM mst_users

 

 

< 2. URL에서 요소 추출하기 >

 

5-2. url에서 요소 추출하기

 

SELECT stamp,
	   substring(referrer FROM 'https?://([^/]*)') AS referrer_host
FROM access_log

 

 

[ 실행전 ]

 

[ 결과 ]

 

 

https?://

http:// 또는 https://와 일치한다

 

([^/]*)

슬래시가 나타나기 전까지의 모든 문자를 추출한다

-> 도메인 이름 부분 추출

 

 

Question) +와 *의 차이점

• + (플러스):
  • 의미: 바로 앞의 패턴이 1회 이상 반복되어야 함을 의미합니다.
  • 예: a+는 a가 1회 이상 나타나는 경우를 매칭합니다.
  • 예제: a+는 a, aa, aaa 등은 매칭하지만 빈 문자열은 매칭하지 않습니다.
• * (별표):
  • 의미: 바로 앞의 패턴이 0회 이상 반복될 수 있음을 의미합니다.
  • 예: a*는 a가 0회 이상 나타나는 경우를 매칭합니다.
  • 예제: a*는 빈 문자열도 매칭하며, a, aa, aaa도 매칭합니다

 

 

5-3. url경로와 get매개변수에 있는 특정 키 값을 추출하는 쿼리

SELECT stamp,
	   url,
	   substring(url FROM '//[^/]+([^?#]+)') AS PATH,
	   substring(url FROM 'id=([^&]*)') AS id
FROM access_log

 

+ 기호: 정규 표현식에서 +는 앞에 있는 패턴이 최소 1회 이상 반복될 수 있음을 나타냅니다.

 

[^/]

[^/]는 슬래시(/)가 아닌 모든 문자들을 의미합니다

 

 

[ 실행전 ]

 

 

 

[ 실행후 ]

 

 

< 3. 문자열을 배열로 분해하기>

 

 

5-4. url 경로를 슬래시로 분할해서 계층을 추출하는 쿼리

 

SELECT stamp,
	   url,
	   substring(url FROM '//[^/]+([^?#]+)') AS sub_url,
	   split_part(substring(url FROM '//[^/]+([^?#]+)'), '/', 1) AS path1,
	   split_part(substring(url FROM '//[^/]+([^?#]+)'), '/', 2) AS path2,
	   split_part(substring(url FROM '//[^/]+([^?#]+)'), '/', 3) AS path3
FROM access_log