[TIL Day45-1] SQL과 데이터분석 - DDL, DML, SELECT

2. SELECT 배우기

예제 테이블 소개

• 관계형 데이터베이스 예제: 웹서비스 사용자/세션 정보(1)
  - 사용자 ID: 웹서비스에서 등록된 사용자마다 부여하는 유일한 ID
  - 세션 ID: 세션마다 부여되는 ID
  • 세션: 사용자의 방문을 논리적인 단위로 나눈 것
    • 사용자가 외부 링크(보통 광고)를 타고 들어오거나, 직접 방문해서 올 경우 세션 생성
    • 사용자가 방문 후 30분(time bound)간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성
  • 즉 하나의 사용자는 여러 개 세션을 가질 수 있음
  • (마케팅 관련 기여도 분석을 위해) 세션을 만들어낸 접점(경유지)을 채널이란 이름으로 기록
  • 세션이 생긴 시간도 기록

• 관계형 데이터베이스 예제: 웹서비스 사용자/세션 정보(2)
  - 위 정보를 기반으로 다양한 데이터 분석과 지표 설정 가능
  • 마케팅 관련, 사용자 트래픽 관련
  • DAU, WAU, MAU 등의 일/주/월별 Active User(해당 기간동안 웹사이트를 한 번이라도 방문한 사용자) 차트
  • Marketing Channel Attribution 분석
    • 어느 채널에 광고를 하는 것이 가장 효과적인가?

• 관계형 데이터베이스 예제: 웹서비스 사용자/세션 정보(3)
  - 사용자 ID 100번이 총 3개의 세션(파란 배경)을 갖는 예제
  
  • 사용자의 상품 구매에 어떤 채널이 가장 크게 기여했는지 마케팅적 관점에서 분석
    • First touch attribution: 가장 처음에 해당 웹사이트로 유입하도록 이끈 채널에 기여도 부여
    • Last touch attribution: 구매 직전에 유입된 채널에 기여도 부여
    • Multi touch attribution: 관여한 모든 채널에 기여도 부여

• 데이터베이스와 테이블
  - raw_data 데이터베이스
  

SQL 소개

Redshift 중심으로 DDL과 DML 소개

• SQL 기본
  - 다수의 SQL 문을 실행한다면 세미콜론으로 분리
  - SQL 주석: -- 인라인 한줄짜리 주석, /--/ 여러 줄 주석
  - SQL 키워드는 대문자를 사용하는 것이 편리
  - 테이블/필드 이름의 명명규칙 정하기(단수형 vs. 복수형, snake_case vs. CamelCase)

DDL: 테이블 구조 정의 언어

• CREATE TABLE
  - primary key 속성을 지정할 수 있으나 데이터 웨어하우스에서 uniqueness는 보장되지 않음
  - CTAS: CREATE TABLE table_name AS SELECT (테이블 생성과 동시에 내용 추가하기 vs. CREATE TABLE and then INSERT)
  - raw_data 스키마 안에 user_session_channel 테이블 만들기

     	CREATE TABLE raw_data.user_session_channel (
     		--필드명 필드타입 고유키지정
        	userid int,
        	sessionid varchar(32) primary key,
        	channel varchar(32)
        );

• DROP TABLE
  - DROP TABLE table_name; 없는 테이블을 지우려고 하면 에러 발생
  - DROP TABLE IF EXISTS table_name;
  - DELETE FROM 조건에 맞는 레코드들을 지움(테이블 자체는 존재)

• ALTER TABLE
  - 새로운 컬럼 추가: ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;
  - 기존 컬럼 이름 변경: ALTER TABLE 테이블이름 RENAME 현재필드이름 to 새필드이름;
  - 기존 컬럼 제거: ALTER TABLE 테이블이름 DROP COLUMN 필드이름;
  - 테이블 이름 변경: ALTER TABLE 현재테이블이름 RENAME to 새테이블이름;

DML: 테이블 데이터 조작 언어

• SELECT: 레코드 질의 언어
  - SELECT FROM: 테이블에서 레코드와 필드 읽어오기
  - WHERE를 사용하여 레코드 선택 조건 지정
  - GROUP BY를 통해 정보를 그룹 레벨에서 추출
  - ORDER BY를 사용해 레코드 순서 결정
  - 다수의 테이블을 JOIN해서 사용하기도 함

• 레코드 수정 언어
  - INSERT INTO: 테이블에 레코드를 추가하는데 사용
  - UPDATE FROM: 테이블 레코드의 필드 값 수정
  - DELETE FROM(vs. TRUNCATE): 테이블에서 레코드를 삭제