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

SQL_Analysis_Day2-1

 오늘은 AWS 콘솔을 통해 Redshft를 launch하여 직접 실습을 한다. 아래는 실습할 예제 테이블이다.

• 관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보
  • 사용자 ID: 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID
  • 세션 ID: 세션마다 부여되는 ID
    • 세션: 사용자의 방문을 논리적인 단위로 나눈 것(사용자가 외부 링크(보통 광고)를 타고 오거나 직접 방문해서 올 경우나 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우에 세션을 생성)
    • 하나의 사용자는 여러 개의 세션을 가질 수 있음
    • 보통 세션의 경우 마케팅 관련 기여도 분석을 위해 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록
    • 세션이 생긴 시간도 기록
  • 이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능
    • 마케팅, 사용자 트래픽
    • DAU, WAU, MAU등의 일, 주, 월별 Active User 차트
    • Marketing Channel Attribution 분석
  • 사용자 ID 100번: 총 3개의 세션을 갖는 예제
    • 09:30 AM - 구글 광고로 방문(세션 1) →  09:31 AM - 상품 광고 클릭 →  09:33 AM - 상품 리뷰 페이지 클릭 →  09:45 AM - 페이스북 광고로 방문(세션 2) →   09:47 AM - 다른 상품 광고 클릭 →  10:30 AM - 네이버 광고로 방문(세션 3) →   10:35 AM - 상품 구매   
    • Last channel attribution: 상품 구매 바로 전인 세션 3에 모든 기여를 줌
    • Multi channel attribution: 3개의 세션에 모두 기여를 줌
    • First channel attribution: 맨 처음 방문하게 된 세션 1에 모든 기여를 줌
• 관계형 데이터베이스 예제: 데이터베이스와 테이블
  • raw_data 데이터베이스(스키마): primary key는 userId가 아닌 sessionId

user_session_channel 테이블

session_timestamp 테이블

user_session_channel 테이블

session_timestamp 테이블

 

 다음으로 SQL 기본에 대해 알아 보겠다.

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

 

 SQL DDL - 테이블 구조 정의 언어에 대해 알아보겠다.

• CREATE TABLE
  • Primary key 속성을 지정할 수 있으나 무시됨(Big Data 데이터 웨어하우스에서는 Primary key uniqueness가 지켜지지 않음)
  • CTAS: CREATE TABLE table_name AS SELECT vs. CREATE TABLE and then INSERT

  • 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: DELETE FROM은 조건에 맞는 레코드들을 지움(테이블 자체는 존재)
•   ALTER TABLE
  • 새로운 컬럼 추가: ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;
  • 기존 컬럼 이름 변경: ALTER TABLE 테이블이름 RENAME 현재필드이름 to 새필드이름;
  • 기존 컬럼 제거: ALTER TABLE 테이블이름 DROP COLUMN 필드이름;
  • 테이블 이름 변경: ALTER TABLE 현재테이블이름 RENAME to 새테이블이름;

 

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

• 레코드 질의 언어: SELECT
  • SELECT FROM: 테이블에서 레코드와 필드를 읽어오는데 사용
  • WHERE: 레코드 선택 조건을 지정
  • GROUP BY: 정보를 그룹 레벨에서 뽑는데 사용(DAU, WAU, MAU 계산에 필요)
  • ORDER BY: 레코드 순서 결정
  • 다수의 테이블을 조인해서 사용하기도 함 
• 레코드 수정 언어
  • INCERT INTO: 테이블에 레코드를 추가하는데 사용
  • UDATE FROM: 테이블 레코드의 필드 값 수정
  • DELETE FROM: 테이블에서 레코드를 삭제(TRUNCATE도 비슷하나 전체 레코드만 삭제할 수 있음)

 

SQL_Analysis_Day 2-2

 실습에 들어가기에 앞서 다음 사항을 기억해야 한다. 현업에서 깨끗한 데이터란 존재하지 않는다. 항상 데이터를 믿을 수 있는지 의심해야 하고 실제 레코드를 몇 개 살펴봐서 확인해야 한다. 데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요하다. 중복된 레코드, 최근 데이터의 존재 여부, Primary key uniqueness가 지켜지는지, 값이 비어있는 컬럼이 있는지 체크해야 한다. 이러한 체크는 코딩의 unit test 형태로 만들어 매번 쉽게 할 수 있다. 회사가 성장하고 시간이 지날수록 너무나 많은 테이블들이 생기므로 Data Discovery 문제가 생기기 때문에  중요 테이블들이 무엇이고 그것들의 메타 정보를 잘 관리해야 한다. 

 

 예제 테이블을 통해 SELECT 문을 실습하겠다. 

