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

1. ARRAY, STRUCT 연습문제

연습 문제 1번

# 1)array_exercises테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요
SELECT title, genre
FROM `advanced.array_exercises` AS exercise CROSS JOIN UNNEST(genres) AS genre


SELECT title, # 기존에 array_exercises에 저장되어 있던 컬럼 
			 genre
FROM `advanced.array_exercises` AS ae, UNNEST(genres) AS genre

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

연습 문제 2번

# 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다
SELECT title,
			 actor.actor,
			 actor.character
-- FROM `advanced.array_exercises` AS ae, UNNEST(actors) AS actor
FROM `advanced.array_exercises` AS ae
CROSS JOIN UNNEST(actors) AS actor
# actors = [STRUCT(STRING, STRING)]

연습 문제 3번

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

SELECT title,
			 actor.actor,
			 actor.character,
			 genre

FROM `advanced.array_exercises` AS ae
,UNNEST(actors) AS actor, UNNEST(genres) as genre
-- FROM `advanced.array_exercises` AS ae
-- CROSS JOIN UNNEST(actors) AS actor
-- CROSS JOIN UNNEST(genres) as genre
# 이 문제의 의도 : UNNEST를 2번 연속 사용할 수 있다.
# CROSS JOIN => JOIN 연속 2번과 맥락은 동일한데, UNNEST라는 것이 어색할 수 있었다
# 데이터의 중복이 어느정도 생기는데, 그것은 어쩔 수 없는 이슈(CROSS JOIN)

-- FROM `advanced.array_exercises` AS ae
-- CROSS JOIN UNNEST(actors) AS actor, UNNEST(genres) as genre

SELECT title,
			 actor.actor,
			 actor.character,
			 genre

FROM `advanced.array_exercises` AS ae
,UNNEST(actors) AS actor, UNNEST(genres) as genre
-- FROM `advanced.array_exercises` AS ae
-- CROSS JOIN UNNEST(actors) AS actor
-- CROSS JOIN UNNEST(genres) as genre
# 이 문제의 의도 : UNNEST를 2번 연속 사용할 수 있다.
# CROSS JOIN => JOIN 연속 2번과 맥락은 동일한데, UNNEST라는 것이 어색할 수 있었다
# 데이터의 중복이 어느정도 생기는데, 그것은 어쩔 수 없는 이슈(CROSS JOIN)

-- FROM `advanced.array_exercises` AS ae
-- CROSS JOIN UNNEST(actors) AS actor, UNNEST(genres) as genre
WHERE actor.actor = 'Chris Evans' 
	AND genre = 'Action'
-- WHERE actor = 'Chris Evans' (X)
# 실행 순서 : FROM -> JOIN -> SELECT
# actors : ARRAY<STRUCT> => UNNEST => STRUCT
# genres : ARRAY<STRING> => STRING 

연습 문제 4번

# 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요
SELECT event_date,
			 event_timestamp,
			 event_name,
			 event_param.key as key,
			 event_param.value as value,
			 event_param.value.string_value as string_value,
			 event_param.value.int_value as int_value,
			--  event_params,
			 user_id,
			 event_param
FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS event_param
WHERE event_date ='2022-08-01'

2. PIVOT

PIVOT 연습 문제 1

## SubQuery 방식
SELECT 
	order_date,
	SUM(IF(user_id = 1, sum_of_amount, NULL)) AS user_1,
	SUM(IF(user_id = 2, sum_of_amount, NULL)) AS user_2,
	SUM(IF(user_id = 3, sum_of_amount, NULL)) AS user_3
	-- MAX를 써도 동일한 결과 값이 나옴 
	-- 그룹화 할때 값이 하나밖에 없음 
FROM (
	SELECT 
		order_date,
		user_id,
		# Amount의 합
		SUM(amount) AS sum_of_amount
	FROM advanced.orders
	GROUP BY order_date, user_id
	-- ORDER BY order_date
) 	
GROUP BY order_date
ORDER BY order_date

PIVOT 연습 문제 2

