마케터를 위한 SQL

Intro

과정 목표

● 데이터 분석에 대한 핵심만 설명

● 단순 SQL이 아닌 분석 과정/환경과의 연계 설명

 

 

주요 커리큘럼

●시대의 흐름과 SQL의 역할

● 데이터베이스 기본 개념 및 용어 소개

● 실습환경 및 사내 분석환경/툴 소개

● 분석에 필요한 SQL 이론 설명

● 실습

 

 

기획자와 마케터에게 SQL이 필요한 이유

기술과 트렌드의 대홍수

 

Digital Transformation

결론적으로 보면, 분석을 해야 하는 데이터의 양과 종류가 예전보다 훨씬 많아짐

그래서 회사는 우리에게, Data Scientist 역량을 요구

 

Data scientist

엑셀과 SQL로 데이터를 다루고 분석하는 영역의 상당 부분을 커버할 수 있음

 

 

일반적인 기업의 내부 데이터 저장/분석 환경은?

기업 내부의 데이터 저장/분석 환경

SQL을 할 줄 알면, 데이터 웨어하우스에서 직접 내가 원하는 데이터를 추출할 수 있다.

 

 

데이터베이스 핵심용어

데이터 저장 및 분석 프로세스

데이터 수집 (크롤링, 내부수집) -> 데이터 저장(엑셀, 데이터베이스-MySQL, MSSQL, Oracle, Mongdb) -> 데이터 분석(통계, 조회) -> 데이터 시각화(차트, 그래프) -> 모델링(머신러닝, 딥러닝) ->서비스(프로그램)

 

데이터베이스(database)

● 위키피디아 데이터베이스 정의

- 데이터베이스(database, db)는 체계화된 데이터의 모임이다. 즉, 작성된 목록으로써 여러 응용 시스템들의 통합된 정보들을 저장하여 운영할 수 있는 공용 데이터들의 묶음이다.

 

● 데이터베이스의 역사

- 1970년 관계형 모델이 제안되면서 현재의 MySQL, Oracle 등 Relational Database(RDBMS)가 보편적으로 됨. -

최근 Bigdata 시대와 더불어 대용량 데이터를 분산 저장 및 조회할 수 있는 NoSQL 데이터베이스가 등장

-NoSQL 데이터베이스의 높은 성능에 부합하면서 관계형/SQL 모델을 보유하는 NewSQL 구현 시도

 

(관계형, 공용) 데이터베이스의 장점(엑셀로 하기 어려운 부분)

● 데이터 중복 최소화 ● 데이터 공유 용이 ● 일관성, 무결성, 보안성 유지

● 최신의 데이터 유지 ● 데이터의 표준화 가능 ● 데이터의 논리적, 물리적 독립성

● 데이터 접근 용이 ● 데이터 저장공간 절약

 

(관계형, 공용) 데이터베이스의 단점

● 데이터베이스 전문가 필요 ● 많은 비용 부담(오라클 같은 유료 db 사용 시)

● 데이터 백업과 복구가 어려움 ● 시스템 복잡함

● 대용량 디스크로 엑세스가 집중되면 과부하 발생

 

엑셀 개념 vs 데이터베이스, 테이블, 컬럼, 로우

● 엑셀 파일은 데이터베이스 개념과 유사

● 엑셀 sheet(USER_INFO)는 테이블 개념과 유사

● 데이터베이스에서 로우(row) or 레코드(record), 컬럼(column)은 엑셀의 행, 열과 유사

 

RDBMS

-다수의 테이블을 만든 후, 테이블끼리의 관계를 이용해서 data를 관리한다.

RDBMS장점 - 역사가 오래되었다, 신뢰성이 높다, 데이터 분류, 정렬, 탐색 속도가 빠르다

 

스키마

- 데이터 베이스의 구조, 형식, 제약조건을 정의한 것, 같은 스키마끼리의 구조는 동일하다

-여러 테이블을 담을 수 있다

-MySQL은 데이터베이스가 없고, 스키마가 데이터베이스이다.

 

데이터베이스 관리 시스템(DBMS, Database Management System)

● DMMS : 데이터베이스에서 데이터 조작, 저장, 검색, 보안 및 통합을 제어하는 프로그램

● DBMS는 파일 시스템이 가진 데이터 중복 문제와 데이터 종속 문제를 해결하기 위해 제시된 소프트웨어

 

