[2] 데이터 가공을 위한 SQL - 하나의 값 조작하기 : case, substring, 정규표현식, split_part, cast, extract, coalesce

 

---

목차

3. 데이터 가공을 위한 SQL

 3-1. 하나의 값 조작하기 (5강)

 3-2. 여러 개의 값에 대한 조작 (6강)

  3-3. 하나의 테이블에 대한 조작 (7강)

4. 매출을 파악하기 위한 데이터 추출

  4-1. 시계열 기반으로 데이터 집계하기

  4-2. 다면적인 축을 사용해 데이터 집약하기

5. 사용자를 파악하기 위한 데이터 추출

  5-1. 사용자 전체의 특징과 경향 찾기

  5-2. 시계열에 따른 사용자 전체의 상태 변화 찾기

  5-3. 시계열에 따른 사용자의 개별적인 행동 분석하기

6. 웹사이트에서 행동을 파악하는 데이터 추출하기

  6-1. 사이트 전체의 특징/경향 찾기

  6-2. 사이트 내의 사용자 행동 파악하기

  6-3. 입력 양식 최적화하기

7. 데이터 활용의 정밀도를 높이는 분석 기술

  7-1. 데이터를 조합해서 새로운 데이터 만들기

  7-2. 이상값 검출하기

  7-3. 데이터 중복 검출하기

  7-4. 여러 개의 데이터셋 비교하기

8. 데이터를 무기로 삼기 위한 분석 기술

  8-1. 검색 기능 평가하기

  8-2. 데이터 마이닝

  8-3. 추천

  8-4. 점수 계산하기

9. 지식을 행동으로 옮기기

  9-1. 데이터 활용의 현장

---

3장 - 5강. 하나의 값 조작하기

데이터를 가공해야 하는 이유  (~66p)

데이터 가공의 목적 중 하나 : 로그 데이터와 업무 데이터를 함께 다루는 경우 형식이 일치하지 않아 연산 시 비교 가능한 형태로 만들기 위함

---

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

 CASE 식

CASE 식 : 기본적이지만, 데이터 변환에 자주 사용된다. 복잡한 구문과 조합되는 경우가 많음

 

create table mst_users

table mst_users

CASE WHEN~THEN~END AS [컬럼명]

--1,2,3 숫자로 할당되어 있는 디바이스명을 name으로 바꿔주기
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;

register device -> device name

---

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

URL 함수

create table access_log

table access_log

 

레퍼러 도메인 추출하기

SUBSTRING ( 문자열, 시작 위치, 길이 )

: 문자열에서 시작위치부터 길이만큼 추출한다. 

*LEFT(문자열, 길이)

*RIGHT(문자열, 길이)

정규표현식 해석

'http?://([^/]*)'

: htp 혹은 http:// 를 포함하고, 슬래쉬(/)를 제외한 모든 문자열을 추출해라. 

-- 레퍼러 도메인 추출 쿼리(postgreSQL기준)
-- 미들웨어에 따라 함수 이름이 다르거나, 정규 표현식이 다르므로 주의
select stamp
, substring(referrer from 'http?://([^/]*)') as referrer_host
from access_log;

레퍼러 도메인이 추출된 모습

 

경로와 매개변수 추출하기

경로 정규표현식 - '//[^/)]+)[^?#]+)'

: '//' 다음의 문자열에서, /또는 ) 문자를 제외한 하나 이상의 문자에 일치하고, ?또는 # 문자를 제외한 모든 문자열

매개변수 정규표현식 - 'id=([^&])*'

: 'id=' 다음의 문자열에서, &문자를 제외한 모든 문자열

-- URL에서 경로와 요청 매개변수 값 추출하기
select stamp
	,url
	, substring(url from '//[^/)]+([^?#]+)') as path
	, substring(url from 'id=([^&])*') as id
from access_log;

path와 매개변수 값이 추출된 모습

---

정규 표현식

<정규 표현식 메타 문자 정리>

** 대소문자는 서로 반대되는 의미를 갖고 있는 경우가 많다.
1. ^ : 문자열의 시작을 표현 
(**1번이 [...] : 대괄호 안에서 쓰일 시, ^뒤에 오는 문자열 패턴과 일치하지 않는 것을 매칭)

ex. ab[^0-9] = ab뒤에 0~9 숫자가 오지 않는 경우  -> abc (O), ab1(X)

2. $ : 문자열의 끝을 표현
ex. tem$ = 문자열이 tem으로 끝난 경우  -> item (O), contemporary (X)

3.₩b: 문자와 공백 사이를 표현
ex. ₩bplay₩b : 단어 앞뒤가 공백인 경우, 즉 단어 그 자체인 경우
-> 'I play the game on the playground' 에서 'playground (X), play(O)

