[데이터 분석을 위한 SQL 레시피] 5강 - 하나의 값 조작하기

목차

1) 코드 값을 레이블로 변경하기

2) URL에서 요소 추출하기

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

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

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

 

1) 코드 값을 레이블로 변경하기

코드 값 (1:데스크톱, 2: 스마트폰, 3: 애플리케이션)으로 저장되어 있을 때 레이블로 변경하기

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

SELECT
	user_id
	,CASE
		WHEN register_device =1 THEN '데스크톱'
		WHEN register_device =2 THEN '스마트폰'
		WHEN register_device =3 THEN '애플리케이션'
--디폴트 값을 지정할 경우 ELSE 구문을 사용
--ELSE ''
	END AS device_name
FROM mst_users
;

 

2) URL에서 요소 추출하기

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

페이지 단위가 아니라, 호스트 단위로 집계하기.

# 5-2 레퍼러 도메인을 추출하는 쿼리

SELECT
	stamp
--referrer의 호스트 이름 부분 호출하기
--PostgreSQL의 경우 substring함수와 정규표현식 사용하기
,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

 

- 정규표현식

substring(추출하려는 대상 from 추출 시작할 위치)

추출 시작할 위치가 정수(ex 5)라면 5번째 문자열 부터 추출하고

정규표현식이면 정규표현식이 의미하는 그대로 추출

 

https?://([^/]*)

() : 캡처 그룹, 일치하는 부분을 추출

s? : s가 있을수도 있고, 없을수도 있음.

([^/]) : / 가 아닌 어떤 문자

*: 직전의 패턴([^/])이 0회 이상 반복될 수 있음.

http:// 또는 https:// 뒤에 /가 아닌 문자가 반복되는 부분을 추출

//[^/]+([^?#]+)

// 뒤에 /가 아닌 문자가 1회 이상 반복

+: 직전의 패턴이 1회 이상 반복될 수 있음.

