[TIL] 6주차_Day22: 데이터 웨어하우스와 SQL과 데이터분석 (2)

💡Today I Learned

• 데이터 웨어하우스와 SQL, 데이터분석에 대한 두 번째 수업을 진행했습니다.
• Redshift, SQL에 대한 개념과 SQL 문법을 이용한 Redshift에서의 쿼리 실습

 

---

 

1. Redshift 론치 데모

- Launch하고자 하는 지역 확인 → 'Seoul'에 있는 AWS region에서 Redshift launch

- 외부에서 (실습_코랩 환경)에서 Redshift로 접근 가능하게 해야함 (원래 보안 설정 = 같은 aws 네트워크 안에 있는 서버에서만 접근 가능함)

 

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

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

- 세션 ID: 세션마다 부여되는 ID (세선 = 사용자의 방문을 논리적인 단위로 나눈 것)

→ 하나의 사용자는 여러 개의 세션을 가질 수 있음

→ 세션이 생긴 시간 기록

→ 일반적으로 세션을 만들어낸 접점(경유지) = '채널' (혹은 터치포인트) 이라는 이름으로 기록해둠 (ex: 어느 채널에서 가장 많이 매출로 이어졌는지, 마케팅 관련 기여도 분석 가능)

 

- 마케팅 관련, 사용자 트래픽 관련 정보

- DAU, WAU, MAU ... 일/주/월 별 AU(Active User) 차트 (SQL로 count 가능)

- 마케팅 채널 기여도 분석 (어느 채널에 광고를 하는 것이 가장 효과적일까?, 우리에게 가장 많은 매출을 만들어 준 채널?)

 

- raw_data db 스키마 = user_session_channel 테이블 + session_timestamp 테이블

- sessionid가 primary_key

 

3. SQL (Redshift 중심의 DDL, DML 소개)

- 다중 sql 문 사용 시 세미콜론(;)으로 분리

- -- 한 줄 주석

- /*--*/ 여러 줄 주석

 

- DDL

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

: 아직 뼈대만 만든 상태 (저장된 레코드는 아직 없어서 insert into, copy 등으로 추가해줘야 함)

 

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name; -- 해당 테이블이 존재할 때만 (에러 발생 x)

 

ALTER TABLE [테이블 이름] ADD COLUMN [필드 이름] [필드 타입]
ALTER TABLE [테이블 이름] RENAME [현재 필드 이름] to [새 필드 이름]
ALTER TABLE [테이블 이름] DROP COLUMN [필드 이름]
ALTER TABLE [테이블 이름] RENAME to [새 테이블 이름]

 

 

- DML

INSERT INTO -- 내용 추가
UPDATE FROM -- 내용 수정
DELETE FROM -- 내용 삭제 (* 레코드를 다 지워도 테이블의 구조, 스키마는 여전히 존재함), 트랜잭션에 사용 가능
TRUNCATE -- 모든 레코드 삭제, but 트랜잭션에는 사용 불가
COPY -- 벌크 업데이트 (추가)

: 'INSERT INTO'는 시간 오래 걸림

: 따라서 'COPY' (벌크 업데이트) 명령어로 레코드들을 csv, json 등의 파일에 저장 → AWS의 S3(스토리지)에 업로드 → S3에서 원하는 테이블로 벌크 업데이트 (한꺼번에)

 

SELECT
FROM
WHERE
GROUP BY -- DAU, WAU, MAU
HAVING -- 그룹에 대한 조건
ORDER BY
LIMIT N; -- 상/하위 N개만

 

4. SQL을 이용한 데이터 분석

: 현업에서는 깨끗한 데이터란 존재하지 않음

: 데이터의 품질을 항상 의심하고 체크하는 버릇 (중복 레코드, 최근 데이터의 존재 여부, 기본키의 유일성이 지켜지는지, null인 필드들이 있는지 .. ) → 코드를 짜서 unit test 형태로 만들어 매번 쉽게 체크 (자동화)

 

