✅연습문제

(1) Array & Struct

-- 연습문제 1) 영화 제목 별로 장르를 unnest하여 출력
-- select 절에서 새로운 이름으로 사용해야 unnest한 값이 출력됨 (기존의 array칼럼은 사용하지 않기)

select 
  title,
  genre
from`avdanced.array_exercises` as ori
,unnest(genres) as genre 
 
 
 
--연습문제 2) 영화별로 배우와 배역을 출력.
-- safe_offset 사용시, 모든 칼럼에 대하여 일일히 [0], [1].. 해줘야 하는 문제 -> UNNEST 사용.

select 
  title,
  ac.actor,
  ac.character
from`avdanced.array_exercises` as ori
,unnest(actors) as ac 
 

-- 연습문제 3) title, actor, character, genre 출력

select 
  title,
  ac.actor,
  ac.character,
  genre
from`avdanced.array_exercises` as ori
,unnest(actors) as ac 
,unnest (genres) as genre

--연습문제 4) 배열을 풀기

select 
  user_id, 
  event_date,
  event_name,
  user_pseudo_id, 
  eve.key, 
  eve.value.string_value,
  eve.value.int_value
from`avdanced.app_logs_temp` as ori
, unnest (event_params) as eve
where event_date = '2022-08-01'

(2) Pivot 연습문제

--Pivot : 집계함수(if (조건문, true일 때 값, False일때 값)) as new_column + GROUP BY (False일때의 값은 NULL또는 0으로 해주기)
--1) id를 기준으로 amount sum을 pivot, order_date를 행, user_id를  열

-- 연습문제 1-1)  서브쿼리 사용하기 
select
  order_date, 
  sum(if(user_id=1, sum_of_amount, 0)) as user_1,
  sum(if(user_id=2, sum_of_amount, 0)) as user_2,
  sum(if(user_id=3, sum_of_amount, 0)) as user_3
from (
  select 
  order_date,
  user_id, 
  sum(amount) as sum_of_amount
from `avdanced.orders`
group by order_date, user_id
)
group by order_date
order by order_date

-- 연습문제 1-2) 간략하게 작성하기 
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 `.avdanced.orders`
group by order_date
order by order_date

