[인프런 빅쿼리 빠짝스터디 1주차] ARRAY, STRUCT, PIVOT,퍼널 쿼리

연습 문제

(1) Array, Struct

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

• 처음에 'title'을 기준으로 묶여 있길래 UNNEST의 요소로 'title'을 넣으려 함 → ERROR

• 생각해보니 CROSS JOIN이 목적이므로 개별 값을 가지는 'genres'를 기준으로 UNNEST

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

• CROSS JOIN 대신 ','를 통해 UNNEST를 시도해 봄

   

-- 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 
--    한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다
SELECT
  title,
  ae2.actor,
  ae2.character,
  genre
FROM (
  SELECT
    title,
    actor.actor,
    actor.character,
    genres
  FROM `advanced.array_exercises` AS ae,
  UNNEST(actors) AS actor
) AS ae2,
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

• UNNEST 두번해야해서 서브쿼리로 해결하려 시도 → 서브 쿼리 안하고 UNNEST 두번 사용 가능

• 실행 순서 : FROM → JOIN → SELECT

• cmd + D : 다음 해당 항목 같이 선택 → 반복되는 단어 쉽게 수정 가능

-- 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요
SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  EV.key,
  EV.value.string_value,
  EV.value.int_value
FROM `advanced.app_logs` AS al,
UNNEST(event_params) AS EV

-- 강의 코드
SELECT
  event_date,
  event_timestamp,
  event_name,
  event_param.key AS key,
  event_param.value AS value,
  user_id
FROM `advanced.app_logs`,
UNNEST(event_params) AS event_param
WHERE
  event_date = "2022-08-01"

• WHERE에 조건을 넣어 연산량 낮출 수 있음

• event_params로 UNNEST했을때 value값만 사용해도 string, int 둘다 표시됨

   

(2) PIVOT

• parquet : 대용량 데이터를 효율적으로 저장하고 처리하기 위해 설계된 컬럼 기반의 저장 형식

• 공통 사항 : 행과 열에 대한 정의가 한번에 떠오르지 않았음

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

• NULL과 0은 다르다

• 서브쿼리 이용한 방법도 있음(집계 후 PIVOT)

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

• AS에서 “ ”(따옴표) 대신 (백틱) 사용

• ANY_VALUE : 그룹화 할 대상 중에 임의의 값을 선택한다(NULL 제외) 따라서 나머지 값들이 NULL이거나 값이 확정적일 때 사용

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

• 조건문에서 행 별로 주문 있으면 1, 없으면 0 설정한 뒤 MAX 조건 걸어줌
  → 최종적으로 값이 있으면 1

• 횟수일 경우에는 SUM

-- user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? => 쿼리 작성이 어려움
-- => key를 Column으로 두고, string_value나 int_value를 Column의 값으로 설정하는 것이 필요
SELECT
  user_id,
  event_date,
  event_name,
  event_timestamp,
  ANY_VALUE(IF(event_param.key="firebase_screen", event_param.value.string_value, NULL)) AS `firebase_screen`,
  ANY_VALUE(IF(event_param.key="food_id", event_param.value.int_value, NULL)) AS `food_id`,
  ANY_VALUE(IF(event_param.key="session_id", event_param.value.string_value, NULL)) AS `session_id`,
FROM `advanced.app_logs`,
UNNEST(event_params) AS event_param
WHERE
  (event_date = "2022-08-01")
  AND (event_name = "click_cart")
  AND (user_id = 32888) # 선택 사항
GROUP BY 
  user_id, event_date, event_name, event_timestamp

• 문제를 봤을 때 문제가 원하는게 무엇인지 한번에 파악되지 않았음

• 32888의 click cart 행위에만 집중하는 것으로 판단

• GROUP BY ALL 을 통해 한번에 해결 가능

(3) 퍼널 쿼리

WITH base AS (
  SELECT
    user_id,
    user_pseudo_id,
    event_date,
    event_name,
    event_timestamp,
    platform,
    ANY_VALUE(IF(event_param.key="firebase_screen", event_param.value.string_value, NULL)) AS `firebase_screen`,
    ANY_VALUE(IF(event_param.key="food_id", event_param.value.int_value, NULL)) AS `food_id`,
    ANY_VALUE(IF(event_param.key="session_id", event_param.value.string_value, NULL)) AS `session_id`,
  FROM `advanced.app_logs`,
  UNNEST(event_params) AS event_param
  WHERE
    event_date BETWEEN "2022-08-01" AND "2022-08-18"
  GROUP BY ALL
)

• event_name과 event_params가 연결되어야 하므로 우선 UNNEST 필요하다고 생각함
  → 이전에 사용했던 쿼리문 그대로 사용 후 WITH

• 하지만 이후 과정에서 CONCAT 함수를 떠올리지 못해 진행이 불가했음

), concat_event_screen AS (
  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
)

• 이후에 concat_event_screen이라는 이름으로 테이블 재가공 해줌

  • CONCAT을 통해 event_name-firebase-screen

  • event_timestamp 알아보기 쉽게 바꿔준 후 제거

SELECT
  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 concat_event_screen
GROUP BY ALL
HAVING step_number IS NOT NULL # CASE-WHEN에서 포함되지 않는 행위는 제거

• CASE-WHEN 이용해서 step_number 컬럼 만들어줌

• COUNT + DISTINC 이용해 해당 퍼널에 진입한 고유 유저 수 세주기

• 퍼널이 아직 익숙하지 않아 혼자서 해결할 수는 없었음

+) 일자별

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 concat_event_screen
GROUP BY ALL
HAVING step_number IS NOT NULL # CASE-WHEN에서 포함되지 않는 행위는 제거
ORDER BY event_date

간단한 EDA

지금까지 배운 것을 바탕으로 간단한 데이터 탐색 및 분석

• 행 개수 → 731873행

• 2022-08-01(월) ~ 2023-01-20(금) 의 데이터

• user_id 고유 개수 → 49678

• pseudo_user_id 고유 개수 → 52823

• event_params → 22개

  
  아래 표는 전체 기간에 대한 각 event_params별 개수 총합(내림차순)

-> 개수가 같은 항목들이 존재

-> 앱 작동 흐름에 대해 알 필요 있어 보임

• event_date에 따른 pseudo_user_id 고유 개수 그래프

-> 8월부터 10월 중순까지는 이용자수가 늘어나는 추세
-> 이후 안정기 들어서면서 일정한 주기로 반복되는 형태

느낀점

• 보통 데이터 분석 프로젝트를 하면 Python이나 R을 통해 진행했기에 아직 쿼리문이 생소함

• 머릿속으로는 대충 어떤 식으로 데이터를 건드려야겠다는 생각이 들긴 하지만, 표현이 잘 안되는 경우가 많아서 까다로웠던 것 같음

• 업무 경험이 없는 입장에서 퍼널 분석 같은 것들은 실제로 해본적이 없는데, 비교적 이해하기 쉽게 알려주신 것 같음