out_of_anjoong.dev

ROW_NUMBER()

SELECT된 레코드들에 특정 기준을 붙여서 일련번호 추가

• PARTITION BY : 특정 값을 기준으로 그룹핑(GROUP BY와는 다르지만 ROW_NUMBER에서 쓰일 땐 역할이 비슷함)

ROW_NUMBER() OVER(PARTITION BY field1 ORDER BY field2)
-- 필드1을 그룹핑해서 필드2를 기준으로 정렬

FIRST_VALUE/LAST_VALUE

FIRST_VALUE는 쿼리 결과에서 첫번째 행을,  LAST_VALUE는 마지막 행을 가져옴.( 사용법 참조: https://gent.tistory.com/338 )

트랜잭션

• Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법
  • DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음.
  • SELECT에는 트랜잭션을 사용할 이유가 없음
  • BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL 들을 사용
  • SQL이 하나라도 실패하면 ROLLBACK
• 트랜잭션 커밋 모드 : autocommit
  • autocommit = True
    • 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 DB에 쓰여짐. 이름 커밋 된다고 함.
    • 만약 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT)/ROLLBACK 으로 처리
  • autocommit = False
    • 모든 레코드 수정/삭제/추가 작업이 COMMIT 호출될 때 까지 커밋되지 않음
• Google Colab의 트랜잭션
  • 기본적으로 모든 SQL statement가 바로 커밋됨 (autocommit = True)
  • 이를 바꾸고 싶다면 BEGIN;END; 혹은 BEGIN;COMMIT;을 사용(혹은 ROLLBACK)
• psycopg2의 트랜잭션
  • autocommit이라는 파라미터로 조절 가능
  • autocommit = True : 기본적으로 PostgreSQL의 커밋 모드와 동일
  • autocommit = False : 커넥션 객체의 .commit()과 .rollback()함수로 트랜잭션 조절 가능

DELETE FROM vs TRUNCATE

• DELETE FROM table_name
  • 테이블에서 모든 레코드를 삭제
  • DROP TABLE table_name과 다른 점은 레코드를 삭제하지만 테이블은 남아있음.
  • WHERE 사용해 특정 레코드만 삭제 가능.
• TRUNCATE table_name
  • 테이블에서 모든 레코드를 삭제
  • DELETE FROM은 속도가 느림
  • 전체 테이블의 내용 삭제시에는 DELETE보다 유리
  • 단점 2가지
    • WHERE 지원 X
    • 트랜잭션 지원 X

GROUP BY

테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산

• 먼저 GROUP BY로 그룹핑을 할 필드를 결정(하나 이상의 필드가 될 수 있음, 필드 이름 또는 필드 일련번호를 사용)
• 다음 그룹별로 계산할 내용을 결정
  • Aggregate 함수를 사용
  • COUNT, SUM, AVG, MIN, MAX 등등

※ TO_CHAR(str, 'YYYY-MM') vs LEFT(str, 7) vs DATE_TRUNC('month', str) vs SUBSTRING(str, 1, 7)
   TO_CHAR, LEFT, SUBSTRING은 다 리턴값이 문자열이지만 DATE_TRUNC는 시간.
    DATE_TRUNC 문서 참고 https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_DATE_TRUNC.html

SELECT
    channel,
    COUNT(1) AS session_count,
    COUNT(DISTINCT userId) AS user_count
FROM raw_data.user_session_channel
GROUP BY 1                         -- GROUP BY channel
ORDER BY 2 DESC;              -- ORDER BY session_count DESC

CTAS(CREATE TABLE table_name AS SELECT TABLE A)

간단하게 새로운 테이블을 만드는 방법. 자주 조인하는 테이블들이 있다면 CTAS를 사용해서 조인해두면 편리함

DROP TABLE IF EXISTS adhoc.joonghyeon_session_summary;
CREATE TABLE adhoc.joonghyeon_session_summary AS
SELECT B.*, A.ts FROM raw_data.session_timestamp A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid;

CTE

중복된 레코드 체크하기

SELECT COUNT(1)
FROM adhoc.joonghyeon_session_summary;
-- code A


SELECT COUNT(1)
FROM (
	SELECT DISTINCT userID, sessionID, ts, channel
    FROM adhoc.joonghyeon_session_summary
);
-- code B

-- 두개 결과 확인하기

