[TIL - 22일 차] 데이터 웨어하우스와 SQL과 데이터 분석 (2)

SELECT 배우기

예제 테이블 소개

웹서비스 사용자/세션 정보

세션이란 사용자의 방문을 논리적인 단위로 나눈 것이다. 하나의 사용자는 여러 개의 세션을 가질 수 있으며, 보통 세션을 만들어낸 접점(경유지)을 채널이란 이름으로 기록해 두어 마케팅 관련 기여도 분석에 활용한다.

• 사용자 ID : 보통 웹 서비스에서 등록된 사용자마다 부여하는 유일한 ID
• 세션 ID : 세션마다 부여되는 ID

이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능하다. 일주월별로 Unique User의 수를 사용자 ID를 통해 카운트하여 Active User를 확인할 수 있다. 예를 들어 WAU의 경우, 일주일 내에 여러 번 방문을 한 User를 1로 계산하여 Active User를 카운트한다.

• 마케팅 관련, 사용자 트래픽 관련
• DAU, WAU, MAU 등의 일주월별 Active User 차트
• Marketing Channel Attribution 분석(어느 채널에 광고를 하는 게 가장 효과적인가?)

세션 예제

아래의 그림은 사용자가 총 3개의 세션(녹색 배경)을 갖는 예제이다.

• 세션 1 : 구글 키워드 광고로 시작된 세션
• 세션 2 : 페이스북 광고를 통해 생긴 세션
• 세션 3 : 네이버 광고를 통해 생긴 세션

세션 예제

데이터베이스와 테이블

raw_data의 데이터베이스(스키마)를 나타낸 것이다. userId는 여러 개가 생길 수 있기 때문에 Primary Key가 될 수 없다. session_timestamp의 ts는 해당 sessionId가 생긴 시간을 의미한다.

데이터베이스	컬럼	타입
user_session_channel	userId	int
	sessionId	varchar(32)(PK)
	channel	varchar(32)

데이터베이스	컬럼	타입
session_timestamp	sessionId(PK)	varchar(32)(PK)
	ts	timestamp

SQL 소개

SQL 기본

• 다수의 SQL 문을 실행한다면, 세미콜론으로 분리 ex) SQL1;SQL2;SQL3;
• 주석 : --(한 줄), /* ~ */(여러 줄)
• SQL 키워드는 나름의 포맷팅이 필요, 특히 팀 프로젝트라면 팀에서 사용하는 공통 포맷 필요
• 테이블/필드 이름의 명명규칙을 정하는 것이 중요 ex) 단수형 vs 복수형, _ vs CamelCasting

DDL - 테이블 구조 정의 언어

• CREATE TABLE : 테이블 생성, PK를 지정하더라도 데이터 웨어하우스는 PK Uniqueness가 보장되지 않을 수 있음

CREATE TABLE raw_data.user_session_channel (
  userid int,
  sessionid varchar(32) primary key,
  channel varchar(32)
);

• DROP TABLE : 테이블 삭제, 존재하지 않는 테이블을 지우려고 할 경우 에러 발생

DML의 DELETE은 조건에 맞는 레코드를 지우고, 테이블 자체는 유지된다. 그러나 DROP TABLE은 테이블 자체를 삭제한다.

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

• ALTER TABLE : 새로운 컬럼 추가, 기존 컬럼 이름 변경, 기존 컬럼 제거, 테이블 이름 변경

ALTER TABLE table_name ADD COLUMN field_name field_type; -- 새로운 컬럼 추가
ALTER TABLE table_name RENAME field_name to new_field_name -- 기존 컬럼 이름 변경
ALTER TABLE table_name DROP COLUMN field_name; -- 기존 컬럼 제거
ALTER TABLE table_name RENAME to new_table_name; -- 테이블 이름 변경

DML - 테이블 조작 언어

• SELECT : 레코드 질의 언어, FROM / WHERE / GROUP BY / ORDER BY 등과 함께 사용
• INSERT INTO : 테이블에 레코드를 추가
• UPDATE FROM : 테이블 레코드의 필드 값 수정
• DELETE FROM : 테이블에서 레코드를 삭제, (vs TRUNCATE)

SQL 실습 환경 소개

실습 전 기억할 점 (1)

현업에서 깔끔한 데이터는 존재하지 않기 때문에 항상 데이터를 의심하는 자세가 필요하다. 아래는 데이터의 품질을 체크하는 방법이다.

• 중복된 레코드 제거
• 최근 데이터 존재 여부 체크
• PK Uniqueness 체크
• 값이 비어있는 컬럼 체크
• Unit Test 형태로 만들어 위의 내용을 쉽게 검사 가능

실습 전 기억할 점 (2)

어느 시점이 되면 많은 테이블이 존재하게 된다. 이는 회사 성장과 밀접한 관련이 있으며, 중요 테이블이 무엇이고 해당 테이블의 메타 정보를 관리하는 것이 중요해진다. 그 시점부터 Data Discovery 문제가 발생하게 된다. 이러한 문제를 해결하기 위해 DataHub(LinkedIn), Amundsen(Lyft), Select Star, DataFrame 등과 같은 오픈소스와 서비스가 생겨났다.

