SQL

발단

SQL을 배워야 할 것 같다.

​

왜? 데이터 관련 인력을 뽑는 스타트업들의 Job Description을 비교하다 보니 그런 답이 나왔다. python이나 scala를 요구하는 건 모델링, 알고리즘 개발 쪽에 가깝고, 비즈니적인 인사이트를 필요로 하는 쪽 포지션에서는 SQL/R을 보더라.

데이터 업무의 성격에 따라 아래와 같은 스펙을 요구하는 것 같다.

​

Excel/SQL ------ R ------ Python ----///-- Scala, 기타 Hadoop 관련 경험

​

분석에 필요한 데이터를 DB에서 자유자재로 추출하려면 SQL 정도(!)는 능숙해야 하는 모양이다.

​

1-2. 생활코딩: 웹 애플리케이션 만들기

(https://opentutorials.org/course/1688)

​

웹에 대한 이해, 클라이언트 - 서버라는 전체 구도를 이해시키는 데에 중점을 둔 수업이다.

​

30여개 가량의 토픽으로 구성되어 있고, HTML, CSS, JavaScript, PHP, mySQL을 필요한 만큼만 사용하면서 실제로 동작하는 웹페이지를 만들어 본다. 그 중에서도 DB에서 데이터를 넣고빼고 연결하는 부분이 이 수업의 정점이라고. 그걸 모르고 난 작년에 그부분만 쏙 빼고 들었다.

​

수업은 이론-실습-이론-실습... 이런 식으로 교차 진행된다. 즉 HTML로 골격을 만들고, 다른 언어를 하나씩 배우면서 살을 붙여나가는 형식이다. 이론 부분은 mySQL에 해당하는 부분만 떼어서 들어도 무리 없다.

​

실습은, 앞부분은 그냥 따라해보면 되는데 뒷쪽은 앞에서 만들어온 코드에 덧붙이므로 맥락이 좀 붕 뜬다.

난 전체 그림을 이해하고 싶어서 겸사겸사 처음부터 다시 들었다.

SQL과 DB에 해당되는 수업 목록은 아래와 같다.

​

데이터베이스(mySQL) 이론 1~3 (18:58 / 15:21 / 33:09)

mySQL 실습 1~5 (13:37 / 25:16 / 11:41 / 15:29 / 20:10)

관계형 데이터베이스 이론 (17:43)

관계형 데이터베이스 실습 1~3 (10:13 / 8:31 / 36:42)

 

2. 실습 환경 구축하기

​

쓰다보니 순서가 좀 꼬이는데, 위의 생활코딩 수업을 full로 듣느냐, 필요한 부분만 듣느냐에 따라 실습환경 구축 시점이 달라질 수 있다.

​

개인적으로는 그 Bark 기획자의 권고사항을 따른 게 내 프로그래밍 공부의 신의 한수였다고 생각한다. 처음에는 생활코딩 동영상만 그냥 쭉 보고, 실습 부분도 눈으로만 보라는 거다.

당장 해보겠다고 달려들어 뭐 깔고 뭐 설정하고, 그런거 하다보면 비개발자 비전공자 문돌이로서 지쳐나가떨어지기 딱 좋다. 대강 어떻게 돌아가는지 감만 잡고, 해보고 싶어 손이 근질근질할 때 차근차근 해나가면 된다.

 

2-1. 생활코딩

​

아까 그 '웹 애플리케이션 만들기' 수업 중에 보면 내컴퓨터에 웹서버 설치하는 실습이 있다.

​

윈도우에 웹서버 설치 / 맥에 웹서버 설치

​

이 수업을 들으면 Bitnami를 이용해 Apach, mySQL, PHP를 내 컴퓨터에 한번에 설치하게 된다. 그러고 나면 위의 mySQL/RDB 실습의 이런저런 내용을 따라해볼 수 있는 것이다.

​

이런 설치 과정을 수업따라 수월하게 넘어가는 게 중요하다. 괜히 초반에 힘빼면 정작 재미를 느껴야 할 부분에 쓸 기력이 없다.

 

SQL

SQL은 Structured Query Language의 약자로 관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어입니다. 그냥 데이터를 뽑아내기 위한 도구라고 생각하면 조금 이해하기 쉬울 것 같습니다. 대부분의 회사에서 데이터를 RDB에 저장하고 있습니다. 많이 사용되는 것은 MySQL, PostgreSQL, MariaDB, Oracle 등이 있습니다

기본적인 구조

 

 

실습 환경

실습 환경은 별도 구성 없이 온라인에서 진행합니다
http://sqlfiddle.com/으로 들어가신 후, 왼쪽 창에 아래 내용을 복사 붙여넣기하고 Build Schema 버튼을 눌러주세요!( MySQL 5.6 사용 )

1. “1”번 유저의 모든 이벤트 로그를 확인해보세요

SELECT user_id, event, event_date
FROM user_log
WHERE user_id = '1';

 

3줄로 원하는 데이터를 뽑아냈습니다! 데이터를 보면 1번 유저는 3월 12일에 페이스북으로 로그인했습니다. 그리고 포스팅을 1번 작성했고 포스팅을 2번 봤습니다. 3월 14일에 포스팅을 1번 봤습니다.

쿼리를 통해 이런 정보를 알 수 있었는데, 여기서 아쉬운 점은 같은 이벤트가 중복해서 2줄로 나와있는 점입니다. 위 결과에선 1번 유저가 view_posting을 3월 12일에 2번 했다는 것을 저희가 직접 Count를 해야합니다.

 

우선 GROUP BY하기 위해 같은 값들을 모아두고, 그 후에 연산(COUNT 혹은 SUM 같은 집계 함수)을 수행합니다

코드를 통해 보여드리겠습니다.

 

SELECT user_id, event, event_date, COUNT(DISTINCT user_id) AS 'unique', COUNT(user_id) AS 'total'

FROM user_log

WHERE user_id = '1' GROUP BY user_id, event, event_date;

• COUNT
  • 개수를 Count하는 친구입니다
  • COUNT(개수를 셀 컬럼) 이런 방식으로 사용합니다
  • COUNT(user_id)를 하면 user_id의 개수를 세주는 것입니다
• DISTINCT
  • COUNT(DISTINCT user_id)를 하면 중복을 제외한 고유한 user_id의 개수를 세주는 것입니다
• AS ‘unique’
  • 값을 센 후 이름을 unique로 칭하겠다라는 뜻입니다

 

여기서 또 조금 아쉬운 점은 event_date 기준으로 정렬되어 있지 않은 점!!! 이건 ORDER BY 로 해결할 수 있습니다.

ORDER BY

• ORDER BY [컬럼 이름] 으로 사용합니다. 기본 옵션은 오름차순이며 DESC을 붙여주면 내림차순으로 정렬됩니다. ORDER BY event_date DESC 이런 식으로 하면 최신 날짜부터 정렬됩니다

위 결과를 해석하면 1번 유저는 write_posting을 3월 12일에 1회 했고, view_posting을 2회 했습니다! WHERE 조건에 user_id를 1로 고정했기 때문에 unique값은 모두 1입니다. 만약 id를 제외하고 event, event_date만 뽑으면 어떻게 될까요?

SELECT event, event_date, COUNT(DISTINCT user_id) AS 'unique', COUNT(user_id) AS 'total'

FROM user_log

GROUP BY event, event_date

ORDER BY event_date;

 

이제 특정 이벤트가 날짜별로 몇명이 했고, 몇번 했는지를 알 수 있습니다! 조금 더 쉽게 보고싶다면 데이터를 엑셀로 가져가 그래프로 그리면 될 것 같습니다!

다시 돌아와서 또 다른 쿼리를 짜볼게요. 아마 데이터를 자주 보는 회사라면 DAU, WAU, MAU라는 말을 들을 수 있을거에요! DAU를 뽑아내는 쿼리를 만들어 봅시다

 

2. DAU 뽑기

• DAU의 정의 : Daily Active User로 저희 서비스에서 어떤 이벤트라도 했던 사람을 Active로 정의하겠습니다
• 이 경우 어떻게 쿼리를 날려야 할까요? 직접 쿼리를 짜보세요!
•  

SELECT event_date, COUNT(DISTINCT user_id) AS 'DAU'

FROM user_log

GROUP BY event_date ORDER BY event_date;

 

 

2-1. DAU가 2 이상인 날짜 뽑기

• GROUP BY를 통해 나온 값을 조건으로 걸고싶은 경우는 어떻게 해야할까요?
• 이럴 경우엔 HAVING이란 친구가 나옵니다

HAVING

• GROUP BY의 바로 아래에 작성해주시면 됩니다
• HAVING 조건
  
  그룹화를 하기 전 컬럼에 대한 조건이라면 WHERE, 그룹화를 한 후의 컬럼에 대한 조건은 HAVING을 사용하는 것입니다

처음 SQL 접하시는 분들이 자주 하시는 질문

• WHERE과 HAVING의 차이가 무엇인가요?
  • WHERE은 현재 Table에서 조건을 뽑아내는 것이고 HAVING은 그룹화한 후 결과에서 조건을 뽑는 것입니다. 동시에 사용하는 경우도 있으며, HAVING은 주로 GROUP BY와 함께 쓰입니다

 

짧은 정리

맨 처음에 보여드렸던 기본적인 SQL 구조를 다시 보여드릴게요. 생각보다 많은 것이 보일거에요!

맨 처음에 보여드렸던 기본적인 SQL 구조를 다시 보여드릴게요. 생각보다 많은 것이 보일거에요!

 

SQL은 집합적 관점에서 접근하면 조금 더 쉬운데, 여태까지 배운 내용을 집합으로 표현해보겠습니다

 

 

Join

Join은 2개 이상의 Table을 조합해 새로운 가상 Table처럼 만들어 결과로 보여줍니다. 여러 Table을 연결한다고 생각하면 좋을 것 같습니다

Join은 왜 필요할까?

데이터베이스에 대한 지식이 없을 땐, 모든 Data를 하나의 Table에 넣으면 Join이 필요없을텐데 왜 굳이 Table을 나눠서 저장할까?라는 의문을 가졌습니다.

관계형 데이터베이스는 정규화 과정을 거쳐 데이터 중복을 최소화해 데이터를 관리합니다.
이 정규화 과정을 거치면 Table끼리 관계(Relation)를 갖게 됩니다. 또한 저장 공간의 효율성과 확장성이 증가됩니다!

Table에 저장된 데이터를 효과적으로 검색하기 위해 Join을 사용합니다. 제 사례를 들자면, User의 상태 데이터는 user_state table에 저장하고 User의 로그 데이터는 user_log table에 저장했습니다. 그리고 다양한 행동 패턴을 분석할 때 (예를 들어, 블로그에 누적 글이 3개 이상인 유저들과 3개 미만인 유저들의 행동 패턴을 비교하고 싶을 경우) Join을 사용했습니다

Join의 종류

• Inner Join
• Cross Join
• Self Join
• Outer Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Join 문법

 

 

데이터베이스개론 듣다가 무슨 소리인지 모르겠어서

후다닥 비전공자 SQL를 맛만 봤다 ㅠㅠ

http://sqlfiddle.com/

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

앞으로 여기서 SQL연습해야겠다.