[빠짝스터디 1주차 과제] ARRAY, STRUCT 연습 문제/ PIVOT 연습문제/ 퍼널 쿼리 연습 문제

ARRAY, STRUCT 연습문제

-- 1)영화별 장르를 unnest해서 보여줘라.
SELECT
  title,
  genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) AS genre;


--2) 영화별 배우와 배역을 보여줘라. 배우와 배역은 별도의 컬럼으로 나와야 한다.
SELECT
  title,
  ac.actor,
  ac.character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS ac;

--3. 영화별로 배우, 배역, 장르를 출력해라.
SELECT 
  title,
  actor.actor,
  actor.character,
  genre
FROM `advanced.array_exercises`
CROSS JOIN UNNEST(actors) as actor
CROSS JOIN UNNEST(genres) as genre;

--4. 앱 로그 데이터의 배열을 풀어라
SELECT 
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  event_param.key,
  event_param.value.string_value,
  event_param.value.int_value
FROM `advanced.app_logs`
CROSS JOIN UNNEST(event_params) AS event_param

PIVOT 연습문제

-- 1. 유저별 주문금액의 합계를 poviot해라. 
-- 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 order_date
-- ORDER BY order_date;

-- 2. 날짜별 유저들의 주문금액의 합계를 pivot해라.
-- 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. 사용자별, 날짜별 주문이 있다면 1, 없다면 0으로 pivot해라.
-- 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. key값을 column으로 pivot해라
SELECT 
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  IF(key = 'food_id', IF(string_value IS NOT NULL, string_value, CAST(int_value AS STRING)), NULL) AS food_id
FROM advanced.app_logs_unnest  # app_logs 테이블을 unnest한 결과는 자주 쓰일 듯 싶어 app_logs_unnest 테이블을 따로 생성했다.
WHERE event_name = 'click_cart'
  AND event_date = '2022-08-01';

기억할 것

• IF 조건문을 사용할 때, TRUE일때의 값과 FALSE일때의 값의 데이터 타입이 같아야 한다.
  PIVOT 연습문제 4번을 풀 때 food_id를 PIVOT하는 과정에서 IF(key = 'food_id', IF(string_value IS NOT NULL, string_value, int_value AS STRING), NULL)와 같이 표현했었다.

  KEY값에 따라 string_value와 int_value중 타입에 맞는 하나의 컬럼에만 값이 있었고, 처음에는 이를 명시적으로 적기보다 범용적으로 사용될 수 있도록 하는게 더 좋지 않을까 싶었다. 값이 있는 컬럼의 값을 사용하자는 의도로 IF(string_value IS NOT NULL, string_value, int_value AS STRING)라고 표현했고 다음과 같은 에러를 만났다.

  No matching signature for function IF for argument types: BOOL, STRING, INT64. Supported signature: IF(BOOL, ANY, ANY) at [55:23]

  
  에러를 피하고자 CAST를 이용했는데 데이터 타입을 억지로 바꾸기보다는(추후에 숫자형 데이터로 대소 비교를 한다던지 나열을 한나던지의 상황이 있을 수 있으니..) 명시적으로 표현하는 것을 마냥 피할것은 아니겠다는 생각을 했다.

