터칭 데이터
Redshift - 복습겸 숙제 본문


채널별, 월별 매출액 테이블을 CTAS로 만들기
1. session_timestamp,user_session_channel,session_transaction 3개의 테이블들을 사용합니다.
2. 아래와 같은 필드로 구성합니다.
month
channel
uniqueUsers (총방문 사용자)
paidUsers (구매 사용자: refund한 경우도 판매로 고려)
conversionRate (구매사용자 / 총방문 사용자)
grossRevenue (refund 포함)
netRevenue (refund 제외)
1. 혹시 OUT JOIN이 필요한지 테이블 점검
select distinct sessionid from raw_data.session_timestamp
minus
select distinct sessionid from raw_data.user_session_channel
;

session_timestamp 테이블과 user_session_channel에 존재하는 sessionid들 중에서 한 테이블에만 존재하는 sessionid가 있는지 확인합니다.
만일 한 테이블에만 존재하는 sessionid가 존재한다면 두 개의 테이블안에 매칭이 되지 않는 레코드가 존재한다는 뜻입니다. 큰 문제가 아닐 수도 있지만 점검차원에서 실행해보았습니다.
적어도 첫번째 테이블 session_timestamp의 sessionid 필드 레코드들은 적어도 두번째 테이블 user_session_channel에도 모두 존재한다는 뜻입니다.
두 테이블의 순서를 바꿔 마이너스를 진행해도
select distinct sessionid from raw_data.user_session_channel
minus
select distinct sessionid from raw_data.session_timestamp
;

결과는 똑같습니다.
user_session_channel 테이블의 sessionid들 역시 session_timestamp에도 모두 존재한다는 뜻입니다.
순서를 바꿔 2차례 minus를 진행했음에도 모두 리턴되는 결과가 없으므로 두 테이블은 완벽하게 매칭된다는 뜻입니다.
2. 월별, 채널별 매출액 조회
한번에 쿼리를 작성하지 말고 쉬운 것부터 단계적으로 풀어 봅시다.
채널별 월 매출액 테이블 만들기 - 먼저 유니크한 사용자 수부터 세보자
SELECT
LEFT(ts, 7) "month",
usc.channel,
COUNT(DISTINCT userid) uniqueUsers
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
GROUP BY 1, 2
ORDER BY 1, 2;
매출액이 필요하므로 session_transaction 테이블을 추가로 더 JOIN한다.
우리는 위에서 user_session_channel테이블과 session_timestamp테이블을 이미 INNER JOIN해둔 상태입니다. 이 곳에 매출액 정보를 담은 session_transaction 테이블을 추가로 JOIN해줘야 합니다.
그런데 어떤 JOIN을 해줘야 할까요?
INNER JOIN을 해버리면 session_transaction에는 모든 sessionid가 존재하지 않기 때문에(모든 사용자가 방문할 때마다 물건을 구매하지는 않을 것입니다.) 3개의 테이블의 교집합만 JOIN됩니다. 즉 매출이 있는 사용자(User)만 조회되므로 당장 uniqueUsers 필드부터 맞지 않게 됩니다.
user_session_channel테이블과 session_timestamp테이블이 INNER JOIN된 테이블을 왼쪽에 두고 session_transaction테이블을 LEFT JOIN하는 것이 옳은 방법입니다.
SELECT
LEFT(ts, 7) "month",
usc.channel,
COUNT(DISTINCT userid) uniqueUsers
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid -- 이 한줄을 추가
GROUP BY 1, 2
ORDER BY 1, 2;
paidUsers, conversionRate, grossRevenue, netRevenue 4개 필드 추가하기
paidUsers(물건을 구매한 사용자의 수) 필드 추가
SELECT
LEFT(ts, 7) "month",
usc.channel,
COUNT(DISTINCT userid) uniqueUsers,
COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers, -- 물건을 구매한 경력이 있는 User만
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid
GROUP BY 1, 2
ORDER BY 1, 2;
amount가 0이상이면 물건을 구매한 사용자(user)라는 뜻입니다. 단 동일한 사용자가 물건을 여러번 구매해도 1명으로 카운트되도록 DISTINCT를 붙였습니다. CASE WHEN에 별도의 ELSE를 붙이지 않았으므로 amout가 0보다 크지 않은 사용자들은 NULL이되어 알아서 카운트되지 않는다는 점 기억해주세요.
이제 conversionRate(구매를 한 사용자/총사용자)를 추가하자
○ 첫 번째 시도: 부적합
■ paidUsers/uniqueUsers AS conversionRate
INT를 INT로 나눌 때 분모가 더 크면 0이 나온다고 했습니다.
○ 두 번째 시도: 부적합
■ paidUsers::float/uniqueUsers AS conversionRate
분모나 분자 둘중 한곳에 float 타입을 지정해줘 계산은 가능하지만 우리는 0.0~1.0 사이의 소수보다는 확률이 보고 싶습니다.
○ 세 번째 시도: 희박한 확률로 에러
■ ROUND(paidUsers*100.0/uniqueUsers, 2) AS conversionRate
float을 굳이 지정해주지 않아도 100.0으로 float형태의 숫자를 곱했으므로 float형태의 확률이 나올 것입니다. float이 int보다 더 강한 타입이므로 float값으로 자동으로 타입캐스팅됩니다. 그리고 ROUND로 소수 둘째 자리에서 반올림이 됩니다.
그런데 문제는 uniqueUsers가 0이라면 divided zero 에러가 발생합니다. 모든 사용자인 uniqueUsers가 0일 확률은 사실상 없다고 봐도 좋지만 그래도 만에 하나를 위해 예외처리가 필요합니다.
○ 네 번째 시도:
■ ROUND(paidUsers*100.0/NULLIF(uniqueUsers, 0), 2) AS conversionRate
NULLIF(uniqueUsers, 0)의 뜻은 uniqueUsers가 0이면 NULL을 리턴하라는 뜻입니다. 반대로 uniqueUsers가 0이 아니라면 첫번째 인자인 uniqueUsers를 리턴하라는 뜻입니다. uniqueUsers가 0이라 NULLIF가 NULL을 리턴하게 되면 NULL은 사칙연산에서 모든 것을 NULL로 만드므로 총 사용자 uniqueUsers가 0이면 구매 사용자 비율도 NULL로 의도한대로 정상 출력되게 됩니다.
마무리로 grossRevenue와 netRevenue 필드도 추가
SELECT
LEFT(ts, 7) "month", -- "year month"
channel,
COUNT(DISTINCT usc.userid) uniqueUsers,
COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers,
ROUND(paidUsers::float*100/NULLIF(uniqueUsers, 0),2) conversionRate,
SUM(amount) grossRevenue,
SUM(CASE WHEN refunded is False THEN amount END) netRevenue
FROM raw_data.user_session_channel usc
LEFT JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid
GROUP BY 1, 2
ORDER BY 1, 2;
SELECT LEFT(ts, 7) "month", -- "year month"
channel,
COUNT(DISTINCT usc.userid) uniqueUsers,
COUNT(DISTINCT CASE WHEN amount > 0 THEN usc.userid END) paidUsers,
ROUND(paidUsers*100.0/NULLIF(uniqueUsers, 0),2) conversionRate,
SUM(amount) grossRevenue,
SUM(CASE WHEN refunded is False THEN amount END) netRevenue
FROM raw_data.user_session_channel usc
LEFT JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
LEFT JOIN raw_data.session_transaction st ON st.sessionid = usc.sessionid
GROUP BY 1, 2
ORDER BY 1, 2;

