[데이터 엔지니어링 데브코스 2기] TIL-6주차-파트06 데이터 웨어하우스와 SQL과 데이터분석(5)

이재호·2023년 11월 18일
TIL데브코스데이터 엔지니어링프로그래머스
0

[데이터 엔지니어링 데브코스 2기]

목록 보기
25/70

1. 실습 예제

예제 1. 사용자별 처음과 마지막 채널 찾기

  • 노가다 방식 (예, 251번 사용자 단일)
SELECT ts, channel
FROM raw_data.user_session_channel usc
	JOIN raw_data.session_timestamp st
    ON usc.sessionId = st.sessionId
WHERE userId = 251
ORDER BY 1;
  • ROW_NUMBER 활용
    ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY field2) nn
    => field1을 기준으로 그룹핑을 하며, field2를 기준으로 레코드마다 일련 번호를 붙이겠다는 의미.
-- 방법 1 - CTE를 빌딩 블록으로 사용
-- first : 모든 사용자별 첫 번째(옛날) 채널 리턴
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 : 모든 사용자별 마지막(최근) 채널 리턴
     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 frist.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;
-- 방법 2 - JOIN 방식. 방법 1의 CTE 빌딩 블록을 FROM 문으로 넣어서 한 것과 동일.
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;
-- 방법 3 - GROUP BY 방식 (복잡한 방법)
SELECT userId,
	MAX(CASE WHEN rn1 = 1 THEN channel END) first_channel,
    MAX(CASE WHEN rn2 = 1 THEN channel END) last_channel
FROM (
	SELECT userId, channel,
    	(ROW_NUMBER() OVER(PARTITION BY usc.userId ORDER BY st.ts ASC)) AS rn1,
        (ROW_NUMBER() OVER(PARTITION BY usc.userId ORDER BY st.ts DESC)) AS rn2
    FROM raw_data.user_session_channel usc
    	JOIN raw_data.session_timestamp st
        ON usc.sessionId = st.sessionId )
GROUP BY 1;
-- 방법 4 - FIRST_VALUE / LAST_VALUE 활용 (간단한 방법)
SELECT DISTINCT A.userId,
	FIRST_VALUE(A.channel) OVER(PARTITION BY A.userId ORDER BY B.ts
    -- default Attribute 필요.
    rows between unbounded preceding and unbounded following) AS first_channel,
    LAST_VALUE(A.channel) OVER(PARTITION BY A.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가 가장 큰 사용자 ID 10개 찾기

-- 방법 1 - 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의 경우의 문제점 : 동점자들에 대한 정보가 누락될 수 있다.
LIMIT 10;
-- 방법 2 - SUM OVER 활용. 앞의 방법과 달리 모든 userId가 선택됨.
SELECT DISTINCT usc.userId, SUM(amount) OVER(PARTITION BY usc.userId)
FROM raw_data.user_session_channel AS usc
	JOIN raw_data.session_transaction AS revenue
    ON revenue.sessionId = usc.sessionId
ORDER BY 2 DESC
LIMIT 10;

예제 3. 월별 NPS 계산하기

  • 점수표
    • detractor : 0 ~ 6
    • passive : 7 or 8
    • promoter : 9 or 10
  • NPS = promoter 비율 - detractor 비율
-- 방법 1.
SELECT month, ROUND((promoters-detractors)*100.0/NULLIF(total_count, 0)), 2) AS overall_nps
FROM (
	SELECT LEFT(created, 7) AS month,
    	COUNT(CASE WHEN score >= 9 THEN 1 END) AS promoters,
        COUNT(CASE WHEN score <= 6 THEN 1 END) AS detractors,
        COUNT(CASE WHEN score > 6 AND score < 9 THEN 1 END) AS passives,
        COUNT(1) AS total_count
    FROM raw_data.nps
    GROUP BY 1
    -- ORDER BY 1
);
-- 방법 2.
SELECT LEFT(created, 7) AS month,
	ROUND(SUM(CASE
    	WHEN score >= 9 THEN 1
        WHEN score <= 6 THEN -1 END) * 100.0 / COUNT(1), 2)
FROM raw_data.nps
GROUP BY 1
ORDER BY 1;


2. 트랜잭션 (Transaction)

트랜잭션이란?

  • Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법입니다.

  • 예) 은행 계좌 이체 (입금, 출금이 atmoic하게 실행되어야 함.)

    -- 아래 두 명령어는 마치 하나의 명령어처럼 작동됨.
BEGIN
	A의 계좌로부터 인출;
    B의 계좌로 입금;