--연습문제 2) orde_date별로 amount의 합을 pivot (user_id row, drder_date column)
-- (`)backstick을 사용하여 별칭지정
-- any_value: 그룹화할 대상 중 임의의 값을 선택(NULL 제외). any_value에서는 나머지 값이 NULL이거나 확정적으로 이값이 나올 것이다 기대할 때 사용!
-- ANY_VALUE: 임의의 값을 반환하며, 특정한 규칙이나 일관성을 요구하지 않을 때 사용. (대표값, 랜덤 샘플링)
-- MAX: 그룹 내에서 가장 큰 값을 반환하며, 항상 일관된 결과를 제공합니다.

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 `avdanced.orders`
group by user_id
order by user_id

-- +) 강의 내용 (any_value)
-- select
--   user_id, 
--   any_value(if(order_date='2023-05-01', amount, 0)) as `2023-05-01`,
--   any_value(if(order_date='2023-05-02', amount, 0)) as `2023-05-02`,
--   any_value(if(order_date='2023-05-03', amount, 0)) as `2023-05-03`,
--   any_value(if(order_date='2023-05-04', amount, 0)) as `2023-05-04`,
--   any_value(if(order_date='2023-05-05', amount, 0)) as `2023-05-05`
-- from `avdanced.orders`
-- group by user_id
-- order by user_id

--연습문제 3) user_id, order_date별로 주문이 있으면 1, 없으면 0으로 pivot, user_id row, order_date column. 
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 `avdanced.orders`
group by user_id
order by user_id

-- 연습문제 4) app_log를 pivot하기
with date_user_cnt as(
select 
  ori.user_id, 
  ori.event_date,
  ori.event_name,
  ori.event_timestamp,
  ori.user_pseudo_id, 
  max(if(eve.key = "firebase_screen", eve.value.string_value, null)) as firebase_screen,
  max(if(eve.key ="food_id", eve.value.int_value, null)) as food_id, 
  max(if(eve.key ="session_id", eve.value.string_value, null)) as session_id
from`avdanced.app_logs_temp` as ori
, unnest (event_params) as eve
where 
   event_date ='2022-08-01'
group by all
)

select 
  event_date, 
  count(user_id) as user_cnt
from date_user_cnt
where
  event_name ="click_cart" 
group by event_date

(3) 퍼널 연습문제

-- 퍼널 별 유저수 집계
-- 1-1) UNNEST하기 & PIVOT하기
WITH base AS(
  SELECT 
    event_date,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_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 session_id, 
    MAX(IF(param.key = 'firebase_screen', param.value.string_value,NULL)) AS firebase_screen, 
  from `bigquery-432401.avdanced.app_logs_temp` 
  , unnest (event_params) as param
  where event_date between '2022-08-01' and '2022-08-18'
  GROUP BY ALL
), base2 AS(
-- 1-2) CONCAT하기
  SELECT 
    * EXCEPT (event_name, firebase_screen, event_timestamp),
  CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM base
  WHERE
    event_name IN ("screen_view", "click_payment")
), base3 AS (
  --1-3) 일자별 이벤트 별 집계
  SELECT
    event_date, 
    event_name_with_screen,
    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 base2
  GROUP BY ALL
  HAVING step_number IS NOT NULL
)
--1-4) 집계한 데이터를 PIVOT
SELECT
  event_date,
  MAX(IF(base3.event_name_with_screen ="screen_view-welcome", cnt, NULL)) AS screen_view_welcome,
  MAX(IF(base3.event_name_with_screen ="screen_view-home", cnt, NULL)) AS 	screen_vie_home,
  MAX(IF(base3.event_name_with_screen ="screen_view-food_category", cnt, NULL)) AS screen_view_food_category,
  MAX(IF(base3.event_name_with_screen ="screen_view-restaurant", cnt, NULL)) AS screen_view_restaurant,
  MAX(IF(base3.event_name_with_screen ="screen_view-cart", cnt, NULL)) AS screen_view_cart
FROM base3
GROUP BY ALL

✅배운점

• ‘cross join unnest 칼럼 as 별칭’에서 반드시 ‘AS 별칭’ 을 이용해야 ‘Cannot access field on a value with type ARRAY’ 에러가 안 뜬다.

• 빅쿼리에서는 ‘’이 아닌, ``을 사용하여야 한다.

   

• pivot이라고 하면 pivot()함수를 반드시 사용해야 편할 것이라고 생각했었는데, max와 if로 pivot을 구현하는 것이 인상 깊었다.

• SQL 기반이라고 하면 모든 DB가 비슷한 기능을 가지고 있다고 생각했는데, DB마다 생성된 함수 및 기능이 미묘하게 다르다는 것을 느꼈다. 예를 들어 빅쿼리에서 배운 countif()을 MySQL에서 사용했는데 안되는 것도 그 예가 아닐까.

• 생각보다 빅쿼리와 PostgreSQL에서 사용하는 함수가 동일한 게 많아 인상적이었다.

✅익숙해져야 할 부분

• 퍼널 분석을 배우면서, 이론적으로 이해하는 것은 쉽지만 내가 아는 쿼리적인 지식을 실제 데이터에 적용하는 것은 생각보다 손이 많이 가고 쉽지 않은 작업이라는 것을 느꼈다. 많이 해봐야 노하우가 생길 것 같다. 어떤 데이터를 어떻게 해야 어떤 결과가 나오고, 이를 어떻게 이용하고 해석할 수 있는지에 대한 일련의 과정에 익숙해져야 할 것 같다.

• TIMESTAMP를 DATE로 바꾸고 확인하는 부분에 대한 연습이 필요

   

• 쿼리 작성 시, 예약어를 대문자로 사용하는 것이 좋다고 알고 있는데, 아직 이 부분이 익숙하지 않아 연습이 필요하다.

• 코드를 작성하면서 문장을 그대로 쿼리로 옮기는 편이라서 그런지 서브 쿼리를 사용하는 경우가 많다. 단순히 문장을 쿼리로 옮기기 보다 어떻게 하면 더 간결하고 가독성 높은 쿼리를 작성할 수 있을 지 고민하는 습관을 들여야 겠다.

• 함수 이름 정하는 거 중요한 건 아는데, 잘 안되는 것 같다. 사용 기능을 이용하여 함수 이름 짓는 습관을 지어야겠다.