• SELECT * : 테이블 레코드의 모든 필드값을 불러온다.
• LIMIT n : n개의 레코드만 불러온다.
• SELECT DISTINCT channel : 유일한 채널의 이름(채널의 종류)을 불러온다.
• SELECT channel, COUNT(1) \\ GROUP BY 1 : 첫 번째 컬럼(channel)별 카운트한 수를 불러온다. 
• SELECT COUNT(1) : 테이블의 모든 레코드 수 카운트(= COUNT(*))
• SELECT COUNT(1) \\ WHERE channel = 'Facebook' : channel이 Facebook인 레코드 수만 카운트

 CASE WHEN은 여러 조건을 통해 필드 값의 변환을 위해 사용한다.

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로 지정 가능하고 테이블 정의 시에도 디폴트 값으로 지정 가능하다. 어떤 필드의 값이 NULL인지 아닌지 확인하기 위한 문법은 field1 is NULL 또는 field1 is not NULL을 사용한다. NULL은 사칙연산을 해도 결괏값은 NULL이 나온다. 

 

 가상의 테이블(count_test)의 필드(value)에 7개의 레코드(NULL, 1, 1, 0, 0, 4, 3)가 있다고 하자. 각 쿼리의 COUNT 함수 결과를 구해보겠다.

• SELECT COUNT(1) FROM count_test  →  7
• 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 : 1,0,4,3이 카운트

WHERE문의 여러 활용법을 알아보겠다.

• IN 
  • WHERE channel in (‘Google’, ‘Youtube’) 는 WHERE channel = ‘Google’ OR channel = ‘Youtube’와 같다.
  • NOT IN 
• LIKE and ILIKE
  • LIKE는 대소문자를 구분하고 ILIKE는 대소문자를 구분하지 않는다.
  • WHERE channel LIKE 'G%' : channel 중 G가 맨 앞에 들어가는 문자
  • WHERE channel LIKE ‘%o%’ : channel 중 o가 앞이나 뒤에 들어가는 문자
  • NOT LIKE or NOT ILIKE
• BETWEEN
  • 범위 내에 해당하는 값

  STRING(문자열)에 쓰이는 여러 함수들이 있다.

• LEFT(str, N) : 문자열에서 N개의 문자열만큼 추출
• REPLACE(str, exp1, exp2) : str 문자열에서 exp1 인자를 exp2 인자로 바꿈
• UPPER(str) : 문자열을 전부 대문자로 바꿈
• LOWER(str) : 문자열을 전부 소문자로 바꿈
• LPAD, RPAD : 문자열 왼쪽 혹은 오른쪽에 문자열을 붙임
• SUBSTRING : 문자열 시작점을 지정해 일부를 추출

 ORDER BY는 레코드 순서를 정하며 디폴트는 ASC(오름차순)이고 DESC로 내림차순 정렬할 수 있다. 각 필드마다 오름차순과 내림차순을 지정할 수도 있다. NULL은 오름차순일 경우 마지막에 위치하고 내림차순일 경우 처음에 위치한다. 이를 바꾸고 싶다면 NULLS FIRST 또는 NULLS LAST를 사용하면 된다. 

 

 DATE 타입을 변환하는 여러 함수들이 존재한다. select pg_timezone_names()로 타임존의 종류를 확인하여 CONVERT_TIMEZONE('America/Los_Angeles', ts)을 통해 타임존 관련 변환 함수도 있고 DATE_TRUNC를 통해 week, month, day 등을 추출할 수 있으며 이 외에 DATE를 계산하는 DATEDIFF, DATEADD, GET_CURRENT 등 다양한 함수들이 있다. TO_CHAR와 TO_TIMESTAMP을 통해 DATE형식으로 되어 있는 날짜와 시간을 문자열 형식 날짜와 시간을 바꾸거나 그 반대로 바꿀 수 있다. 

 

 1/2를 어느 프로그래밍 언어에서든 구하면 0이 된다. 왜냐하면 정수 간의 연산은 정수가 되어야 하기 때문이다. 그래서 분자나 분모 중 하나를 float로 캐스팅해야 0.5가 나온다. 보통 :: 오퍼레이터를 사용(category::float)하거나 cast 함수를 사용(cast(category as float))한다.

 

공부하며 어려웠던 내용

 AWS의 Redshift을 통해 구글 코랩으로 데이터 테이블을 가져와서 sql을 실행하는 과정이 신기하면서 이해가 되지 않았다. 지지난주에 sql을 배울 때와 같은 내용도 있지만 새로 배운 내용도 많아 sql에 대해 더 깊이 알게 되었다.