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

정용님의 프로필 이미지
정용

작성한 질문수

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

[바짝스터디 1주차 과제] ARRAY,STRUCT,PIVOT,FUNNEL

작성

·

21

0

Q1 STRUCT, UNNEST


1. array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요.



SELECT title,
      --  genres,
       genre
       

FROM advanced.array_exercises AS ae
CROSS JOIN UNNEST(genres) AS genre

-- genres는 평면화가 된 데이터를 의미
-- genres가 지금 배열
-- ARRAY : 같은 타입의 여러 데이터를 저장하고 싶을 때
-- ARRAY를 풀때 Flattten(평면화) -> UNNEST
-- UNNEST릃 할 때는 CROSS JOIN + UNNEST(ARRAY_COLUMN) 컬럼 명시









2) array_exercises 테이블에서 각 영화(title)q별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 함.



SELECT title,
      --  actors

-- actor에 직접 접근하면 어떨까 -> 새로운 컬럼으로 가능하나, 매번 SAFE_OFFSET을 지정해야 함
-- actors = [STRUCT(STRING,STRING)]

actors[SAFE_OFFSET(0)].actor AS first_actor,
actors[SAFE_OFFSET(0)].actor AS first_character,
actors[SAFE_OFFSET(1)].actor AS second_actor,
actors[SAFE_OFFSET(1)].actor AS second_character

-- 배열에 직접 접근이 아닌 UNNEST로 풀어야 편리할 듯

FROM advanced.array_exercises as ae



---------------------------------------------------------------
---------------------------------------------------------------




SELECT title,
       actor.actor,
       actor.character

FROM advanced.array_exercises as ae
CROSS JOIN UNNEST(actors) AS actor
-- actors가 배열










3) array_exercises 테이블에서 각 영화(title) 별로 배우(actor), 배역(character), 장르(genre)를 출력. 한 row에 배우, 배역, 장르가 모두 표시되어야 함.




SELECT title,
      --  actors, #ARRAY<STRUCT(STRING, STRING)>
      actor.actor as actor,
      actor.character as character,
      -- genres # ARRAY<STRING>
      genre

FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor
CROSS JOIN UNNEST(genres) AS genre

 

 

 

Q2 PIVOT


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

SELECT order_date,
       IF(user_id = 1, amount , NULL) AS user_1,
       IF(user_id = 2, amount , NULL) AS user_2,
       IF(user_id = 3, amount , NULL) AS user_3

FROM(
  SELECT order_date,
        user_id,
        amount       

  FROM advanced.orders
  GROUP BY order_date, user_id, amount
  ORDER BY order_date
)
---------------------------------------------------------------
---------------------------------------------------------------

1-2)
SELECT order_date,
       MAX(IF(user_id = 1, amount , NULL)) AS user_1,
       MAX(IF(user_id = 2, amount , NULL)) AS user_2,
       MAX(IF(user_id = 3, amount , NULL)) AS user_3

FROM(
  SELECT order_date,
        user_id,
        amount       

  FROM advanced.orders
  GROUP BY order_date, user_id, amount
  ORDER BY order_date
)

GROUP BY order_date
ORDER BY order_date




---------------------------------------------------------------
---------------------------------------------------------------


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

SELECT order_date,
       SUM(IF(user_id = 1, amount , NULL)) AS user_1,
       SUM(IF(user_id = 2, amount , NULL)) AS user_2,
       SUM(IF(user_id = 3, amount , NULL)) AS user_3
FROM advanced.orders
GROUP BY order_date       
ORDER BY order_date



backtick 활용
any value는 어디에 활용할 수 있을지? -> 데이터는 믿을수 없기에 일부 데이터만 보고 사용 판단하기엔 위험할 것 같음.

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) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다

3-1) 주문 여부 1,0

SELECT user_id,
       SUM(IF(order_date = "2023-05-01", 1, 0)) AS `2023-05-01`,
       SUM(IF(order_date = "2023-05-02", 1, 0)) AS `2023-05-02`,
       SUM(IF(order_date = "2023-05-03", 1, 0)) AS `2023-05-03`,
       SUM(IF(order_date = "2023-05-04", 1, 0)) AS `2023-05-04`,
       SUM(IF(order_date = "2023-05-05", 1, 0)) AS `2023-05-05`,


