[바짝스터디 1주차 과제] ARRAY,STRUCT,PIVOT,FUNNEL

1. ARRAY, STRUCT

• 중요 문법

CROSS JOIN UNNEST ( )

• 연습문제 1 ) ARRAY 데이터의 기본 추출

   

   

  SELECT 
        title,
        genre
  FROM `advanced.array_exercises`
  CROSS JOIN UNNEST(genres) AS genre

   

• 연습문제 2 ) STRUCT 데이터의 기본 추출

   

   

  SELECT 
        title,
        actor
  FROM `advanced.array_exercises`
  CROSS JOIN UNNEST(actors) AS actor
  
  --위와 같이 추출할시 actor 컬럼과 character 컬럼이명이 명확하게 나오지 않음으로
  
  SELECT 
        title,
        actor.actor,
        actor.character
  FROM `advanced.array_exercises`
  CROSS JOIN UNNEST(actors) AS actor
  
  --위와 같이 명확한 명칭을 적어주면 데이터의 컬럼을 확인하기 좋다 

   

• 연습문제 3 ) CROSS JOIN 2번 사용

   

   

  SELECT 
        title,
        actor.actor,
        actor.character,
        genre
  FROM `advanced.array_exercises`
  CROSS JOIN UNNEST(actors) AS actor
  CROSS JOIN UNNEST(genres) AS genre
  
  -- OR
  
  SELECT 
        title,
        actor.actor,
        actor.character,
        genre
  FROM `advanced.array_exercises`,
  UNNEST(actors) AS actor,
  UNNEST(genres) AS genre

   

• 연습문제 4 ) 로그 데이터 풀어보기

   

   

  SELECT 
        event_date,
        event_timestamp,
        event_name,
        event_param.key AS key,
        event_param.value.string_value AS string_value,
        event_param.value.int_value AS int_value,
        user_id,
        user_pseudo_id,
        platform
  FROM `advanced.app_logs`
  CROSS JOIN UNNEST (event_params) AS event_param

2. PIVOT

• 중요 문법

IF(조건 = , TRUE , FALSE )
컬럼명을 숫자로 설정하고 싶을시 ` 을 사용하여 감싸주어야한다

• 연습 문제 1 ) PIVOT 기본

   
  

  SELECT
    order_date,
    SUM(IF(user_id = 1, total_amount, 0)) AS user_id_1,
    SUM(IF(user_id = 2, total_amount, 0)) AS user_id_2,
    SUM(IF(user_id = 3, total_amount, 0)) AS user_id_3
  FROM(
    SELECT 
          order_date,
          user_id,
          SUM(amount) AS total_amount
    FROM `advanced.orders`
    GROUP BY order_date , user_id
  )
  GROUP BY order_date
  ORDER BY order_date

   

• 연습문제 2 ) PIVOT 기본 2
  

  SELECT 
        user_id,
        SUM(IF(order_date = '2023-05-01', amount, 0)) AS `2023-05-01`,
        SUM(IF(order_date = '2023-05-02', amount, 0)) AS `2023-05-02`,
        SUM(IF(order_date = '2023-05-03', amount, 0)) AS `2023-05-03`,
        SUM(IF(order_date = '2023-05-04', amount, 0)) AS `2023-05-04`,
        SUM(IF(order_date = '2023-05-05', amount, 0)) AS `2023-05-05`
  FROM `advanced.orders`
  GROUP BY user_id
  ORDER BY user_id

   

•  연습문제 3) TRUE 값의 변화
  

  SELECT 
        user_id,
        MAX(IF(order_date = '2023-05-01', 1, 0)) AS `2023-05-01`,
        MAX(IF(order_date = '2023-05-02', 1, 0)) AS `2023-05-02`,
        MAX(IF(order_date = '2023-05-03', 1, 0)) AS `2023-05-03`,
        MAX(IF(order_date = '2023-05-04', 1, 0)) AS `2023-05-04`,
        MAX(IF(order_date = '2023-05-05', 1, 0)) AS `2023-05-05`
  FROM `advanced.orders`
  GROUP BY user_id
  ORDER BY user_id

   

• 연습문제 4 ) 로그 데이터를 이용한 컬럼 정리
  

  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`
  CROSS JOIN UNNEST (event_params) AS param
  GROUP BY ALL

  GROUP BY ALL에 대한 이해,

  
  UNNEST 이후 컬럼을 어떤식으로 정리할것인가

3. 퍼널분석

• 퍼널분석 쿼리

   

  WITH logs 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`
    CROSS JOIN UNNEST (event_params) AS param
    WHERE event_date BETWEEN '2022-12-01' AND '2022-12-31'
    GROUP BY ALL
  ), filter_logs AS (
    SELECT
      * EXCEPT(event_name, firebase_screen, event_timestamp),
      CONCAT(event_name, '-', firebase_screen) AS event_name_screen,
      DATETIME(TIMESTAMP_MICROS(event_timestamp)) AS event_time
    FROM logs
    WHERE event_name IN ('screen_view', 'click_payment')
  ) funnel AS (
  
  SELECT
    event_date,
    event_name_screen,
    CASE
      WHEN event_name_screen = 'screen_view-welcome' THEN 1
      WHEN event_name_screen = 'screen_view-home' THEN 2
      WHEN event_name_screen = 'screen_view-food_category' THEN 3
      WHEN event_name_screen = 'screen_view-restaurant' THEN 4
      WHEN event_name_screen = 'screen_view-cart' THEN 5
      WHEN event_name_screen = 'click_payment-cart' THEN 6
    ELSE NULL 
    END AS flow,
    COUNT(DISTINCT user_pseudo_id) AS cnt
  FROM filter_logs
  GROUP BY ALL
  HAVING flow IS NOT NULL
  ORDER BY 1,3
  )

• PIVOT

SELECT
  event_date,
  MAX(IF(flow = 1, cnt, 0)) AS screen_view_welcome,
  MAX(IF(flow = 2, cnt, 0)) AS screen_view_home,
  MAX(IF(flow = 3, cnt, 0)) AS screen_food_category,
  MAX(IF(flow = 4, cnt, 0)) AS screen_restaurant,
  MAX(IF(flow = 5, cnt, 0)) AS screen_cart,
  MAX(IF(flow = 6, cnt, 0)) AS click_payment_cart,
FROM funnel
GROUP BY event_date
ORDER BY 1