Week 6 - 2

Redshift

AWS에서 제공하는 데이터 웨어하우스 서비스

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

• 사용자 ID : 보통 웹서비스에서 등록된 사용자마다 부여하는 유일한 ID
• 세션 ID : 세션마다 부여되는 ID
  • 세션 : 사용자의 방문을 논리적인 단위로 나눈 것
    • 사용자가 외부 링크(보통 광고)를 타고 오거나 직접 방문해서 올 경우 세션을 생성
    • 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성(time bound)
  • 즉 하나의 사용자는 여러 개의 세션을 가질 수 있음
  • 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해둠
    • 마케팅 관련 기여도 분석을 위함
  • 또한 세션이 생긴 시간도 기록
• 위 정보들을 기반으로 다양한 데이터 분석과 지표 설정이 가능
  • 마케팅 관련, 사용자 트래픽 관련
  • DAU, WAU, MAU등의 일주월별 Active User 차트
  • Marketing Channel Attribution 분석(어느 채널에 광고를 하는 것이 가장 효과적인지)

 

상품 구매로 이어지면 기여도를 나눠야 함.

• first channel attribution : 첫 광고
• last channel attribution : 구매 직전 마지막 광고
• multi channel attribution : 모든 광고에게 기여도 부여

 

SQL 기본 문법

• 다수의 SQL 문을 실행할 때는 세미콜론으로 분리 필요
  • SQL 1; SQL 2; SQL 3;
• SQL 주석
  • -- : 한줄짜리 주석.
  • /* */ : 여러 줄 주석
• SQL 키워드는 대문자 사용 등 포맷팅이 필요.
  
  • 팀 프로젝트라면 팀에서 사용하는 공동 포맷 설정
• 테이블/필드 이름의 명명 규칙 설정
  • 단수형 vs 복수형 : User vs Users
  • _ vs CamelCasing : user_session_channel vs UserSessionChannel

DDL

테이블 구조 정의 언어

• CREATE TABLE : 테이블 생성
  <예시>
  CREATE TABLE raw_data.user_session_channel (
      userid int, (실수형)
      sessionid varchar(32) primary key, (문자열 32자)
      channel varchar(32)
  );
  
  • primary key uniqueness : 빅데이터 데이터 웨어하우스에서는 지켜지지 않음(Redshift, Snowflake, Bigquery 등)
  • CTAS : CREATE TABLE table_name AS SELECT TABLE A : table_name 테이블 생성 후 곧바로 TABLE A 에서 추출한 정보 입력( 다른 방법 : CREATE TABLE and then INSERT)
• DROP TABLE : 테이블 제거
  • DROP TABLE table_name
  • 없는 테이블 제거하려는 경우는 에러 발생
  • 존재할 경우 제거하기
    • DROP TABLE IF EXISTS table_name;
    • DELETE FROM 조건(조건에 맞는 레코드들 삭제하지만 테이블 자체는 존재)
• ALTER TALBE
  • 새로운 컬럼 추가 : ALTER TABLE 테이블명 ADD COLUMN 필드명 필드타입;
  • 기존 컬럼 이름 변경 : ALTER TABLE 테이블명 RENAME 현재필드명 to 새필드명;
  • ALTER TABLE 테이블명 DROP COLUMN 필드명;
  • ALTER TABLE 현재 테이블명 RENAME to 새테이블명
• 레코드 수정 언어
  • INSERT INTO : 테이블에 레코드를 추가하는데 사용
  • UPDATE FROM : 테이블 레코드의 필드 값 수정
  • DELETE FROM : 테이블에서 레코드 삭제 (vs TRUNCATE)

DDL

레코드 질의 언어

• SELECT FROM : 테이블에서 레코드와 필드를 읽어오는데 사용
  • DISTINCT : 중복 제거
  • COUNT : 필드의 수를 세줌
  • 테이블의 모든 레코드 수 카운트하는 방법
    SELECT COUNT(1)
    FROM 테이블명
• WHERE : 레코드 선택 조건 지정
  • IN, NOT IN
    WHERE channel IN ('Google', 'Youtube')
    WHERE channel = 'Google' OR channel = 'Youtube')
  • LIKE and ILIKE : 문자열 매치시키기
    WHERE channel LIKE 'G%' : G*******
    WHERE channel LIKE '%o%' : ********o*********
  • BETWEEN A AND B : A와 B 사이(날짜와 날짜 사이, 값과 값 사이)
  • CASE WHEN에서도 사용 가능
• GROUP BY : 정보를 그룹 레벨에서 추출하는데 사용
  • DAU, WAU, MAU 계산은 GROUP BY 에서함
• ORDER BY : 레코드 순서를 결정
  • 디폴트는 ASC(오름차순)
    ORDER BY 1 ASC
  • DESC는 내림차순으로
  • 여러 컬럼(필드) 순서 결정 가능
    ORDER BY 1 DESC 2 , 3 : 1 값은 내림차순, 같은 값은 2 값을 보고 오름차순, 또 같으면 3으로 오름차순
  • NULL은 오름차순 일 경우 마지막, 내림차순 일 경우 처음, NULLS FIRST(LAST)를 하면 NULL의 위치 조정 가능

SELECT 필드명1, 필드명2 ...
FROM 테이블명
WHERE 선택조건
GROUP BY 필드명1, 필드명2 ...
ORDER BY 필드명 [ASC][DESC] 필드명 대신 숫자 사용 가능
LIMIT N;

• CASE WHEN : 필드 값의 변환을 위해 사용하거나 조건을 넣을 때 사용.(여러 조건도 가능)

CASE
    WHEN 조건 1 THEN 값 1
    WHEN 조건 2 THEN 값 2
    ELSE 조건 3
END 필드명

• NULL : 값이 존재하지 않음을 나타내는 상수. 0과 "" 와는 다름
  • 필드 지정시 값이 없는 경우 NULL로 지정 가능(테이블 정의 시 디폴트 값으로도 가능)
  • 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로함.( IS NULL or IS NOT NULL)
• STRING 함수
  • LEFT(str, N) : 문자열 str의 왼쪽부터 N개
  • REPLACE(str, exp1, exp2) : 문자열 str에서 exp1을 찾은 후 exp2로 바꿈
  • UPPER(str) : 문자열 str을 대문자로
  • LOWER(str) : 문자열 str을 소문자로
  • LEN(str) : 문자열 str의 길이
  • LPAD(str, N, exp), RPAD(str, N, exp) : exp가 없는 디폴트 값은 문자열 str 왼(오른)쪽에 공백을 채움. exp가 있을 시 exp를 추가함.
  • SUBSTRING(str, st, N) : 문자열 str을 st부터 시작해서 N만큼 자름

타입 변환과 Type Casting

참고 사항