• Data Discovery 문제 : 무슨 테이블에 내가 원하는 정보가 있는가?, 테이블에 대한 질문을 누구에게 해야 하는가?

SELECT 소개

SELECT (1)

테이블에서 레코드를 읽어오는 데 사용한다.

SELECT field_name_1, field_name_2, ...
FROM table_name
WHERE condition
GROUP BY field_name_1, field_name_2, ...
ORDER BY field_name [ASC|DESC] -- 필드 이름 대신에 숫자 사용 가능
LIMIT N;

SELECT 예제

raw_data.user_session_channel 테이블의 모든 필드(*)를 읽어오도록 하는 SELECT문이다.

SELECT *
FROM raw_data.user_session_channel;

/*
userid	sessionid	channel
1491	00029153d12ae1c9abe59c17ff2e0895	Organic
59	0002ac0d783338cfeab0b2bdbd872cda	Naver
117	0006246bee639c7a7b11a08e34dd3cc6	Youtube
...
*/

유일한 channel을 가져오도록 SELECT문에 DISTINCT를 사용하였다.

SELECT DISTINCT channel                      -- 유일한 채널 이름을 알고 싶은 경우
FROM raw_data.user_session_channel;

/*
channel
Naver
Youtube
Instagram
Facebook
Organic
Google
*/

COUNT를 사용하여 해당 channel의 개수를 카운트할 수 있다. 여기서 '1'은 일련번호로 나타낸 것으로 channel을 나타낸다. 따라서 1을 channel로 변경해도 똑같은 결과를 얻을 수 있다.

SELECT channel, COUNT(1)                    -- 채널별 카운트를 하고 싶은 경우. COUNT 함수!!
FROM raw_data.user_session_channel
GROUP BY 1;

/*
channel	count
Facebook	16791
Instagram	16831
Naver	16921
Youtube	17091
Organic	16904
Google	16982
*/

CASE - WHEN

필드 값의 변환을 위해 사용 가능하며, 여러 조건을 사용하여 변환하는 것도 가능하다.

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE value3
END field_name

CASE - WHEN을 사용하여 channel_type 컬럼을 새롭게 구성하는 SELECT문이다.

SELECT
  channel,
  CASE
    WHEN channel in ('Facebook', 'Instagram') THEN 'Social-Media'
    WHEN channel in ('Google', 'Naver') THEN 'Search-Engine'
    ELSE 'Something-Else'
END channel_type
FROM raw_data.user_session_channel
LIMIT 100;

/*
channel	channel_type
Organic	Something-Else
Facebook	Social-Media
Facebook	Social-Media
Facebook	Social-Media
Instagram	Social-Media
Facebook	Social-Media
Naver	Search-Engine
...
*/

NULL

0과 ""과는 다른 값이 존재하지 않음을 나타내는 상수이다. 필드 지정 시 값이 없는 경우 NULL로 지정이 가능하며, 어떤 필드의 값이 NULL인지 파악하기 위해서는 특수한 문법(is NULL, is not NULL)을 사용해야 한다. 사칙연산에 NULL이 적용되면 결괏값은 모두 NULL이 된다.

COUNT

COUNT 인자의 값이 NULL이 아니면 카운트가 된다. 따라서 1, 0의 경우 모든 레코드가 포함되며, NULL은 0이 된다. value는 NULL이 하나 있기 때문에 6이며, DISTINCT를 사용하면 NULL을 제외하여 4가 된다.

• count_test 테이블 : value(컬럼) - NULL, 1, 1, 0, 0, 4, 3

SELECT COUNT(1) FROM count_test; -- 7
SELECT COUNT(0) FROM count_test; -- 7
SELECT COUNT(NULL) FROM count_test; -- 0
SELECT COUNT(value) FROM count_test; -- 6
SELECT COUNT(DISTINCT value) FROM count_test; -- 4

WHERE - IN / LIKE, ILIKE / BETWEEN

• IN

WHERE channel in ('Google', 'Youtube'); -- channel이 Google 혹은 Youtube

• LIKE, ILIKE : 해당 문자가 포함된 레코드 탐색, LIKE는 대소문자를 구별하고 ILIKE는 구별하지 않음

WHERE channel LIKE 'G%'; -- G로 시작하는 channel, 뒤에는 아무거나
WHERE channel LIKE '%o%'; -- o가 포함된 channel

• BETWEEN : 날짜 범위 매칭을 위해 사용

STRING Functions

• LEFT(str, N), RIGHT(str, N), REPLACE(str, exp1, exp2)
• UPPER(str), LOWER(str), LEN(str), LPAD, RPAD, SUBSTRING
• ...

ORDER BY

읽어온 레코드를 특정 필드를 기준으로 오름차순 혹은 내림차순으로 정렬하여 나타낼 수 있다. NULL은 오름차순일 경우 마지막에 위치하며, 내림차순일 경우 처음에 위치한다. 이를 바꾸고 싶다면 NULLS FIRST 혹은 NULLS LAST를 사용하면 된다.

ORDER BY 1 ASC; -- Default는 ASC
ORDER BY 1 DESC;
ORDER BY DESC, 2, 3