# WITH를 활용해서 임시 테이블 만들기
# 임시 테이블 해서 ROW_NUMBER를 부르는데, 정렬을 다르게 한 후
# 채널을 구하기 위해서 SELF 조인형태처럼 진행
first_last_channel_df = spark.sql("""
WITH RECORD AS (
SELECT /*사용자의 유입에 따른, 채널 순서 매기는 쿼리*/
userid,
channel,
ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ts ASC) AS seq_first,
ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ts DESC) AS seq_last
FROM user_session_channel u
LEFT JOIN session_timestamp t
ON u.sessionid = t.sessionid
)
SELECT /*유저의 첫번째 유입채널, 마지막 유입 채널 구하기*/
f.userid,
f.channel first_channel,
l.channel last_channel
FROM RECORD f
INNER JOIN RECORD l ON f.userid = l.userid
WHERE f.seq_first = 1 and l.seq_last = 1
ORDER BY userid
""")
ROW_NUMBER를 활용할 때는 WITH를 활용한 테이블 조인을 활용한다
첫번째 채널을 위한 ROW_NUMBER, 마지막 채널을 위한 ROW_NUMBER를 구하고
seq_first가 1일 때, seq_last가 1일 때의 channel을 구한다.
다소 쿼리가 복잡해지는 측면이 있음
(5) FIRST_VALUE, LAST_VALUE를 사용해서 처음 채널과 마지막 채널 알아내기
# BOUND 한 것 중에 PARTITION 하고 같은 PARTITION 안에서 오름 차순으로 세팅을 한 다음에
# 채널의 FIRST VALUE, LAST VALUE를 뽑는다
# rows between : rolling window를 어떻게 가져갈거냐, unbounded를 양쪽에 쓰면 rolling window가 아니라 전체 partition 안에서 전부 보겠다
first_last_channel_df2 = spark.sql("""
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 user_session_channel A
LEFT JOIN session_timestamp B
ON A.sessionid = B.sessionid""")