SQL(Structured Query Language)

●표준 관계형 데이터베이스 언어이며, 관계 대수와 관계 해석을 기초로 한 혼합 데이터 언어이다.

● SQL의 유형은 아래 그림처럼 DML, DDL, DCL, TCL 등이 있으며, 이 중 DML과 DDL을 가장 많이 다루게 됨.

 

SQL은 데이터베이스에서 데이터를 다루기 위한 표준 언어

 

데이터베이스 핵심용어

DDL, DML

 

● 데이터베이스 정의어(DDL, Data Definition Language)

- 스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 변경 또는 제거할 때 사용하는 언어

- 주로 데이터 분석을 하는 사용자/분석가 보다는 관리자(DBA)가 다루는 유형의 언어

 

● 데이터베이스 조작어(DML, Data Manipulation Language)

- 응용 프로그램이나 질의어를 통해 저장된 데이터를 관리 및 조작하는 언어

- DML의 주요 커맨드 유형인 SELECT, INSERT, UPDATE, DELETE 중에서도,

데이터분석가/마케터 등 데이터 조회 및 분석 업무를 하는 사용자는 SELECT문을 주로 사용함.

 

인덱스(INDEX)

인덱스 : 데이터를 찾아갈 수 있도록 추가적으로 생성한 데이터

● 인덱스는 일반 테이블이나 클러스터에서 쓰여지는 선택적인 객체로서, 데이터베이스 테이블 내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조를 말함.

 

● 인덱스 4가지 유형

1. Primary Key(PK, 기본키) - 중복되지 않은 유일키

2. Normal - 중복을 허용하는 인덱스

3. Unique - 중복을 허용하지 않는 유일키

4. Foreign Key(FK, 외래키) - 다른 테이블과 관계성을 부여하는 키

 

기본키, 두번째키, 외래키

 

● Primary Key는 Not null + Unique 개념, 즉 고유한 ID를 말함(그림1)

● Secondary Key는 Unique할 필요가 없으며, 데이터를 빠르게 찾기 위해 지정함

● Foreign Key는 하나 이상의 테이블을 서로 연결하여 사용하는 관계형 데이터베이스에서 하나의 테이블 속성 또는 속성 집합이 다른 테이블의 기본키가 되는 것을 의미(그림2)

 

파티션(Partition)

● 물리적으로 하나의 하드디스크를 논리적으로 두 개 이상의 파티션으로 나누는 것과 유사한 개념

● 논리적으로 나뉜 파티션은 데이터 조회 시 다른 파티션에 IO를 최대한 발생시키지 않도록 설계

● DBA 등 데이터베이스 관리자가 주로 신경 쓰는 영역

 

스키마(Schema)

● 데이터베이스 종류에 따라 스키마는 여러 의미로 사용되기도 함

● 기본적으로 데이터베이스 관리 시스템(DBMS)에서 데이터 구조와 그 표현법의 기술을 수용한 파일. - “그 테이블 스키마가 어떻게 되니?” 와 같은 질문에서의 스키마 의미

● db마다 스키마란 용어는 데이터베이스와 함께 사용되기도, 하위 개념으로 사용되기도 함.

- MySQL에서는 Database와 동일한 용어로 Schema 라는 용어를 사용함

- Oracle, DB2 에서는 Schema가 Database의 하위 개념으로 사용되기도 함.

 

개체(Entity, 엔터티)

● 엔터티 = 테이블 과 거의 유사한 개념

● 엔터티 타입 = 컬럼과 거의 유사한 개념 (속성(attribute)이라고 부르기도 함)

● 스키마 = 테이블 스키마 = 테이블 구조

 

ERD(Entity Relationship Diagram)

엔터티(테이블) 간 관계를 그린 다이어그램

 

뷰(VIEW)

여러 개의 테이블에서 원하는 모든 데이터(컬럼들)를 선택하여, 그들을 사용자 정의하여 나타낸것

● 실제 테이블과 같이 물리적으로 데이터를 포함하지 않음. 논리적으로 하나의 테이블처럼 보이게 함

- Join 기능을 적용한 것과 유사

 

저장 프로시져(Stored Procedure)

일련의 쿼리를 마치 하나의 쿼리처럼 실행할 수 있는 쿼리의 집합

● 자주 사용되는 일련의 쿼리 절차를 RDBMS 시스템에 저장한 것으로 “영구저장모듈”이라 부르기도

 

