[AI class W11 D2] SQL Analysis 2

Contents

1. 1일차 퀴즈 리뷰

2. Redshift 런치 데모

3. 예제 테이블 소개

4. SQL 소개 (DDL과 DML)

5. SQL 실습 환경 소개

6. SELECT 소개

 

2. Redshift 런치 데모

• AWS Management 콘솔을 통해 Redshift 런치
  • 먼저 region 부터 확인해야 함 (서울)
  • Services -> Analytics -> Redshift
• Redshift 클러스터 정보
  • Node Type : 1 x dc2.large instance : 160GB
  • colab에서 접근이 가능하도록 Publicly accessible 가능하게 해야
  • Host : ...
  • Port number: ...
  • Database name: ...

3. 예제 테이블 소개

1. 관계형 데이터 베이스 예제 : 웹서비스 사용자/ 세션 정보

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

--> 사용자 ID, 세션 ID, 채널, 발생 시간 을 기본으로 기록해야

• 이 정보를 기반으로 다양한 데이터 분석과 지표 설정 가능
  • 마케팅 관련, 사용자 트래픽 관련
  • DAU, WAU, MAU 등의 일주월별 Active User 차트 (SQL group by로 쉽게 가능)
  • Marketing Channel Attribution 분석
    • 어느 채널에 광고를 하는 것이 가장 효과적인가?
• ex>  사용자 ID 100번이 총 3개의 세션(blue)을 갖는 예제
  • 세션 1: 구글 키워드 광고로 시작한 세션
  • 세션 2: 페이스북 광고를 통해 생긴 세션
  • 세션 3: 네이버 광고를 통해 생긴 세션

 

issue example : 10:35에 상품 구매한 것은 어느 채널에 credit을 줘야할까?

1. first channel attribution?

2. last channel attribution?

3. multi channel attribution?

2.  관계형 데이터 베이스 예제 : 데이터베이스와 테이블

(실습을 위해 star schema로 만든 구조)

sessionId 가 primary key

join을 통해서 두 개의 테이블을 이어줄 수 있음

4. SQL 소개 (DDL과 DML)

1. SQL의 기본

• 먼저의 다수의 SQL 문을 실행한다면 세미콜론으로 분리 필요
  • SQL문1; SQL문2; SQL문3;
• SQL 주석
  • --: 인라인 한줄짜리 주석, 자바의 //에 해당
  • /*--*/ : 여러 줄에 걸쳐 사용 가능한 주석
• SQL 키워드는 대문자를 사용한다던지 하는 나름대로의 포맷팅이 필요
  • 팀 프로젝트라면 팀에서 사용하는 공통의 포맷 필요
• 테이블 / 필드 이름의 명명 규칙 정하는 것이 중요
  • 단수형 vs. 복수형 ex> User vs. Users
  • _ vs. CamelCasing ex> user_session_channel vs. UserSessionChannel

2. SQL DDL - 테이블 구조 정의 언어

• CREATE TABLE (SCHEMA - TABLE의 계층 구조 CREATE SCHEMA도 있음)
• Primary Key 속성을 지정할 수 있으나 무시됨
  • primary key uniqueness는 빅데이터 웨어하우스에서는 지켜지지 않음 (Redshift, Snowflake, BigQuery)
• CTAS : CREATE TABLE table_name AS SELECT (테이블을 만드는 동시에 내용을 채우는 것)
  • vs. CREATE TABLE and then INSERT

    CREATE TABLE raw_data.user_session_channel(
    	user int,
        sessionid varchar(32) primary key, -- uniqueness 보장 안됨
        channel varchar(32)
    );

• DROP TABLE
  • DROP TABLE table_name;
    • 없는 테이블을 지우려고 하면 에러 발생
  • DROP TABLE IF EXISTS table_name;
  • vs. DELETE FROM : DELETE FROM은 조건에 맞는 레코드들을 지움 (테이블은 존재)