- 테이블이 점점 늘어나감에 따라 Data Discovery 문제들이 생겨남

: 중요한 테이블 & 메타 정보 에 대한 이해/관리가 중요해짐

: 무슨 테이블에 원하는 & 신뢰할 수 있는 정보가 들어있는지?

: 테이블에 관한 질문을 누구에게?

→ 다양한 오픈소스 서비스 출현 (DataHub(Linkedin), Amundsen(Lyft), Select Star, DataFrame, etc ...)

 

5. SELECT

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

: 조건 →  WHERE 절 사용

: SELECT의 결과 = 마찬가지로 테이블!

SELECT DISTINCT channel -- 유일한 채널 필드 이름
FROM [테이블 이름]

SELECT channel, COUNT(1) -- 채널별 카운트
FROM [테이블 이름]
GROUP BY 1;

 

SELECT CASE
		WHEN [조건1] THEN [값1]
    		WHEN [조건2] THEN [값2]
    		ELSE [값3]
 		END [필드 이름[

 

6. NULL

: '값이 존재하지 않음'을 나타내는 상수

: 필드 지정 시 값이 없을 경우 NULL로 지정 가능

: 사칙연산 사용 후에도 결과는 NULL

WHERE [필드 이름] is NULL
WHERE [필드 이름] is not NULL

 

7. COUNT, WHERE, STRING, ORDER BY

SELECT COUNT(1) FROM count_test -- 7 (주어진 테이블의 전체 레코드의 개수 반환함)
SELECT COUNT(0) FROM count_test -- 7 
SELECT COUNT(NULL) FROM count_test -- 0 (주어진 인자의 값이 NULL이면 0이 됨, not NULL이 인자면 1씩 더해감)
SELECT COUNT(value) FROM count_test -- 6 (NULL이 아닌 것들만 +1씩)
SELECT COUNT(DISTINCT value) FROM count_test -- 4 (distinct한 값 5개 중 NULL 제외 4개만 카운트)

: 주어진 인자의 값이 NULL이면 0, not NULL이면 레코드 개수만큼 1씩 더해감

 

- WHERE

WHERE channel IN ('Google', 'Youtube')
WHERE channel NOT IN ('Google', 'Youtube')

WHERE channel LIKE 'G%' -- 'G*', 대소문자 구분 o
WHERE channel ILIKE '%o%' -- '*o*', 대소문자 구분 x
WHERE channel NOT LIKE 'G%'
WHERE channel NOT ILIKE '%o%'

BETWEEN

 

- STRING 함수

: LEFT(str, N) = 왼쪽부터 N개

: REPLACE(str, exp1, exp2) = str에서 exp1를 exp2로 변경

: UPPER(str)

: LOWER(str)

: LEN(str)

: LPAD, RPAD = 왼/오른쪽 패딩

: SUBSTRING = 부분 문자열

 

- ORDER BY

: ASC, DESC로 오름/내림차순 정렬

: NULL은 ASC면 마지막에, DESC면 처음에 위치함

 

8. 날짜 관련 함수

CONVERT_TIMEZONE('America/Los_Angeles', ts) = 타임존 변환
GETDATE() = 현재 시간 반환
TRUNCATE
DATE_TRUNC
EXTRACT
DATE_PART = 날짜시간에서 특정 부분의 값 추출 가능
DATEDIFF = 두 날짜 사이 일수 계산
DATEADD
GET_CURRENT

 

9. Type Casting

1) :: 오퍼레이터 사용 (category::float)

2) cast 함수 사용 ( cast(category as float) )

 

---

💡Furthermore

• SQL day_2 퀴즈 풀이
• AWS Redshift 클러스터 생성 (클러스터 서브넷이 없다...?) 문제 해결 & 코랩 실습 (Redshift에 연결 불가 ㅠㅠ)