하나의 값 조작하기

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

데이터를 가공해야하는 이유

1. 다룰 데이터가 데이터 분석 용도로 상정되지 않은 경우
2. 연산할 때 비교 가능한 상태로 만들고 오류를 회피하기 위한 경우

테이블을 만들어서 데이터를 가공해보겠습니다.

테이블 만들기

USE study;

CREATE TABLE mst_users(user_id CHAR(5), register_date DATE , register_device TINYINT unsigned);
INSERT INTO mst_users VALUES('U001','2016-08-26', 1);
INSERT INTO mst_users VALUES('U002', '2016-08-26',2);
INSERT INTO mst_users VALUES('U003', '2016-08-27',3);

SELECT * FROM mst_users;

CASE문을 이용해서 코드값을 레이블로 변경해 보겠습니다.
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
;

URL에서 요소 추출하기

CREATE TABLE access_log(stamp DATETIME, referrer text, url text);

INSERT INTO access_log VALUES('2016-08-26 12:02:00','http://www.other.com/path1/index.php?k1=v1&k2=v2#Ref1','http://www.example.com/video/detail?id=001');
INSERT INTO access_log VALUES('2016-08-26 12:02:01','http://www.other.net/path1/index.php?k1=v1&k2=v2#Ref1','http://www.example.com/video#ref');
INSERT INTO access_log VALUES('2016-08-26 12:02:01','http://www.other.com/','http://www.example.com/book/detail?id=002');

SELECT * FROM access_log

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

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

현재날짜와 타임스탬프를 추출하는 쿼리

SELECT CURRENT_DATE AS dt, localtimestamp AS stamp ;

문자열을 날짜 자료형, 타임스탬프 자료형으로 변환하는 쿼리

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

타임스탬프 자료형의 데이터에서 연,월, 일 등을 추출하는 쿼리

SELECT stamp
, YEAR(stamp) AS year
, MONTH(stamp) AS month
, DAY(stamp) AS day
, HOUR(stamp) AS hour
FROM (SELECT timestamp('2016-01-30 12:00:00') AS stamp) AS t
;

타임스탬프를 나타내는 문자열에서 연, 월, 일 등을 추출하는 쿼리

SELECT stamp
, substr(stamp, 1, 4) AS year
, substr(stamp, 6, 2) AS month
, substr(stamp, 9, 2) AS day
, substr(stamp, 12, 2) AS hour
FROM (SELECT timestamp('2016-01-30 12:00:00') AS stamp) AS t
;

• 날짜와 시간 정보는 로그 데이터에서 빠지지 않는 정보입니다. 타임존을 고려해야 하고, 미들웨어들의 차이를 주의해야합니다.

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

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