인프런 커뮤니티 질문&답변
작성한 질문수
[바짝스터디 1주차 과제] ARRAY, STRUCT 연습 문제 / PIVOT 연습 문제 / 퍼널 쿼리 연습 문제
작성
·
50
·
수정됨
0
1. ARRAY, STRUCT 연습문제
문제 1) array_exercise테이블에서 각 영화(title)별로 장르(genres)를 UNNEST 해서 보여주세요
SELECT
title,
genres
FROM `analystic-project.advanced.array_exercises` , UNNEST(genres) AS genres
;문제 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야합니다
SELECT
title,
actors.actor,
actors.character
FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors
;문제 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다
SELECT
title,
actors.actor,
actors.character,
genres
FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors, UNNEST(genres) genres
;문제 4) 앱 로그 데이터(app_logs) 배열 풀기
SELECT
user_id,
event_date,
event_name,
user_pseudo_id,
pr.key,
pr.value.string_value,
pr.value.int_value
FROM `analystic-project.advanced.app_logs` , UNNEST(event_params) AS pr
WHERE event_date = "2022-08-01"
LIMIT 1000
;2. PIVOT 연습문제 풀이
문제 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다
SELECT
order_date,
COALESCE(SUM(IF(user_id = 1, amount, null)),0) AS user_1,
COALESCE(SUM(IF(user_id = 2, amount, null)),0) AS user_2,
COALESCE(SUM(IF(user_id = 3, amount, null)),0) AS user_3
FROM advanced.orders
GROUP BY order_date
ORDER BY order_date
;문제 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다
SELECT
user_id,
COALESCE(SUM(IF(order_date = '2023-05-01', amount, null)),0) AS `2023-05-01`,
COALESCE(SUM(IF(order_date = '2023-05-02', amount, null)),0) AS `2023-05-02`,
COALESCE(SUM(IF(order_date = '2023-05-03', amount, null)),0) AS `2023-05-03`,
COALESCE(SUM(IF(order_date = '2023-05-04', amount, null)),0) AS `2023-05-04`,
COALESCE(SUM(IF(order_date = '2023-05-05', amount, null)),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로 처리합니다
SELECT
user_id,
MAX(IF(order_date = '2023-05-01' AND order_id is not null, 1, 0)) AS `2023-05-01`,
MAX(IF(order_date = '2023-05-02' AND order_id is not null, 1, 0)) AS `2023-05-02`,
MAX(IF(order_date = '2023-05-03' AND order_id is not null, 1, 0)) AS `2023-05-03`,
MAX(IF(order_date = '2023-05-04' AND order_id is not null, 1, 0)) AS `2023-05-04`,
MAX(IF(order_date = '2023-05-05' AND order_id is not null, 1, 0)) AS `2023-05-05`,
FROM advanced.orders
GROUP BY user_id
ORDER BY user_id
;문제 4)
user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?
WITH app_order_raw AS (
SELECT
user_id,
event_date,
event_name,
user_pseudo_id,
pr.key,
pr.value.string_value,
pr.value.int_value
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date = '2022-08-01'
)
SELECT
user_id,
event_date,
event_name,
user_pseudo_id,
MAX(IF(key = 'firebase_screen', string_value, null)) AS firebase_screen,
MAX(IF(key = 'food_id', int_value, null)) AS food_id,
MAX(IF(key = 'session_id', string_value, null)) AS session_id,
FROM app_order_raw
GROUP BY user_id, event_date, event_name, user_pseudo_id
;3. 퍼널분석
문제 1) 각 퍼널의 유저 수를 집계 / 데이터 기준: 2022-08-01 ~ 2022-08-18
WITH funnel_data_raw AS (
SELECT
event_date,
event_timestamp,
event_name,
user_id,
user_pseudo_id,
MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
)
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 END AS step_number,
COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2
ORDER BY 2
;문제 2) 일자별 퍼널 유저 수 집계
WITH funnel_data_raw AS (
SELECT
event_date,
event_timestamp,
event_name,
user_id,
user_pseudo_id,
MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
)
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 END AS step_number,
COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2,3
ORDER BY 1,3
;문제 3) 일자별 퍼널 유저 수 집계
형태를 PIVOT형태로 전환하기
WITH funnel_data_raw AS (
SELECT
event_date,
event_timestamp,
event_name,
user_id,
user_pseudo_id,
MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
), daily_funnel_user_count as (
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 END AS step_number,
COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2,3
ORDER BY 1,3
)
SELECT
event_date,
MAX(IF(step_number = 1, cnt, null)) AS `screen_view-welcome`,
MAX(IF(step_number = 2, cnt, null)) AS `screen_view-home`,
MAX(IF(step_number = 3, cnt, null)) AS `screen_view-food_category`,
MAX(IF(step_number = 4, cnt, null)) AS `screen_view-restaurant`,
MAX(IF(step_number = 5, cnt, null)) AS `screen_view-cart`,
MAX(IF(step_number = 6, cnt, null)) AS `click_payment-cart`,
FROM daily_funnel_user_count
GROUP BY ALL
ORDER BY 1
;4. 새롭게 배웠던 점
쿼리를 활용하여 PIVOT 형태로 데이터를 변환 할 수 있는 스킬을 습득함.
"GROUP BY ALL" 이라는 방식으로 전체 집계를 할 수 있다는 방법을 처음 알게 됨.
`` (백틱) 기호를 사용하면 특수문자 '-'와 한글까지 컬럼명으로 사용할 수 있다는 것을 처음 알게 됨.
실무에서 FROM절에 UNNEST() 함수 앞에 ',' 를 왜(Why) 써야되는지에 대한 궁금했는데, CROSS JOIN의 약자로 사용하고 있다는 것을 처음 알게 됨.
ARRAY와 STRUCT 구조의 차이점과 해당 데이터타입 구조의 데이터를 추출하기 위한 방법을 명확하게 알게 됨.
5. 느낀점
사소하지만 스킬적인 부분으로 새롭게 배웠던 부분은 따로 잘 정리해두어 업무에 바로 사용해볼 예정. 또한 데이터 탐색 방법으로 Funnel 사용하는데 있어, Open 퍼널과 Closed 퍼널 방식의 종류를 배울 수 있어 뿌듯했음.
답변 1
0
안녕하세요, 인프런 AI 인턴입니다. DA유치원님. BigQuery에서 ARRAY, STRUCT, 및 PIVOT 같은 자료형을 활용하여 데이터를 효율적으로 분석하는 것은 데이터 분석 스킬을 향상하는 데 큰 도움이 됩니다. 이러한 기능들을 이해하고 실습하는 과정은 다양한 데이터 시나리오에서 강력하게 활용될 수 있습니다.
각 문제에 대해 간략히 설명드리면:
ARRAY, STRUCT 문제
문제 1: 영화 별로 각각의 장르를 분리하여 표시하는 것으로, UNNEST 함수를 활용하여 ARRAY를 풀어헤치고 각각의 요소를 별도의 행으로 출력할 수 있습니다. 이를 통해 각 영화의 모든 장르를 확인할 수 있습니다.
문제 2: UNNEST를 통해 배우와 그들이 맡은 배역을 각각의 컬럼으로 분리하여 표현합니다. STRUCT를 분리하여 각각의 필드를 접근할 수 있도록 설계합니다.
문제 3: 배우, 배역, 장르가 한 행에 모두 나타나도록
CROSS JOIN구조를 사용하여, 각 요소가 가능한 모든 조합으로 결합되어 출력되도록 합니다.문제 4: 앱 로그 데이터에서
event_params등을 UNNEST하여, 각 이벤트의 상세한 정보(예: 파라미터들)를 개별적으로 출력할 수 있습니다.
혹시 이와 관련하여 더 상세한 설명이나 실제 쿼리 예제에 대한 정보를 원하신다면 아래의 유사 질문과 답변을 참고하실 수 있습니다:
퍼널 SQL 쿼리 작성하기 - 오류 해결 사례: 퍼널 분석을 위한 SQL 쿼리를 작성하면서 발생할 수 있는 오류와 그 해결 방식에 대하여 다룬 질문입니다.
구글 스프레드시트 사용 시 데이터 로그 설계에 대한 질문: 다양한 파라미터 설계 및 사용에 대한 논의가 포함된 질문으로, 복잡한 데이터 소스에서 유용하게 사용할 수 있는 정보가 포함되어 있습니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.