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

1. ARRAY, STRUCT

### 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요

# ARRAY : 같은 타입의 여러 데이터를 저장하고 싶을 때
# ARRAY를 Flatten(평면화) => UNNEST
# UNNEST를 할 때는 CROSS JOIN + UNNEST(ARRAY_COLUMN)
# UNNEST(ARRAY_COLUMN) AS 새로운 이름
# SELECT 절에서 새로운 이름으로 사용한다. 기존의 ARRAY_COLUMN은 사용하지 않는다!

-- SELECT 
--       title
--     , genre
-- FROM `advanced.array_exercises`
-- CROSS JOIN UNNEST(genres) AS genre
-- ORDER BY 1, 2   ## 같은 결과를 출력하기 위해 정렬함.
-- ;


### 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다.

# 직접 접근하려면 actors = [ STRUCT(STRING, STRING)]
# actors[SAFE_OFFSET(0)].actor
# actors[SAFE_OFFSET(0)].character

-- SELECT
--       title
--     , act.actor# AS actor
--     , act.character# AS character
-- FROM `advanced.array_exercises`
-- CROSS JOIN UNNEST(actors) AS act
-- ORDER BY 1
-- ;


### 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다.

# 데이터의 중복이 어느정도 생기는데, 그것은 어쩔 수 없는 이슈(CROSS JOIN)

-- SELECT
--       title
--       -- actors, # ARRAY<STRUCT(STRING, STRING)>
--     , act.actor# AS actor
--     , act.character# AS character
--       -- genres # ARRAY<STRING> 
--     , genre 
-- FROM `advanced.array_exercises`
-- CROSS JOIN UNNEST(actors) AS act
-- CROSS JOIN UNNEST(genres) AS genre
-- -- WHERE 1=1
-- --   ## 강의 촬영 시점 이후에 수정된 듯 두 쿼리 모두 오류없이 실행 되는 것 같아요 ! 
-- --   AND act.actor = "Chris Evans"
-- --   AND actor = "Chris Evans"
-- ORDER BY 1
-- ;

### 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요.

-- SELECT
--       user_id
--     , event_date
--     , event_name
--     , user_pseudo_id
--     , evt_prm.key AS key
--     , evt_prm.value.string_value AS string_value
--     , evt_prm.value.int_value AS int_value
-- FROM `advanced.app_logs`
-- CROSS JOIN UNNEST(event_params) AS evt_prm
-- WHERE 1=1
--   AND event_date = "2022-08-01"
-- ORDER BY 2
-- ;


### WITH 문 변경

WITH base AS (
  SELECT
        user_id
      , event_date
      , event_name
      , user_pseudo_id
      , evt_prm.key AS key
      , evt_prm.value.string_value AS string_value
      , evt_prm.value.int_value AS int_value
  FROM `advanced.app_logs`
  CROSS JOIN UNNEST(event_params) AS evt_prm
  WHERE 1=1
    AND event_date = "2022-08-01"
)

SELECT
      event_date
    , event_name
    , COUNT(DISTINCT user_id) AS cnt
FROM base
GROUP BY ALL
ORDER BY cnt DESC

2. PIVOT

# 1) orders 테이블에서 유저(user_id)별로 주문금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다.

-- 기대하는 output의 형태
-- order_date | user_1 | user_2 | user_3
-- PIVOT : MAX(IF(조건, TRUE일 때의 값, FALSE일 때의 값)) AS new_column + GROUP BY
  -- MAX 대신 집계 함수를 사용할 수도 있음. SUM
-- FALSE일 때의 값은 NULL

-- SELECT
--       order_date
--      , SUM(IF(user_id = 1, amount, 0)) AS user_1
--     , SUM(IF(user_id = 2, amount, 0)) AS user_2
--     , SUM(IF(user_id = 3, amount, 0)) AS user_3
-- FROM `advanced.orders`
-- GROUP BY 1
-- ORDER BY 1
-- ;


# 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요.user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다.