트랜잭션(Transaction)

● 데이터베이스 내에서 한꺼번에 수행되어야 할 일련의 연산들을 말함

● 간단하게 말하자면 전부 되거나, 혹은 전부 안 되거나.

● 한꺼번에 완료가 된 경우에는 성공적인 종료 COMMIT 

이 경우에 작업 결과는 데이터베이스에 반영이 되게 됨.

 

● 취소가 된 경우에는 비정상적인 종료 ROLLBACK

- 이 경우에 작업 결과는 모두 취소되어 데이터베이스에 영향을 미치지 않음

 

트랜잭션(Transaction) 예제 : 은행 인출기에서 카드로 인출하는 경우

● 은행 인출기에서 카드로 인출하는 경우

- 첫번째. 카드를 넣습니다.

- 두번째. 어떤 거래를 할지 선택을 하고,

- 세번째. 비밀번호를 눌러 인증을 받고,

- 네번째. 거래 완료

 

● 위 4가지 과정을 묶어서 트랜잭션이라 함.

● 네가지 다 정상적으로 종료되어 거래까지 완료되었으면 COMMIT 이라고 하고,

● 중간에 비밀번호를 틀리거나 인증을 받았는데 거래를 취소하거나 하는 일이 발생해서 처음으로 돌아갈 경우에는 ROLLBACK 이라고 함

 

SQL 기반의 데이터 분석 환경 및 도구

Database Client Tools for MySQL

 

MySQL Workbench

● 장점 - 직관적인 인터페이스 지원

- Community 버전은 무료 다운로드 가능

- 윈도우, Mac, 리눅스 운영체제 지원

- MySQL Servers 의 최신 업데이트 특성들을 잘 지원

 

● 단점 - 가끔 버그가 있으며, 큰 쿼리 실행 시 죽기도 함

 

HeidiSQL

● 장점

- 직관적이고 매우 쉬운 유저 인터페이스 지원

- 무료로 사용 가능

- 포터블 버전 지원으로 설치 없이 사용 가능

- 문법체크 및 자동완성기능 지원이 뛰어남

 

● 단점 - 가끔 버그가 있으나 매일 쓰기에는 큰 지장 없음

- 윈도우 친화적이며, 공식적으로 Mac OS 지원 안 함

 

 

Client Tools for Data Analytics

1. 제플린(Zeppline)

● 장점

- 오픈 소스(아파치 탑레벨 프로젝트)

- 인터렉티브한 데이터 분석 가능

- SQL 뿐만 아닌 다양한 언어 지원

- 시각화 차트와의 연계 가능

- 쉽고 빠른 공유 가능(데이터 분석의 협업 가능)

- 데이터를 분석해가는 Flow 형태 지원

- 쥬피터와 유사

 

● 단점 - SQL 언어에 대한 지원 기능만 봤을 때, 전용 클라이언트 툴 대비 기능이 부족함 https://www.youtube.com/watch?v=_PQbVH_aO5E&t=7s : 데모

https://www.youtube.com/watch?v=raHmOCApWg0 : 11분 55초 까지는 윈도우에 제플린 설치 과정

 

 

2. 쥬피터(Jupyter)

● 장점

- 오픈 소스

- 인터렉티브한 데이터 분석 가능

- 파이썬 언어와 호환성 높음

- 시각화 차트와의 연계 가능

- 쉽고 빠른 공유 가능(데이터 분석의 협업 가능)

- 데이터를 분석해가는 Flow 형태 지원

- 제플린과 유사

 

● 단점 - SQL 언어에 대한 지원 기능만 봤을 때, 전용 클라이언트 툴 대비 기능이 부족함

 

 

3. 메타 트론(Metatron)

● 장점

- 오픈 소스

- SQL 전용 워크벤치

- 강력한 차트 기능

- 데이터가 매우 많은 경우에도 빠르게 처리 가능

- 빅데이터 쿼리 엔진(하이브 등) 연동 가능

 

● 단점

- 설치 시 드루이드 엔진 설치가 필요하여 엔지니어 지원 필요

- 차트 버그 존재

 

SK Telecom에서 개발

https://www.youtube.com/watch?v=pVrXecuAqOM : 간단한 활용 데모

https://www.youtube.com/watch?v=Xv4CQm2tbnY : 메타트론 사내 적용 사례발표 동영상 (37분

 