with ds AS (
	SELECT DISTINCT userID, sessionID, ts, channel
    FROM adhoc.joonghyeon_session_summary
)
SELECT COUNT(1)
FROM ds;
-- CTE를 사용해서 중복제거 후 카운트(code B와 결과는 완전 같지만 CTE를 사용하면 ds 테이블을 계속 사용 가능)

최근 데이터의 존재 여부 체크하기(freshness)

SELECT MIN(ts), MAX(ts)
FROM adhoc.joonghyeon_session_summary
-- 타임스탬프 필드를 찾아보는 방법

Primary Key uniqueness가 지켜지는지 체크하기

SELECT sessionID, COUNT(1)
FROM adhoc.joonghyeon_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT(1)
-- sessionID를 그룹핑하여 COUNT를 해주고 COUNT된 값으로 내림차순으로 해주는 방법

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

SELECT
	COUNT(CASE WHEN sessionID is NULL THEN 1 END) sessionid_null_count,
    COUNT(CASE WHEN userID is NULL THEN 1 END) userid_null_count,
    COUNT(CASE WHEN ts is NULL THEN 1 END) ts_null_count,
    COUNT(CASE WHEN channel is NULL THEN 1 END) channel_null_count,
FROM adhoc.joonghyeon_session_summary;
-- NULL일 때 1을 리턴 아니면 0 리턴

Redshift

AWS에서 제공하는 데이터 웨어하우스 서비스

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

• 사용자 ID : 보통 웹서비스에서 등록된 사용자마다 부여하는 유일한 ID
• 세션 ID : 세션마다 부여되는 ID
  • 세션 : 사용자의 방문을 논리적인 단위로 나눈 것
    • 사용자가 외부 링크(보통 광고)를 타고 오거나 직접 방문해서 올 경우 세션을 생성
    • 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성(time bound)
  • 즉 하나의 사용자는 여러 개의 세션을 가질 수 있음
  • 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해둠
    • 마케팅 관련 기여도 분석을 위함
  • 또한 세션이 생긴 시간도 기록
• 위 정보들을 기반으로 다양한 데이터 분석과 지표 설정이 가능
  • 마케팅 관련, 사용자 트래픽 관련
  • DAU, WAU, MAU등의 일주월별 Active User 차트
  • Marketing Channel Attribution 분석(어느 채널에 광고를 하는 것이 가장 효과적인지)

상품 구매로 이어지면 기여도를 나눠야 함.

• first channel attribution : 첫 광고
• last channel attribution : 구매 직전 마지막 광고
• multi channel attribution : 모든 광고에게 기여도 부여

SQL 기본 문법

• 다수의 SQL 문을 실행할 때는 세미콜론으로 분리 필요
  • SQL 1; SQL 2; SQL 3;
• SQL 주석
  • -- : 한줄짜리 주석.
  • /* */ : 여러 줄 주석
• SQL 키워드는 대문자 사용 등 포맷팅이 필요.
  
  • 팀 프로젝트라면 팀에서 사용하는 공동 포맷 설정
• 테이블/필드 이름의 명명 규칙 설정
  • 단수형 vs 복수형 : User vs Users
  • _ vs CamelCasing : user_session_channel vs UserSessionChannel

DDL

테이블 구조 정의 언어

• CREATE TABLE : 테이블 생성
  <예시>
  CREATE TABLE raw_data.user_session_channel (
      userid int, (실수형)
      sessionid varchar(32) primary key, (문자열 32자)
      channel varchar(32)
  );
  
  • primary key uniqueness : 빅데이터 데이터 웨어하우스에서는 지켜지지 않음(Redshift, Snowflake, Bigquery 등)
  • CTAS : CREATE TABLE table_name AS SELECT TABLE A : table_name 테이블 생성 후 곧바로 TABLE A 에서 추출한 정보 입력( 다른 방법 : CREATE TABLE and then INSERT)
• DROP TABLE : 테이블 제거
  • DROP TABLE table_name
  • 없는 테이블 제거하려는 경우는 에러 발생
  • 존재할 경우 제거하기
    • DROP TABLE IF EXISTS table_name;
    • DELETE FROM 조건(조건에 맞는 레코드들 삭제하지만 테이블 자체는 존재)
• ALTER TALBE
  • 새로운 컬럼 추가 : ALTER TABLE 테이블명 ADD COLUMN 필드명 필드타입;
  • 기존 컬럼 이름 변경 : ALTER TABLE 테이블명 RENAME 현재필드명 to 새필드명;
  • ALTER TABLE 테이블명 DROP COLUMN 필드명;
  • ALTER TABLE 현재 테이블명 RENAME to 새테이블명
