주제
1. 관계형 데이터베이스 예제
2. Redshift 중심의 SQL
2.1 DDL
2.2 DML
3. 실습 전 기억할 점
관계형 데이터베이스 예제 - 웹 서비스 사용자 / 세션 정보
- 사용자 ID : 사용자마다 부여하는 유일한 ID
- 세션 ID : 세션마다 부여되는 ID
- 세션 : 사용자의 방문을 논리적인 단위로 나눈 것
- 사용자가 외부 링크를 타고 오거나 직접 방문해서 올 경우 세션을 생성
- 사용자가 방문 후 30분간 interaction이 없다가 무언가를 하는 경우 새로 생성
- 세션이 생긴 시간도 기록
- 사용자 ID 100번 : 총 3개의 세션을 갖는 예제
- 세션 1 : 구글 키워드 광고로 시작한 세션
- 세션 2 : 페이스북 광고를 통해 생긴 세션
- 세션 3 : 네이버 광고를 통해 생긴 세션
- 어떤 채널에게 기여도를 주어야 하는 지 결정 -> Marketing Channel Attribution 분석
- First channel attribution : 첫번째 채널(구글)에게 기여도를 부여한다
- Last channel attribution : 마지막 채널(네이버)에게 기여도를 부여한다
- Multi channel attribution : 다양한 채널(구글, 페이스북, 네이버)에게 기여도를 나눠준다
- raw_data 데이터베이스 (스키마)
- user_session_channel 테이블 : userId, sessionId, channel
- session_timestamp 테이블 : sessionId, ts
SQL 기본
- 다수의 SQL문을 실행한다면 세미콜론으로 분리 필요
- 주석 :
- -- : 인라인 한줄짜리 주석
- /* -- */ : 여러 줄 주석
- SQL 키워드는 대문자를 사용하기 등의 포맷팅이 필요
- 팀 프로젝트라면 팀에서 사용하는 공통 포맷이 필요
- 테이블 / 필드 이름의 명명 규칙을 정하는 것이 중요
- 단수형 vs. 복수형
- _ vs. CamelCasing
DDL - 테이블 구조 정의 언어
- CREATE TABLE
- Primary key 속성을 지정할 수 있으나 무시됨
- Big Data 데이터웨어하우스에서는 지켜지지 않음
- CTAS : CREATE TABLE table_name AS SELECT
- vs. CREATE TABLE and then INSERT
CREATE TABLE raw_data.user_session_channel (
userid int,
sessionid varchar(32) primary key,
channel varchar(32)
);
- DROP TABLE
- 없는 테이블을 지우려고 하는 경우 에러가 남
- vs. DELETE FROM (조건에 맞는 레코드를 지움, 테이블 자체는 유지)
DROP TABLE table_name;
-- 이미 존재하는 테이블이 있을수도 있을 때
DROP TABLE IF EXISTS table_name;
-- 새로운 컬럼 추가
ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;
-- 기존 컬럼 이름 변경
ALTER TABLE 테이블이름 RENAME 현재필드이름 to 새필드이름;
-- 기존 컬럼 제거
ALTER TABLE 테이블이름 DROP COLUMN 필드이름;
-- 테이블 이름 변경
ALTER TABLE 현재테이블이름 RENAME to 새테이블이름;
DML - 테이블 데이터 조작 언어
- 레코드 질의 언어 SELECT
- SELECT FROM : 테이블에서 레코드와 필드를 읽어오는데 사용
- WHERE 를 사용해서 레코드 선택 조건을 지정
- GROUP BY를 통해 정보를 그룹 레벨에서 뽑는데 사용하기도 함
- DAU, WAU, MAU 계산은 GROUP BY 필요
- ORDER BY를 사용해서 레코드 순서를 결정하기도 함
- 보통 다수의 테이블을 조인해서 사용
SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
WHERE 선택조건
GROUP BY 필드이름1, 필드이름2, ...
ORDER BY 필드이름 [ASC | DESC] -- 필드 이름 대신에 숫자 사용 가능
LIMIT N;
SELECT *
FROM raw_data.user_session_channel;
SELECT userId, sessionId, channel
FROM raw_data.user_session_channel;
SELECT *
FROM raw_data.user_session_channel
LIMIT 10;
SELECT DISTINCT channel
FROM raw_data.user_session_channel; -- 유일한 채널 이름을 알고 싶은 경우
SELECT channel, COUNT(1)
FROM raw_data.user_session_channel -- 채널별 카운트를 하고 싶은 경우. COUNT 함수
GROUP BY 1;
- 레코드 수정 언어 :
- INSERT INTO : 테이블에 레코드를 추가하는데 사용
- UPDATE FROM : 테이블 레코드의 필드 값 수정
- DELETE FROM : 테이블에서 레코드를 삭제
CASE WHEN
- 필드 값의 변환을 위해 사용
- 여러 조건을 사용하여 변환하는 것도 가능
CASE
WHEN 조건1 THEN 값1
WHEN 조건2 THEN 값2
ELSE 값3
END 필드이름
SELECT 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;
NULL
- 값이 존재하지 않음을 나타내는 상수. 0 혹은 ""과는 다름
- 필드 지정시 값이 없는 경우 NULL로 지정 가능
- 테이블 정의 시 디폴트 값으로도 지정 가능
- 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로 함
- NULL의 사칙연산의 결과는 항상 NULL
WHERE
- IN / NOT IN
- LIKE and ILIKE
- LIKE : 대소문자를 구분.
- ILIKE : 대소문자를 구분하지 않음
- BETWEEN : 데이터 범위 매칭에 사용
- 이들은 CASE WHEN에서도 사용이 가능함
데이터를 건들기 전 기억할 점
- 현업에서 깨끗한 데이터란 존재하지 않음
- 항상 데이터를 믿을 수 있는지 의심할 것
- 실제 레코드 몇 개를 살펴보는 것이 가장 좋은 방법
- 데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요함
- 중복된 레코드 확인
- 최근 데이터의 존재 여부 확인 (freshness)
- Primary key uniqueness가 지켜지는지 확인
- 값이 비어있는 컬럼들이 있는지 확인
- 위의 항목들을 unit test 형태로 만들어 매번 쉽게 확인해볼 수 있음
- 어느 시점부터 너무 많은 테이블이 존재할 때
- 회사의 성장과 관련
- 중요 테이블이 무엇이고 그것들의 메타 정보를 잘 관리하는 것이 중요
- 이 시점부터 발생하는 Data Discovery 문제들 :
- 무슨 테이블에 내가 원하고 신뢰할 수 있는 정보들이 담겨있나?
- 테이블에 대해 질문을 하고 싶은데 누구에게 해야하나?