SQL 실습 환경 구성하기

MySQL community 설치(윈도우) (윈도우는 자동으로 workbench가 설치됨, 맥은 별도 설치 필요)

 

데이터베이스 구축 실습

데이터 불러오기 (SELECT 문법)

 

SQL 특성 – 소문자, 대문자 비구별

● SQL은 소문자, 대문자를 구분하지 않음 - 아래의 쿼리는 모두 동일한 쿼리

 

SQL 특성 – 공백, 줄바꿈 무시

● SQL은 공백, 줄바꿈을 무시함

- SQL 작성 시 공백은 무시된다. 때문에 쿼리문이 길어지는 경우 여러 줄로 나눠어서 작성하는 것이 좋다

 

SQL 특성 – 키워드 or 예약어는 변수로 사용 x

● SQL의 키워드 또는 예약어를 컬럼명으로 사용하면 안 됨

- 키워드란 SELECT, FROM, WHERE 등 SQL 문법 상 특별한 의미를 가지며 미리 사용방법이 정해져 있는 예약

 

SQL 특성 – 쿼리문은 세미콜론(;)으로 끝마침

● SQL을 마칠 때는 세미콜론으로 마치는 것이 좋으며, 하나의 쿼리 에디터에 여러 개의 쿼리문을 작성하는 경우 세미콜론을 사용하지 않으면 에러가 난다

 

특정 컬럼 불러오기 : SELECT 컬럼명 FROM 테이블명;

● SELECT USER_NAME FROM USER_INFO; -- 조회할 컬럼이 한 개인 경우

● SELECT USER_NAME, GENDER FROM USER_INFO; -- 조회할 컬럼이 여러 개인 경우

 

데이터 정렬하기 (ORDER BY)

한 개 컬럼으로 정렬하기

● ORDER BY 구문을 활용하여 컬럼명 기준으로 정렬 or 컬럼 위치 기준으로 정렬

 

데이터 중복없이 불러오기 (DISTINCT, COUNT)

DISTINCT : 중복값 제거

ALL : 중복값 허용

 

COUNT : 로우 개수 반환

● COUNT 함수와 DISTINCT 조합으로 중복 없는 row 수를 셀 수 있다

 

ALIAS(별칭): 컬럼명을 원하는 별칭으로 설정하는 방법에는 AS 키워드를 사용하거나 생략하는 방법이 있다.

별칭은 한글도 가능하지만, 비추천. 영문을 사용하는 것이 좋다.

 

조건절을 통한 데이터 불러오기 (WHERE, LIMIT)

WHERE를 사용한 데이터 필터링

● WHERE 조건절은 연산자(등호 등)와 함께 쓰이며, FROM 뒤쪽, ORDER BY 앞쪽에 위치한다.

 

LIMIT : 출력 데이터 제한하기

● LIMIT는 ORDER BY와 함께 사용하여 상위/하위 n개의 제한된 데이터 조회 기능

 

 

논리연산자 활용 및 NULL값 처리 (IFNULL, COALESCE, IN, NOT IN)

NULL값이란?

● NULL값은 0이나 공백과는 다른 의미의 데이터

● NULL값에 대한 데이터 조회를 하기 위해서는 IS NULL 또는 IS NOT NULL 연산자 사용

● MySQL에서 NULL은 가장 작은 값으로 정렬 됨(데이터베이스마다 다름)

 

COALESCE(exp1, exp2, ..., expN)

● 첫번째 NULL이 아닌 값을 출력

 

IFNULL vs COALESCE

COALESCE가 더 권장됨

● COALESCE가 더 여러 개의 expression을 비교할 수 있음

● COALESCE는 다른 데이터베이스(오라클 등)에서도 동일 함수 제공(IFNULL은 MySQL에서만 사용)

● IFNULL(NULL, 1)과 COALESCE(NULL, 1)은 같은 결과를 리턴

 

 

논리연산자 IN과 NOT IN 사용법

- WHERE 절에서 OR 연산자를 여러 번 사용하는 것 대신 IN, NOT IN 연산자 사용하면 효율적

 

 

문자 패턴 적용하여 데이터 불러오기 (LIKE, REGEXP)

LIKE, NOT LIKE 연산자

● 특정 패턴을 포함하는(또는 포함하지 않는) 데이터 조회

 

%(퍼센트), _(언더스코어) 와일드카드

