[데이터 분석을 위한 SQL 레시피](작성중)[3장] 데이터 가공을 위한 SQL

이 글은 "데이터분석을 위한 SQL 레시피 (가사키 나가토, 다미야 나오토 지음)"을 읽고 주관적으로 작성된 글입니다.

출처 : https://www.hanbit.co.kr/store/books/look.php?p_code=B8585882565

※ 목차

1. 하나의 값 조작
2. 여러 개의 값 조작
3. 하나의 테이블 조작
4. 여러 개의 테이블 조작

 

1. 하나의 값 조작

• 데이터 가공이 필요한 이유
  1. 데이터가 분석 용도로 상정되지 않은 경우
     • 업무 데이터는 코드값을 저장하고, 다른 테이블에 코드값의 의미를 관리하는 경우가 있음
       - 리포트에서 코드*의 의미를 알 수 없음
     • (참고) 코드
       - 속성에 사용되는 값을 간단하고 알기쉽게 나타낼 수 있도록 약속한 또 다른 값
       - 데이터 일관성 / 데이터 구분 목적 + 저장공간 효율
     • 접근 로그는 여러 정보가 하나의 문자열로 저장될 수 있음
  2. 연산을 위해 비교 가능 상태로 전처리 + 오류 회피
     • 로그 데이터와 업무 데이터를 함께 다룰 경우, 데이터 형식이 일치하지 않을 수 있음
     • 어떤 값에 NULL이 포함되어 NULL과 연산될 수 있음

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

• 코드 값을 그대로 집계에 사용할 경우, 리포트의 가독성이 떨어짐
• 코드값을 레이블로 변경이 필요함
• CASE, WHEN, THEN, ELSE
  
  • CASE
    - 구문의 시작
  • WHEN
    - 코드값
  • THEN
    - 레이블
  • ELSE
    - 디폴트값
  • END
    - 구문의 끝
• 에제 테이블
  - 사용자 마스터(mst_users) 테이블
  - 1:데스크톱, 2:스마트폰, 3:애플리케이션

user_id	register_date	register_device
U001	2016-08-26	1
U002	2016-08-26	2
U003	2016-08-27	3

• 예제 쿼리

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

• 결과

user_id	device_name
U001	데스크톱
U002	스마트폰
U003	애플리케이션

2) URL에서 요소 추출하기

• 최소한의 요건으로 레퍼러, 페이지 URL을 저장하는 경우가 있음
• 저장한 URL을 기반으로 요소들을 추출
• 예제 테이블
  - 접근 로그(access_log) 테이블

RECORD 1
stamp	2016-08-26 12:02:00
referrer	http://www.other.com/path1/index.php?k1=v1&k2=v2#Ref1
url	http://example.com/video/detail?id=001
RECORD 2
stamp	2016-08-26 12:02:01
referrer	http://www.other.net/path1/index.php?k1=v1&k2=v2#Ref1
url	http://example.com/video#ref
RECORD 3
stamp	2016-08-26 12:02:01
referrer	https://www.other.com/
url	http://example.com/book/detail?id=002

• 레퍼러로 어떤 웹페이지를 거쳐 넘어왔는지 판별하기
  • 페이지 단위로 집계하면 밀도가 너무 작아 복잡함
  • 호스트 단위로 집계하는 것이 일반적
  • Hive 또는 BigQuery는 URL을 다루는 함수가 있음

SELECT
    stamp
    -- (http | https:// 제거), (/가 아닌 문자 다수)
    , substring(referrer from 'https?://([^/]*)') AS refferer_host
FROM access_log
;

•  

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

• 쩜
  • 쩜

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

• 쩜
  • 쩜

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

• 쩜
  • 쩜

 

2. 여러 개의 값 조작

1) 문자열 연결하기

• 쩜
  • 쩜

2) 여러 개의 값 비교하기

• 쩜
  • 쩜

3) 2개의 값 비율 계산하기

• 쩜
  • 쩜

4) 두 값의 거리 계산하기

• 쩜
  • 쩜

5) 날짜/시간 계산하기

• 쩜
  • 쩜

6) IP 주소 다루기

• 쩜
  • 쩜

 

3. 하나의 테이블 조작

1) 그룹의 특징 잡기

• 쩜
  • 쩜

2) 그룹 내부의 순서

• 쩜
  • 쩜

3) 세로 기반 데이터를 가로 기반으로 변환하기

• 쩜
  • 쩜

4) 가로 기반 데이터를 세로 기반으로 변환하기

• 쩜
  • 쩜

 

4. 여러 개의 테이블 조작

1) 여러 개의 테이블을 세로로 결합하기

• 쩜
  • 쩜

2) 여러 개의 테이블을 가로로 정렬하기

• 쩜
  • 쩜

3) 조건 플래그를 0과 1로 표현하기

• 쩜
  • 쩜

4) 계산한 테이블에 이름 붙여 재사용하기

• 쩜
  • 쩜

5) 유사 테이블 만들기

• 쩜
  • 쩜