데.분.레 - 복습 11

사용자 전체의 특징과 경향 찾기

사용자의 속성 또는 행동과 관련된 정보를 집계해서 사용자 행동을 조사하고, 서비스를 개선할 때 실마리가 될 수 있는 리포트를 만들어보자.

mst_users 테이블

action_log 테이블

사용자의 액션 수 집계하기

WITH stats AS (
  SELECT COUNT(DISTINCT session) AS total_uu
    FROM action_log
    )

SELECT l.action
     , COUNT(DISTINCT l.session) AS action_uu
     , COUNT(1) AS action_count
     , s.total_uu
     , 100.0 * COUNT(DISTINCT l.session)/s.total_uu AS usage_rate
     , 1.0 * COUNT(1) / COUNT(DISTINCT l.session) AS count_per_user
  FROM action_log AS l
  CROSS JOIN stats AS s
  GROUP BY l.action, s.total_uu

로그인 사용자와 비로그인 사용자를 구분해서 집계하기

WITH action_log_with_status AS (
  SELECT session
       , user_id
       , action
       , CASE WHEN COALESCE(user_id, '') <> '' THEN 'login' ELSE 'guest' END
         AS login_status
    FROM action_log
  )
SELECT *
  FROM action_log_with_status

COALESCE : 괄호 안의 값 중에서 null이 아닌 값을 먼저 반환한다. 위 쿼리에서는 user_id가 null 값인 경우에는 ''이 출력되고, user_id가 null 값이 아닌 경우에는 user_id를 그대로 출력하게 되는 것이다.

WITH action_log_with_status AS (
  SELECT session
       , user_id
       , action
       , CASE WHEN COALESCE(MAX(user_id)
                            OVER(PARTITION BY session 
                                 ORDER BY stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                            , '') <> '' THEN 'member' ELSE 'none'
          END AS member_status
    FROM action_log
  )

SELECT *
  FROM action_log_with_status

단 한번이라도 로그인 이력이 있는 사람은 member로 처리하는 쿼리이다.