END;
-- BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK 실행.

  • 트랜잭션 커밋 모드 : autocommit (postgresql)

    • autocommit = True : 모든 DML 작업이 바로 DB에 적용(commit)됨.
    • autocommit = False : 모든 DML 작업이 COMMIT이 호출될 때까지 커밋되지 않음.

  • Google Colab의 트랜잭션

    • 기본적으로 autocommit = True로 세팅되어 있음.
    • 이를 바꾸고 싶다면 BEGIN;END; or BEGIN;COMMIT을 사용 (or ROLLBACK;)

  • psycopg2의 트랜잭션

    	- autocommit이라는 파라미터로 조절 가능.
    • autocommit=True => 기본적으로 PostgreSQL의 커밋 모드와 동일.
    • autocommit=False => 커넥션 객체의 .commit() or .rollback() 함수로 트랜잭션 조절 가능.

  • DELETE FROM vs. TRUNCATE

  • DELETE FROM table_name

    • 테이블 내의 모든 레코드를 삭제.
    • DROP TABLE은 비슷하지만, 테이블 자체를 삭제함.
    • WHERE 절을 사용해 특정 레코드만 삭제 가능.
    • TRUNCATE보다 속도가 느림.

  • TRUNCATE table_name

    • 테이블 내의 모든 레코드를 삭제.
    • 전체 테이블의 내용 삭제 시에는 여러모로 유리함.
    • ROLLBACK이 안 됨.
    • WHERE과 Transaction이 지원되지 않음.
      => ROLLBACK이 필요 없는 테이블의 모든 레코드를 삭제할 때 사용됨.


3. 고급 SQL 문법

  • UNION, EXCEPT, INTERSECT

  • CALESCE, NULLIF

  • LISTAGG

  • LAG

  • WINDOW 함수

    • ROW_NUMBER OVER
    • SUM OVER
    • FIRST_VALUE, LAST_VALUE

  • JSON Parsing 함수

UNION, EXCEPT, INTERSECT

  • UNION (합집합)

    • 여러 개의 테이블이나 SELECT 결과를 하나의 결과로 합쳐 줌.
    • UNION vs. UNION ALL
      - UNION은 중복을 제거

  • EXCEPT(or MINUS) (차집합)

    • 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것이 가능.
    • 두 테이블은 동일한 필드 개수와 필드 타입을 가지고 있다는 전제 조건이 있음.
  • INTERSECT (교집합)
    • 여러 개의 SELECT문에서 같은 레코드들만 찾아 줌.

COALESCE, NULLIF

  • COALESCE(exp1, exp2, ...):
    • exp1 값이 NULL이 아니면 해당 값을 리턴, NULL이면 exp2로 -> exp3 -> ...
    • 모든 exp 값이 NULL이면 NULL 리턴.
  • NULLIF(exp1, exp2):
    • exp1과 exp2의 값이 같으면 NULL을 리턴.

LISTAGG

  • GROUP BY에서 사용되는 Aggregate 함수 중의 하나.

  • ex) 사용자 ID별로 채널을 순서대로 리스트:

-- 구분자 X
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 YotubeGoogleInstagramYoutube...
-- 구분자 O
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 Yotube->Google->Instagram->Youtube...

WINDOW

  • Syntax: function(exp) OVER (PARTITION BY exp ORDER BY exp)

  • Useful functions:

    • ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG
    • Math func. : AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE

LAG

  • 어떤 사용자 세션에서 시간순으로 봤을 때,
    • 앞 세션의 채널이 무엇인지?
    • 다음 세션의 채널이 무엇인지?
      를 알고 싶을 때 사용됩니다.
-- 이전 채널 찾기
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;
-- 다음 채널 찾기
SELECT usc.*, st.ts,
	LAG(channel, 1) OVER (PARTITION BY userId ORDER BY ts DESC) next_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 구조 지원)

  • 예제) JOSN_EXTRACT_PATH_TEXT

    • SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":"1"}, "f4":{"f5":"99", "f6":"star"}}', 'f4, 'f6');
      => star가 선택됨.
    • SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":"1"}, "f4":{"f5":"99", "f6":"star"}}', 'f4);
      => {"f5":"99", "f6":"star"}가 선택됨.
profile
이재호
천천히, 그리고 꾸준히.
이전 포스트

[데이터 엔지니어링 데브코스 2기] TIL-6주차-파트05 데이터 웨어하우스와 SQL과 데이터분석(4)

다음 포스트

[데이터 엔지니어링 데브코스 2기] TIL-7주차-파트02 AWS 클라우드(1)

0개의 댓글