[데브코스][데이터 분석] SQL 심화 (SELECT 배우기)

< 5주차 데이터 웨어하우스와 SQL 기본에 대해 배우고 이를 바탕으로 데이터 분석에 대해 학습 (2) >

 

1. Redshift 론치 데모

: AWS 콘솔을 통해 Redshift를 론치해보자. (강사님이 만든 서버를 이용하여 실습 진행)

AWS 홈페이지 -> 제품 -> 데이터베이스 -> Amazon Redshift 이동

 

2. 예제 테이블 소개

: 실습에 사용할 테이블 소개

 

사용자 ID : 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID가 있음

세션 ID : 세션마다 부여되는 ID

 

* 세션이란? 

: 사용자의 방문을 논리적인 단위로 나눈 것(크게 2가지 종류가 있음)

- 사용자가외부링크(보통광고)를 타고 오거나 직접 방문해서 올 경우 세션을 생성

- 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성

-> 즉하나의사용자는여러개의세션을가질수있음

: 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해둠 + 세션이 생긴 시간 기록

---> 마케팅 관련 기여도 분석을 위해. 

 

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

: 마케팅 관련, 사용자 트래픽 관련
: DAU, WAU, MAU등의 일주월별 Active User 차트

: Marketing Channel Attribution 분석

=> 어느 채널에 광고를 하는 것이 가장 효과적인가?

 