• ALTER TABLE
  • 새로운 컬럼 추가 : ALTER TABLE 테이블 이름 ADD COLUMN 필드이름 필드타입;
  • 기존 컬럼 이름 변경 : ALTER TABLE 테이블 이름 RENAME 현재 필드 이름 to 새 필드 이름;
  • 기존 컬럼 제거 : ALTER TABLE 테이블 이름 DROP COLUMN 필드 이름;
  • 테이블 이름 변경 : ALTER TABLE 현재 테이블 이름 RENAME to 새 테이블 이름;

3. SQL DML - 테이블 데이터 조작 언어

• 레코드 질의 언어 SELECT
  • 하나, 또는 다수의 테이블로부터 조건에 맞는 레코드 들을 읽어오는 것
  • SELECT FROM: 테이블에서 레코드와 필드를 읽어오는데 사용
  • WHERE: 특정 레코드 선택 조건을 지정
  • GROUP BY: 정보를 그룹 레벨에서 뽑는데 사용하기도 함
    • ex> DAU, WAU, MAU 계산은 GROUP BY를 필요로 함
  • ORDER BY: 레코드의 순서 결정
  • 보통 다수의 테이블을 조인해서 사용하기도 함
• 레코드 수정 언어
  • INSERT INTO: 테이블에 리코드 추가
  • UPDATE FROM: 테이블 레코드의 필드 값 수정
  • DELETE FROM: 테이블에서 레코드 삭제 
    • vs. TRUNCATE (transaction에 사용 불가)

5. SQL 실습 환경 소개

1. 실습에 들어가기 앞서 기억해야 할 점

• 현업에서 깨끗한 데이터란 존재하지 않음
  • 항상 데이터를 믿을 수 있는지 의심할 것
  • 실제 레코드를 몇 개 직접 살펴보는 것 만한 것이 없다
• 데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요
  • 중복된 레코드들 체크하기
  • 최근 데이터의 존재 여부 체크하기 (freshness)
  • primary key uniqueness 가 지켜지는지 체크하기
  • 값이 비어있는 컬럼이 있는지 체크하기 (python으로 불러와서 pandas 활용하는 방법)
  • 위의 체크는 코딩의 unit test 형태로 만들어 매번 쉽게 체크해볼 수 있음 (pipe line 만들어 두기)
• 어느 시점이 되면 너무나 많은 테이블이 존재하게 됨
  • 회사 성장과 밀접한 관련
  • 중요 테이블이 무엇인지, 그것의 메타 정보를 잘 관리하는 것이 중요해짐
• Data Discovery 문제 생겨남
  • 무슨 테이블에 내가 원하고 신뢰할 수 있는 정보가 있나?
  • 테이블에 대해 질문하고 싶은데 누구에게 질문해야 하나?
• 해결을 위해 다양한 오픈 소스와 서비스들의 출현
  • DataHub(LinkedIn), Amundsen(Lyft)...
  • Select Star, DataFrame... 
  • 데이터 디스커버리 문제 해결: 가지고 있는 테이블을 쉽게 찾고, 그 테이블이 얼마나 사용되었는지, 누가 많이 사용하는지...
• 모든 테이블을 다 관리할 수는 없다 무엇이 중요한지 파악하고 그것을 잘 관리하는 것이 중요

6. SELECT 소개

1. SELECT

• 테이블에서 레코드들(또는 레코드 수)을 읽어오는데 사용
• WHERE를 사용해 조건을 만족하는 레코드
• SELECT 필드이름1, 필드이름2
• FROM 테이블이름
• WHERE 선택조건
• GROUP BY 필드이름1, 필드이름2
• ORDER BY 필드이름[ASC|DESC] -- 필드 이름 대신에 숫자 사용 가능
• LIMIT N; -- N개만 보여달라
• DISTINCT -- 유일한 채널 이름 알고 싶은 경우
• COUNT(1) -- 채널별 카운트 하고 싶은 경우

SELECT * -- 테이블 전부 다
FROM raw_data.user_session_channel;

SELECT DISTINCT channel -- 유일한 채널 이름을 알고 싶은 경우
FROM raw_data.user_session_channel;

SELECT channel, COUNT(1) -- 어떤 그룹 안에 속한 record들이 몇개 인지 세어줌 
FROM raw_data.user_session_channel
GROUP BY 1; -- 1은 컬럼의 ordianal number를 의미

