MySQL "데이터 분석을 위한 SQL 레시피" - part1 값 조작하기

SQL 역량을 키워보고자 책을 통해 공부하기로 마음먹었고, 기왕이면 실무와 가까운 SQL 쿼리문을 을 다뤄보고자 도서 '데이터분석을 위한 SQL 레시피'를 구매하여 공부하고 있습니다. 또한 배운 내용을 앞으로도 그대로 활용하기 위해 개인적으로 정리해보고자 공부 티스토리에 올리게 되었습니다.

 

 

 

 

(참고사항)

혹시나 오해없으실까 말씀드리지만, 이 책에서는 MySQL 에 대한 실습코드를 제공하지 않습니다!

 

상호성을 갖는 SQL 인터페이스, 즉 실무에서 많이 다루는 SQL 인 postageSQL, Apache Hive, Amazon Redshift, BigQuery, SparkSQL을 주로 다루고 있고 이에 대한 실습 코드에 대해서는 실습 코드가 세분화되어 설명되어 있지만, 저는 혼자 공부해봤던 MySQL 과 workbench 환경이 이미 있기도 하고, 머리를 써야 내 것이 된다는 생각에 코드를 최대한 자의적으로 수정해가며 작성중에 있습니다.

 

 

 

 

1. 코드값을 레이블로 변경 (CASE, WHEN)

 

분석에 사용되는 데이터중 일부는 분석 용도로 수집된 데이터가 아닐 수 있으므로, 이를 활용한 분석이나 보고서 작성에 문제가 생기거나 자칫 가독성을 떨어뜨릴 수 있다. 예를들어 업무 데이터로 저장된 데이터를 그대로 사용할 경우, 우리가 직관적으로 이해할 수 있는 레이블 값이 아닌 코드값이 추출 및 분석에 활용되어 차후 결과 해석에 어려움을 겪을 수 있다.

 

ex)  데이터베이스 저장방식이 아래와 같을때, 홍삼의 카테고리에 대한 추출 결과가 코드값(3)으로 작성되어 있기 때문에, 이를 간과하고 데이터 분석 및 보고서를 작성하게 되면 의미를 직관적으로 이해하기 어려워지므로 레포트 가독성이 매우 떨어진다.

 

 

               데이터베이스 - 카테고리                                                                 데이터베이스 - 제품

        <카테고리 이름> <카테고리 번호>                                           <제품이름> <카테고리번호> <가격>

         ->  '의류 1', '전자제품 2', '식품 3'                                                              -> '홍삼 3 2000'

 

 

 

이를 해소하기 위하여, 코드값을 직관적인 레이블값으로 변경해주는 조취를 취해줄 수 있는데, 이럴때 활용되는 구문이 'CASE <조건식> WHEN <조건을 만족할 때의 값>' 이다. 아래는 실습 코드를 직접 따라해보며 진행하였다.

 

예제코드 진행방식은 'MySQL Workbench' 를 활용할 계획이고, 차후 방법으로 'SparkSQL' 을 선택할 계획이다.

 

예제1) register_device 코드 컬럼을 {1: 데스크톱, 2: 스마트폰, 3: 애플리케이션} 을 활용하여 레이블로 변경해보자.

 

 

오리지널 데이터

 

변경된 레이블

 

 

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
;

 

(else 는 디폴트 값으로, 조건에 부합하지 않는 값에 대해 else 문을 적용한다.)

 

 

 

 

 

2. URL에서 요소 추출 (정규표현식)

 

현장에서 최소한의 요건으로 레퍼러와 페이지 url을 저장해둔 경우를 생각해보자. 이때, 분석 목적에 맞게 레퍼러를 따로 추출하여보자. 이때 페이지 단위로 집계하면 밀도가 너무 작아 복잡해져, 일반적으로 호스트 단위로만 집계하므로 아래와 같이 referrer에서 host만을 따로 추출할 필요가있다.

 

 

오리지널 테이블

추출된 데이터

 

 

 

select
	stamp
    -- 아래 코드는 postagesql 에서는 가능하지만, 정규표현식에 그룹을 사용할 수 없는 redshift, mysql에서는 불가능
    -- , substring(referrer, 'https?://[^/]*') as referrer_host
    , regexp_replace(regexp_substr(referrer, 'https?://[^/]*'), 'https?://', '')
    -- 또는, 그냥 정규표현식 없이 아래와 같이도 가능. 분해하는 방식 활용한거.
    -- SUBSTRING_INDEX(SUBSTRING_INDEX(referrer, '://', -1), '/', 1) as referrer_host
    -- 하이브나 스파크는 아예 내장 url parse 함수가 존재함.
from access_log ;

 

 

 

(위와 같이, 각 SQL 인터페이스마다 사용할 수 있는 함수가 달라 구현방식이 다르다. mysql같은 경우 정규표현식 그룹을 사용할 수 없어 원하는 패턴을 추출한 이후 불필요한 부분을 삭제하는 방식을 취한다.)

 

추가적으로, 특정 상품이 열람되는 경우, 특정할만한 ID를 따로 저장하지 않는 경우에도 URL 로그데이터를 바탕으로 GET 요청 매개변수를 따로 추출하여 수집할 수 있다.

 

 

 

오리지널 테이블

추출 데이터

 

 

 

select
	stamp
    , url
    , regexp_replace(regexp_substr(url, '//[^/]+[^?#]+'), '//[^/]+', '') as path
    , regexp_replace(regexp_substr(url, 'id=[^&]*'), 'id=', '') as id
