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

💡 ※ 데이터 가공의 필요성

다룰 데이터가 데이터 분석 용도로 상정되지 않은 경우

• 데이터 베이스엔 코드 값만 저장하고, 다른 테이블에서 코드값의 의미 관리하는 경우 있음! 이 경우 리포트 작성 시 어려움을 겪을 수 있다! → 데이터 분석에 적합한 형태로 미리 가공하기

연산할 때 비교 가능한 상태로 만들고 오류를 회피하기 위한 경우

• 로그 데이터와 업무 데이터의 형식이 일치하지 않을 수 있음!
• 어떤 값과 NULL 값을 연산할 때 , NULL 이 나오는 경우 있음! 이 경우 원래 어떤 값의 정보가 날아가므로, 미리 데이터를 가공해 NULL이 발생하지 않도록 하기! 

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

사용할 데이터 : register_device ( 1 : 데스크톱, 2: 스마트폰, 3: 어플리케이션)

집계할 때, 코드 값을 미리 레이블로 변경 → CASE 문 이용

CASE WHEN <조건식> THEN <조건을 만족할 때의 값> END

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;

*데이터 변환에 CASE문 많이 사용되므로 반드시 기억!

2. URL에서 요소 추출하기

접근 로그 테이블 - 최소한의 요건으로 레퍼러와 url을 저장해둔 것!

 

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

페이지 단위로 집계하면 밀도가 너무 작아 복잡 → 호스트 단위로 집계

• 이때, 정규표현식으로 호스트 이름의 패턴 추출해야 함! 

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

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

• path 변수로 호스트 단위 다음 주소 추출

-id 변수로 ‘?id = 001’ 부분에서 숫자만 추출

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

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

• 문자열 자료형은 범용적이므로, 더 세부적으로 분해해야 함!
  • ex) 영어 문장을 공백으로 분할해서 하나하나의 단어로 구분, 쉼표로 연결된 데이터를 잘라 하나하나의 값을 추출
• 로그 샘플을 기반으로 페이지 계층 나누기 → URL경로를 슬래시로 분할
• split_part 를 이용해 n번째 요소 추출하기!

# 경로를 슬래시로 잘라 배열로 분할하기
# 경로가 반드시 슬래시로 시작하므로 2번째 요소가 마지막 계층
SELECT 
stamp, url, split_part(substring(url from '//[^/]+([^?#]+)'),'/',2) as path1, 
split_part(substring(url from '//[^/]+([^?#]+)'),'/',3) as path2
FROM access_log;

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

현재 날짜와 타임스탬프 추출하기

• PostgreSQL에서는 CURRENT_TIMESTAMP의 리턴 값으로 타임존이 적용된 타임스탬프 자료형이 나옴!
• 리턴값의 자료형을 맞추기 위해서는 LOCALTIMESTAMP를 사용하는 것이 좋다!

SELECT
CURRENT_DATE AS dt, CURRENT_TIMESTAMP AS stamp, LOCALTIMESTAMP as lcstamp;
# 타임존을 적용하고 싶지 않으면 LOCALTIME STAMP 사용

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

• 현재 시각이 아닌 문자열로 지정한 날짜와 시각을 기반을 날짜 자료형과 타임 스탬프 자료형 데이터 만들기
• CAST(value AS type) 함수 사용!

# 문자열을 날짜 / 타임스탬프로 변환하기
# CAST(value AS type)
SELECT 
	CAST('2016-01-30' AS date) AS dt, CAST('2016-01-30 12:00:00' AS timestamp) AS stamp;

• type value사용

SELECT 
	date '2016-01-30' AS dt, timestamp '2016-01-30 12:00:00' AS stamp;

• value::type 사용

SELECT 
	'2016-01-30'::date AS dt, '2016-01-30 12:00:00'::timestamp AS stamp;

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

• timestamp 자료형에서 특정 필드값 (년/월) 추출하기! → EXTRACT 함수 사용

SELECT 
	stamp
,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;

❓ AS t 해주는 이유: PostgreSQL 에서는 FROM 절 서브쿼리에 alias 를 필히 사용해야한다.

FROM 절 내의 subquery 에는 반드시 alias 를 가져야만 함!

AS t 안해주면 이런 오류 나옴!

• timestamp를 단순한 문자열처럼 취급해서 문자열 조작으로 필드 추출 →SUBSTRING 함수 사용

SELECT 
	stamp
,substring(stamp, 1, 4) AS year # 1부터 4개
,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 (SELECT CAST('2016-01-30 12:00:00' AS text) AS stamp) AS t;
# 문자열 자료형으로 text 사용!

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

• NULL이 들어가 있는 경우, NULL과 문자열 결합 → NULL / NULL과 숫자 사칙연산 → NULL

여기서 빈 값이 NULL !

구매액에서 할인 쿠폰 값을 제외한 매출 금액 구하기

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

discount_amount1은 price에서 coupon을 그대로 뺀 값( NULL값 유지), discount_amount2는 price에서 coupon을 뺄 때 NULL 값은 0으로 간주한 것

❓ COALESCE(column1, column2,...) : 첫번째 인자부터 차례대로 확인하는데, 처음으로 NULL이 아닌 값을 만나면 그 값을 리턴하는 함수 → coupon이 NULL값이면 0을 출력하도록!