-- 기대하는 output의 형태
-- user_id | 2023-05-01 | 2023-05-02 | 2023-05-03 | 2023-05-04 | 2023-05-05

-- 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` 
      -- 컬럼의 이름을 지정할 때, 영어 제외하고 backtick(`)
      -- ANY_VALUE : 그훕화 할 대상 중에 임의의 값을 선택한다 (NULL을 제외하고). ANY_VALUE에선 나머지 값들이 NULL이거나 확정적으로 값을 기대할 수 있을 때 사용한다!
      -- ANY_VALUE(IF(order_date="2023-05-01", amount, NULL)) AS `2023-05-01`
-- FROM `advanced.orders`
-- GROUP BY 1
-- ORDER BY 1
-- ;


# 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다.

-- 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 1
-- ORDER BY 1
-- ;

## 앱 로그 PIVOT

WITH base AS(
  SELECT 
        -- * EXCEPT(event_params) # * EXCEPT(column) : 컬럼을 제외하고 다 보여줘!
        event_date
      , event_timestamp
      , event_name
      , user_id
      , user_pseudo_id
      , MAX(IF(param.key = "fierbase_screen", param.value.string_value, NULL)) AS fierbase_screen
      -- , MAX(IF(param.key = "food_id", param.value.string_value, NULL)) AS food_id   # string_value엔 food_id 값들이 저장되어 있지 않음.
      , 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 sessioon_id

  FROM `advanced.app_logs`
  CROSS JOIN UNNEST(event_params) AS param
  WHERE 1=1
    AND event_date = "2022-08-01"
  GROUP BY ALL
)

SELECT
      event_date
    , COUNT(user_id) AS user_cnt
FROM base
WHERE 1=1
  AND event_name = "click_cart"
  -- AND food_id = 1544
GROUP BY event_date

3. 퍼널 연습 문제

# 퍼널 분석
-- 퍼널 데이터
--  우리가 사용할 이벤트 => 단계
-- - screen_view : welcome, home, food_category, restaurant, cart
-- - click_payment
-- step_number : 추후에 정렬을 위해 만들 것
-- 사용할 데이터 : 앱 로그 데이터, GA/Firebase => UNNEST => PIVOT
-- 기간 : 2022-08-01 ~ 2022-08-18

WITH base as (
  SELECT
        event_date
      , event_timestamp
      , event_name
      , user_id
      , user_pseudo_id
      , platform
      , 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 BETWEEN "2022-08-01" AND "2022-08-18"
  GROUP BY ALL
), base2 as (
  SELECT
        *
        , CONCAT(event_name, "-", firebase_screen) as event_screen
  FROM base
  WHERE 1=1
    AND event_name IN ("screen_view", "click_payment")
), base3 as (
  SELECT
        event_screen
      , event_date
      , CASE
          WHEN event_screen = "screen_view-welcome" THEN 1
          WHEN event_screen = "screen_view-home" THEN 2
          WHEN event_screen = "screen_view-food_category" THEN 3
          WHEN event_screen = "screen_view-restaurant" THEN 4
          WHEN event_screen = "screen_view-cart" THEN 5
          WHEN event_screen = "click_payment-cart" THEN 6
        ELSE NULL
        END as step_number
      , COUNT(DISTINCT user_pseudo_id) as cnt
  FROM base2
  GROUP BY ALL
  HAVING step_number is not NULL
  ORDER BY event_date
)

SELECT
      event_date
    , MAX(IF(base3.event_screen ="screen_view-welcome", cnt, NULL)) AS screen_view_welcome
    , MAX(IF(base3.event_screen ="screen_view-home", cnt, NULL)) AS 	screen_vie_home
    , MAX(IF(base3.event_screen ="screen_view-food_category", cnt, NULL)) AS screen_view_food_category
    , MAX(IF(base3.event_screen ="screen_view-restaurant", cnt, NULL)) AS screen_view_restaurant
    , MAX(IF(base3.event_screen ="screen_view-cart", cnt, NULL)) AS screen_view_cart
FROM base3
GROUP BY ALL
ORDER BY event_date