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

안녕하세요 카일스쿨님:)

강의 마지막에 선택과제(?)로 주신 집계데이터 pivot쿼리 작성해봤습니다.

쿼리를 작성하면서, 지금은 피봇할 컬럼이 많지 않아서 하나하나 피봇해주는 것으로 진행했는데 이게 최선일지가 의문이 들어서요,
피봇하는 컬럼 조건이 다를 뿐 과정은 동일해서 공통으로 적용할 수 있는 방법이 있을 것 같은데 제가 잘못 이해하고 있는 것인지, 아니면 피봇을 항상 이런식으로 진행해야 하는 것인지도 같이 질문드립니다.

감사합니다:)

with base as (
  select event_date
    ,event_timestamp
    ,event_name
    ,user_id
    ,user_pseudo_id
    ,max(if(param.key = 'firebase_screen',param.value.string_value,null)) as `firebase_screen`
    ,max(if(param.key = 'food_id',param.value.int_value,null)) as `food_id`
    ,max(if(param.key = 'session_id',param.value.string_value,null)) as `session_id` 
  from advanced.app_logs , unnest(event_params) as param
  where 1=1
  and event_name in ('screen_view','click_payment')
  and event_date >= '2022-08-01' 
  and event_date <= '2022-08-18'
  group by all 
  order by 1
)
, before_pivot as (
  select event_date
    ,concat(event_name,'-',firebase_screen) as `event_name_with_screen`
    ,case when event_name = 'screen_view' and firebase_screen = 'welcome' then 1 
        when event_name = 'screen_view' and firebase_screen = 'home' then 2
        when event_name = 'screen_view' and firebase_screen = 'food_category' then 3 
        when event_name = 'screen_view' and firebase_screen = 'restaurant' then 4 
        when event_name = 'screen_view' and firebase_screen = 'cart' then 5 
        when event_name = 'click_payment' and firebase_screen = 'cart' then 6
        end as `step_number`
    ,count(*) as cnt
  from base
  where 1=1
  group by 1,2,3
  having step_number is not null
)

select event_date
  ,max(if (event_name_with_screen = 'screen_view-welcome',cnt,null)) as `screen_view-welcome`
  ,max(if (event_name_with_screen = 'screen_view-home',cnt,null)) as `screen_view-home`
  ,max(if (event_name_with_screen = 'screen_view-food_category',cnt,null)) as `screen_view-food_category`
  ,max(if (event_name_with_screen = 'screen_view-restaurant',cnt,null)) as `screen_view-restaurant`
  ,max(if (event_name_with_screen = 'screen_view-cart',cnt,null)) as `screen_view-cart`
  ,max(if (event_name_with_screen = 'click_payment-cart',cnt,null)) as `click_payment-cart`
from before_pivot
group by all
order by 1