[SQL 강의 후기] SeSAC 러닝스푼즈 데이터 드리븐 마케팅 강의 : SQL 기본 문법 배우기! 기초 연산자 및 함수를 알아보자 (2,3)

B2C 기업이라면 필히 고객 데이터를 볼 수밖에 없습니다.

특히 게임 등 온라인 서비스일 수록 고객데이터가 매우 중요! 또 중요!

 

그런 의미에서 이번 강의는 CRM을 위한 데이터 분석입니다!

데이터분석가적인 시각보다는, 조금 더 마케터의 시각에서 보는 데이터가 될 거예요.

 

이번 주차 강의는 CRM 지표를 이해하는 법과 SQL 중심이니 이에 관한 필기가 올라올 예정입니다!

 

---

 

< 집계 함수  >

- 집단별 요약값을 생성하는 집계함수!

특징1) SELECT 절에서 집계 함수를 사용하여 요약 가능

특징2) 집단별 요약값을 추출 할 경우 GROUP BY 절을 이용하여 집단의 기준 정의

특징3) 집단이 여러 개일 경우 GROUP BY 절에 콤마 ’,’ 이용하여 구분

특징4) 집계기준의 컬럼명이 길거나 계산식일 경우 GROUP BY 절에는 컬럼의 순서값만 써 줄 수 있음!

주요 집계함수 종류!

1) COUNT

 

1- COUNT 컬럼명

- NULL값은 제외, 행 들을 카운트

 

2- COUNT DISTINCT 컬럼명

- 컬럼값의 중복 및 NULL값을 제외, 유니크한 값의 수를 카운트

 

3- COUNT *

- 특정 컬럼 지정X, 모든 행 갯수 카운트, NULL도 포함됨

 

 

예제) 상품 중 가장 가격이 비싼 상품과 가장 싼 상품품의 가격, 평균 상품의 가격 추출

 

예제) 고객 데이터에서 각 국가별 유저 수 추출

 

예제) 가장 주문을 많이 한 유저의 CustomerID 와 주문건수 추출 (팁. 내림차수 정렬)

집계기준의 컬럼명이 길거나 계산식일 경우 GROUP BY 절에는 컬럼의 순서값만 써 줄 수 있는 점을 이용하자!

 

너무 길어서 실습 말고 그냥 예제로 캡쳐했다.......

 

2) HAVING

- 이미 집계된 요약값에 조건 부여!!!

- 조건을 만족하는 데이터만 필터링

 

 

이때! 조건 부여하는 WHERE과 HAVING의 관계

=> GROUP BY로 요약되기 전/후 관계에 따라 순서

즉 WHERE - GROUP BY - HAVING 순으로 작성!

 

 

실습) 5번 이상 주문 진행한 사람들의 ID와 주문 갯수를 추출하기

* 이때 ID를 기준으로 내림차순 진행

심화) 위의 실습 예제에서 1997년 1월 1일 이후 구매한 건! 이라는 조건을 추가해보면

WHERE 절로 해당 조건을 추가해주었다!

 

CF. WHERE + HAVING은 어려우니까 연습을 진행해보자. 사이트에서 EXERCISE 제공중

https://www.w3schools.com/sql/sql_exercises.asp

SQL Exercises

 

3) CAST

- 데이터의 성질을 변화시키는 함수

  EX. 문자형으로 작성된 숫자를 숫자형으로 변환

- CAST(컬럼명 또는 컬럼 번호 AS 변환할 데이터 타입)

- 여러 DBMS에서 데이터 타입으로 INT, FLOAT, CHAR, DATE 등을 주로 사용

 

 

<지표를 직접 만들 수 있다>

문제 : 주별 구매자수를 요약하려면? 요일단위로는? -> 어떤 함수를 활용해야 할까?

 

=> MYSQL에서 제공하는 DATE OF TIME FUNCTION 리스트를 이용해보자.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

 

 

1) 주별 리포트 뽑아내기 => YEARWEEK 함수 활용

     CF. 엑셀처럼 요일을 번호로 활용, 7 입력시 일요일을 주의 시작으로 정의

너무 길어서 사진으로 대체...&nbsp;이러한 방식으로 도출해낸 도표를 바탕으로 분석이 시작된다!

 

< 여러 개의 테이블 연결하기 : JOIN >

테이블을 계속 붙이면 다양한 문제 발생

- 중복 데이터 증가

- 불일치 및 오류 데이터 발생 가능

=> 따라서 관계 데이터를 최소 단위로 쪼개서 구성 후 연결

     연결 기준은 KEY로 지정하여 관리!

 

1) PRIMARY KEY

테이블 안의 모든 '행'을 중복 없이 구분할 수 있는 키

 

- 중복값 허용 안하는 고유한 값!!

- NULL 안됨

 

CF. KEY 확인 방법 : 테이블 스키마 확인하기!

=> DESC 테이블명;

 

이렇듯 고유한 값인 PRIMARY KEY를 기준으로 테이블 연결

 

2) FOREIGN KEY

 

