[11주차 - Day2] DDL, DML, SELECT

SQL을 이용한 데이터 분석

2. SELECT 배우기

1. Redshift 론치 데모

2. 예제 테이블 소개

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

• 사용자ID : 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID
• 세션ID : 세션마다 부여되는 ID
• 세션? 사용자의 방문을 논리적인 단위로 나눈 것사용자가 외부 링크를 타고 오거나 직접 방문해서 올 경우 세션을 생성하거나 사용자가 방문 후 일정시간동안 상호작용이 없다가 뭔가를 하는 경우 새로 세션을 생성->하나의 사용자는 여러 개의 세션을 가질 수 있음 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해두고 세션이 생긴 시간도 기록

이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능

ex마케팅 관련, 사용자 트래픽 관련, DAU,WAU,MAU등 일주월별 Active User 차트, 마케팅 채널 attribution분석(어느 채널에 광고를 하는 것이 가장 효과적?)

 

관계형 데이터베이스 예제

사용자 ID 100번 : 총 3개의 세션을 갖는 예제

• 세션1 : 구글 키워드 광고로 시작한 세션
• 세션2 : 페이스북 광고를 통해 생긴 세션
• 세션3 : 네이버 광고를 통해 생긴 세션

기록돼야하는 정보(사용자ID, 세션ID, 세션이 생성된 시간, 채널 정보)를 어딘가에 저장해야함

'user_session_channel'과 'session_timestamp'라는 2개의 테이블(star schema)에 정보를 저장

->2개의 테이블을 merge하면 하나의 세션에 대해 완전한 정보를 얻을 수 있을 것

->merge하는 걸 SQL에서 join이라고 함

join을 하려면 join key(두 테이블을 연결해주는 컬럼)가 필요한데 여기서는 'sessionId'

primary key는 어떤 레코드를 유일하게 지칭해주는 컬럼

여기서는 'sessionId'

'userId'는 다수의 세션을 가질 수 있기 때문에 불가능!

왼 : 'user_session_channel'테이블/오 : 'session_timestamp'테이블

3. SQL소개(DDL과 DML)

SQL기본

• 다수의 SQL문을 실행한다면 세미콜론으로 분리 ex)SQL1;SQL2;SQL3;
• SQL주석 : 1줄짜리는 --, 여러줄짜리는 /*  */

DDL

• CREATE TABLE

  

• DROP TABLE

  

• ALTER TABLE

  

DML

• 레코드 질의 언어
  • SELECT FROM     (+WHERE, GROUP BY, ORDER BY)
• 레코드 수정 언어
  • INSERT INTO
  • UPDATE FROM
  • DELETE FROM

4. SQL실습환경소개

구글 Colab 환경에서 실습할 것

 

실습에 들어가기 전 기억할 것

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

항상 데이터를 의심하고 노가다로 실제 레코드를 살펴볼 것

• 중복된 레코드들 체크
• 최근 데이터 존재 여부 체크
• primary key uniqueness가 지켜지는지 체크
• 값이 비어있는 컬럼들이 있는지 체크
• 위의 체크리스트는 unit test형태로 만들어서 쉽게 체크가능

어느 시점이 되면 너무 많은 테이블이 존재하게 되는데 이 시점부터는 Data Discovery 문제들이 생겨남

이를 해결하기 위해 다양한 오픈소스와 서비스들이 출현(DataHub(LinkedIn),Amundsen(Lyft).../Select Star,DataFrame...)

5. SELECT소개

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

예제1

SELECT *

FROM raw_data.user_session_channel;

: raw_data.user_session_channel테이블의 모든 레코드를 읽어와라

 

예제2

SELECT userId, sessionId, channel

FROM raw_data.user_session_channel;

: raw_data.user_session_channel테이블의 userId, sessionId, channel컬럼들을 읽어와라(위의 SQL문과 동일한 출력)

 

예제3

SELECT *

FROM raw_data.user_session_channel;

LIMIT 10;

: 앞의 10개의 레코드만 읽어와라

 

예제4

SELECT DISTINCT channel

FROM raw_data.user_session_channel;

:raw_data.user_session_channel이 갖고 있는 유일한 채널 이름을 알고 싶을 때->6개의 채널이름이 나올 것

 

예제5

SELECT channel, COUNT(1)

FROM raw_data.user_session_channel

GROUP BY 1;

:raw_data.user_session_channel의 채널별 카운트를 하고 싶은 경우->6이 출력

 

예제6

SELECT COUNT(1)

FROM raw_data.user_session_channel;

:테이블의 모든 레코드 수 카운트

 

예제7

SELECT COUNT(1)

FROM raw_data.user_session_channel

WHERE channel='Facebook';

:channel이름이 Facebook인 경우만 고려새서 레코드 수 카운트

 

CASE WHEN : 필드 값의 변환을 위해 사용가능

 

NULL : 값이 존재하지 않음, 0과 ''과는 다름, is NULL을 사용해서 구함

 

WHERE

CASE WHEN사이에도 사용가능

 

STRING Functions

 

ORDER BY

ASC(default)/DESC

null값은 오름차순일 경우 마지막, 내림차순일 경우 처음에 위치 바꾸고 싶으면 NULLS FIRST/LAST사용

 

타입변환

DATE Conversion : CONVERT_TIMEZONE, DATE, TRUNCATE, DATE_TRUNC, EXTRACT/DATE_PART, DATEDIFF, DATEADD, GET_CURRENT...

TO_CHAR, TO_TIMESTAMP

 

Type Casting

오퍼레이터/cast함수 사용

 

실습