"""

ex. 사용자 ID 100번: 총 3개의 세션(파란 배경)을 갖는 예제

세션1 : 구글키워드 광고로 시작한 세션

세션2 : 페이스북광고를 통해 생긴 세션

세션3 : 네이버광고를 통해 생긴 세션

--> 3가지 기여도 분석 가능

1. First Channel Attribution : 가장 처음 생긴 세션에 기여도 제공

2. Last Channel Attribution : 구매 직전의 마지막 세션에 기여도 제공

3. Multi Channel Attribution : 여러가지 세션을 모두 종합하여 기여도 제공

 

<실습에서 사용할 데이터베이스>

* raw_data 데이터베이스

1. user_session_channel 테이블

- userid : int

- sessionid : varchar(32)

- channel : varchar(32)

 

2. session_timestamp 테이블

- sessionid : varchar(32)

- ts : timestamp

 

* marketing 데이터베이스

 

3. SQL 소개 (DDL과 DML)

SQL 특징

- 세미콜론으로 분리

- 주석은 --, /*--*/로 사용

- 키워드는 대문자 사용

- 테이블/필드명 규칙 필요

 

DDL

: 테이블의 구조를 정의하는 언어

- CREATE

: Primary key 속성을 지정할 수 있으나 무시됨

: big Data 데이터웨어하우스에서는 primary key가 지켜지지 않음(redshift, snowflake, bigquery)

-> 이를 보장하는 순간 레코드를 추가할 때, 계속 체크 필요 -> 시간이 오래 걸림 -> 성능 저하

* primary key : 동일한 값을 갖지 않는 필드

* CTAS(Create Table xx As Select) : 테이블을 만듦과 동시에 값을 채워넣는 것

 

- DROP

DROP TABLE table_name;

: 존재하는 테이블을 삭제해줌. 

: 삭제하려는 테이블이 없으면 에러가 발생

==> DROP TABLE IF EXISTS table_name;

VS DELECT FROM

: delect는 조건에 맞는 레코드들을 지움(테이블 자체는 존재) -> 테이블에 들어있는 값들만 지움. 

 

 

- ALTER

- 새로운 컬럼 추가

: ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;

 

- 기존컬럼 이름변경

: ALTER TABLE 테이블이름 RENAME 현재필드이름 to 새필드이름

 

- 기존 컬럼 제거

: ALTER TABLE 테이블이름 DROP COLUMN 필드이름;

 

- 테이블 이름 변경

: ALTER TABLE 현재테이블이름 RENAME to 새테이블이름;

 

DML

: 테이블 데이터 조작 언어

- SELECT

: 조건에 맞는 레코드들을 읽어옴

: SELECT FROM ->  테이블에서 레코드와 필드를 읽어오는데 사용

: WHERE를 사용해서 레코드 선택 조건을 지정

: GROUP BY를 통해 정보를 그룹 레벨에서 뽑는데 사용하기도 함

-> DAU, WAU, MAU 계산은 GROUP BY를 필요로 함

: ORDER BY를 사용해서 레코드 순서를 결정하기도 함

: 보통 다수의 테이블의 조인해서 사용하기도 함

 

- INSERT INTO

: 테이블에 레코드를 추가하는데 사용

 

- UPDATE FROM

: 테이블 레코드의 필드 값 수정

 

- DELETE FROM

: 테이블에서 특정 레코드를 삭제(테이블은 여전히 존재. 그 안의 값들만 사라짐)

= TRUNCATE

* delect는 트랜잭션에 사용 가능.  truncate는 트랜잭션에 사용 불가. 

 

4. SQL 실습 환경 소개

구글 코랩 사용

 

데이터 직무가 기억할 점

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

: 항상 데이터를 믿을 수 있는지 의심

: 실제 레코드를 몇 개 살펴보는 것만한 것이 없음 (노가다)

 

- 데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요

: 중복된 레코드들 체크

: 최근 데이터의 존재 여부 체크

: primary key의 유일성이 지켜지는지 체크

: 값이 비어있는 컬럼들이 있는지 체크

 

- 어느 시점이 되면 너무나 많은 테이블들이 존재함

-> 회사가 발전하면 많은 테이블들이 생성됨

-> 테이블이 너무 많아지면 모든 테이블들을 알기 힘듦. 

: 회사 성장과 밀접한 관련

: 중요 테이블이 무엇이고 그것들의 메타 정보를 잘 관리하는 것이 중요해짐

 

- 그 시점부터 Data Discovery 문제들이 생겨남

: 무슨 테이블에 내가 원하고 신뢰할 수 있는 정보가 들어있나?

: 테이블에 대해 질문을 하고 싶은데 누구에게 질문을 해야하나?

 

- 이 문제를 해결하기 위해 다양한 오픈소스와 서비스들이 출현

: DataHub, Amundsen, Select Star, DataFrame, 등,,,

 

5. SELECT 소개

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

 

-- 채널별 카운트를 하고 싶은 경우. COUNT 함수 사용
SELECT channel, COUNT(1)
FROM raw_data.user_session_channel GROUP BY 1;

 

-- 테이블의 모든 레코드 수 카운트. COUNT(*). 하나의 레코드
SELECT COUNT(1)
FROM raw_data.user_session_channel;

 

-- channel 이름이 Facebook경우만 고려해서 레코드수 카운트
SELECT COUNT(1)
FROM raw_data.user_session_channel
WHERE channel = 'Facebook';

 

-- 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;

 

* NULL

: 값이 존재하지 않음을 나타내는 상수

: 0 혹은 ""과는 다른 값

: 필드 지정 시 값이 없는 경우 NULL로 지정 가능

(테이블 정의 시 default 값으로 지정 가능)

: IS NULL / IS NOT NULL 과 같은 문법 사용

 

* COUNT 함수 제대로 이해하기

 

-- count의 인자가 null이 아니면 개수를 셈(count 인자 안의 숫자는 행의 존재 여부를 나타냄 (null / 숫자))
SELECT COUNT(1) FROM count_test
-- 7

SELECT COUNT(0) FROM count_test
-- 7

SELECT COUNT(NULL) FROM count_test
-- 0
-- count의 인자가 null이면 레코드 수를 세지 않음

SELECT COUNT(value) FROM count_test
-- 6
-- null이 아닌 것들만 count

SELECT COUNT(DISTINCT value) FROM count_test
-- 4
-- null이 아닌 것들 중 유일한 값을 셈

 

 

* WHERE

- IN

: WHERE channel in (‘Google’, ‘Youtube’)

 = WHERE channel = ‘Google’ OR channel = ‘Youtube’

 

: NOT IN

- LIKE and ILIKE -> 문자열 매칭

LIKE : 대소문자 비교

ILIKE : 대소문자 비교 X

• 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 사이에서도 사용가능

 

 

SELECT의 STRING Function

- LEFT(str, N)

: str에서 N만큼 왼쪽에서부터 문자를 가져옴

 

- REPLACE(str, exp1, exp2)

: str에서 exp1을 찾아 exp2로 변경

 

-UPPER(str)

: 모두 대문자로

 

- LOWER(str)

: 모두 소문자로

 

- LEN(str)

: 문자열의 길이

 

- LPAD, RPAD

: 문자의 왼쪽/오른쪽에 문자열을 패딩함

 

- SUBSTRING

: 시작점을 제공하여, 주어진 시작점부터 문자를 뽑아옴

 

 

* ORDER BY

: 필드 이름 혹은 필드 숫자를 기준으로 사용 가능

ASC - 오름차순 / DESC - 내림차순

 

: NULL 값들은 오름차순일 경우 마지막에 위치함

: NULL 값들은 내림차순일 경우 처음에 위치함

: 이를 바꾸고 싶다면 "NULL FIRST or NULL LAST" 사용

 

* 타입변환

- DATE 변환

- TO_CHAR

- TO_TIMESTAMP

 

- 1/2의 결과는?

: 0이 됨. 정수간의 연산은 정수가 되어야하기 때문

-> 분자나 분모 중의 하나를 float로 캐스팅해야 0.5가 나옴

 

--> :: 오퍼레이터 사용 (category :: float ==> category 변수를 float 형으로 변경)

-->  cast 함수 (cast(category as float))