FROM advanced.orders
GROUP BY user_id
ORDER BY user_id



3-2) 횟수


SELECT user_id,
       SUM(IF(order_date = "2023-05-01", 1, 0)) AS `2023-05-01`,
       SUM(IF(order_date = "2023-05-02", 1, 0)) AS `2023-05-02`,
       SUM(IF(order_date = "2023-05-03", 1, 0)) AS `2023-05-03`,
       SUM(IF(order_date = "2023-05-04", 1, 0)) AS `2023-05-04`,
       SUM(IF(order_date = "2023-05-05", 1, 0)) AS `2023-05-05`,


FROM advanced.orders
GROUP BY user_id
ORDER BY user_id





-- 앱 로그 PIVOT


WITH base AS(
    SELECT event_date,
          event_timestamp,
          event_name,
          user_id,
          user_pseudo_id,
          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

    -- * EXCEPT(event_params)
          
    FROM advanced.app_logs
    CROSS JOIN UNNEST(event_params) AS param
    WHERE event_date = "2022-08-01"
    GROUP BY ALL

)

SELECT event_date,
       COUNT(user_id) AS user_cnt
FROM base
WHERE event_name = "click_cart" AND food_id = 1544
GROUP BY event_date

 

 

 

 

Q3 퍼널 데이터

-- 이중 WITH 문
WITH BASE AS(
      SELECT event_date,
            event_timestamp,
            event_name,
            user_id,
            user_pseudo_id,
            platform,
            --  event_param

            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.string_value, NULL )) AS session_id
            

      FROM advanced.app_logs

      CROSS JOIN UNNEST(event_params) AS event_param

      WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18"
      GROUP BY ALL
)


--event_name + screen (필요한 이벤트만 조건 걸어서 사용)
  ,filter_event_and_concat_event_and_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
  WHERE event_name IN ("screen_view","click_payment")
  )


--step_number + COUNT
--CASE WHEN 사용
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 filter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
ORDER BY event_date

-- food_detail, search, search_result도 파악

 

 

  1. STRUCT 과 UNNEST 처음 접해보는 내용이라, 복습 필요.

  2. PIVOT 내용 중 ANY_VALUE는 데이터 양이 많고, 어떤 데이터들이 어떤 특성을 가지고 담겨있는지 정확하게 모른다면 활용하면 위험하겠다는 생각이 들었음.

 

 

 

 

sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

인프런 AI 인턴님의 프로필 이미지
인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다. 정용님.

강의에서 다룬 BigQuery의 ARRAY, STRUCT, UNNEST, PIVOT을 활용한 다양한 쿼리문 작성은 데이터 분석에서 매우 유용한 기술들입니다. 각 기능에 대한 이해를 돕기 위해 간단히 설명 드리자면:

  1. STRUCT와 UNNEST: STRUCT는 여러 필드를 갖는 구조체 타입이며, UNNEST를 사용하여 배열 데이터를 풀어내고 테이블 형식으로 변환할 수 있습니다. 이를 통해 각 요소에 개별적인 쿼리가 가능합니다.

  2. PIVOT: 특정 행/열 데이터를 변환하여 데이터의 요약과 비교를 쉽게 할 수 있습니다. PIVOT은 데이터 시각화를 위해 자주 사용됩니다.

질문에서 다뤄진 쿼리 문제들은 이러한 기능들을 잘 활용하여 다양한 데이터 형태를 효율적으로 변환하고 분석하는 연습을 할 수 있는 기회가 됩니다.

작성하신 내용 중 몇 가지 참고 링크가 도움이 될 것 같습니다:

  1. 구글 스프레드시트 사용 시 데이터 로그 설계에 대한 질문 - 데이터 로그 설계와 관련된 논의입니다.
  2. 퍼널 SQL 쿼리 작성하기 - 오류 해결 사례 - 퍼널 쿼리 작성 시 발생할 수 있는 오류들과 그 해결 방안에 대한 내용을 다루고 있습니다.
  3. 퍼널별 유저들의 새로운 세션 시작 횟수의 총합 - 쿼리 결과를 해석하고 활용하는 부분에 대한 재미있는 논의입니다.

이 자료들이 정용님의 학습에 큰 도움이 되길 바랍니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

정용님의 프로필 이미지
정용

작성한 질문수

전체 Q&A
질문하기