• 특별한 문자(예약어, 숫자)를 ALIAS로 설정하려면 back tick(`)으로 감싸줘야한다.

퍼널 쿼리 연습문제

-- 퍼널 별 유저 수 집계(2022-08-01 ~ 2022-08-18)
-- welcome -> home -> good category -> restaurant -> cart -> 주문하기 클릭 
/*
event_data | event_name_with_screen | stemp_number | cnt 의 컬럼 형태로 만들것 
*/
-- 처음 작성했던 쿼리
-- SELECT 
--   event_date,
--     CASE 
--     WHEN event_name = 'screen_view' AND string_value = 'welcome' THEN CONCAT(event_name, '-', string_value)
--     WHEN event_name = 'screen_view' AND string_value = 'home' THEN CONCAT(event_name, '-', string_value)
--     WHEN event_name = 'screen_view' AND string_value = 'food_category' THEN CONCAT(event_name, '-', string_value)
--     WHEN event_name = 'screen_view' AND string_value = 'restaurant' THEN CONCAT(event_name, '-', string_value)
--     WHEN event_name = 'screen_view' AND string_value = 'cart' THEN CONCAT(event_name, '-', string_value)
--     WHEN event_name = 'click_payment' AND string_value = 'cart' THEN CONCAT(event_name, '-', string_value)
--   END AS event_name_with_screen,
--   CASE 
--     WHEN event_name = 'screen_view' AND string_value = 'welcome' THEN 1
--     WHEN event_name = 'screen_view' AND string_value = 'home' THEN 2
--     WHEN event_name = 'screen_view' AND string_value = 'food_category' THEN 3
--     WHEN event_name = 'screen_view' AND string_value = 'restaurant' THEN 4
--     WHEN event_name = 'screen_view' AND string_value = 'cart' THEN 5
--     WHEN event_name = 'click_payment' AND string_value = 'cart' THEN 6
--   END AS step_number,
--   COUNT(event_date) AS cnt
-- FROM `advanced.app_logs_unnest`
-- WHERE
--   event_date BETWEEN '2022-08-01' AND '2022-08-18'
-- GROUP BY ALL
-- ORDER BY event_date, step_number

-- =============================================
-- 정석 쿼리
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.int_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"
    -- AND
    -- event_param.key IN ("screen_view", "click_payment") 
    -- AND
    -- event_param.value.string_value IN ("welcome", "home", "food_category", "restaurant", "cart")
  GROUP BY ALL
), filter_event_and_concat_event_and_screen AS(
  SELECT
    * EXCEPT(event_name, firebase_screen),
    CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen,
    DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime
  FROM base
  WHERE     
  event_date BETWEEN "2022-08-01" AND "2022-08-18"
    AND
    event_name IN ("screen_view", "click_payment") 
    AND
    firebase_screen IN ("welcome", "home", "food_category", "restaurant", "cart")
)

SELECT
  event_date,
  event_name_with_screen,
  -- event_datetime,
  -- user_pseudo_id,
  CASE 
    WHEN event_name_with_screen = 'screen_view-welcome' THEN 1
    WHEN event_name_with_screen = 'screen_view-home' THEN 2
    WHEN event_name_with_screen = 'screen_view-food_category' THEN 3
    WHEN event_name_with_screen = 'screen_view-restaurant' THEN 4
    WHEN event_name_with_screen = 'screen_view-cart' THEN 5
    WHEN event_name_with_screen = 'click_payment-cart' THEN 6
    ELSE NULL
  END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM filter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
ORDER BY event_date, step_number

기억할 것

• 각 컬럼에 어떤 값이 있는지 잘! 확인하자.
  event_name_with_screen 부분을 CASE WHEN으로 처리하며 '이게 진정 맞을까......' 싶긴 했었었다. 왜인지 초반에 컬럼값 확인할 때 WHERE 절에 IN 구문으로 확인했을때 원하던 결과로 나오지 않아 CASE WHEN으로 직접 처리했었는데.. 실수였다. (아마 string_value만 IN연산으로 확인하고 섣부른 판단을 했던게 아닐지 싶다.)

• CASE WHEN 구문에서 전체를 포함하도록 조건을 구성하지 않으면 WHEN에 해당하지 않는 부분은 NULL값으로 생성된다.
  처음 쿼리를 짜고 CNT 컬럼의 값을 확인했을 때 event_name_with_screen과 step_number에 왜 NULL값이 있는지 당황스러웠다. 이미 있는 데이터에 새 컬럼을 만든 것이니 조건에 해당되지 않는 부분은 값이 없어 NULL로 남아있다는 사실...!

• DATETIME 함수를 이용하여 "Asia/Seoul" 처리하기
  새까맣게 잊고있었다. 까먹지 말자~!

• 항상 데이터를 뽑아 어떤 내용을 확인하고 싶은 건지를 생각하자.
  문제에 맞는 쿼리를 짜고 작성된 쿼리의 결과가 잘 나오는지에만 급급했던 거 같다. 이 강의를 듣는 목적은 데이터 분석에 대한 감 잡기!지 쿼리 잘 짜기!는 아니었으니까..!(물론 SQL 실력 향상은 필요한 부분..!!) 스스로 고민해보는 시간을 꼭! 갖도록 해보자.