[DEV] 6주차. 데이터 웨어하우스와 SQL과 데이터분석(5)

1. 사용자별로 처음 채널과 마지막 채널 알아내기

ROW_NUMBER 이용

CTE를 빌딩블록으로

WITH first AS (
    SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts) seq
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
), last AS (
    SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts DESC) seq
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
)
SELECT first.userid AS userid, first.channel AS first_channel, last.channel AS last_channel
FROM first
JOIN last ON first.userid = last.userid and last.seq = 1
WHERE first.seq = 1;

JOIN

SELECT first.userid AS userid, first.channel AS first_channel, last.channel AS last_channel
FROM (
    SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts) seq
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
) first
JOIN (
    SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts DESC) seq
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
) last ON first.userid = last.userid and last.seq = 1
WHERE first.seq = 1;

GROUP BY

SELECT uesrid,
MAX(CASE WHEN rn1 = 1 THEN channel END) first_touch,
MAX(CASE WHEN rn2 = 1 THEN channel END) last_touch
FROM (
    SELECT userid,
    channel, 
    (ROW_NUMBER() OVER(PARTITION BY userid ORDER BY st.ts asc)) AS rn1,
    (ROW_NUMBER() OVER(PARTITION BY userid ORDER BY st.ts desc)) AS rn2
    FROM raw_data.user.sesiion_channel usc
    JOIN raw_data.session_stamp st ON usc.sessionid = st.sessionid
)
GROUP BY 1;

FIRST VALUE / LAST VALUE

SELECT DISTINCT
    A.userid,
    FIRST_VALUE(A.channel) OVER(PARTITION BY userid ORDER BY B.ts rows between unbounded preceding and unbounded following) AS first_channel,
    LAST_VALUE(A.channel) OVER(PARTITION BY userid ORDER BY B.ts rows between unbounded preceding and unbounded following) AS last_channel
FROM raw_data.user_session_channel A
LEFT JOIN raw_data.session_timestamp B ON A.sessionid = B.sessionid;

2. Gross Revenue가 가장 큰 userid 10개 찾기

Gross Revenue : Refund를 포함한 매출

GROUP BY

SELECT 
    userid,
    SUM(amount)
FROM raw_data.session_transaction st
LEFT JOIN raw_data.user_session_channel usc ON st.sessionid = usc.sessionid
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

SUM OVER

SELECT DISTINCT
    userid,
    SUM(amount) OVER(PARTITION BY usc.userid)
FROM raw_data.session_transaction st
LEFT JOIN raw_data.user_session_channel usc ON st.sessionid = usc.sessionid
ORDER BY 2 DESC
LIMIT 10;

3. 트랜잭션

Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법
- DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음
- SELECT에는 트랜잭션을 사용할 이유가 없음
- BEGIN과 END / BEGIN, COMMIT 사이에 SQL 문 사용

BEGIN;
    A 계좌에서 인출;     -- 이 명령어들은 하나의 명령어처럼 처리되어
    B 계좌로 인출;       -- 다 성공하든지 다 실패해야 함
END;

END와 COMMIT은 동일
BEGIN 전으로 돌아가고 싶다면 ROLLBACK 실행

트랜잭션 커밋 모드: autocommit

autocommit = True
- 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 DB에 쓰여짐 = commit 된다고 함
- 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT) / ROLLBACK으로 처리
- Google Colab은 기본적으로 모든 SQL문이 바로 커밋됨
- psycopg2는 autocommit으로 조절 가능
autocommit = False
- 모든 레코드 수정/삭제/추가 작업이 COMMIT이 호출될 떄까지 커밋되지 않음
- 많이 쓰이지는 않음

4. DELETE FROM vs. TRUNCATE

DELETE FROM table_name
- 테이블에서 모든 레코드를 삭제
- vs. DROP TABLE table_name : 테이블 자체를 삭제
- WHERE을 사용하여 특정 레코드만 삭제 가능
TRUNCATE table_name
- 테이블에서 모든 레코드를 삭제
- DELETE FROM은 속도가 느림
- TRUNCATE가 전체 테이블 내용 삭제 시에는 유리!
- 단점
  - WHERE을 지원하지 않음
  - 트랜잭션을 지원하지 않음

5. SQL 문법들

UNION, EXCEPT, INTERSECT

UNION (합집합)
- 여러 개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐줌
- UNION ALL은 중복된 값도 모두 출력
- UNION은 중복 제거
EXCEPT (MINUS)
- 하나의 SELECT 결과에서 다른 SELECT 결과를 뺌
INTERSECT (교집합)
- 여러 개의 SELECT문에서 같은 레코드들만 찾아줌
EXCEPT, INTERSECT : SQL을 수정했을 때, 기존 SQL과 새 SQL의 차이점을 실제 레코드를 통해서 테스트하기 위해 많이 사용

COALESCE, NULLIF

COALESCE(exp1, exp2, …)
- 첫번째 exp부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴
- 모두 NULL이면 NULL 리턴
- NULL 값을 다른 값으로 바꾸고 싶을 때 사용!
NULLIF(exp1, exp2)
- exp1과 exp2가 같으면 NULL 리턴
- 다르면 exp1 리턴
- 나누기 연산에서 분모에 사용할 수 있음

LISTAGG

GROUP BY에서 사용되는 Aggregate 함수 중 하나
그룹핑된 레코드들 안에서 특정 필드의 값을 옆으로 붙여주는 함수 (listing 형태로 aggregation)
ex) 사용자 ID 별로 방문한 채널을 시간순으로 리스트

SELECT
    userid,
    LISTAGG(channel, '->') WITHIN GROUP (ORDER BY ts) channels
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
GROUP BY 1
LIMIT 10;

-- 68 Youtube->Google->Instagram->Youtube->Instagram->Instagrm->Instagrm->Organic ...

WINDOW 함수

function(exp) OVER([PARTITION BY exp][ORDER BY exp])
functions
- ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG
- math: AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE

LAG 함수

정렬된 레코드들에서 이전 값, 다음 값을 알고싶을 때 사용
ex) 사용자 세션을 시간 순으로 봤을 때 앞 세션의 채널

SELECT usc.*, st.ts,
    LAG(channel, 1) OVER(PARTITION BY userid ORDER BY ts) prev_channel
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
ORDER BY usc.userid, st.ts;

JSON Parsing Functions

JSON의 포맷을 이미 아는 상황에서만 사용 가능
- JSON String을 입력으로 받아 특정 필드의 값 추출 가능 (nested 구조 지원)
ex)

SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":"1"}, "f4":{"f5":"99", "f6":"star"}}', 'f4', 'f6')   -- star 출력

스크린샷 2023-11-24 오전 3 05 34