• 레코드 수정 언어
  • INSERT INTO : 테이블에 레코드를 추가하는데 사용
  • UPDATE FROM : 테이블 레코드의 필드 값 수정
  • DELETE FROM : 테이블에서 레코드 삭제 (vs TRUNCATE)

DDL

레코드 질의 언어

• SELECT FROM : 테이블에서 레코드와 필드를 읽어오는데 사용
  • DISTINCT : 중복 제거
  • COUNT : 필드의 수를 세줌
  • 테이블의 모든 레코드 수 카운트하는 방법
    SELECT COUNT(1)
    FROM 테이블명
• WHERE : 레코드 선택 조건 지정
  • IN, NOT IN
    WHERE channel IN ('Google', 'Youtube')
    WHERE channel = 'Google' OR channel = 'Youtube')
  • LIKE and ILIKE : 문자열 매치시키기
    WHERE channel LIKE 'G%' : G*******
    WHERE channel LIKE '%o%' : ********o*********
  • BETWEEN A AND B : A와 B 사이(날짜와 날짜 사이, 값과 값 사이)
  • CASE WHEN에서도 사용 가능
• GROUP BY : 정보를 그룹 레벨에서 추출하는데 사용
  • DAU, WAU, MAU 계산은 GROUP BY 에서함
• ORDER BY : 레코드 순서를 결정
  • 디폴트는 ASC(오름차순)
    ORDER BY 1 ASC
  • DESC는 내림차순으로
  • 여러 컬럼(필드) 순서 결정 가능
    ORDER BY 1 DESC 2 , 3 : 1 값은 내림차순, 같은 값은 2 값을 보고 오름차순, 또 같으면 3으로 오름차순
  • NULL은 오름차순 일 경우 마지막, 내림차순 일 경우 처음, NULLS FIRST(LAST)를 하면 NULL의 위치 조정 가능

SELECT 필드명1, 필드명2 ...
FROM 테이블명
WHERE 선택조건
GROUP BY 필드명1, 필드명2 ...
ORDER BY 필드명 [ASC][DESC] 필드명 대신 숫자 사용 가능
LIMIT N;

• CASE WHEN : 필드 값의 변환을 위해 사용하거나 조건을 넣을 때 사용.(여러 조건도 가능)

CASE
    WHEN 조건 1 THEN 값 1
    WHEN 조건 2 THEN 값 2
    ELSE 조건 3
END 필드명

• NULL : 값이 존재하지 않음을 나타내는 상수. 0과 "" 와는 다름
  • 필드 지정시 값이 없는 경우 NULL로 지정 가능(테이블 정의 시 디폴트 값으로도 가능)
  • 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로함.( IS NULL or IS NOT NULL)
• STRING 함수
  • LEFT(str, N) : 문자열 str의 왼쪽부터 N개
  • REPLACE(str, exp1, exp2) : 문자열 str에서 exp1을 찾은 후 exp2로 바꿈
  • UPPER(str) : 문자열 str을 대문자로
  • LOWER(str) : 문자열 str을 소문자로
  • LEN(str) : 문자열 str의 길이
  • LPAD(str, N, exp), RPAD(str, N, exp) : exp가 없는 디폴트 값은 문자열 str 왼(오른)쪽에 공백을 채움. exp가 있을 시 exp를 추가함.
  • SUBSTRING(str, st, N) : 문자열 str을 st부터 시작해서 N만큼 자름

데이터 관련 직군

• 데이터 엔지니어 : 내-외부 원천 데이터를 수집, 가공, 적재하여 데이터가 흐르는 파이프라인을 설계 및 구축하는 역할
  • 파이썬, 자바/스칼라
  • SQL, DB
  • ETL/ELT (Airflow, DBT)
  • Spark, Hadoop

• 데이터 분석가 : 데이터를 분석 및 처리하여 의미 있는 비즈니스 결과를 창출하는 역할
  • SQL, 비즈니스 도메인에 대한 지식
  • 통계 (AB 테스트 분석)
• 데이터 과학자 : 비즈니스 문제를 정의하고, 문제를 해결하기 위해서 데이터 분석 모델(e.g. ML/DL)을 만드는 역할
  • 머신러닝 
  • SQL, 파이썬, 자바/스칼라
  • 통계

