[데브코스] TIL 25일차

---

1. 숙제 리뷰

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

• 방법 1) CTE를 빌딩 블록으로 사용하는 방법

WITH first AS (
  SELECT userId, ts, channel, ROW_NUMBER() OVER(PARTITION BY userId ORDER BY ts ASC) 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; -- WHERE first.seq = 1 AND last.seq = 1 해도 되나, 일반적으로 FROM 테이블에서 사용하는 것을 WHERE에 쓰는 것이 일반적

 

• 방법 2) 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;

 

• 방법 3) GROUP BY 방식. 약간의 편법

SELECT userid,
  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 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
  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 First_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개 찾기

• 방법 1) GROUP BY 방식

-- 1. INNER JOIN. 이 테이블에선 누락이 없으나 sessionid 중에 user_session channel에 없는게 있으면 누락될수도.
SELECT userID, SUM(amount) grossRevenue
FROM raw_data.session_transaction st
JOIN raw_data.user_session_channel usc ON st.sessionId = usc.sessionId
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;


-- 2. LEFT JOIN
SELECT userID, SUM(amount) grossRevenue
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;

 

• 방법 2) SUM OVER - 동일한 userid가 여러번 반복되어 나옴. 그룹으로 묶은게 아닌 반복된 것을 더한 것. 중복 레코드가 존재하게 되어 DISTINCT 사용 필수. 방법 1)이 더 선호되는 방법

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) raw_data.nps 테이블을 바탕으로 월별 NPS 계산

• 방법 1) 내가 풀었던 방법

SELECT LEFT(created_at,7) "month",
    COUNT(CASE WHEN score <= 6 THEN 1 END) detractor,
    COUNT(CASE WHEN score BETWEEN 7 AND 8 THEN 1 END) passive,
    COUNT(CASE WHEN score >= 9 THEN 1 END) promotor,
    COUNT(1) total_count,
    ROUND(promotor*100.0/NULLIF(total,0),2) - ROUND(detractor*100.0/NULLIF(total,0),2) NPS
FROM raw_data.nps
GROUP BY 1
ORDER BY 1;

 

• 방법 2) 해설

SELECT month,
    ROUND((promoters-detractors)::float/total_count*100,2) AS overall_nps
FROM (
    SELECT LEFT(created,7) AS month,
        COUNT(CASE WHEN score <= 6 THEN 1 END) detractor,
        COUNT(CASE WHEN score BETWEEN 7 AND 8 THEN 1 END) passive,
        COUNT(CASE WHEN score >= 9 THEN 1 END) promotor,
        COUNT(1) total_count
    FROM raw_data.nps
    GROUP BY 1
    ORDER BY 1
);

 

• 방법 3) 해설

SELECT LEFT(created,7) AS month,
    ROUND(SUM(CASE
        WHEN score >= 9 THEN 1
        WHEN score <= 6 THEN -1 END)::float*100/COUNT(1),2)
    FROM raw_data.nps
    GROUP BY 1
    ORDER BY 1;

 

 

 

 

2. TRANSACTION

• Atomic하게 실행되어야 하는 SQL들을 묶어, 하나의 작업처럼 처리하는 방법
  • Atomic? 은행 계좌 이체 예시로 설명 가능
    • 계좌이체 : 인출과 입금의 두 과정
    • 만일 인출은 성공했는데, 입금이 실패한다면?
    • 이 두 과정은 동시에 성공하던지 동시에 실패하던지 하나만 해야함 → Atomic
    • 이런 과정은 TRANSACTION으로 묶어줘야함
• DDL이나 DML 중 레코드를 수정/추가/삭제하는 것에만 의미가 있음
  • SELECT(조회)에는 사용할 이유가 없음

 

• BEGIN, END 혹은 BEGIN, COMMIT 사이에 해당 SQL들을 사용
  • END와 COMMIT은 동일함
• ROLLBACK
  • BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK 실행
