postgreSQL

3장 데이터 가공을 위한 SQL

5강 하나의 값 조작하기

2. URL에서 요소 추출하기

sql 정규표현식

레퍼러로 어떤 웹 페이지를 거쳐 넘어왔는지 판별하기

코드 5-2

select stamp
	 , substring(referrer from 'https?://([^/]*)') as referrer_host
from access_log

stamp	referrer_host
2016-08-26 12:02:00	www.other.com
2016-08-26 12:02:01	www.other.net
2016-08-26 12:02:01	www.other.com

https?://는 referrer 문자열의 시작 부분에서 "http://" 또는 "https://"와 일치합니다.
?는 앞 글자를 선택사항으로 간주. For example, the pattern ab?c will match either the strings "abc" or "ac" because the b is considered optional.

([^/]*)는 슬래시가 아닌(^) 모든 문자와 일치하며 그룹에서 캡처
substring( ) 함수는 SUBSTRING(string FROM start FOR length) 또는 SUBSTRING(string, start, length) (링크)

URL에서 경로와 요청 매개변수 값 추출하기

코드 5-3

select stamp
	 , url
	 , substring(url from '//[^/]+([^?#]+)') as path
	 , substring(url from 'id=([^&]*)') as id
from access_log

stamp	url	path	id
2016-08-26 12:02:00	http://www.example.com/video/detail?id=001	/video/detail	001
2016-08-26 12:02:01	http://www.example.com/video#ref	/video
2016-08-26 12:02:01	http://www.example.com/book/detail?id=002	/book/detail	002

[^/] 는 슬래시가 아닌 하나 이상의 문자를 일치
([^?#]+)는 ?(물음표)나 #(해시기호)가 아닌 하나 이상의 문자를 일치

id=([^&]*)는 "id=" 이후부터 "&"가 나오기 전까지의 문자열을 추출

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

sql split_part함수, split함수

코드 5-4

select stamp
	 , url
     , substring(url from '//[^/]+([^?#]+)') as path
	 , split_part(substring(url from '//[^/]+([^?#]+)'), '/', 2) as path1
	 , split_part(substring(url from '//[^/]+([^?#]+)'), '/', 3) as path2
from access_log

stamp	url	path	path1	path2
2016-08-26 12:02:00	http://www.example.com/video/detail?id=001	/video/detail	video	detail
2016-08-26 12:02:01	http://www.example.com/video#ref	/video	video
2016-08-26 12:02:01	http://www.example.com/book/detail?id=002	/book/detail	book	detail

split_part( ) 함수는 SPLIT_PART(string, delimiter, position) 로 문자열, 구분, 순서(1부터) (링크)

4. 날짜와 타임스탬프 다루기

지정한 값의 날짜/시각 데이터 추출하기

코드 5-6

SELECT cast('2016-01-30' AS date) AS dt 
     , cast('2016-01-30 12:00:00' AS timestamp) AS stamp

CAST( ) 함수는 CAST(value AS type) 로 여기서는 문자열을 날짜, 타임스탬프 자료형으로 변환 (링크)

5. 결손 값(NULL)을 디폴트 값으로 대치하기

코드 5-9

COALESCE( ) 함수는 returns the first non-null argument (링크)