3. 위의 결과로 CTAS로 테이블 만들기
--혹시 기존에 생성되어 있으면 삭제
DROP TABLE IF EXISTS adhoc.keeyong_monthly_channel_summary;
--Summary Table 생성
CREATE TABLE adhoc.keeyong_monthly_channel_summary
AS
SELECT TO_CHAR(ts, 'YYYY-MM') year_month,
usc.channel,
COUNT(DISTINCT usc.userid) unique_users,
COUNT(DISTINCT CASE WHEN amount>0 THEN userid END) paid_users,
ROUND(paid_users*100./NULLIF(unique_users,0),2) conversion_rate,
SUM(amount) gross_revenue,
SUM(CASE WHEN refunded is False THEN amount
ELSE 0 END) net_revenue
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
LEFT JOIN raw_data.session_transaction str ON usc.sessionid = str.sessionid
GROUP BY 1, 2;
--정상적으로 생성되었는지 확인
SELECT * FROM adhoc.keeyong_monthly_channel_summary;
ORDER BY가 빠진 이유는 CTAS를 할 때 ORDER BY를 넣어봤자 관계형 데이터베이스들이 자의적으로 판단해 데이터들을 넣기 때문에 전혀 소용이 없기 때문입니다.


'SQL' 카테고리의 다른 글
Redshift - 복습겸 숙제 (0) | 2023.11.16 |
---|---|
Redshift - COALESCE, NULLIF, 특수한 필드명 사용방법 (0) | 2023.11.16 |
Redshift - BOOLEAN과 NULL 팁 (0) | 2023.11.15 |
Redshift - JOIN (0) | 2023.11.15 |
Redshift - CTAS & CTE, 테이블(데이터) 품질 확인 방법 (0) | 2023.11.15 |