인프런 커뮤니티 질문&답변
작성한 질문수
[인프런 빅쿼리 빠짝스터디 1주차] ARRAY, STRUCT 연습 문제/ PIVOT 연습문제/ 퍼널 쿼리 연습문제
작성
·
50
0
연습문제
(1) ARRAY, STRUCT
-- 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 평면화
# 쿼리 계산 방법 : UNNEST
# 데이터의 기간 :
# 사용할 테이블 : array_exercises
# Join KEY :
# 데이터 특징 :
SELECT
title,
genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) AS genre;
-- 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다
# 쿼리를 작성하는 목표, 확인할 지표 : 영화별 배우와 배역 평면화
# 쿼리 계산 방법 : UNNEST
# 데이터의 기간 :
# 사용할 테이블 : array_exercises
# Join KEY :
# 데이터 특징 : 배우와 배역은 별도의 컬럼으로 나와야 합니다
SELECT
title,
actor.actor,
actor.character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor;
-- 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다
# 쿼리를 작성하는 목표, 확인할 지표 : 배우, 배역, 장르
# 쿼리 계산 방법 : UNNEST
# 데이터의 기간 :
# 사용할 테이블 : array_exercises
# Join KEY :
# 데이터 특징 : 한 Row에 배우, 배역, 장르가 모두 표시되어야 함
SELECT
title,
actor.actor,
actor.character,
genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor
CROSS JOIN UNNEST(genres) AS genre;
-- 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 앱 로그 데이터 평면화
# 쿼리 계산 방법 : UNNEST
# 데이터의 기간 :
# 사용할 테이블 : app_logs
# Join KEY :
# 데이터 특징 : event_params안에 ARRAY와 STRUCT 구조가 공존하고 있고 값이 string_value와 int_value 두 종류로 나뉨 확인 필요!!
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
WHERE
event_date = "2022-08-01";(2) PIVOT
-- 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다
# 쿼리를 작성하는 목표, 확인할 지표 :
# 쿼리 계산 방법 : MAX, IF, GROUP BY
# 데이터의 기간 :
# 사용할 테이블 : orders
# Join KEY :
# 데이터 특징 :
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 ALL
ORDER BY
order_date;
-- 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다
# 쿼리를 작성하는 목표, 확인할 지표 :
# 쿼리 계산 방법 : SUM, IF, GROUP BY
# 데이터의 기간 :
# 사용할 테이블 : orders
# Join KEY :
# 데이터 특징 :
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 ALL
ORDER BY
user_id;
-- 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다
# 쿼리를 작성하는 목표, 확인할 지표 :
# 쿼리 계산 방법 : MAX, IF, GROUP BY
# 데이터의 기간 :
# 사용할 테이블 : orders
# Join KEY :
# 데이터 특징 :
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 ALL
ORDER BY
user_id;
-- 연습문제 4) app_log를 pivot하기
# 쿼리를 작성하는 목표, 확인할 지표 : app_log 데이터 PIVOT 테이블로 변경
# 쿼리 계산 방법 : UNNEST, MAX, IF, GROUP BY
# 데이터의 기간 : 2022-08-01
# 사용할 테이블 : app_logs
# Join KEY :
# 데이터 특징 : event_params안에 ARRAY와 STRUCT 구조가 공존하고 있고 값이 string_value와 int_value 두 종류로 나뉨 확인 필요!!
WITH base AS(
SELECT
user_id,
event_date,
event_name,
event_timestamp,
user_pseudo_id,
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 avdanced.app_logs
CROSS JOIN UNNEST (event_params) AS event_param
WHERE
event_date ='2022-08-01'
GROUP BY ALL
)
SELECT
event_date,
COUNT(user_id) AS cnt
FROM base
WHERE
event_name = 'click_cart'
GROUP BY ALL;(3) 퍼널 ( Funnel )
# 쿼리를 작성하는 목표, 확인할 지표 : screen_view-welcome, screen_view-home, screen_view-food_category, screen_view-restaurant, screen_view-cart, click_payment-cart 순서대로 step_number를 지정하고 퍼널 분석하기
# 쿼리 계산 방법 : MAX, IF, UNNEST, CASE WHEN
# 데이터의 기간 : 2022-08-01 ~ 2022-08-18
# 사용할 테이블 : app_logs
# Join KEY :
# 데이터 특징 : event_params안에 ARRAY와 STRUCT 구조가 공존하고 있고 값이 string_value와 int_value 두 종류로 나뉨 확인 필요!!
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 fire_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
), fiter_event_and_concat_event_and_screen AS (
-- event_name + screen (필요한 이벤트만 WHERE 조건에 걸어서 사용)
SELECT
* EXCEPT(event_name, fire_screen, event_timestamp),
CONCAT(event_name, "-", fire_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을 사용해 숫자 지정
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 fiter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
WHERE
user_pseudo_id = "1350836585.3421064109"
-- 일자별
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 fiter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
ORDER BY event_date;배운점
ARRAY와 STRUCT 라는 조금은 생소할 수 있는 데이터 타입과 이를 어떻게 하면 평면화로 풀고 어떻게 데이터를 처리할 수 있을지를 조금은 알 수 있었고
BigQuery로 생각보다 많은 부분을 할 수 있구나 생각이 들면서 이를 프로젝트에 연결시키면 어떨까 하는 재밌는 고민이 생긴거 같다
BIgQuery를 배우고 싶은데 어떤 데이터로 어떻게 다뤄야 할지 막막했던 찰라에 많은 것을 배울 수 있던 1주차 였다
답변 1
0
안녕하세요, 인프런 AI 인턴입니다. 주재환님.
BigQuery에서 ARRAY나 STRUCT를 활용한 데이터 분석은 처음 접하는 사용자를 위한 좋은 학습 주제가 되며, 이를 바탕으로 퍼널 분석과 같은 복잡한 데이터 구조도 다룰 수 있게 됩니다. 아래의 링크를 통해 ARRAY, STRUCT, PIVOT, 퍼널 쿼리 활용에 대한 유사한 질문과 답변을 확인하실 수 있습니다. 주재환님의 스터디에 큰 도움이 되길 바랍니다.
BigQuery에서 ARRAY, STRUCT를 사용하는 방법에 대한 기본 개념
- ARRAY와 STRUCT를 활용한 간단한 데이터 변환 예제를 확인할 수 있습니다.
- ARRAY와 STRUCT를 활용한 간단한 데이터 변환 예제를 확인할 수 있습니다.
-
- PIVOT 구문을 통해 데이터를 효율적으로 전환할 수 있는 방법에 대한 설명이 제공됩니다.
- PIVOT 구문을 통해 데이터를 효율적으로 전환할 수 있는 방법에 대한 설명이 제공됩니다.
-
- 퍼널 분석에 있어 유의해야 할 데이터 처리 방법과 SQL 쿼리 개선 사항이 설명되어 있습니다.
- 퍼널 분석에 있어 유의해야 할 데이터 처리 방법과 SQL 쿼리 개선 사항이 설명되어 있습니다.
이 자료들이 주재환님의 학습과 실무 적용에 많은 도움이 되기를 바랍니다. 데이터 처리 및 분석에 대한 새로운 시각과 접근 방식을 배워가시는 데에 있어, 지속적인 학습과 연습이 중요합니다. 추가적으로 궁금한 점이 있으시면 언제든 질문해 주세요. 곧 지식공유자께서 더 상세히 답변해 주실 것입니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.