문자열을 통해 값 조작하기

목차

1. 코드 값을 레이블로 변경하는 법

 

2. URL에서 경로와 요청 매개변수 값 추출하는 법

 

3. 날짜와 타임스탬프 다루는 법

 

4. 결손 값을 디폴트 값으로 대치하는 법

 

5. CTR의 비율 계산하기

 

실습에 필요한 테이블

mst_users 테이블

access_log 테이블

purchase_log_with_coupon 테이블

advertising_stats 테이블

코드 값을 레이블로 변경하는 법

• 코드 값을 레이블로 변경하기 위해ㅇ서 CASE WHEN <조건식> THEN <조건 만족값>을 이용

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

 

 

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

• 분석 현장에서는 로그 조건과 분석 요건을 확인하지 못하고 URL로 저장하는 경우가 있음
• substring 함수를 통해 문자열 추출
• 정규 표현식으로 //로 시작하고 / 문자 이후에 오는 문자열을 매치하며, 해당 문자열에서 ? 또는 # 문자가 나오기 전까지의 모든 문자를 캡처

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

 

 

날짜와 타임스탬프  다루는 법

• 현재 날짜와 타임 스탬프를 추출하는 법
• PostgreSQL에서는 CURRENT_TIMESTAMP 리턴 값을 타임스탬프 자료형이라 함
• 하지만 PostgreSQL 외의 미들웨어는 타임존 없는 타임스탬프를 리턴하기에 LOCALTIMESTAMP를 써주는 것이 좋음

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

 

 

결손 값을 디폴트 값으로 대치하는 법

• 구매액에서 할인 쿠폰 값을 제외한 매출 금액을 구하는 쿼리
• COALESCE 함수를 통해 coupon값이 NULL인 경우는 0으로 대체 
• NULL값과 함께 계산을 하게되면 다른 값들도 NULL로 나와 쿼리 결과가 이상하게 나올 수 있기 떄문

SELECT
	purchase_id
	, amount
	, coupon
	, amount - coupon AS discount_amount1
	, amount - COALESCE(coupon, 0) AS discount_amount2
FROM
	purchase_log_with_coupon;

 

 

CTR의 비율 계산하기

• CTR = 클릭 수 / 노출 수
• CAST 함수를 사용해 clicks를 double precision으로 변환 후에 계산
• double precision으로 cast하는 이유는 표현할 수 있는 수가 더 많고 데이터, 파일 크기에 유리하다는 점

SELECT 
	dt
	, clicks
	, clicks / impressions AS ctr
	, CAST(clicks AS double precision) / impressions AS ctr
	, 100.0 * clicks / impressions AS ctr_as_percent
FROM 
	advertising_stats
WHERE 
	dt = '2017-04-01'
ORDER BY 
	dt
    	, ad_id;