데이터 분석, 먹고 들어가기 위한 SQL 공부법(2편)
데이터 분석을 위한 SQL 학습법 가이드, 완결편.
(개인 웹사이트(링크)에 재발행했습니다.)
지난 글 읽기:
마케터의 데이터 분석 공부, 어디부터 시작해야 하나요?
데이터 분석, SQL만 잘 다뤄도 먹고 들어갑니다
데이터 분석, 먹고 들어가기 위한 SQL 공부법(1편)
데이터 분석을 위한 SQL을 처음 접하는 분들의 막막함을 조금이라도 줄여 드리기 위한 안내도 2편입니다. 지난 글(1편)에서는 아래와 같은 내용을 다뤘습니다. 대체로 기초 문법을 익히는 방법에 대한 내용이었습니다.
데이터 분석을 위한 SQL은 SQL의 세계 중 극히 일부이니, 압도되지 말고, 불필요한 정보는 걸러내세요.
SQL에는 여러 종류의 DB가 있으나, 하나만 잘 배워 두면 나머지 종류는 쉽게 배울 수 있습니다.
분석을 위한 SQL 문법만 공부하기 좋은 책과 온라인 강좌가 따로 있습니다.
남이 만들어 둔 실제 SQL 쿼리문을 통해 공부하면 좋습니다.
이번 글에서는 기초 문법을 어느 정도 익힌 뒤에, 어떻게 SQL에 대한 이해도를 높이고 실수를 줄일 수 있을지에 대한 이야기를 하려고 합니다. 기초 문법을 어느 정도 공부하신 다음에 읽어 주시면 좋을 것 같습니다.
(들어가기 전에...)
지난 글에서 저는 이렇게 예고했습니다.
복잡한 쿼리문일수록 틀리기 쉽습니다. 이 때 비교적 익숙한 도구인 엑셀을 사용해서 교차검증하면, 쿼리문의 오류를 잡아낼 가능성이 높아집니다. (힌트: Raw Data를 먼저 뽑고, 엑셀에서 vlookup, 피벗 테이블 등을 사용해 쿼리문의 결과를 검증합니다)
그런데, 이 글에서 자세히 설명하기는 어려울 것 같습니다. 기본적으로 엑셀을 능숙하게 활용할 수 있어야 하기 때문에 글 하나로 설명하긴 어렵고, 임기응변적인 성격이 강하기 때문에 체계적으로 설명하기 어려울 것 같습니다. (죄송합니다 ㅠㅠ)
그래도, 혹시나 도움이 될까봐 큰 원칙만 말씀드리면 저는 이럴 때 이런 식으로 쿼리를 검증했습니다.
어떤 때 사용하나?
복잡한 JOIN이나 서브쿼리, GROUP BY, WHERE 구문 등을 활용할 때
= 즉, SQL 쿼리문에 실수가 있을 가능성이 있을 때.
= 다시 말해, SQL 쿼리의 결과로 출력한 데이터가 내가 의도한 것과 다른(혹은 틀린) 것일 가능성이 있을 때
어떻게 사용하나?
1) JOIN할 테이블의 Raw data를 뽑아서 엑셀에 저장하고,
2) 이 Raw data를 가지고 SQL에서 내가 의도한 JOIN이나 GROUP BY, WHERE 구문 등을 엑셀로 구현해 보고 (피벗테이블과 vlookup을 포함한 각종 함수를 이용해서)
3) SQL의 결과와 엑셀 작업의 결과가 일치하는지 보기. 만약에 일치하지 않는다면, 둘 중 하나(혹은 둘 다)에 실수가 있을 가능성이 있음.
4) 실수가 발생했을 가능성이 있는 부분(대체로 어렵고 까다로운 구문에서 실수가 발생합니다)을 하나하나 뜯어 보며 문제를 찾아낸다.
이 내용은 나중에 언젠가 다시 자세히 다룰 기회가 있길 바라며... 지난 글에 이어 본격적으로 데이터 분석을 위한 SQL 공부법 소개를 시작하겠습니다.
1. 데이터베이스 이론 배우기
이전 글인 데이터 분석, SQL만 잘 다뤄도 먹고 들어갑니다에서 저는 이렇게 얘기했습니다.
세상이 작동하는 원리에 호기심이 있는 분이라면 관계형 데이터베이스(RDBMS)란 무엇인지, SQL이란 무엇인지 공부해 보시길 권합니다.
SQL 기초 문법을 공부하고 여기까지 오신 분들이라면, 아마 높은 지적 호기심을 갖고 계실 겁니다. 그러니 이론을 공부하시라는 말씀을 부담 없이(?) 드리겠습니다.
물론, 관계형 데이터베이스에 대해서 데이터베이스 관리 전문가가 될 수준으로 깊은 이론을 익힐 필요는 없습니다. 데이터 분석을 하는 사람은 DB를 설계하거나 모델링하지도 않고, DB의 성능을 최적화하는 엔지니어링을 할 일도 없으니까요.
다만, 배경 이론을 알고 있으면 여러분이 작성하는 SQL 쿼리문을 더 깊이, 더 정확히 이해하는 데 도움이 됩니다. 예를 들면, 테이블(table)이란 무엇이고 칼럼(column)이란 무엇인지. 각 테이블 간의 연결은 무엇을 통해서 이뤄지는지, JOIN이란 무엇인지 등등... 막연하게만 알고 있는 것과, 그 배경이 되는 이론을 알고 있는 것 사이에는 큰 차이가 있습니다.
특히, JOIN 개념(INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN 등)은 헷갈리기도 쉽고, 실수하기도 쉽기 때문에 정확히 알아 두시면 도움이 됩니다.
저는 MOOC를 이용해서 공부했습니다. Quora에서 스탠포드 대학교의 Introduction to Bases 수업을 추천받았고, 수업에서 제공하는 동영상 강의를 듣고 연습문제를 풀면서 큰 도움을 받았습니다.
구글에서 일하시는 이 분도 그렇다고 합니다.스탠포드 강의라 난이도가 높을까봐 걱정하시진 않아도 됩니다. 이 수업은 컴퓨터 공학에 대한 사전 지식을 요구하지 않기 때문에, 이해하기에 크게 어렵지 않습니다. 교재를 구입할 필요도 없고, 동영상 강의를 꽤나 친절하게 구성했습니다.
이 강의는 이렇게 다섯 부분으로 나뉘어 있습니다.
Data Models / Querying Relational Databases / Querying XML Databases / Database Design / SQL Advanced Features
이 중에서 데이터 분석을 위한 SQL과 관련 있는 것은 앞의 두 부분입니다.
(1) Data Models: Introduction and Relational Databases / XML Data / Json Data
관계형 데이터베이스에 대한 배경 지식을 배울 수 있습니다. 왜 관계형 데이터베이스인지, 테이블이 뭐고 칼럼이 무엇인지 등등... 그리고 'XML'과 'Json'이라는 데이터 형식에 대해서도 다룹니다. XML과 Json은 사실 몰라도 SQL을 배우는 데 전혀 지장이 없지만, 기왕 수업을 듣는 김에 알아 두시면 좋습니다.
Json이 뭔지 알면 엔지니어들과 대화가 통하는 느낌이 향상됩니다.2) Querying Relational Databases: Relational Algebra / SQL
데이터베이스에 질의(Querying)하는 것을 다룹니다. 즉, 쿼리문을 날리는 것에 대한 배경 지식입니다.
Relational Algebra 강의에서는 조금더 근본적인 이론을 다루고, 세타, 시그마 등 외계어 같은 기호들이 등장합니다. 수학이나 논리학을 싫어하시는 분은 조금 괴로울 수 있지만, 그래도 참고 배워 두면 다 피가 되고 살이 됩니다.
SQL 강의에서는 본격적으로(!) 기초 SQL 문법을 다룹니다. 다만, 그 수준은 이전 글에서 다룬 기초 문법 강좌들보다 더 쉬운 정도입니다.
데이터베이스 이론을 배울 곳은 많고 많지만, 이 수업은 MOOC의 태동기인 2011년부터 지금까지 계속되어 온 스테디 셀러인만큼 수업의 질은 보장합니다. SQL 기초 문법을 익히기 전에 배워 두어도 괜찮을 것입니다.
2. 우리 조직의 데이터베이스 이해하기
여러분이 일하고 있는 조직의 데이터베이스를 잘 모르면 SQL을 아무리 배워도 소용 없습니다. 근본적으로 쿼리문은 특정 테이블에서, 특정 칼럼을, 특정 조건에 맞춰 불러오는 것이기 때문입니다. 우리 조직의 데이터베이스를 알려면, 엔지니어나 데이터 분석가를 괴롭히는 것이 가장 좋습니다. 아래 내용에 유의해서 우리 조직의 데이터베이스를 파악해 보세요.
(1) 어떤 DB와 어떤 테이블(table)이, 어떤 칼럼(column)이 존재하는지 파악한다.
들어가기 전에: 서버 > DB > 테이블 > 칼럼 순으로 상위 개념이라고 생각하세요. 대부분의 조직에서는 데이터를 여러 대의 서버에 분산해서 저장하고, 한 대의 서버에는 여러 개의 DB가 들어 있고, 한 DB 안에는 여러 개의 테이블이 들어 있고, 한 테이블에는 여러 데이터 항목(칼럼)이 포함되어 있습니다.
온라인 서비스를 운영하는 회사라면, DB와 테이블의 개수가 생각 외로 많습니다. 어느 정도로 많냐면, DB를 실제로 들여다보기 전에 상상했던 것보다 훨씬 많습니다. 왜 그렇게 여러 DB와 테이블을 사용하는지는... 엔지니어 분들에게 물어 보세요.
그 많은 테이블은 다 어디에 쓰이는 걸까요. 저도 궁금합니다.우리에게 중요한 것은 어떤 DB와 어떤 테이블이 존재하는지 파악해야 한다는 점입니다. 그래야 분석하기 위한 데이터를 추출할 수 있으니까요.
DB Schema를 파악하는 데는 두 가지 방법을 병행해야 합니다. 하나는 SQL 클라이언트 프로그램을 이용해서 탐색하기, 또 하나는 회사의 데이터를 잘 아는 엔지니어/데이터 분석가를 붙들고 괴롭히기입니다.
1) SQL 클라이언트 프로그램을 이용해서 데이터베이스 탐색하기
radiodj161이라는 DB 안에 carts, carts_list, category, events 등 여러 개의 테이블이 들어 있습니다.Workbench, HeidiSQL 등의 SQL 클라이언트를 이용해서 데이터베이스 서버에 접속(접속 권한과 방법은 엔지니어링 부서에 문의하세요)하면 위와 같은 화면을 볼 수 있습니다.
보통은 프로그램 창의 왼편에서 DB와 테이블을 탐색할 수 있습니다. 이렇게 탐색을 하면서, 어떤 이름의 DB와 테이블, 칼럼이 있는지 알아 보세요. 우리 조직의 훌륭한 엔지니어들은 DB를 구성하면서, 다른 사람이 보았을 때 각 DB와 테이블에 어떤 데이터가 들어있는지 알 수 있게끔 최대한 직관적으로 이름을 붙였을 것입니다. (예를 들어, 사용자 정보는 users 테이블, 주문 정보는 orders 테이블, 컨텐츠 정보는 contents 테이블 등으로)
그리고 각 DB와 테이블과 칼럼이 무엇을 뜻하는지 생각해 보세요. 무엇인지 정확히 알 수 없는 DB, 테이블, 칼럼들은 이 단계에서는 그냥 넘겨도 됩니다.
2) 엔지니어/데이터 분석가를 질문으로 괴롭히기.
SQL 클라이언트 프로그램을 이용해서 어떤 DB, 테이블, 칼럼이 존재하는지 대강 파악했다면, 이제는 그것들의 실체를 파헤칠 시간입니다.
여러분이 문서 기록을 잘 하는 조직에서 일하고 있다면, 이미 이러한 DB 스키마(Schema) 정도는 어딘가에 문서로 남아있을 겁니다. 각 DB에는 어떤 테이블이 있고, 각 테이블은 어떤 정보들을 저장하고 있는지. 이런 문서가 잘 남아 있다면, 엔지니어와 데이터 분석가를 그렇게 많이 괴롭히지는 않아도 됩니다.
사실 문서가 있더라도 한 번에 이해하기는 어렵습니다. 그래서 결국 질문을 많이 해야 합니다. ㅎㅎㅎ
운나쁘게도 이런 문서가 없다면, 엔지니어와 데이터 분석가를 수많은 질문으로 괴롭힐 수밖에 없습니다. 질문하고 또 질문하세요. 그리고 기억하세요. 이 단계에서 할 일은 이것 뿐입니다.
질문 예시)
"이 테이블은 뭔가요?"
"이 칼럼은 뭔가요?"
"이 테이블의 데이터는 언제 어떤 방식으로 기록되나요?"
"이 데이터는 왜 이런 형태로 저장되나요?"
(2) 각 테이블이 어떤 키(key) 값으로 연결되는지 파악한다.
관계형 데이터베이스의 테이블은 키(key)값을 기준으로 서로 연결되어 있습니다. 데이터베이스의 테이블끼리 모종의 관계가 있기 때문에 관계형(Relational) 데이터베이스라고 부른다고 합니다.
자세한 내용은 생활코딩의 관계형 데이터베이스 이론 영상(https://www.youtube.com/watch?time_continue=160&v=dIdey4mMXnw)에서 쉽게 설명해줍니다. 2분 41초부터 시작합니다.
수많은 DB와 수많은 테이블이 있는데, 각 테이블이 어떤 키값으로 연결되는지 파악하면, SQL 쿼리의 JOIN 구문을 사용해서 여러 테이블의 데이터를 결합해서 한 번에 뽑을 수 있습니다. (위 영상의 8분 23초)
기본 문법을 익히셨다면 아시겠지만, JOIN 구문은 SQL을 활용한 데이터 분석에서 상시적으로 사용하는 구문입니다. JOIN 구문을 활용하기 위해서는 각 테이블이 어떤 키값으로 연결되어 있는지 파악해야 합니다.
각 테이블의 연결을 파악하는 법은? DB/테이블/칼럼 파악 방법과 마찬가지입니다.(엔지니어들이 최선을 다해 직관적으로 DB를 설계했을 것이기 때문에) 여러분이 직접 DB 테이블을 탐색하면서 파악할 수도 있고, 그래도 모르겠다면 데이터를 잘 아는 엔지니어/분석가에게 질문해서 파악하시면 됩니다.
우리 조직의 데이터베이스를 파악하는 법을 이 글에서는 짧게 썼지만, 실제로 하려면 꽤나 오랜 시간이 필요합니다.
제 개인적인 경험을 말씀드리면, 쏘카에서 풀타임으로 데이터 분석을 할 때조차 어느 정도 DB를 파악하는 데 1달 정도가 걸렸습니다. 그리고 지금 11개월째 일하고 있는 PUBLY에서도 (부끄럽지만) 아직 DB에서 모르는 항목들이 많습니다.
결국 인내심(오랜 시간이 걸리는 데 대한)과 호기심(우리 조직이 데이터에 대한)을 가지고, 꾸준히 하는 수밖에는 없습니다. 어렵다고 중간에 포기하지 마시구요.
3. 수많은 함수(function) 사용법 익히기
SQL에는 정말 많은 종류의 함수(function)가 있습니다.
정말 많기 때문에, 영어 단어장 외우듯이 꾸역꾸역 외우려고 하면 스트레스만 받습니다. 그렇지만 다행히도, 엑셀(Excel)을 공부해 보신 분들은 아시는 것처럼, 엑셀에 존재하는 그 많은 함수를 모두 외워야만 엑셀로 실무를 할 수 있는 것은 아니듯이 SQL에 존재하는 함수 모두 외워야 할 필요는 없습니다.
제가 추천하는 방법은 다음과 같습니다.
(1) SQL 기본 함수 익히기
SUM(합계 구하기), COUNT(개수 세기) 등의 함수는 어떤 업종, 어떤 분석에서든 자주 사용됩니다. SQL 기초 문법을 배울 때 이런 함수들은 자연스럽게 익히게 되실 겁니다.
(2) 우리 조직에서 자주 사용하는 함수 익히기
업종과 부서에 따라서 데이터 분석에 자주 사용하는 함수가 달라질 수 있습니다. 예를 들어, '시간'이 중요한 업종에서는 시간을 다루는 함수를 많이 사용합니다. 특히나 마케팅 부서에서는 고객의 구매 시점, 이탈 시점, 이용 시점, 이용 시간 등 시간을 가지고 분석을 할 일이 많습니다.
지난 글에서, 기존에 다른 사람들이 만들어 둔 쿼리문을 들여다 보며 SQL 문법을 익히라고 말씀드렸는데, 이 방법은 함수를 익히기에도 좋습니다. 쿼리를 들여다 보다 보면, 자주 쓰이는 함수들이 있습니다. 이런 함수의 사용법을 잘 익혀 두면 실무에 분석을 빨리 활용할 수 있습니다.
참고로, 저는 MySQL에서 이런 함수를 많이 이용합니다.
TIMESTAMPDIFF:
- 두 시점 사이의 차이를 계산하는 함수. 예를 들어 이용 시작 시간과 종료 시간 데이터가 있을 때, 몇 시간 동안 이용했는지를 이 함수를 가지고 계산합니다.
- TIMESTAMPDIFF(minute, "2017-11-18 22:00:00", "2017-11-18 23:00:00")를 실행하면 60이라는 값을 뱉어냅니다. (11월 18일 22시와 11월 18일 23시 사이의 시간 간격은 60분이기 때문에)
- TIMESTAMPDIFF(day, "2017-11-01", "2017-11-18")를 실행하면 17이라는 값을 뱉어냅니다.
DATE_SUB:
- 특정 시점에서 정해진 시간을 뺀 값을 계산합니다.
- DATE_SUB("2017-06-15", INTERVAL 10 DAY)를 실행하면 "2017-06-05" 값을 뱉어냅니다.
- DATE_SUB("2017-11-18 22:00:00, INTERVAL 1 HOUR)를 실행하면 "2017-11-18 21:00:00" 값을 뱉어냅니다.
- 자세히 설명하는 것은 이 글의 범위를 벗어나지만, date_add 함수와 함께 굉장히 요긴하게 쓰입니다. 시간 데이터를 다룬다면 빠질 수 없는 함수입니다.
DATE_ADD:
- 특정 시점에서 정해진 시간을 더한 값을 계산합니다.
- DATE_ADD("2017-06-15", INTERVAL 10 DAY) 함수를 실행하면 "2017-06-25" 값을 뱉어냅니다.
- DATE_ADD("2017-11-18 22:00:00, INTERVAL 1 HOUR)를 실행하면 "2017-11-18 23:00:00" 값을 뱉어냅니다.
- 정말 요긴한 함수입니다. 믿어주세요.
DATE_FORMAT:
- 시간 데이터의 형식을 바꿔서 보여 주는 함수입니다.
- 데이터베이스에는 시간 데이터가 특정한 형태로 기록됩니다. 예를 들면 2017년 1월 1일을 "2017-01-01"로 기록했을 수 있습니다.
- date_format 함수를 사용하면 이를 "2017년 1번째 주"로 표현할 수도 있고, "2017년 1월 1일"로 표현할 수도 있고, "1일 1월 2017년"으로 표현할 수도 있고, "2017년 1월 1일 Sun"으로 표현할 수도 있습니다.
- 저는 보통 DATETIME 형식(2017-11-18 22:41:10 식의)의 데이터를 주(Week) 단위나 월(Month) 단위로 표현하기 위해서 자주 사용합니다.
(3) 필요할 때마다 인터넷 검색하기
이런 책은 없습니다.SQL 쿼리문을 작성하다 보면, 숙련도가 올라갈수록 점점 '내가 원하는 것'과 '내가 할 수 있는 것' 사이에 간극을 느낄 때가 많아집니다. 개발자나 데이터 분석가에게 물어볼 수도 있겠지만, 그것도 하루 이틀입니다(이쯤 되었으면 그만 좀 괴롭혀야 합니다). 이제 인터넷 검색을 적극 활용할 때입니다.
구글 검색을 하면 주로 Stack Exchange(혹은 Stack Overflow)의 검색 결과가 나올 겁니다. 저는 개인적으로 제가 하던 고민 중 Stack Exchange에서 답을 찾지 못한 경우가 없었습니다. Stack Exchange의 질문과 답변은 물론 영어입니다. 기술 관련 공부를 하려면 영어와 친해져야 하는 이유입니다.
(4) 종종 함수 사전을 보면서 하나둘씩 새로운 함수를 시도하기
저는 기본적으로 "내가 해결하고자 하는 문제"에서 출발해서 해결책을 찾아내는 것이 가장 좋은 공부 방법이라고 생각하지만, 가끔은 "가능한 해결책의 범위(솔루션 스페이스)"를 넓힘으로 인해 문제를 더 잘 정의할 수 있게 되기도 합니다. 평소에 새로운 함수를 하나둘씩 시도해 보는 것은 그런 의미에서 좋은 공부 방법입니다.
새로운 함수를 찾는 방법은 무엇일까요? MySQL, PostgreSQL 등 SQL 종류별로, 그리고 버전별로 공식 매뉴얼이 인터넷에 공개되어 있습니다. 공식 매뉴얼에는 함수의 종류와 사용법도 설명되어 있습니다. 공식 매뉴얼이기 때문에, 설명되지 않는 함수가 없다고 보시면 됩니다.
MySQL: https://dev.mysql.com/doc/refman/5.7/en/functions.html
PostgreSQL: https://www.postgresql.org/docs/9.1/static/functions.html
MariaDB: https://mariadb.com/kb/en/library/built-in-functions/
이런 공식 문서들도 좋지만, 저는 개인적으로 w3schools의 MySQL 함수 사전을 잘 이용하고 있습니다. 인터넷을 찾아 보면 더 좋은 자료들도 있을 겁니다.
이렇게 데이터 분석을 위한 SQL 공부법을 1편, 2편에 나눠서 알아 봤습니다. SQL은 컴퓨터 언어 중에서는 배우기 쉬운 편에 속하지만, 한 언어를 배운다는 것이 결코 만만하지는 않습니다. 그렇지만 SQL은 데이터 분석의 기본이기 때문에, 잘 배워 두면 여러분의 커리어에 큰 도움이 될 수 있을 것입니다.
다음 글부터는 첫 글(마케터의 데이터 분석 공부, 어디부터 시작해야 하나요?)에서 강조한, 데이터 분석을 둘러싼 사고방식(세계관)에 대해 다뤄 보겠습니다. 끝까지 읽어 주셔서 감사합니다!