Join 활용 시, 관계가 있는 테이블의 기본값을 참조하는 컬럼

=> 엑셀의 VLOOKUP과 비슷한 개념 (고유한 값을 기준으로, 열번호를 입력해 데이터 불러왔음)

 

즉 이러한 KEY들 (PRIMARY - FOREIGN) 기반으로 데이터 연결

 

EX. CustomerID를 KEY로 2개 테이블을 합친 버전

3) 2개 테이블 연결하기 (JOIN)

 

두 개의 테이블을 하나로 연결하기 위한 함수 - JOIN 절 / ON 절

 

- JOIN 절은 FROM에서 불러온 테이블에 연결할 테이블을 명시하는 절

- ON 절은 두 테이블을 합치는 기준(KEY)를 명시하는 절

   => 테이블1이름.KEY = 테이블2이름.KEY 형태로 입력

 

이러한 순서로 JOIN 절과 ON 절을 작성한다.

 

예제) 주문 상세 표와 제품 표를 합치기; 제품 ID를 KEY로 활용하기

JOIN 절에서 Products 표를 P로 칭함. 이와같이 AS 구문을 활용해 테이블명을 축약해서 활용해도 좋다.

 

실습) 카테고리 ID를 기준으로 PRODUCTS 표와 CATEGORY 표를 합쳐보자.

 

3) JOIN 함수의 여러 종류

집합처럼 생각해보자 FROM 절에서 불러오는 테이블을 A, JOIN 절에서 불러오는 테이블을 B라 하면

 

1- INNER JOIN : 두 테이블이 공통으로 가지고 있는 데이터만 기준으로 합치기 => A와 B의 교집합

 

강사님의 INNER JOIN 예시. SELECT 절에 불러올 열 이름을 다 적어줘야 한다.

 

2- LEFT JOIN : JOIN 절을 기준으로 왼쪽(FROM절, A)에 있는 테이블을 기준으로 합치기 => A

     => FROM 절의 데이터에서 매칭되는 데이터가 JOIN 절의 테이블에 없더라도

          데이터가 빠자지 않고 유지됨!!! (해당 값은 NULL로)

     => 기준이 되는 테이블의 행이 유지되기 때문에 매칭이 안된 케이스를 파악하기 쉬움

 

강사님의 LEFT JOIN 예시

 

강사님의 예제. 우측 테이블의 맨 마지막, 데이터가 지워지는 절은 합쳤을 시 빈칸으로 나타남(NULL)

 

CF1. WHERE 절에 조건을 넣으면 모든 전체 행에 조건이 적용되는 특징

=> 따라서 JOIN 작업을 완료한 후에, WHERE 절로 조건을 작성해 데이터 필터링 가능

 

WHERE 절에 JOIN의 테이블명 - . - 칼럼명 - 수식 - 조건 순

 

 

CF2. ON 절에 조건이 들어가면 FROM 절 데이터는 유지, JOIN 절의 데이터만 제약 발생

 

ON 절 작성 후, 뒤에 AND 연산자 - JOIN에 있는 테이블명 -. - 칼럼명 - 수식 - 조건&nbsp; &nbsp;순서로 작성

 

 

3- RIGHT JOIN : JOIN 절을 기준으로 오른쪽(JOIN절, B)에 있는 테이블을 기준으로 합치기 => B

 

CF. 두 테이블 중 하나라도 가지고 있는 모든 경우를 합치기 => 이건 MYSQL에서 지원 안하므로 스킵

 

 

<서브쿼리와 임시 테이블>

CF. MYSQL 의 특정 버전에서는 임시테이블 기능을 사용할 수 없다.

 

- 서브쿼리 : 쿼리문을 통해 생성한 결과를 마치 테이블처럼 사용하는 것!

 

=> FROM 뒤에 테이블 명 적는 자리에 괄호로 묶은 쿼리문을 넣어주는 형식

 

cf. 데이터와 관련된 가장 기초 언어는 SQL

데이터 전문 분석 관련으로 파이썬과 R 등을 활용

 

< 행 단위 데이터 합치기 : UNION >

1. UNION이란?

 

- 추출된 결과값을 아래로 추가하는 형식 - 동일한 형태의 데이터를 하나로 합치기!

=> 형태가 동일한 결과값을 행으로 추가!

- 컬럼수 / 컬럼 형식이 같으면 union 가능

- JOIN문처럼 기준이 되는 KEY가 필요하지 않음

- 중복을 제거하는 작업을 기본으로 탑재중 : 중복 허용하고 싶으면 UNION ALL 활용

 

UNION 을 통해 데이터 테이블을 하나로 합친 모습 예시

 

- 주의사항 : 컬럼 갯수, 형식 등이 다르면 합칠 수 없음 (VLOOKUP 을 생각해보자!!!)

ex. A 데이터테이블은 이름 나이 학년, B데이터테이블은 이름 학년 나이 순으로 열 형식이 다르므로 둘을 합칠 수 없음!

 

< 실습 > 지표 만들기

 

 

 

 

 

 

 

 

 

date function