Redshift - 복습겸 숙제

 

 

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

 

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

 

3. raw_data.nps 테이블을 바탕으로 월별 NPS 계산

 

 

 

 

 

 

 

 

 

 

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

 

 

 

먼저 사용자 1명을 기준으로 첫 채널과 마지막 채널을 조회하는 방법입니다.

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

 

251번 사용자 단 한명의 첫채널이 맨위 마지막 채널이 맨 마지막으로 오는 쿼리입니다.

WHERE 절 조건마다 모든 사용자별 ID를 입력한 후 결과에서 맨위와 맨아래를 체크하는 방식으로는 힘들 것 같습니다.

 

이 때 등장하는 것이 ROW_NUMBER입니다.

 

 

 

 

ROW_NUMBER

 

ROW_NUMBER OVER 이하 옵션에서 partition by 뒤의 컬럼을 그룹별로 묶고 order by 뒤의 컬럼을 기준으로 sorting하여 1부터 서수를 붙입니다.

 

 

예를 들어 아래와 같이 ROW_NUMBER OVER 옵션을 설정하면

ROW_NUMBER OVER (partition by userid order by ts) seq

 

 

 

위와 같이 partition by 뒤의 필드 userid를 기준으로 그룹핑하고 order by 뒤의 필드 ts를 기준으로 sorting한 테이블이 조회됩니다.

 

 

 

그동안 배운 JOIN과 ROW_NUMBER 등을 활용해 사용자별 처음과 마지막 채널을 조회하는 쿼리들을 살펴보겠습니다.

 

 

 

 

 

1) 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;

 

 

CTE 방식으로 first 테이블과 last 테이블을 만듭니다.

first 테이블에서 seq가 1인 채널은 가장 처음 만든(가장 오래 된) 채널입니다.

last 테이블에서 seq가 1인 채널은 가장 마지막으로 만든(DESC) 채널입니다.

 

그렇게 만들어진 두 테이블들을 INNER JOIN하되 WHERE절로 seq가 1인 채널을 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;

 

CTE(WITH) 방식을 사용하지 않고 바로 first와 last로 별칭을 준 후 INNER JOIN한 방식입니다. 방식의 차이일뿐 거의 동일한 방식입니다.

 

 

 

 

 

 

 

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;

 

ROW_NUMBER를 이용해 rn1과 rn2라는 컬럼명의 테이블을 조회합니다. rn1은 사용자별 채널 생성일을 오름차순, rn2는 내림차순 정렬한 테이블 입니다. 이 테이블을 바탕으로 userid를 기준으로 GROUP BY한 뒤 MAX와 CASE WHEN을 이용해 각 사용자별 첫번째 채널 생성일 first_touch, 마지막 채널 생성일 last_touch를 조회했습니다.

 

GROUP BY를 사용하면 Aggregate 함수를 사용하기 때문에 MAX를 사용해 조금 어색할 수 있습니다만 첫 채널 생성일과 마지막 채널 생성일을 GROUP BY를 사용해 찾기 위함이라고 이해하시면 됩니다.

 

 

 

 

 

 

 

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 Last_Channel
FROM raw_data.user_session_channel A
LEFT JOIN raw_data.session_timestamp B ON A.sessionid = B.sessionid
ORDER BY 1;

 

 

권장되지는 않는 방법입니다.

userid가 반복되는 만큼 같은 레코드가 중복되어서 나오기 때문입니다. DISTINCT를 사용한 이유입니다. 퍼포먼스 측면에서도 불리하므로 이런 방식이 있다는 점만 알아주세요.

 

FIRST_VALUE와 LAST_VALUE를 이용해 첫, 마지막 레코드에 접근할 수 있습니다. 함수가 받는 인수 등에 대해서는 Redshift 공식 설명을 동봉합니다.

 

https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_WF_first_value.html

FIRST_VALUE 창 함수 - Amazon Redshift

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

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;

 

GROUP BY를 사용한 방식입니다.

 

session_transaction에는 존재하는 userID이지만 user_session_channel에는 존재하지 않는 userID는 없기 때문에 LEFT JOIN을 하든 INNER JOIN을 하든 차이는 없습니다.

 

LIMIT을 사용할 때는 동점자임에도 동점자들 중 일부가 잘려나갈 수도 있습니다. 이 점 염두에 두세요.

별도의 방법으로 보정할 수 있습니다만 이 곳에서는 설명하지 않겠습니다.

 

 

 

 

 

SUM OVER 방식

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;

 

 

SUM OVER 방식입니다.

 

SUM(amount)이되 OVER를 이용해 userID별로 그룹핑했습니다. 문제는 첫번째 GROUP BY 방식은 userid당 sum(amount) 레코드가 하나씩만 나오지만 GROUP BY대신 OVER PARTITION BY를 사용한 두번째 방식은 userid가 FROM JOIN에서 나온 숫자만큼 중복되어 나온다는 점입니다. DISTINCT가 붙은 이유도 이 때문입니다.

 

첫번째 GROUP BY 방식이 비교적 깔끔하므로 더 선호됩니다. SUM OVER는 퍼포먼스 측면에서도 좋지 않습니다. 이런 방식이 있구나 정도로만 이해해주세요.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. raw_data.nps 테이블을 바탕으로 월별 NPS 계산

 

 

NPS 계산 방식

고객들이 0 (의향 없음) 에서 10 (의향 아주 높음)
detractor (비추천자) : 0 에서 6
passive (소극자) : 7이나 8점
promoter (홍보자) : 9나 10점
NPS = promoter 퍼센트 - detractor 퍼센트

 

 

 

 

SELECT 
	month,
	ROUND((promoters-detractors)::float/total_count*100, 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
);

 

total_count가 NULL인 경우까지 고려해 NULLIF를 사용하면 더 바람직합니다.

 

 

 

 

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;