• COMMIT mode에 따라 달라짐 
  • autocommit = True
    • 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰임. 이를 커밋(commit)된다고 함
    • 만일 특정 작업을 transaction으로 묶고 싶다면 BEGIN과 END(COMMIT)/ROLLBACK으로 처리함
  • autocommit = False
    • 모든 레코드 수정/삭제/추가 작업이 COMMIT 호출될 때까지 커밋되지 않음
  • Google Colab
    • 기본적으로 autocommit = True
    • 이를 바꾸고 싶다면, BEGIN, END 혹은 BEGIN, COMMIT을 사용(혹은 ROLLBACK)
      
  • psycopg2
    • autocommit이라는 파라미터로 조절 가능
    • autocommit = True는 기본적으로 PostgreSQL의 커밋 모드와 동일
    • autocommit = False는 커넥션 객체의 .commit()과 .rollvack()함수로 transaction 조절 가능
    • 무엇을 사용할지는 개인 취향
• DELETE FROM vs. TRUNCATE
  • DELETE FROM table_name(not DELETE * FROM)
    • 테이블에서 모든 레코드를 삭제함
    • vs. DROP TABLE table_name
    • WHERE 사용해 특정 레코드만 삭제 가능함
      • DELETE FROM raw_data.user_session_channel WHERE channel = 'Google'
  • TRUNCATE
    • 테이블에서 모든 레코드를 삭제
    • DELETE FROM에 비해 속도가 빠름
    • 전체 테이블 내용 삭제 시에는 여러모로 유리하나,
    • WHERE, TRANSACTION 지원하지 않음(ROLLBACK 불가!!)

 

 

3. 기타 고급 문법 소개

• UNION, EXCEPT, INTERSECT
  • UNION(합집합)
    • 여러 개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐줌
    • UNION(중복 x) vs. UNION ALL(중복 o)
  • EXCEPT(차집합)
    • 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것이 가능함
  • INTERSECT(교집합)
    • 여러 개의 SELECT문에서 같은 레코드들만 찾아줌

 

• COALESCE, NULLIF
  • COALESCE(exp1, exp2, ...)
    • exp1부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고, 모두 NULL이면 NULL 리턴
    • NULL 값을 다른 값으로 바꾸고 싶을 때 사용함
  • NULLIF(exp1, exp2)
    • exp1과 exp2의 값이 같으면 NULL 리턴

 

• LISTAGG
  • GROUP BY에 사용되는 AGGREGATE 함수 중 하나
  • 사용자 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;

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
LI

 

 

• WINDOW
  • FUNCTION(expression) OVER(PARTITION BY exp ORDER BY exp)
  • ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG
  • AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE

• 예시1) WINDOW - LAG
  • 어떤 사용자 세션에서 시간순으로 봤을 때, 이전 세션의 채널이 무엇인지 알고 싶다면?
  • 혹은 다음 세션의 채널이 무엇인지 알고 싶다면?

-- 이전 채널 찾기. LAG(..., 1)을 하면 지금 채널 1 앞에 있는 값을 가져옴
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

-- 다음 채널 찾기. ORDER BY를 내림차순으로 바꾸면 됨
SELECT usc.*, st.ts,
    LAG(channel, 1) OVER(PARTITION BY userId ORDER BY ts DESC) post_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 구조 지원)

-- 결과가 star --
SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3" : "1"}, "f4":{"f5" : "99","f6" : "star"}}', 'f4', 'f6');


-- 결과가 {"f5" : "99","f6" : "star"} --
SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3" : "1"}, "f4":{"f5" : "99","f6" : "star"}}', 'f4');

 

 

 

 

4. 다시 한번 당부하신 배움의 패턴

• 배움에 있어서 누구나 속도가 다르다는 것, 그리고 우상향 그래프가 아닌 정체기나 하락기 또한 존재한다는 것을 처음에도 그리고 중간 중간에도 강조해 주신다. 이런 것들을 즐기는 것은 정말정말 어렵지만, 인생의 선배로서 정말 좋은 조언이자 응원이 아닌가싶다. 
• 내 속도에 절망하지 않고, 자책하지 않고 꾸준히 열심을 다해보자