# ANY_VALUE 활용
-- ANY_VALUE : 그룹화 할 대상 중에 임의의 값을 선택한다 (NULL)을 제외하고
-- ANY_VALUE에선 나머지 값들이 NULL 이거나 확정적으로 이 값이 나올 것이다 기대할 때 사용한다
SELECT user_id,
       ANY_VALUE(IF(order_date = PARSE_DATE('%Y-%m-%d', '2023-05-01'), amount, NULL)) AS `2023-05-01`,
       ANY_VALUE(IF(order_date = date('2023-05-02'), amount, NULL)) AS `2023-05-02`,
       ANY_VALUE(IF(order_date = date('2023-05-03'), amount, NULL)) AS `2023-05-03`,
       ANY_VALUE(IF(order_date = date('2023-05-04'), amount, NULL)) AS `2023-05-04`,
       ANY_VALUE(IF(order_date = date('2023-05-05'), amount, NULL)) AS `2023-05-05`
FROM `advanced.orders` 
GROUP BY user_id
ORDER BY user_id 

PIVOT 연습 문제 3

# 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요.user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다
SELECT user_id,
			 # amount 대신 1 이라고 표시. IF 문 안에 TRUE 일 때의 값이 항상 특정 컬럼이 아니라 1이라고 할 수도 있음(유무에 따라서)
       MAX(IF(order_date = PARSE_DATE('%Y-%m-%d', '2023-05-01'), 1, 0)) AS `2023-05-01`,
       MAX(IF(order_date = date('2023-05-02'), 1, 0)) AS `2023-05-02`,
       MAX(IF(order_date = date('2023-05-03'), 1, 0)) AS `2023-05-03`,
       MAX(IF(order_date = date('2023-05-04'), 1, 0)) AS `2023-05-04`,
       MAX(IF(order_date = date('2023-05-05'), 1, 0)) AS `2023-05-05`
FROM `advanced.orders`
GROUP BY user_id

PIVOT 연습 문제 4 앱 로그 데이터 배열 PIVOT 하기

WITH base AS (
  SELECT event_date,
       event_name,
       user_id,
       user_pseudo_id,
       event_timestamp,
       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
-- WHERE event_name ='click_cart'
ORDER BY user_pseudo_id
LIMIT 100
)

SELECT 
  event_date,
  COUNT(user_id) AS user_cnt
FROM base
WHERE event_name ='click_cart'
GROUP BY event_date
ORDER BY event_date

3. 퍼널 분석

-- event_name + screen (필요한 이벤트만 WHERE 조건에 걸어서 사용)
-- step_number + COUNT

WITH base AS (
  SELECT
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  platform,
  -- param
  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 = '2022-08-01'
  GROUP BY ALL
  -- LIMIT 100
), filter_event_and_concat_event_and_screen AS (
  SELECT 
    * EXCEPT(event_name, firebase_screen),
    CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen
  FROM base
  WHERE 
    event_name IN ("screen_view", "click_payment") 

)

SELECT 
  *
FROM filter_event_and_concat_event_and_screen

최종 RESULT

# 일자 상관 없이 퍼널의 유저 수를 집계한 쿼리 => 일자별로 하기 위해 event_date 추가

WITH base AS (
  SELECT
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  platform,
  -- param
  MAX(IF(param.key = 'firebase_screen', param.value.string_value, NULL)) AS firebase_screen, 
  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 = '2022-08-01' # 적은 데이터로 쿼리를 작성하기 위해 만들어둔 조건
    event_date BETWEEN '2022-08-01' AND '2022-08-18'
  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_name IN ("screen_view", "click_payment") 

)

-- step_number + COUNT
-- step_number : CASE WHEN 을 사용해 숫자 지정, 조건문을 여러 개 하고싶을 때 사용하는 함수 
# 일자 상관 없이 퍼널의 유저 수를 집계한 쿼리 => 일자별로 하기 위해 event_date 추가
SELECT 
  event_date, # 일자별로 퍼널별 유저 수 쿼리 
  event_name_with_screen,

      --  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