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

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

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

1
2
3
4
5
6

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)
);

1

select * from mst_users_with_dates;

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

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

1
2
3
4
5
6

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')
;

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

1
2
3
4

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의 언어로 변형시켜 주어야 한다.

1
2
3
4
5

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로 계산한 경우 연 부분만의 차이가 계산되므로, 해당 연의 생일을 넘었는지 제대로 계산이 되지 않는 문제가 발생한다.

1
2
3
4
5

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임을 명심해야 한다. 코드를 바꿔주고 실행하면 문제없이 돌아가는 것을 확인할 수 있다.

MySQL Workbench에서 쿼리를 날렸는데 결과창이 안나온다면!, For MAC OSX

source from : [https://stackoverflow.com/questions/45967413/results-grid-not-showing-on-mysql-workbench-6-3-9-for-macos-sierra]
Thank you Yogev!

빅데이터 시대에 SQL공부가 필수적이다. 머신러닝, 딥러닝 뭐 할게 너무 많지만 일단 데이터를 이해하기 위해서는 SQL공부를 먼저 해야한다고 생각한다.

이런 마음에 SQL을 공부하기로 마음먹고 세달전에 샀던 데이터 분석을 위한 SQL 레시피를 다시 폈더랬다. 책은 매우 훌륭했다. 기본적인 SQL뿐 아니라, 내가 관심있었던 SparkSQL, Big Query에 대해서도 다뤄주고 있었다. 눈으로 SQL을 슬슬 할 때쯤, SQL을 직접 입력해보고 결과를 보고 싶어졌다.

소스코드가 있나 뒤져봤더니, 한빛 미디어에서 제공해 주는 코드가 있었다. 신나게 받아놓고 1년전에 세팅해둔 Mysql 서버를 실행시켰다.

뭐 잡 에러 덩어리가 많았지만 우여곡절 끝에 해결하고

드디어 MySQL Workbench로 들어가서 쿼리를 날렸다.

Success!가 나왔다.

음, 근데 결과창이 보이지 않는다.

바로 StackOverFlow를 뒤져봤다.
쿼리 옆에 있는 돋보기를 눌러보고 Result Grid를 눌러보랜다. 안된다.

Mac에 있는 버그라며 쿼리 박스에 마우스를 신중히 갖다대고 바를 늘려보랜다. 회색화면만 나온다.

껐다 다시 키면 될 것이란다. 바뀐 게 없다.

아, 모든 게 거짓말 같았다. 오늘은 만우절ㅎㅎ***

이렇게 6시간 넘게 삽질을 지속하다가, 빛 갓 Yogev의 Stackoverflow 글을 보게 되었다.

요약하자면 수정된 버전이 올라와 있다는 말이다.
‘아니 최신 버전을 받았는데 왜 또 안됐었던 거지???’ 이해가 안되긴 하지만
빛요게프 선생님께서는 친절히 공유경제의 장점에 대해 설파하고 계시었다.

upvote를 찍어드리기 위해서 stackexchange에 가입했고 upvote를 꾸우우욱 눌러드렸다.

해결방법 : [https://dev.mysql.com/downloads/workbench/] 다운 후
그대로 덮어쓰기 (Workbench 삭제 안해도 된다!)