[^?#] : ? or # 문자를 제외한 어떤 문자

id=([^&]*)

id= 뒤에 &가 아닌 문자가 0회 이상 반복되는 부분을 추출

 

 

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

어떤 상품이 열람되는지 특정하는 id를 데이터로 따로 저장해두지 않은 경우, url을 로그데이터로 저장해두었다면 경로 가공해서 상품 리포트 만들 수 있음

# 5-3 URL 경로와 GET 매개변수에 있는 특정 키 값을 추출하는 쿼리

SELECT
	stamp
	,url
--URL 경로 또는 GET 매개변수의 id 추출하기
--PostgreSQL의 경우 substring 함수와 정규 표현식 사용하기
	,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

 

 

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

영어 문장을 공백으로 분할해서 하나하나의 단어로 구분하는 경우, 쉼표로 연결된 데이터를 잘라 하나하나의 값을 추출하는 경우

# URL 경로를 슬래시로 분할해서 계층을 추출하는 쿼리

SELECT 
	stamp
	,url
--경로를 슬래시로 잘라 배열로 분할하기
--경로가 반드시 슬래시로 시작하므로 2번째 요소가 마지막 계층
--PostgreSQL의 경우 split_part로 n번째 요소 추출하기
	,split_part(substring(url from '//[^/]+([^?#]+)'), '/',2) AS path 1
	,split_part(substring(url from '//[^/]+[^?#]+)'),'/',3) AS path 2
FROM access_log
;

#실행결과
stamp     |    url    | path1   |  path2
-------------------------------------------
2016-08-26 12:02:00 | http://www.example.com/video/detail?id=001 | video | detail

 

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

• 현재 날짜와 타임스탬프 추출하기 - 미들웨어에 따라 추출 방법이 다름.

#현재 날짜와 타임스탬프를 추출하는 쿼리

SELECT
--PostgreSQL, Hive, BigQuery의 경우
--Current_datae상수와 CURRENT_TIMESTAMP 상수 사용하기
	CURRENT_DATE AS dt
	,CURRENT_TIMESTAMP AS stamp
;

# 실행결과
dt     |    stamp
-----------------------
2017-01-30 | 2017-01-30 18:42:57.584993

 

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

CAST 함수 사용하는 것이 가장 범용적.

→ SELECT CAST(컬럼명 as 지정할 데이터 타입) from 테이블명

# 5-6 문자열을 날짜 자료형, 타임스탬프 자료형으로 변환하는 쿼리

SELECT
--모든 미들웨어 'CAST(value AS type)' 사용하기
	CAST('2016-01-30' AS date) AS dt
	,CAST('2016-01-30 12:00:00' AS timestamp) AS stamp

-- 모든 미들웨어 'type value' 사용하기
-- 다만, value는 상수이므로 컬럼이름으로 지정할 수 없음
	date '2016-01-30' AS dt
	, timestamp '2016-01-30 12:00:00' AS stamp

#실행 결과
dt    |  stamp
-------------------
2016-01-30 | 2016-01-30 12:00:00

 

 

• 날짜/시각에서 특정 필드 추출하기

타임스탬프 자료형의 데이터에서 년과 월 등의 특정 필드 값을 추출할 때는 EXTRACT 함수를 사용함.

# 5-7 타임스탬프 자료형의 데이터에서 연,월,일 등을 추출하는 쿼리 

SELECT 
	stamp
--postgreSQL의 경우 EXTRACT 함수 사용하기
	,EXTRACT(YEAR FROM stamp) AS year
	,EXTRACT(MONTH FROM stamp) AS month
	,EXTRACT(DAY FROM stamp) AS day
	,EXTRACT(HOUR FROM stamp) AS HOUR

FROM
(SELECT CAST('2016-01-30 12:00:00' AS timestamp) AS stamp) AS t
;


# 실행결과
  stamp      |   year   |  month  | day   |   hour
--------------------------------------------------------------
2016-01-30 12:00:00 | 2016 | 1  | 30 |12

 

 

타임스탬프를 단순한 문자열로 취급해서 문자열 조작을 통해 필드 추출할 수도 있음. - substring 함수 사용해서→ 연과 월 동시 추출하여 월별 리포트 만들 때 많이 사용

substring(컬럼명, 시작인덱스(1부터), 시작인덱스로부터 몇개뽑을지) = substring(string, start, length)

# 5-8 타임스탬프를 나타내는 문자열에서 연,월,일 등을 추출하는 쿼리

SELECT
	stamp 
--postgresql은 substring, substr 함수 사용
	,substring(stamp,1,4) AS year
	,substring(stamp,6,2) AS month
	,substring(stamp,9,2) AS day
	,substring(stamp,12,2) AS hour
-- 연과 월을 함께 추출하기
	,substring(stamp,1,7) AS year_month

FROM
-- postgresql의 경우 문자열 자료형으로 text 사용하기
(SELECT CAST('2016-01-30 12:00:00' AS text) AS stamp) AS t
;

❗ 날짜와 시간 정보는 로그 데이터에 빠지지 않는 정보. 타임존 고려, 미들웨어들의 차이 주의!

 

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

문자열과 숫자 다룰 때 중간의 NULL 주의하기! null과 문자열 결합하면 null, 사칙연산해도 null..

COALESCE 함수 → parameter를 순차적으로 확인하여 앞에 값이 null이면 뒤에 값을 사용하고, 만약 해당값도 null이면, 그 뒤에 들어온 값을 사용한다.

+ifnull vs nullif

ifnull(1,2) : 1이 null 이면 2 반환 -> coalesce 함수와 같은 기능. coalesce는 1,2 뿐만 아니라 여러 값을 넣을 수 있음

nullif(1,2) : 1과 2가 같으면 null 반환 -> nullif(impression, 0) : impression이 0이면 null 반환

# 5-9 구매액에서 할인 쿠폰 값을 제외한 매출 금액을 구하는 쿼리

SELECT
	purchase_id
	,amount
	,coupon
	,amount-coupon AS discount_amount1
	,amount-COALESCE(coupon, 0) discount_amount2  #coupon이 null일 때 coalesce함수를 사용해 0으로 대치.
FROM
	purchase_log_with_coupon
;


#실행결과
purchase_id |  amount  |  coupon  | discount_amount1 | discount_amount2
----------------------------------------------------------------------------
10001       |  3280    | null    |       null        | 3280