4. ₩B: 반대로 문자열의 일부인 경우 (앞뒤가 공백이 아닌 경우)
ex. play₩B = play 뒤의 단어
-> 'I play the game on the playground' 에서 'playground' (O), play (X)

5. ₩s: 공백인 경우

6. ₩S: 공백이 아닌 경우

7. ₩d: 숫자인 경우

8. ₩D: 숫자가 아닌 경우 (=[^0-9])

9. ₩w: 문자 (영어,숫자,_ 포함) (=[A-Zz-z0-9_]

10. ₩W: 위9번에 포함되지 않는 모든 문자

11. ₩n: 엔터

11. ₩: 정규식 표현식이나, 정규식이 아닌 문자 그대로 사용하고자 할 때 

12. . : 모든 문자 1개 (공백 포함)

13. 선택 패턴 - |('OR'), [...]('대괄호 안의 문자 하나하나가 OR의 요소. ex. [cfh]all -> 'call', 'fall', 'hall' 

14. 수량 한정자 

** 수량한정자는 가능한 한 많은 글자를 포함하기에, '.'처럼 모든 문자를 의미하는 메타 문자와는 가급적 사용하지 않는 것이 좋음

** ex. 'I like apples and bananas' 에서 ^.*s 사용 시, 개수 제한이 없어 "s로 끝나는 가장 긴 문자열"이 되어, 문장 그대로 매칭되어 정규 표현식을 사용하는 의미가 없어짐

 

- ?: 바로 앞 글자가 1개 or 0 개이다. (둘다포함) ex. apples?인 경우, apple인 경우와 apples인 경우 모두 포함

- *: 0개 이상이다. ex. n₩d*= n뒤에 숫자가 0개 이상이다. -> 'n', 'n1', 'n123'

- +: 1개 이상이다. ex. n₩d+= n뒤에 숫자가 1개 이상이다. -> 'n1', 'n123', n(X)

- {n}: n개 있다. ex. n₩d{2} =  n뒤 숫자가 2개 온다, -> 'n12', 'n'(X), 'n123'(X)

- {n,m}: n개 이상, m개 이하, m생략 가능, ex. n₩d{2,3} = 'n12', 'n123', 'n1234'(X)

 

그 외 그룹, 전후방탐색 정규표현식이 있음 ...(너무 많아서 다음에 추가)

---

5-3. 문자열을 배열로 분해하기 (split_part, split)

같은 테이블 access_log에서 url의 슬래쉬 앞뒤 문자를 추출

SPLIT_PART( 문자열, 구분자, 문자열 위치 )

path1은 슬래쉬로 시작하고 나서 2번째 요소 (문자열, '/' , 2)

path2는 슬래쉬로 시작하고 나서 3번째 요소 (문자열, '/', 3)

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

path1, path2

---

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

(날짜/시간 함수, 날짜(date) 자료형, 타임스탬프(timestamp) 자료형, 문자열 함수)

 

현재 날짜/스탬프 추출

--현재 날짜,타임스탬프 추출
select current_date as dt
, localtimestamp as stamp;

 

 

문자열을 날짜/타임스탬프로 변환하기

1. CAST( [컬럼명] AS 변환하고자 하는 Data Type)

2. [컬럼명] : Data Type

-- 문자열을 날짜/타임스탬프로 변환하기
select 
cast('2016-01-30' as date) as dt
, cast('2016-01-30' 12:00:00 as timestamp) as stamp
--or 'value::type'사용
-- '2016-01-30'::date AS dt
-- '2916-01-30' 12:00:00'::timestamp AS stamp;

 

 

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

EXTRACT(year, month, day, hour, minute, second) from [날짜요소])

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;

year~hour 추출

 

substring(문자열,시작위치,길이)을 활용한 날짜 추출

* substring으로 사용할 경우, 문자열 형식이어야 하기 때문에, 날짜 형식을 cast함수로 text로 바꿔줘야 함

select stamp
, substring(stamp, 1,4) as year
, substring(stamp, 6,2) as month
, substring(stamp, 9,2) as day
, substring(stamp, 1,7) as year_month
from
	(select cast('2016-01-30 12:00:00' as text) as stamp) as t;

substring 활용, 같은 결과

---

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

create table purchase_log_with_coupont

table purchase_log_with_coupon

구매액에서 DISCOUNT금액을 뺀 값을 구하고, DISCOUNT가 없는 경우 0으로 간주하고 계산

COALESCE 함수([컬럼명1], [컬럼명2], ...) : 두 컬럼을 합치는 기능, NULL 값 처리 시 자주 쓰임

-- 구매액에서 할인 쿠폰 값을 제외한 매출 금액 구하기
select purchase_id
, amount
, coupon
, amount - coupon as discount_amount1
, amount - COALESCE(coupon, 0) as discount_amount2
from 
	purchase_log_with_coupon;

discount_amount2가 coalesce함수가 적용된 모습

---

6강. 여러 개의 값에 대한 조작 이어서