인프런 커뮤니티 질문&답변

안녕하세요 카일님

강의 마지막에 주신 연습문제 해보았습니다.

1. PIVOT에 어떤 연산자를 사용할 것인가?

   1. MAX로 할 경우 -> 별도의 연산된 결과값이 있는 base`가 필요할 것 같음. 이 경우 강의 중 작성된 cnt를 참조하여 작성할 수 있을 것 같으나 3중 쿼리라는게 마음에 걸림.

   2. SUM으로 할 경우 -> 강의 중 작성한 base 만으로 충분할 것 같음. 2중 쿼리로 충분해보임.

따라서 B안으로 작성해보았습니다.

WITH base AS (
  SELECT
    event_date,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    platform,
    -- event_param,
    MAX(IF(event_param.key = 'firebase_screen',event_param.value.string_value, NULL)) AS firebase_screen,
    -- MAX(IF(event_param.key = 'food_id',event_param.value.int_value, NULL)) AS food_id,
    MAX(IF(event_param.key = 'session_id',event_param.value.string_value, NULL)) AS session_id,
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
  WHERE 
    -- event_date = '2022-08-01' -> 적은 데이터로 쿼리를 작성하기 위해 입력한 임시 조건
    event_date BETWEEN '2022-08-01' AND '2022-08-18'
  GROUP BY ALL
  -- LIMIT 100 -> 기초 구문 작성 완료 시 삭제 필요!
), filter_event_and_concat_event_and_screen AS (
-- name, screen concatate (필요한 이벤트만 WHERE 조건 걸어 사용)
  SELECT
    * EXCEPT(event_name,firebase_screen, event_timestamp),
    CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen,
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM base
  WHERE
    event_name IN('screen_view', 'click_payment')
)
# PIVOT 연습문제
SELECT
  event_date,
  SUM(IF(event_name_with_screen = 'screen_view-welcome',1,0)) AS `screen_view-welcome`,
  SUM(IF(event_name_with_screen = 'screen_view-home',1,0)) AS `screen_view-home`,
  SUM(IF(event_name_with_screen = 'screen_view-food_category',1,0)) AS `screen_view-food_category`,
  SUM(IF(event_name_with_screen = 'screen_view-restaurant',1,0)) AS `screen_view-restaurant`,
  SUM(IF(event_name_with_screen = 'screen_view-cart',1,0)) AS `screen_view-cart`,
  SUM(IF(event_name_with_screen = 'click_payment=cart',1,0)) AS `click_payment-cart`,
FROM filter_event_and_concat_event_and_screen
GROUP BY ALL
ORDER BY event_date ASC