관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보

• 사용자 ID: 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID
• 세션 ID: 세션마다 부여되는 ID
  • 세션: 사용자의 방문을 논리적인 단위로 나눈 것
    • 사용자가 외부 링크(보통 광고)를 타고 오거나 직접 방문해서 올 경우 세션을생성
    • 즉 하나의 사용자는 여러 개의 세션을 가질 수 있음
    • 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록 해둠
      • 마케팅 관련 기여도 분석을 위함
    • 또한 세션이 생긴 시간도 기록
• 이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능
• 마케팅 관련, 사용자 트래픽 관련

raw data DB 에 존재하는 3개의 테이블

• 3 table

  

• table 목록

  

  

  

• 고려할 점

  • 실제 환경에서는 매칭이 안되는 session이 있을 수 있다는 점을 명심! JOIN이 아닌 LEFT JOIN이 필요

  • 중복 레코드가 존재할 수 있음도 항상 명심하고 검사해볼 것

    GRANT SELECT ON TABLE raw_data.session_transaction to group analytics_users;

2번 문제

문제 : 사용자별로 처음과 마지막 채널 알아내기

• 사용자 251번의 시간순으로 봤을 때 첫 번째 채널과 마지막 채널은 무엇인가?

  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
  LIMIT 30;
  

• 출력 포맷

  • 사용자ID, 첫번째 채널, 마지막 채널
  • ROW_NUMBER vs. FIRST_VALUE/LAST_VALUE
  • ROW_NUMBER 이용해서 해보자

    • ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY field2) nn

    • ROW_NUMBER를 쓰면 2를 구현 가능

      ROW_NUMBER() OVER (partition by userid order by ts ASC) seq

• 내 풀이

  %%sql
  
  SELECT 
   
  userid, 
  FIRST_VALUE(channel) OVER (PARTITION BY 3) AS FIRST_CHANNEL, 
  LAST_VALUE(channel) OVER (PARTITION BY 3) AS LAST_CHANNEL
  
  FROM (
    SELECT userid, channel, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ts) AS rank
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
    WHERE userid = 251 
    ORDER BY 3
  )
  
  LIMIT 1;
  

• 모범 풀이 ROW_NUMBER을 활용, 임시 테이블(with subquery)을 하나 생성하여 임시테이블을 통해 원하는 자료를 조회

  -- cte 임시 테이블 생성
  WITH cte AS (
   SELECT userid, channel, (ROW_NUMBER() OVER (PARTITION BY usc.userid ORDER BY st.ts asc))
  AS arn, (ROW_NUMBER() OVER (PARTITION BY usc.userid ORDER BY st.ts desc)) AS drn
   FROM raw_data.user_session_channel usc
   JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
  )
  
  -- 임시 테이블을 self join을 하여 조회
  -- cte1은 first channel
  -- cte2는 
  SELECT cte1.userid, cte1.channel AS first_touch, cte2.channel AS last_touch
  FROM cte cte1 
  JOIN cte cte2 ON cte1.userid = cte2.userid
  WHERE cte1.arn = 1 and cte2.drn = 1
  ORDER BY 1;