SQL_Analysis 2

안재영·2024년 4월 23일

SQL_Analysis

데이터 웨어하우스 세팅

세팅

구글 aws management console
가입
aws management console에서 Amazon Redshift 검색
cluster나 serverless 생성

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

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

이를 통해서 다양한 마케팅 기여도 분석이 가능해진다

예를 들어 채널정보를 이용하여 어느경로를 통해 접근한 사용자들이 서비스 이용시 이득을 만들어내고 이득없이 단순히 트래픽만있는 사용자들을 구분하여 마케팅 방향을 잡을수 있음

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

이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능
- 마케팅 관련, 사용자 트래픽 관련
- DAU(Day), WAU(Week), MAU(Month)등의 일주월별 Active User 차트
- Marketing Channel Attribution 분석(경유지)
  - 어느 채널에 광고를 하는 것이 가장 효과적인가
  - 어느 채널이 가장 큰 이득을 만들 수 있는가
  - 어느 채널이 가장 많은 접근을 하는가

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

사용자 ID 100: 총 3개의 세션을 갖는 예제
- 세션1 : 구글 키워드 광고로 시작한 세션
- 세션2 : 페이스북 광고를 통해 생긴 세션
- 세션3 : 네이버 광고를 통해 생긴 세션
  
  사용자의 행동 경로
1. 9:30 AM 구글 광고타고 방문 - 세션1 생성
2. 9:31 AM 상품 광고 클릭
3. 9:33 AM 상품 리퓨 페이지 클릭
4. 9:45 AM 페이스북 광고 타고 방문 - 세션2 생성
5. 9:47 AM 다른 상품 광고 클릭
6. 10:30AM 네이버 광고 타고 방문 - 세션3 생성
7. 10:35AM 상품 구매
  
  이를 마케팅 관점에서 본다면 상품을 처음 구매자에게 노출시킨곳은 구글 상품을 사용자에게 구매시킨 곳은 네이버가 된다는것을 알수있음
  
  이를 통해 비용을 책정할때
  
  구매자에게 처음 노출시킨 부분을 중요하게 생각할지
  
  구매자를 생성한 부분을 중요하게 생각할지
  
  아니면 모든 부분을 신경쓸지를 선택할수 있게됩니다

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

raw_data 데이터베이스(스키마)

user_session_channel
- userId: int
- sessionid: varchar(32)
- channel: varchar(32)
session_timestamp
- sessionId: varchar(32)
- ts : timestamp

예제 테이블에서 세션의 온전한 값을 찾기 위해서는 sessionId를 Primary Key로 잡고 해당 키를 기준으로 두 테이블을 join시켜주면 됨

SQL 기본

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

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

CREATE TABLE
- Primary Key 속성을 지정할수 있으나 무시됨
  - Primary Key uniqueness
    - Big Data 데이터웨어하우스에서는 지켜지지 않음(Redshift, Snowflake, BigQuery)
- 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;
  - 없는 테이블을 지우려하는경우도 에러는 내지않고 그냥 넘김
- vs DELETE FROM
  - DELETE FROM 은 조건에 맞는 레코드들을 지움(테이블은 존재)
ALTER TABLE
- 새로운 컬럼 추가:
  - ALTER TABLE table_name ADD COLUMN field_name field_type
- 기존 컬럼이름 변경:
  - ALTER TABLE table_name RENAME field_name to new_field_name
- 기존 컬럼 제거
  - ALTER TABLE table_name DROP COLUMN field_name
- 테이블 이름 변경
  - ALTER TABLE table_name RENAME to new_table_name

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

레코드 질의 언어 : SELECT
- SELECT FROM : 테이블에서 레코드와 필드를 읽어오는데 사용
- WHERE : 레코드 선택조건을 지정
- GROUP BY : 정보를 그룹레벨에서 뽑는데 사용
- ORDER BY : 레코드 순서를 선택
- 보통 다수의 테이블의 조인해서 사용하기도함
레코드 수정언어
- INSERT INTO : 레코드 추가
- UPDATE FROM: 레코드의 필드값 수정
- DELETE FROM : 레코드 삭제 - 조건을 안줄경우 모든 레코드를 지워버림
  - vs. TRUNCATE

