📖 오늘의 학습

• SQL : DDL, DML
• AWS Redshift

---

AWS Redshift 실습

AWS Redshift 클러스터를 생성하는 방법을 알아보고
현실적인 마케팅 관련 예제를 통해서 SQL 사용법을 알아본다.

예제 테이블

• 마케팅 관련 테이블로 실습한다.
  사용자 ID : 등록된 사용자마다 부여하는 유일한 ID
  세션 ID : 세션마다 부여되는 ID
• 세션 : 사용자의 방문을 논리적인 단위로 나눈 것
• 사용자가 외부 링크를 타고 오거나 직접 방문해서 올 경우 세션을 생성
• 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성
• 보통 세션의 경우 세션을 만들어낸 접점을 채널이란 이름으로 기록해 둠
  • 마케팅 관련 기여도 분석을 위함
• 세션이 생긴 시간도 기록
• 한 사용자가 다른 경로를 통해 방문함으로써 여러 개의 세션을 가질 수 있다.
  
  이러한 사용자 세션 데이터를 담은 스키마와 테이블이다. raw_data와 marketing 으로 데이터베이스(스키마)를 나눔으로써 용도에 맞게 테이블이 분류될 수 있도록 한다.
  use_session_channel 과 session_timestamp 테이블의 PK는 session_id 라고 볼 수 있다.
  앞으로 이 테이블들의 데이터로 Redshift를 사용하면서 SQL을 실습해본다.

예제 데이터 분석을 통해 알 수 있는 것들...

• 어떤 채널을 통해 얼마나 구매를 했는지?
• 같은 돈을 썼을 때 어느 채널에서 더 많은 매출을 만들어내는지 분석
• 마케팅 관련, 사용자 트래픽 관련
• DAU, WAU, MAU 등 일주월별 Active User 차트
• Marketing Channel Attribution 분석 - 어느 채널에 광고를 하는 것이 가장 효과적인가?

SQL 알아보기

SQL 기본

• 다수의 SQL문을 실행한다면 세미콜론(;)으로 분리 필요
• 주석은 —, /* */
• SQL 키워드는 대문자를 사용한다던지 하는 나름대로의 포맷팅이 필요
• 테이블/필드이름의 명명규칙을 정하는 것이 중요
• 단수형 vs. 복수형 ( User vs. Users)
• _ vs. CamelCase (user_session vs. UserSession)

SQL DDL : 테이블 정의 언어

테이블 생성

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

• Redshift의 경우 DW이기 때문에 Primary key를 지정해주어도 무시됨
• Primary key uniqueness가 보장되지 않음
• 생성 후 비어있는 테이블에 COPY를 통해서 Bulk 로 한번에 집어넣는다.

테이블 생성하면서 원하는 데이터 가져오기 : CTAS

CREATE TABLE and then SELECT ~
CREATE TABLE and then INSERT ~

테이블 삭제

DROP TABLE table_name; --이 경우 없는 테이블이면 오류 발생
DROP TABLE IF EXISTS table_name;

테이블의 레코드 삭제

DELETE FROM 테이블 WHERE ~ --조건에 맞는 레코드들을 지움 (테이블 자체는 존재)

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

SQL DML : 테이블 조작 언어

레코드 질의

보통 다수의 테이블에서 조인해서 사용

SELECT fields 
FROM table
WHERE --를 통해 조건 지정
GROUP BY --를 통해 그룹별로 데이터 조회
ORDER BY --를 사용하여 레코드 순서 결정

레코드 수정

INSERT INTO : 테이블에 레코드 추가
UPDATE FROM : 테이블 레코드의 필드 값 수정
DELETE FROM : 테이블에서 레코드를 삭제, 조건을 걸 수 있다.
TRUNCATE : 테이블에서 레코드를 삭제, 조건을 걸 수 없이 모두 삭제한다.

실습에 들어가기에 앞서 기억할 점

• 현업에서 깨끗한 데이터란 존재하지 않음
• 항상 데이터를 믿을 수 있는 지 의심할 것
• 실제 레코드를 몇 개 살펴보는 것 만한 것이 없음
• 중복된 레코드를 체크하기
• 최근 데이터의 존재 여부 체크하기 (freshness)
• primary key uniqueness가 지켜지는지 체크하기
• 값이 비어있는 컬럼들이 있는지 체크하기
• 위의 체크는 코딩의 unit test 형태로 만들어 매번 쉽게 체크해볼 수 있음
• 어느 시점이 되면 너무나 많은 테이블들이 존재하게 되어 data discovery(내가 원하는 데이터를 찾기) 문제 발생
  • 중요 테이블들이 무엇이고 그것들의 메타 정보를 잘 관리하는 것이 중요해짐
  • 무슨 테이블에 내가 원하고 신뢰할 수 있는 정보가 들어있나?
  • 테이블에 대해 질문을 하고 싶은데 누구에게 질문을 해야하나?
  • -> 데이터를 스키마로 나누어 관리
• 이 문제를 해결하기 위한 다양한 오픈소스와 서비스들이 출현
  • DataHub, Amundsen

NULL이란?

• 값이 존재하지 않음을 나타내는 상수
• 0, “” 와는 다름
• 필드 지정시 값이 없는 경우 NULL로 지정

COUNT 함수 제대로 이해하기

내가 원하는 조건의 레코드를 센다.

COUNT(1) : 7
COUNT(0) : 7
COUNT(NULL) : 0 -> 주어진 인자가 NULL일 경우 세지 않으므로 0
COUNT(value) : 6 -> value의 값들 중 NULL은 세지 않으므로 6
COUNT(DISTINCT value) : 4 -> unique한 value값들 중 NULL 세지 않으므로 4

WHERE

• IN , NOT IN 으로 여러 개의 값이 포함/비포함되는지 확인한다.
• LIKE , ILIKE : ILIKE 의 경우 대소문자 따지지 않는다.
• BETWEEN : 날짜 범위 조건을 걸 때 사용한다.

위의 오퍼레이터들은 CASE WHEN THEN END 사이에서도 사용이 가능하다!

STRING FUnctions

• LEFT(str, N) str의 N번째 문자까지만 리턴
• REPLACE(str, exp1, exp2)
• LPAD, RPAD
• SUBSTRING

ORDER BY

• default는 ASC 오름차순
• NULL 값 순서
  • 오름차순일 경우 마지막에 위치
  • 내림차순일 경우 처음에 위치
  • 이를 변경하고 싶다면 NULLS FIRST or NULLS LAST 사용

DATE Conversion

• 타임존 변환 : CONVERT_TIMEZONE(‘America’)
• DATE, TRUNCATE
• DATE_TRUNC
• EXTRACT or DATE_PART
• DATEDIFF
• DATEADD
• GET_CURRENT
• TO_CHAR
• TO_TIMESTAMP

Type Casting

• 1/2의 결과는? 0
• 실제 값을 얻고 싶은 경우 cast 함수를 사용하거나 오퍼레이터를 사용한다
• category::float
• cast(category as float)

---

📝 주요메모사항

---

😵 공부하면서 어려웠던 내용