관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보
사용자 ID : 보통 웹서시스에서는 등록된 사용자마다 부여하는 유일한 ID
세션 ID : 세션마다 부여되는 ID
세션 : 사용자의 방문을 논리적인 단위로 나눈 것
사용자가 외부 링크를 타고 오거나 직접 방문해서 올 경우 세션을 생성(외부링크)
사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성(time bound)
즉 하나의 사용자는 여러개의 세션을 가질 수 있음
보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해둠
-> 이런 페이지에 오래 머문다!
마케팅 관련 기여도 분석을 위함
또한 세션이 생긴 시간도 기록
관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보
이 정보를 기반으로 다양한 데이터 분석 지표 설정이 가능
마케팅 관련 사용자 트래픽관련
DAU WAU MAU 등의 일주월별 액티브 유저 차트
marketing channel attribution 분석
- 어느 채널에 광고를 하는 것이 유리할까?
해당 사이트를 처음 발견해준 사이트에게 크레딧을 몰아주기
- first channel attribute
상품 구매 직전 마지막 채널에게 모든 걸 몰아주는게
- last channel attribute
모든 채널
- multi channel attribute
SQL Redshift DDL, DML
postgre sql 8.x 와 호환
SQL 기본
먼저 다수의 sql 문을 실행한다면 세미콜론으로 분리 필요
- SQL1문;SQL2문;SQL3문;
SQL 주석
- --: 한줄 주석
- /--/ : 여러줄에 걸쳐 사용가능한 주석
SQL 키워드는 대문자를 사용한다던지 하는 나름대로의 포맷팅이 필요
- 팀프로젝트라면 팀에서 사용하는 공통 포맷이 필요
- 개인적인 습관 및 규칙이 필요
테이블/필드 이름의 명명규칙을 정하는 것이 중요
- 단수형 vs 복수형
user vs users
_ vs CmelCasing
ex) user_session vs UserSession
DDL
create table(CTAS)
primary key uniqueness
- warehouse에서는 지켜지지 않음
DROP TABLE
- 테이블 드랍 (지우려고 하는 경우 에러를 냄)
DROP TABLE IF EXISTS table_name;
vs
DELETE FROM - 모든 레코드 삭제
ALTER TABLE
- ADD COLUMN
- RENAME -> 컬럼 이름 변경
- DROP COLUMN
- RENAME -> 테이블 이름 변경
DML(데이터 조작 언어)
SELECT 문
INSERT INTO
UPDATE FROM
DELETE FROM
TRUNCATE
기억해야할 점
현업에서 깨끗한 데이터란 존재하지 않음
- 항상 데이터를 믿을 수 있는지 의심할 껏
- 실제 레코드를 몇개 살펴보는 것 만한 것이 없음
데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요
- 중복된 레코드 확인
- 최근 데이터의 존재 여부
- primary key uniqueness가 켜지는지
- 값이 비어있는 컬럼들이 있는지
- unit test
어느 시점이 되면 너무나 많은 테이블들이 존재하게 됨
- 회사 성장과 밀접한 관련
- 중요 테이블들이 무엇이고 그것들의 메타 정보를 잘 관리하는 것이 중요해짐
그 시점 부터는 Data Discovery 문제들이 생겨남
- 무슨 테이블에 내가 원하고 신뢰할 수 있는 정보가 들어 있나?
- 테이블에 대해 질문을 하고 싶은데 누구에게 질문을 해야하나?
CASE WHEN
Case when 구무의 end 는 새로운 필드 이름을 공지
Count
값이 들어온다면, 값 vs null로 판단
select count(1) -> 모든 레코드
select count(null) -> 0
- 레코드 수 만큼 읽어오지만 값이 null이기 때문에 0
select count(컬럼 이름)
- null을 제외한 레코드
select count(distinct(column name))
- 유일한 레코드 수 읽기
WHERE
IN
LIKE & ILIKE
- 대소문자 구별은 LIKE, ILIKE는 아님
BETWEEN
위의 오퍼레이터는 CASE WHEN 에서 모두 사용 가능
ORDER BY
Null 값들은 오름차순 일 경우 마지막에 위치
내림차순일 경우 처음에 위치
이름을 바꾸고 싶다면 NULLS FIRST,NULLS LAST로 사용
Data Conversion
convert_timezon('america/los_angeles',ts)
to_char, to_timestamp
Type Castig
- 1/2의 결과는
0이 됨. 정수간의 연산은 정수가 되어야하기 때문
분자나 분모 중 하나를 float으로 casting
오퍼레이터 사용
- category::float
cast 함수 사용 - cast(category as float)
groupby
GROUP BY 1를 참조합니다.first column in select statement어느 것이account_id.