SELECT

CASE WHEN
- 필드 값의 변환을 위해 사용 가능
  - CASE WHERN 조건 THEN 참 일때 값 ELSE 거짓일때 값 END 필드이름
- 여러 조건을 사용하여 변환하는것도 가능
  
  CASE
  
  WHEN 조건1 THEN 값1
  
  WHEN 조건2 THEN 값2
  
  ELSE 값3
  
  END 필드이름
NULL 이란?
- 값이 존재하지 않음을 나타내는 상수 0 혹은 “”과는 다름
- 필드 지정시 값이 없는경우 NULL로 지정 가능
  - 테이블 정의시 디폴트 값으로도 지정 가능
- 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로함
  - field1 is NULL 혹은 field is not NULL
- NULL이 사칙연산에 사용되면 결과는?
  - 0+NULL, 0-NULL. 0*NULL, 0/NULL : 모두 NULL이됨
COUNT 함수
- COUNT함수가 카운팅을 할땐 인자로 숫자가 들어오면 값을 카운팅하고 NULL은 스킵이됩니다
- 예시 테이블
  value
  NULL
  1
  1
  0
  0
  4
  3
  - SELECT COUNT(1) FROM 예시테이블
    - 7
  - SELECT COUNT(0) FROM 예시테이블
    - 7
  - SELECT COUNT(NULL) FROM 예시테이블
    - 0
  - SELECT COUNT(value) FROM 예시테이블
    - 6
  - SELECT COUNT(DISTINCT value) FROM 예시테이블
    - 4
WHERE
- IN
  - WHERE field in(’value1’, ‘value2’)
    - WHERE field = “value1” OR field = ‘value2
- LIKE and ILIKE
  - WHERE field LIKE ‘V%’ - > ‘V*’
    - V로 시작하는 모든 값
  - WHERE field LIKE ‘%V%’ - > ‘V’
    - V가 들어가는 모든 값
- BETWEEN
  - BETWEEN 1 AND 10
    - 1부터 10을 가진 값
- 위의 오퍼레이터들은 CASE WHEN 사이에서도 사용 가능
STRING Function
- LEFT(str,N)
  - str에서 N만큼의 문자들을 추출한다
- REPLACE(str, exp1, exp2)
  - str에서 exp1을 찾아 exp2로 교체한다
- UPPER(str)
  - str을 대문자로 바꾼다
- LOWER(str)
  - str을 소문자로 바꾼다
- LEN(str)
  - str의 길이를 리턴한다
- LPAD, RPAD
  - LPAD는 문자에 왼쪽으로 패딩해준다
  - RPAD는 문자에 오른쪽으로 패딩해준다
- SUBSTRING
  - st에서 x번째부터 y번째까지 출력한다
ORDER BY
- ASC
  - 오름차순
- DESC
  - 내림차순
- multiple
  - ORDER BY 1 DESC, 2, 3
    - 1번으로 내림차순하고 같은값은 2로 정렬하고 그래도 같은값은 3으로 다시정렬한다
타입 변환
- DATE Conversion
  - 타임존 관련 변환
  - DATE, TRUNCATE
  - DATE_TRUNC
    - 첫번째 인자가 어떤값을 추출하는지 지정(week, month, day …)
  - EXTRACT or DATE_PART : 날짜 시간에서 특정 부분의 값을 추출
  - DATEDIFF : 날짜간의 차이
  - DATEADD : 날짜를 더함
- TO_CHAR, TO_TIMESTAMP
  - 문자열을 시간으로바꾸거나 시간을 문자열로 바꿈
Type Casting
- 1/2의 결과는?
  - 0이됨 정수간의 연선은 정수가 되어야하기 때문
    - 분자나 분모중의 하나를 float으로 캐스팅해야 0.5가 나옴
    - 이를 프로그래밍언어에서도 일반적으로 동일하게 동작
- :: 오퍼레이터를 사용
  - field::float
- cast 함수를 사용
  - cast(field as float)

value
NULL
1
1
0
0
4
3

데이터 작업을 하며 주의할점

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

안재영

이전 포스트

SQL_Analysis 1-1

다음 포스트