스타트업 마케터의 데이터 분석, SQL 입문하기(2)
SQL 예제 문으로 연습하기
OO님, 데이터 분석하려면 뭐부터 배우는 게 좋아요?
음... 우선 SQL부터 해보는 게 어때요? 마케터시잖아요. 취미로 배우시는 거면 상관없는데 깊게 배우실 거면 엄청 어렵고 시간도 많이 들어요. 일단 SQL을 한번 해보고 다시 고민해보는 게 어떨까요?
내가 SQL을 처음 접하게 된 계기는 이 대화 때문이었다. 잠깐만 생각해봐도 개발 툴을 사용하는 건 내게 먼 영역이었고 딱히 취미로 배우고 싶다는 생각도 없는 게 확실했다. 데이터 분석이 내가 담당하고 있는 업무였기에 관심이 있는 정도였다. 무언가를 배운다는 건 꽤 노력이 필요한 일이니 일단 SQL부터 해보라는 말이 무척 설득력이 있었다.
대화를 하고 잠시 후 데이터를 다루는 기업들은 모두 SQL 방식의 관계형 데이터베이스를 쓴다는 걸 구글 검색을 통해 알게 됐다. 우리 기업은 그중에 'MySQL Workbench'을 사용하고 있었고 곧바로 점심시간을 이용해 MySQL을 노트북에 설치했다. 일단 무엇이든 시작이 있어야 진척이 있으니까.
1. SQL이란 무엇일까?
우선 배우려는 게 무엇인지 알아야 배우든지 말지 결정할 수 있다. SQL이란 무엇일까? SQL은 'Structured Query Language'의 약자이다. 앞서 말했듯이 데이터를 다루는 기업이면 데이터를 어딘가에 저장을 해둬야만 한다. 대부분의 기업은 데이터를 보유하고 있다. 간단하게 아이디, 이메일, 전화번호 등을 포함한 회원 정보들도 이런 데이터의 범주에 들어간다.
데이터가 매우 적다면 딱히 관리할 필요가 없다. 소규모일 땐 손으로, 혹은 엑셀로만 관리해도 충분하다. 하지만 회원이 수 십 명뿐인 회사가 얼마나 될까? 또한 상품을 판다면 상품 명, 카테고리, 게시 날짜, 가격 등의 정보도 저장해둬야 한다. 이뿐만 아니라 유저의 행동까지도 데이터로 변환해서 저장하고 관리할 수 있다. 이렇게 데이터가 많다 보니 손이나 엑셀로만은 데이터의 저장하기 어려운 문제가 생긴다.
Q. 다음 주어진 방에서 휴지를 찾으시오.그렇다고 데이터를 다 넣어두기만 하면 되나? 다시 데이터를 꺼내서 쓸 수 있어야 한다. 원하는 대로 가공도 할 수 있어야 한다. 어지럽혀진 방에서 원하는 물건만 딱 꺼내오고, 네모난 물건만 전부 골라서 꺼내올 수 있어야 한다는 말이다. 방 정도라면 그나마 사정이 낫지만 데이터 더미 속에서 찾으려면? 하루 종일 스크롤만 내리며 하나씩 골라내야 할 텐데 답도 없지 않겠는가.
이런 이유 때문에 SQL 방식의 관계형 데이터베이스를 사용한다. 특정한 기준에 따라 데이터들을 차곡차곡 저장(Structured) 해두는 거다. 그러면 필요할 때 질문을(Query Language) 통해 특정 데이터들만 골라서 뽑아낼 수 있다. 예를 들어, "이 1번 방에서 하얗고 네모나고 반짝이는 물건들만 골라내."라고 입력하면 순식간에 좌르륵 결과가 출력된다.
SQL을 안다는 건 SQL 방식으로 데이터를 추출할 수 있다는 말이다. 이는 곧 원하는 데이터들을 골라볼 수 있다는 뜻이 된다. 데이터 분석의 기초가 SQL이란 말이 이런 이유 때문에 나오는 것이다.
2. SQL 시작하기
내가 'My SQL workbench'를 깔면서 생각한 목적은 간단했다.(기업들은 My SQL, Oracle DB, Postgre SQL, Maria DB 등 다양한 관계형 데이터베이스를 쓰지만 사용하는 쿼리문은 입력 방식이 거의 똑같으니 기업에서 사용하는 걸로 공부하면 된다.) '딱 내가 필요한 수준까지만 하자!' 페이스북을 통해 올리는 콘텐츠를 만들 때 특정 데이터를 골라서 뽑는 작업이 필요했고 1~2번으로 끝나는 게 아니라 반복적으로 해야 했다. 자주 이뤄지는 작업이니 개발자가 아닌 내가 필요한 데이터를 뽑는 게 서로한테 훨씬 좋겠다는 생각이었다.
쿼리문 종류
SELECT: ~를 선택해라!(*를 입력하면 전체 값)
FROM: 어디서?
WHERE: ~한 조건에 부합하는
BETWEEN: 사이 값
IN: 포함
DROP: DB와 Table을 제거할 때 사용
DELETE: Table 안의 데이터를 지울 때 사용
...
SUM: 합
COUNT: 수 세기
GROUP BY: 특정 값으로 데이터 묶기
...
INNER JOIN ~ on: 교집합
LEFT JOIN ~ on: 두 값에서 left 값을 출력(중복 값 제거)
RIGHT JOIN ~ on: 두 값에서 right 값을 출력(중복 값 제거)
...
앞으로 위와 같은 쿼리 문의 구문들과 친해져야 한다. 그리고 시작에 앞서 몇 가지 알아둬야 할 게 있다. 첫 번째는 하나부터 열까지 하나하나 개념부터 공부하는 방식이 가장 좋다는 것이다. 대입 시험공부할 때를 떠올리면 된다. 개념을 잘 이해 못 해도 모의고사만 주야장천 풀다 보면 당장 나오는 점수는 좋아질 수 있다. 하지만 잘못된 지식을 바로잡기 어렵고 100점에 가까운 점수를 받기도 힘들다. 그래서 진짜 마음 잡고 공부하고자 한다면 생활 코딩이나 SQL 서적, SQL 강의 등을 통해 배워나가는 게 좋다.
두 번째는 CREATE, DROP, INSERT, DELETE 등 데이터 테이블을 직접적으로 건드리는 명령문은 쓸 일이 없다는 것. 이런 명령문은 다 건너뛰면 된다. 오히려 건들면 뭐 된다. "그... 제가 연습하다가 회원 정보를 전부 날려버렸습니다."같은 말을 하게 될지도 모른다. 애초에 데이터에 접근하고 분석할 수 있는 권리를 아무한테나 주지 않는다. 이런 권한을 받았는데 실제 사용하는 DB를 건드렸다간 신뢰뿐이 아니라 일자리마저 잃을 수 있다.
세 번째는 질문과 시행착오가 답이란 것이다. 우리에겐 훌륭한 '갓 발자님'이라는 스승이 계신다. 그들은 이미 우리 기업의 데이터 구조를 알고 있으며 SQL 쿼리문을 많이 써왔다. 기업마다 자주 쓰는 쿼리문은 조금씩 다르기 마련이다. 어차피 우리 기업에서 쓸 쿼리문을 다루는 게 목적이기에 쓰고 있던 쿼리문을 건네받아서 시작하는 게 여러모로 좋다.
배우는 행위는 다소 힘들고 자주 틀릴 때 기억에 더 잘 남는다. 건네받은 예문을 이리 보고 저리 보며 수 'MySQL workbench'에 입력해 출력해보면 된다. 처음엔 출력이 잘 안 된다. 자괴감도 들곤 하지만 어디가 잘못됐을까 곰곰이 추적하다 보면 실무에서 사용하는 쿼리문을 비슷하게 만들어내는 데까지 성공할 수 있다.
왜 Error 말고는 나오질 못 하니... 자괴감 들고 괴로워...3. SQL 실제 예문
먼저 기업에서 사용하고 있는 쿼리문을 하나 건네받도록 한다. 이 쿼리문을 돌려 보면서 SQL의 쿼리문은 어떻게 작성되는지 구조를 이해하면 된다. 아래 사진은 내가 자주 사용하는 쿼리문이다.
MySQL workbench(연습 용 DB)SELECT rp.refer_type, lp.cam_id, cp.cam_name, lc.class_type, dp.dept_name, lc.class_name, pp.pro_name FROM MOCAM.MC_REFERENCE_PARENT rp
INNER JOIN MOCAM.MC_LECTURE_PARENT lp on rp.lec_id = lp.lec_id
INNER JOIN MOCAM.MC_CAMPUS_PARENT cp on cp.cam_id = lp.cam_id
INNER JOIN MOCAM.MC_DEPARTMENT_PARENT dp on lp.dept_id = dp.dept_id
INNER JOIN MOCAM.MC_PROFESSOR_PARENT pp on lp.pro_id = pp.pro_id
INNER JOIN MOCAM.MC_LECTURE_CLASS lc on lp.class_id = lc.class_id
WHERE refer_type = '기출문제'
group by class_name, pro_name, dept_name
처음에 보면 정말 복잡해 보이지만 겁먹지 말고 하나씩 뜯어보면 사실 별 게 없다. 개인적으로 많이 쓰는 연습방법은 '한글로 바꿔 적기'다. 비슷해 보이는 문자들을 쳐다보고 있으면 적다가도 헷갈린다. 그래서 한글로 옮겨 적는 방식을 택해봤더니 생각보다 좋아서 계속 채택하고 있다.
SELECT (학습자료 parent에서 학습자료 타입), (강의 parent에서 학교 id),
(캠퍼스 parent에서 학교 명), (강의 parent에서 강의 타입), (학과 parent에서 학과 명), (강의 클래스에서 강의 명), (교수 parent에서 교수 명)
SELECT에서는 출력하고 싶은 항목들을 적는다. 이 쿼리문을 작성한 목적은 '학교 별로 학습자료를 출력해 가공하기' 위해서였다. 그래서 이 학습 자료가 어디 학교 것인지, 전공인지 교양인지, 강의 명과 속한 학과 명은 무엇인지, 또 교수님의 성함은 어떻게 되는지를 포함했다.
FROM 학습자료 parent
데이터를 어디서 가져올 것인지 묻는다. 데이터를 가져올 메인테이블을 입력해주면 된다.
INNER JOIN 강의 parent: 학습자료_강의 id=강의_강의 id
INNER JOIN 캠퍼스 parent: 캠퍼스_캠퍼스 id=강의_캠퍼스 id
INNER JOIN 학과 parent: 학과_학과 id=강의_학과 id
INNER JOIN 교수 parent: 교수_강의 id=강의_교수 id
INNER JOIN 강의 class: 강의 C_강의 id=강의 P_강의 id
데이터들은 기업에서 정한 기준에 따라 테이블이 나뉘며, 테이블에 따라서 따로 분리된 채 저장되어 있다. 예를 들어 한 테이블에는 '회원 정보'라고 회원 ID, 전화번호, 이메일이 저장되어 있으며, 다른 테이블에는 '구매 평가'로 작성자 ID, 별점, 만족도 등이 포함되어 있다고 해보자. 특정 회원이 적은 구매 평가를 연결해서 살펴보고 싶다. 어떻게 해야 할까? 여기서 두 테이블이 연결되는 데이터는 회원 ID(작성자 ID)이다. 이걸 활용하는 방법이 INNER JOIN이다.
SELECT에서 기본으로 학습자료 테이블에서 데이터를 가져오기로 했다. 'INNER JOIN 강의 parent: 학습자료_강의 id=강의_강의 id'는 강의 parent 테이블의 데이터를 가져오기 위해 강의 id끼리 연결을 시켜주는 것이다. 위의 예시에선 회원 ID였던 게 여기선 강의 ID가 되는 것이다. 여기까지 하면 학습자료 parent 테이블과 강의 parent 테이블이 연결됐다.
다음 'INNER JOIN 캠퍼스 parent: 캠퍼스_캠퍼스 id=강의_캠퍼스 id'에서도 연결고리를 찾는다. 캠퍼스 id가 공통 데이터 값이 된다. 앞에서 강의 id를 통해 학습자료 parent 테이블과 강의 parent 테이블을 연결해뒀고, 이번엔 강의 parent 테이블과 캠퍼스 parent 테이블이 연결됐다.
대충 그림으로 나타내면 이런 느낌이다.이런 과정을 반복해서 필요한 항목을 출력하기 위해 테이블들을 서로 연결시켜주는 작업을 하면 된다.
WHERE 학습자료 타입이 기출문제인 것만.
앞에서 테이블을 연결하는 작업을 했다면 이제 실제 목적이었던 '학교 별로 학습자료를 출력해 가공하기'를 위해 학습자료의 중에 원하는 것들로만 출력한다. 여기선 기출문제만을 가져오도록 했다.
group by 과목 명, 교수 명, 학과 명이 같은 것은 묶어라.
출력하는 값 중에서 중복되는 것은 없애고 싶은 경우가 많다. 이런 경우엔 'A, B가 같은 건 묶어!'라든지 'A, B, C가 다 같은 건 묶어버려!'라는 쿼리문으로 group by를 사용해주면 된다.
쿼리문을 작성할 때는 다음 3가지를 유의 깊게 보면 좋다.
1) 어떤 구문을 사용했는가? (SELECT, FROM, WHERE, GROUP BY 등)
2) 어떤 테이블을 이용하는가? (앞서 보여준 각 테이블들처럼)
3) 테이블의 어떤 항목이 필요한가? (어떤 테이블에 어떤 항목이 있는지 안다=우리 회사의 데이터 구조를 안다)
여기에 필요한 값을 찾기 위해 엑셀에서 사용하던 함수들도 하나 둘 넣어서 이용해 보면 된다. 기존 쿼리문을 이용해 특정 항목을 조금씩 바꿔 출력해보기를 반복하면 SQL을 익히는데 도움이 된다. 이렇게 약 하루 동안 시간을 쏟으면 딱 내가 필요한 데이터를 뽑아낼만한 쿼리문 작성까지 가능해진다. 개발 분야와 데이터 분석을 잘 모르는 사람도 말이다.
물론 이런 방식은 무비판적으로 기존의 쿼리문을 수용하게 되기에 좋지 못한 쿼리문을 만들어낼 때가 많다. SQL에선 출력된 결과물은 같더라도 좋은 쿼리문이 따로 있다. 이유는 데이터가 많으면 많을수록 불필요한 출력 과정은 줄여주는 게 좋기 때문이다.
아까 이야기했던 "이 1번 방에서 하얗고 네모나고 반짝이는 물건들만 골라내."를 예로 들면 이해하기 쉽다. 먼저 하얀 걸 다 골라내고, 그다음에 네모난 걸 골라내고, 그다음에 반짝이는 걸 골라낸 다음에 교집합을 찾는 1번 방법. 처음부터 하얗고 네모나면서 반짝이는 물건을 순차적으로 골라내는 2번 방법. 어떤 방법이 더 빠르게 가능할까? SQL을 잘 다루는 사람은 전자보다 후자에 가깝게 쿼리문을 만들어낸다.
다만 크게 신경 쓸 필요는 없다. 이런 게 있다는 것만 알아두면 된다. 앞서 말했듯이 제대로 개념부터 배워가는 사람이 참고하면 되는 내용이다. 입문자이면서 딱 실무에서 쓰는 쿼리문을 바꿔 쓸 정도를 원했기에 내게는 해당되지 않는 내용이었다. 서로의 업무를 효율적으로 해나가기 위한 목적이었으니까. 만약 SQL을 해보다가 재미를 느꼈다면 그때 차근차근 도전해봐도 좋다고 생각한다.
