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

ARRAY, STRUCT 연습문제

-- 1. array_exercises 테이블에서 title 별로 genres를 UNNEST하기 
SELECT
  title
  , genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) as genre;


-- 2. array_exercises 테이블에서 title 별로 actor, character 추출
-- actor, character는 별도의 컬럼으로 빼기 (struct의 key로써 존재하면 안 됨.)
SELECT 
  title
  , ACTORS.actor AS actor
  , ACTORS.character AS character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS ACTORS;


-- 3. array_exercises 테이블에서 title 별로 actor, character, genre 추출
-- 여러 ARRAY 컬럼을 UNNEST할 경우, 각 컬럼별로 UNNEST한 것을 CROSS JOIN 진행하면 됨.

SELECT 
  title
  , ACTORS.actor AS actor
  , ACTORS.character AS character
  , genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS ACTORS
CROSS JOIN UNNEST(genres) as genre;


-- 4. app_logs 테이블(약 73만 건의 로그 데이터)의 ARRAY를 UNNEST 하기
-- event_params 형태?
-- ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>>[
--  STRUCT('firebase_screen', STRUCT('food_detail', NULL))
--  , ...
-- ]
SELECT
  user_id
  , event_date
  , event_name
  , user_pseudo_id
  , EVENT_PARAMS.key AS key
  , EVENT_PARAMS.value.string_value AS string_value
  , EVENT_PARAMS.value.int_value AS int_value
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS EVENT_PARAMS

PIVOT 연습문제

-- 1. 날짜별 유저별 주문금액 합계
-- 첫번째 그룹 axis는 GROUP BY 대상 컬럼임. (여기서는 '날짜별'에 해당함.)
-- 두번째 그룹 axis는 IF 혹은 CASE WHEN을 이용하여 각 컬럼으로 만들어야 함. (여기서는 '유저별'에 해당함.)
-- 세번째 그룹 axis는 두번째 그룹 axis를 다루는 과정에서 '값'으로 들어가야 함. (여기서는 '주문금액'에 해당함.)
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`
WHERE 1=1
AND user_id IN (1,2,3)
GROUP BY order_date
ORDER BY order_date ASC;


-- 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`
WHERE 1=1
AND user_id IN (1,2,3)
AND order_date IN ('2023-05-01','2023-05-02','2023-05-03','2023-05-04','2023-05-05')
GROUP BY user_id
ORDER BY user_id ASC;


-- 3. 유저별 날짜별 주문내역 존재여부
-- 주문 존재하면 1, 없으면 0; 주문횟수가 아님에 유의
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`
WHERE 1=1
AND user_id IN (1,2,3)
AND order_date IN ('2023-05-01','2023-05-02','2023-05-03','2023-05-04','2023-05-05')
GROUP BY user_id
ORDER BY user_id ASC;


-- 4. app_logs 테이블 PIVOT 하기
-- user_id=32888이 카트 추가하기(click_cart)를 누를 때 어떤 음식(food_id)를 담았나요?
-- 일반화된 문제 정의: 유저별 이벤트별 이벤트 파라미터 key-value 보기
-- 특정 유저가 특정 action을 취했을 때 (event가 발생했을 때) 앱 내부적으로 어떤 정보가 오갔는지 보고싶을 때


-- 방법 1) 쪼개서 생각하기
WITH base AS (
  -- step 1) app_logs 테이블은 ARRAY 컬럼이 존재하는 테이블: UNNEST 하기 → UNNEST한 테이블을 임시테이블화
  SELECT
    event_date,
    event_timestamp,
    event_name,
    params.key AS key_,
    params.value.string_value AS string_value_,
    params.value.int_value AS int_value_,
    user_id,
    user_pseudo_id,
    platform
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS params
  WHERE 1=1
  -- AND user_id=32888
  -- AND event_name='click_cart'
  AND event_date >= '2022-08-01' 
  AND event_date <= '2022-08-31'
)

-- step 2) event_params에 대해 PIVOT 진행
SELECT 
  user_id,
  event_date,
  event_name,
  event_timestamp,
  user_pseudo_id,
  MAX(IF(key_='food_id',int_value_,NULL)) AS `food_id`,
  MAX(IF(key_='session_id',string_value_,NULL)) AS `session_id`,
  MAX(IF(key_='firebase_screen',string_value_,NULL)) AS `firebase_screen`,
FROM base
GROUP BY user_id, event_date, event_name, event_timestamp, user_pseudo_id;


-- 방법 2) UNNEST + PIVOT을 한 쿼리에
WITH base AS (
  SELECT 
    user_id,
    event_date,
    event_name,
    event_timestamp,
    user_pseudo_id,
    MAX(IF(params.key='food_id',params.value.int_value,NULL)) AS `food_id`, 
    MAX(IF(params.key='session_id',params.value.string_value,NULL)) AS `session_id`, 
    MAX(IF(params.key='firebase_screen',params.value.string_value,NULL)) AS `firebase_screen`, 
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS params
  GROUP BY ALL
)

-- user_id=32888이 카트 추가하기(click_cart)를 누를 때 어떤 음식(food_id)을 담았나요?
SELECT 
  *
FROM base
WHERE 1=1
AND user_id=32888
AND event_name='click_cart';

퍼널 쿼리 연습문제

WITH main AS (
  SELECT

    event_date,
    CONCAT(event_name,'-', event_param.value.string_value) AS event_name_with_screen,
    CASE
      WHEN event_name = 'screen_view' AND event_param.value.string_value = 'welcome' THEN 1
      WHEN event_name = 'screen_view' AND event_param.value.string_value = 'home' THEN 2
      WHEN event_name = 'screen_view' AND event_param.value.string_value = 'food_category' THEN 3
      WHEN event_name = 'screen_view' AND event_param.value.string_value = 'restaurant' THEN 4
      WHEN event_name = 'screen_view' AND event_param.value.string_value = 'cart' THEN 5
      WHEN event_name = 'click_payment' AND event_param.value.string_value = 'cart' THEN 6
    END AS step_num,
    COUNT(DISTINCT user_pseudo_id) AS cnt

  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
  WHERE 1=1
  AND event_date BETWEEN '2022-08-01' AND '2022-08-18'
  AND event_param.key = 'firebase_screen'
  AND event_name IN ("screen_view",'click_payment')
  GROUP BY 1,2,3
  HAVING step_num IS NOT NULL
)

SELECT
  event_date,
  SUM(IF(step_num = 1, cnt, 0)) AS `screen_view-welcome`,
  SUM(IF(step_num = 2, cnt, 0)) AS `screen_view-home`,
  SUM(IF(step_num = 3, cnt, 0)) AS `screen_view-food_category`,
  SUM(IF(step_num = 4, cnt, 0)) AS `screen_view-restaurant`,
  SUM(IF(step_num = 5, cnt, 0)) AS `screen_view-cart`,
  SUM(IF(step_num = 6, cnt, 0)) AS `click_payment-cart`
FROM main
GROUP BY 1
ORDER BY event_date ASC