from access_log ;

 

 

 

(여기서 정규표현식은 다음과같이 패턴 //로 시작하는 문자열에서 /가 나타나기 전까지, 이후 ? 또는 # 의 문자가 나타날때까지 문자열을 탐색한 이후, 앞의 //로 시작하는 패턴은 공백으로 대체하는 방식으로 원하는 부분만을 추출한다.)

 

 

 

 

3. 문자열 분리 (split_part(postageSQL,redshift) / substring_index)

 

특정 구분자로 문자열을 분리하는 경우 대부분의 sql 프레임워크에서는 split, split_part 함수를 지원하지만 mysql에서는 이와깉은 함수를 구분하지 않아서 substring index 구문을 이용하였습니다.

 

다만 이러한 함수와 구분되는 점은, substring_index(문자열, 구분자, 구분자 index) 함수는 구분자를 제거하여 가져오는것이 아닐 뿐더러, 구분자 index를 입력해주면 '문자열 왼쪽부터 구분자 index까지의 모든 문자열'을 추출하는 것이며, index 값에 '음수 count' 가 들어오게 되면 그 구분자 index부터 끝까지의 문자열이 출력되는 특징이 있다. 또, 문자열에 포함되는 구분자 갯수 이상의 index가 들어오더라도 문자열을 모두 내뱉는다는 점에서 굉장히 특이한 함수이다.

 

그래서 일반적으로 구분자를 제외한 특정 문자열을 추출할 땐 substring_index를 두번 쓰는 방식을 활용하는 듯 하다.

(참고 블로그)

 

 

select
	stamp,
    url,
    substring_index(SUBSTRING_INDEX(
		REGEXP_REPLACE(REGEXP_SUBSTR(url, '//[^/]+[^?#]+'), '//[^/]+', ''), '/', 2), '/', -1) AS path1,
	substring_index(substring_index(
		regexp_replace(regexp_substr(url, '//[^/]+[^?#]+'), '//[^/]+', ''), '/', 3), '/', -1)  AS path2  
FROM access_log;
-- substring_index를 사용하면 구분자를 기준으로 분할한 후 (n)번째 구분자 이전까지 문자를 가져옴
-- 그러나 (n)개 까지의 구분자가 존재하지 않으면 문자 전체를 출력하기때문에, /video 만 입력되어 오면 path1, path2가 그대로 video로 나옴

 

 

 

 

 

4. 날짜와 타임스탬프 (Current_date, Current_timstamp)

 

현재 날짜와 타임스탬프를 추출하는 쿼리는 다음과 같다.

 

MySQL 에서는 현재 시각 타임스탬프를 찍는 방식으로 NOW, SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP 등 여러 방식이 있는데, CURRENT_DATE는 일(DAY) 단위까지만, CURRENT_TIMESTAMP, NOW는 현재시간까지 찍어주는 함수로 똑같은 출력값을 뱉어준다.

 

 

 

SELECT
    CURRENT_DATE AS dt,
    CURRENT_TIMESTAMP() AS stamp;

 

 

 

---

 

 

 

문자열로 지정한 날짜와 시각을 시간 형식의 TIMESTAMP로 바꿔줄 수 있는데 일반적인 SQL문에서는 CAST 함수를 사용한다. MySQL에서 CAST는 아래와 같은 기능을 제공한다고 한다. 이때 TIMESTAMP 형식으로 변환하고 싶다면 CAST의 DATETIME 을 쓰자.

 

 

 

 

 

select    
	CAST('2016-01-30' as date),
    CAST('2016-01-30 12:00:00' as datetime);

 

 

---

 

 

TIMESTAMP 에서 자유롭게 요일 및 시간 요소를 뽑아볼 수도 있다. EXTRACT를 이용하면 TIMESTAMP 내의 년/월/일 등의 시간 요소를 추출할 수 있다. 어떤 추출이 가능한지에 대한 자세한 설명은 아래에서 참고가능하다. 

(참고 블로그)

 

 

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 datetime) as stamp) as t ;

 

 

 

 

 

5. NULL 값 처리 (COALESCE)

 

Null 값에 대한 처리 없이 문자열 및 숫자열을 결합하거나 사칙연산 등을 하게 되면 Null값이 나오는 등 원하는 결과가 나오지 않을 수 있으므로 주의해야 한다. 이때 COALESCE 함수를 사용할 수 있다.

 

COALESCE(exp1, exp2, ...)

 

위의 함수는 exp1 이 null 이 아닐 경우 exp1 을 그대로 출력, null 일 경우 exp2 출력, exp2 역시 null 이라면 exp3 ... 를 배출하는 함수로 이를 간단하게 변형하여

 

 COALESCE(exp1, 0)

 

를 활용하면 간단하게 null 이 연산내로 들어가는것을 방지하는 역할을 수행한다. 아래의 discount_amount1 은 null값에 대한 처리를 하지 않은 경우, discount_amount2는 coalesce 를 이용하여 null 값에 대한 처리를 진행한 경우이다.

(열 내에 Null이 포함되어 있는지는 어떻게 알지?)

 

 

 

 

SELECT
	purchase_id,
    amount,
    coupon,
    amount - coupon as discount_amount1,
    amount - coalesce(coupon, 0) as discount_amount2
from
	purchase_log_with_coupon;