같은연도 월, 채널을 기준으로 총 매출액, 순매출액 계산 SUM, CASE WHEN

총 방문자는 DISTINCT를 계산

앞에 써봤던 SUM, CASE WHEN
CASTING, 분모가 0이 되는 경우를 막는 테크닉 필요.

• 각기 레드쉬프트 -> 테이블로
• JOIN 선택 잘해야함.

파이스파크, 파이4j 설치.
레드쉬프트 jar 받음.
스파크세션 오브젝트 생성

3개 데이터 테이블 로딩.
sql로 작업하기 위한 테이블 이름 지정.
5개씩 레코드를 확인.
spark.table("session_timestamp").show(5)

월별 채널별 총 방문자, 매출 발생 방문자

데이터 의심하기.

• 세션 ID가 정말로 각 1개씩만 있는지 체크하는 것이 매우 중요함

spark.sql("""SELECT sessionid, COUNT(1) count
FROM user_session_channel
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1""").show()

count가 1보다 클 경우 중복이 있다는 소리임.
세션 ID가 유니크하지 않다는 걸 알 수 있음.
PK가 지켜지고 있는지 아닌지 알 수 있다.

빅데이터는 PK를 보장해주지 못함. 체크하고 보장하는건 엔지니어, 분석가의 책임.

월별 채널별 총 방문자 계산

mon_channel_rev_df = spark.sql("""
	SELECT LEFT(sti.ts, 7) year_month,
    usc.channel channel,
    COUNT(DISTINCT userid) total_visitors
    FROM user_session_channel usc
    LEFT JOIN session_timestamp sti ON usc.sessionid = sti.sessionid
    GROUP BY 1, 2
    ORDER BY 1, 2""")

연도 오름차순, 그리고 채널도 오른차순이 된걸 보임.

월별 채널별 총 방문자와 구매 방문자 계산

INNER JOIN을 str과 함.
구매 방문자 수는 조금 다르다. 순매출 때랑 비슷하게 CASE WHEN을 써서 refund = false일 경우로 나눴었음. 이번에도
COUNT(DISTINCT CASE WHEN amount is not NULL THEN userid END) paid_visitors 로

mon_channel_rev_df = spark.sql("""
SELECT LEFT(sti.ts, 7) year_month,
usc.channel channel,
COUNT(DISTINCT userid) total_visitors
COUNT(DISTINCT CASE WHEN amount is not NULL THEN userid END) paid_visitors
FROM user_session_channel usc
LEFT JOIN session_timestamp sti ON usc.sessionid = sti.sessionid
JOIN session_transaction str ON usc.sessionid = str.sessionid
GROUP BY 1, 2
ORDER BY 1, 2""")

INNER JOIN을 str과 하게 되면 구매이력이 있는 사람들로 한정이 되어버림. LEFT 조인을 해서 구매이력이 없는 사람들도 살려야 함.

LEFT JOIN 특정상 str에서 매칭이 되지 않는 경우에 amount 값이 NULL이 될 것임.
이 조건으로 구매 방문자 여부를 확인할 것임.

제대로 들어온 것을 확인할 수 있음.

월별 채널별 총 매출액 (리펀드 포함), 총 방문자, 매출발생 방문자, 전환률 계산

그냥 유일 유저의 amount의 합을 구하는
SUM(amount) grossRevenue,
환불하지 않은 실구매의 합은,
not True의 이유는 Null도 제외하고 싶기 때문임
SUM(CASE WHEN refunded is not True THEN amount END) netRevenue

두 값을 붙이니 뒤에 컬럼 2개가 붙은걸 확인. 환불하지 않은 경우가 값이 작은 걸 확인.
다음은 conversion rate 방문사용자 중에 몇명이나 구매했는지.
방문사용자가 분모, 구매사용자가 분자가 됨.

분자는,
paidUser를 그대로 가져옴

COUNT(DISTINCT CASE WHEN amount >= 0 THEN userid END))
	/ COUNT(DISTINCT userid) conversionRate

이를 소숫점 둘째자리까지로 바꿔본다. 분자에 100을 곱함

ROUND(COUNT(DISTINCT CASE WHEN amount >= 0 THEN userid END)) * 100
	/ COUNT(DISTINCT userid) conversionRate

좀 더 읽기 편한 형태로 된걸 알 수 있음.

여기선 분모가 0인 경우를 처리하지 않았음
SELECT column1 / NULLIF(column2, 0)

이렇게 NULLIF를 써서 0인경우 NULL로 날리면 에러없이 돌아갈 것임.