2. CASE WHEN

• 필드값 변환을 위해 사용 가능
  •  CASE WHEN 조건 THEN 참일 때 값 ELSE 거짓일 때 값 END 필드이름
• 여러 조건을 사용하여 변환하는 것도 가능

• SELECT CASE
  	WHEN channel in ('Facebook','Instagram') THEN 'Social - Media'
      WHEN channel in ('Google', 'Naver') THEN 'Search-Engine'
      ELSE 'Something-Else'
  END channel_type
  FROM raw_data.user_session_channel;

3. NULL 이란?

• 값이 존재하지 않음을 나타내는 상수. 0 혹은 ""와는 다름
• 필드 지정시 값이 없는 경우 NULL로 지정 가능
  • 테이블 정의시 디폴트 값으로 지정 가능
• 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로 함
  • field1 is NULL 혹은 field1 is not NULL
• NULL이 사칙연산에 사용되면 그 결과는? NULL

4. COUNT 함수 제대로 이해하기

 

• SELECT COUNT(1) FROM count_test -> 7 (1은 모든 record)
• SELECT COUNT(0) FROM count_test -> 7
• SELECT COUNT(NULL) FROM count_test -> 0 
• SELECT COUNT(value) FROM count_test -> 6 (NULL 아닌 것만)
• SELECT COUNT(DISTINCT value) FROM count_test -> 4 (NULL 빼고)

 

 

 

 

 

 

 

 

 

 

 

 

 

5. WHERE

• IN
  • WHERE channel = 'Google' OR 'channel = 'Youtube'
  • NOT IN
• LIKE and ILIKE
  • LIKE is a case sensitive string match. ILIKE is a case-insensitive string match
  • WHERE channel LIKE 'G%' -> 'G*'
  • WHERE channel LIKE '%o%' -> '*o*'
  • NOT LIKE or NOT ILIKE
• BETWEEN
  • Used for date range matching
• 위의 오퍼레이터들은 CASE WHEN 사이에서도 사용가능

6. IN & LIKE/ILIKE

 

7. STRING functions

• LEFT(str, N) -- str에서 N만큼 문자를 추출
• REPLACE(str, exp1, exp2)
• UPPER(str)
• LOWER(str)
• LEN(str)
• LPAD, RPAD --왼쪽이나 오른쪽에 특증 스트링을 붙여줌
• SUBSTRING --특정 시작점에서 N만큼 문자를 추출

8. ORDER BY

• Default ordering is ascending
  • ORDER BY 1 ASC
• Descending requres "DESC"
  • ORDER BY 1 DESC
• Ordering by multiple columns
  • ORDER BY 1 DESC, 2, 3
• NULL 값 순서는?
  • NULL 값들은 오름차순일 경우 (ASC), 마지막에 위치함
  • NULL 값들은 내림차순일 경우 (DESC), 처음에 위치함
  • 이를 바꾸고 싶다면, NULL FIRST, 혹은 NULL LAST를 사용

9. 타입 변환

• DATE conversion:
  • 타임존 관련 변환
    • CONVERT_TIMEZONE('America/Los_Angeles', ts)
    • select pg_timezone_names();
  • DATE, TRUNCATE
  • DATE_TRUNC
    
    • 첫번째 인자가 어떤 값을 추출하는지 지정(week, month, day...)
  • EXTRACT or DATE_PART: 날짜시간에서 특정 부분의 값을 추출가능
  • DATEDIFF
  • DATEADD
  • GET_CURRENT...
• TO_CHAR, TO_TIMESTAMP -- 시간을 문자열로, 문자열을 시간으로

10. Type Casting

• 1/2의 결과는?
  • 0이 됨 정수 간의 연산은 정수가 되어야 하기 때문
  • 분자나 분모 중의 하나를 float로 바꿔야 0.5가 나옴 (일반적인 프로그래밍 언어에서)
• :: 오퍼레이터를 사용
  • category::float
• cast 함수를 사용
  • cast(category as float)