SQL_Recipe_01

데이터 분석을 위한 SQL 레시피 with MySQL

데이터 분석을 위한 SQL 레시피의 3장에 있는 코드 내용들을 실습하고 MySQL코드로 변형시켜보았다.
데이터 분석을 위한 SQL 레시피 책에서는 PostgreSQL, Redshift, BigQuery, Hive, SparkSQL의 코드를 다룬다. 책에 있는 코드는 어떤 건 그대로 쳤을 때 돌아가고, 몇몇 개는 MySQL 쿼리대로 수정을 해주어야 한다.

3장의 mst_users_with_dates 테이블을 가지고 실습을 진행한다.
실습 진행 전에 테이블을 만들어준다.

DROP TABLE IF EXISTS mst_users_with_dates;
CREATE TABLE mst_users_with_dates (
    user_id        varchar(255)
  , register_stamp varchar(255)
  , birth_date     varchar(255)
);

select * from mst_users_with_dates;

위 코드로 테이블이 잘 만들어졌는지 확인해본다.

잘 만들어졌으면, 데이터를 삽입한다.

INSERT INTO mst_users_with_dates
VALUES
    ('U001', '2016-02-28 10:00:00', '2000-02-29')
  , ('U002', '2016-02-29 10:00:00', '2000-02-29')
  , ('U003', '2016-03-01 10:00:00', '2000-02-29')
;

먼저 날짜 데이터들의 차이를 계산해보자. 현재 날짜와 등록한 날짜를 빼주는 방식이다.

select user_id, CURRENT_DATE AS today, 
date(timestamp(register_stamp)) AS regitser_date,
datediff(CURRENT_DATE(), date(register_stamp)) AS diff_days
from mst_users_with_dates ;

이렇게 만들어주면 원하는 결과가 나온다. 책에 있는 결과와 조금은 다를 수 있는데, 왜냐하면 CURRENT_DATE를 하면 현재의 날짜를 가져와 주기 때문에, 책에 있는 2017-02-05가 아니라, 지금 작성하고 있는 2019-05-21로 계산된다.

여기까지는 datediff함수가 MySQL에도 있기 때문에 책에 있는 그대로 쳐도 잘 돌아간다.

이번에는 사용자의 생년월일로 나이를 계산해보자.
나이를 계산하기 위한 전용함수가 구현되어 있는 것은 PostgreSQL뿐이다. PostgreSQL에는 age함수가 구현이 되어있어 편하게 나이를 구할 수 있다. MySQL의 경우에는 책에 있는 코드를 MySQL의 언어로 변형시켜 주어야 한다.

SELECT user_id, 
CURRENT_DATE AS today, date(register_stamp) as register_date,  birth_date,
(YEAR(CURRENT_DATE)-YEAR(birth_date))- (RIGHT(CURRENT_DATE,5)<RIGHT(birth_date,5)) AS age,
(YEAR(register_stamp)-YEAR(birth_date))- (RIGHT(register_stamp,5)<RIGHT(birth_date,5)) AS register_age
FROM mst_users_with_dates;

책에 있는 코드와 다른점은 EXTRACT를 사용하지 않았다는 것이다. MySQL에는 EXTRACT가 없기 때문에 년도를 이용해 일일이 계산해 주어야 한다. YEAR함수를 이용해 년도만 가져와서 계산해준다. 주의해야 할 점은, YEAR함수에 today를 넣어주는 게 아니라 CURRENT_DATE를 넣어주어야 한다는 것이다. today를 넣어주면 syntax에러가 발생한다.

하지만 YEAR로 계산한 경우 연 부분만의 차이가 계산되므로, 해당 연의 생일을 넘었는지 제대로 계산이 되지 않는 문제가 발생한다.

SELECT user_id,
substring(register_stamp, 1, 10) as register_date, birth_date,
  floor(( cast(replace(substring(register_stamp, 1,10), '-', '') AS unsigned) - cast(replace(birth_date, '-', '') AS unsigned))  / 10000) as register_age,
  floor(( cast(replace(CAST(CURRENT_DATE as signed), '-', '') as unsigned) - cast(replace(birth_date, '-', '') AS unsigned)) / 10000) as current_age
from mst_users_with_dates;

이 코드로 실행시켜주면 문제가 해결된다. MySQL에서는 CAST함수 실행시에 주의해야 할 점이 있는데, 보통 프로그래밍 언어에서는 Integer나 String등으로 타입을 정해주는데, MySQL에서는 UNSIGNED–>INTEGER이고, SIGNED–>STRING임을 명심해야 한다. 코드를 바꿔주고 실행하면 문제없이 돌아가는 것을 확인할 수 있다.