● %는 0개 이상의 문자를 의미하는 와일드카드

● _(언더스코어)는 정확히 한 개 이상의 문자를 대체하고자 할 때 사용하는 와일드카드

 

REGEXP(정규표현식)을 활용한 데이터 조회

● LIKE 연산자보다 더 복잡한 데이터 패턴을 적용해야 할 때

● 정규표현식 패턴 : 모든 프로그래밍 언어에서 사용 가능

 

 

조건문 활용하기

CASE WHEN

파이썬의 if, elif, else 문과 비슷

조건에 해당하면 결과값을 출력하라.

- 조건절은 FROM 키워드 뒤쪽으로, ORDER BY 키워드 보다는 앞쪽에 위치해야 한다.

 

IF ELSE

● 조건절이 TRUE면 첫 번째 value, FLALSE면 두 번째 value 출력

IF(condition, value_if_true, value_if_false)

 

테이블 합치기(join) 개념이해

SQL JOIN

●JOIN이란

- 두 개 이상의 테이블을 연결하여 데이터를 출력하는 것을 JOIN이라고 함

- RDBMS(관계형 데이터베이스)의 가장 큰 장점

- FROM 절에 여러 개의 테이블이 나열되더라도, 실제 처리 시 두 개의 테이블만 조인 됨 예를 들어 A, B, C, D 4개의 테이블을 조인하고자 할 경우 쿼리 옵티마이저는 ( ( (A JOIN D) JOIN C ) JOIN B )와 같이 순차적으로 조인을 처리하게 됨

● JOIN의 방법과 방식

- 조인 방법 : Equi Join(등가조인, 내부조인), Non-Equi Join, Self Join, Outer Join

- 조인 방식 : Nested Loop Join, Sort Merge Join, Hash Join

 

 

칼럼(COLUMN) 합치기 개념

파이썬의 merge, 엑셀의 vloopup 기능과 유사

 

●ERD

- PLAYER 테이블

- STADIUM 테이블

- TEAM 테이블

 

 

로우(ROW) 합치기 개념

파이썬의 concat 개념과 유사

같은 의미의 컬럼 정보를 가지고 있는 서로 다른 테이블을 하나의 테이블로 합치는 경우

 

내부조인

테이블 간 교집합을 구하는 방식

● 방법 2가지 : WHERE 조건 이용, INNER JOIN, ON 이용

 

내부조인 – 테이블명 별칭 사용 가능

● 별칭을 이용한 내부조인

 

내부조인 시 필터 조건, 정렬 사용 가능

● PLAYER, TEAM 테이블을 조인하는데, 

TEAM_ID가 같고, POSITION=‘GK’인 것들만. BACK_NO 정렬

 

 

EQUI-JOIN

Equality Condition(=) 에 의한 조인

(DBA) Equi-JOIN 의 성능을 높이기 위해서는,

조인의 조건 컬럼에 대해 인덱스 기능을 사용하는 것이 좋다. 

 

NON-EQUI-JOIN 이란?

●Equal(=) 연산자가 아닌 다른 (BETWEEN, >=, <=, >, < 등) 연산자를 사용하여 조인을 수행한다

●성능이 좋지 않으며, 거의 사용하지 않는 유형

● BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN

 

JOIN의 유형 살펴보기

JOIN 유형

● (INNER) JOIN - Returns records that have matching values in both tables

● LEFT (OUTER) JOIN - Return all records from the left table, and the matched records from the right table

● RIGHT (OUTER) JOIN - Return all records from the right table, and the matched records from the left table

● FULL (OUTER) JOIN - Return all records when there is a match in either left or right table

 

INNER JOIN (INNER 생략가능)

JOIN 구문의 조건이 A 와 B 테이블 양쪽 테이블에 일치하는 결과, 쉽게 말해 교집합을 구함

 

LEFT OUTER JOIN (OUTER 생략가능)

왼쪽 테이블을 기준으로 오른쪽 테이블의 JOIN 조건에 일치하는 데이터를 출력

 

LEFT OUTER JOIN (활용)

조건절을 통해 A테이블에만 존재하는 데이터를 조회할 수 있음

 

FULL OUTER JOIN

테이블 A 와 B 의 합집합을 구하는 JOIN

 

FULL OUTER JOIN (활용)

조건절을 활용하여 각각의 테이블에만 존재하는 데이터를 조회할 수 있음