관계형 데이터베이스(RDB)

정의

• 구조화된 데이터를 저장하고 질의할 수 있도록 해주는 스토리지
  • 엘셀 스프레드시트 형태의 테이블로 데이터를 정의하고 저장
  • 테이블에는 컬럼(열)과 레코드(행)이 존재

대표적인 RDB

• 프로덕션 데이터베이스 : MySQL, PostgreSQL, Oracle
  • OLTP(OnLine Transaction Processing)
  • 빠른 속도에 집중, 서비스에 필요한 정보 저장
  • 서비스에 필요한 정보가 저장되어 있기에 쿼리문을 실행하면 서비스에 영향을 줄 수 있음.
• 데이터 웨어하우스 : Redshift, Snowflake, bigQuery, Hive
  • OLAP(OnLine Analytical Processing)
  • 처리 데이터 크기에 집중, 데이터 분석 혹은 모델 빌딩등을 위한 데이터 저장
  • 보통 프로덕션 데이터베이스를 복사에서 데이터 웨어하우스에 저장

구조

• 2단계로 구성됨
  • 가장 밑단에 테이블들이 존재(엑셀의 시트에 해당)
  • 테이블들은 데이터페이스(스키마)라는 폴더 밑으로 구성(엑셀의 파일에 해당)
• 스키마 : DB의 구조와 제약조건에 관해 전반적인 명세를 기술한 것
              개체의 특성을 나타내는 속성(Attribute), 속성들의 집합인 개체(Entity) 그리고 개체 사이에 존재하는 관계(Relation)에
              대한 정의와 이것들이 유지해야 하는 제약조건으로 이루어져 있음.
• 테이블의 구조(테이블 스키마)
  • 레코드 들로 구성(행)
  • 레코드는 하나 이상의 필드(열)으로 구성
  • 필드(컬럼)은 이름과 타입 속성(primary key)로 구성

SQL(Structured Query Language)

관계형 데이터베이스에 있는 데이터(테이블)를 질의하거나 조작해주는 언어

• DDL(Data Definition Language)
  • 테이블의 구조를 정의하는 언어
• DML(Data Manipulation Language)
  • 테이블에서 원하는 레코드들을 읽어오는 질의 언어
  • 테이블에 레코드를 추가/삭제/갱신할 때 사용하는 언어
• 단점
  • 비구조화된 데이터를 다루는 데에 제약이 심함(Spark, Hadoop 같은 분산 컴퓨팅 환경이 필요)
  • RDB마다 SQL 문법이 조금씩 상이함.

Data Modeling

• Star Schema
  • Production DB용 RDB 에서는 보통 해당 스키마를 사용해 데이터 저장
  • 데이터를 논리적 단위로 나눠 저장하고 필요시 조인. 스토리지의 낭비가 덜하고 업데이트가 쉬움
• Denormalized Schema
  • 데이터 웨어하우스에서 사용하는 방식. 단위 테이블로 나눠 저장하지 않음.
  • 스토리지를 더 사용하지만 조인이 필요 없기에 빠른 계산 가능

데이터 웨어하우스

회사에 필요한 모든 데이터를 저장

• SQL 기반의 RDB
• 프로덕션 DB와는 별도여야 함
• 데이터 웨어하우스는 고객이 아닌 내부 직원을 위한 DB
• ETL 혹은 데이터 파이프라인
  외부에 존재하는 데이터를 읽어서 데이터 웨어하우스로 저장해주는 데에 필요해지고 이를 ETL 혹은 데이터 파이프라인이라고 함.

데이터 인프라

• 데이터 엔지니어가 관리
  • 더 발전하면 Spark와 같은 대용량 분산처리 시스템이 일부로 추가됨.
   

클라우드

정의

• 컴퓨팅 자원(하드웨어, 소프트웨어 등)을 네트워크를 통해 서비스 형태로 사용하는 것
• No Provisioning(내가 준비할 필요 X), Pay As You Go(사용한 만큼 지불)
• 자원을 필요한 만큼 실시간으로 할당하여 사용한 만큼 비용 지불

장점

• 초기 투자 비용이 줄어듦
  CAPEX(Capital Expenditure) vs OPEX(Operating Expenditure)
• 리소스 준비를 위한 대기시간 대폭 감소(Shorter Time to Market)
• 노는 리소스 제거로 비용 감소
• 글로벌 확장 용이
• 소프트웨어 개발 시간 단축(SaaS 이용)