인프런 커뮤니티 질문&답변
작성한 질문수
[빠짝스터디 1주차 과제] ARRAY, STRUCT 연습 문제/ PIVOT 연습문제/ 퍼널 쿼리 연습 문제
작성
·
40
0
ARRAY, STRUCT 연습문제
-- 1)영화별 장르를 unnest해서 보여줘라.
SELECT
title,
genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) AS genre;
--2) 영화별 배우와 배역을 보여줘라. 배우와 배역은 별도의 컬럼으로 나와야 한다.
SELECT
title,
ac.actor,
ac.character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS ac;
--3. 영화별로 배우, 배역, 장르를 출력해라.
SELECT
title,
actor.actor,
actor.character,
genre
FROM `advanced.array_exercises`
CROSS JOIN UNNEST(actors) as actor
CROSS JOIN UNNEST(genres) as genre;
--4. 앱 로그 데이터의 배열을 풀어라
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
PIVOT 연습문제
-- 1. 유저별 주문금액의 합계를 poviot해라.
-- 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;
-- 2. 날짜별 유저들의 주문금액의 합계를 pivot해라.
-- 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. 사용자별, 날짜별 주문이 있다면 1, 없다면 0으로 pivot해라.
-- 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
-- ORDER BY user_id;
--4. key값을 column으로 pivot해라
SELECT
user_id,
event_date,
event_name,
user_pseudo_id,
IF(key = 'food_id', IF(string_value IS NOT NULL, string_value, CAST(int_value AS STRING)), NULL) AS food_id
FROM advanced.app_logs_unnest # app_logs 테이블을 unnest한 결과는 자주 쓰일 듯 싶어 app_logs_unnest 테이블을 따로 생성했다.
WHERE event_name = 'click_cart'
AND event_date = '2022-08-01';
기억할 것
IF 조건문을 사용할 때, TRUE일때의 값과 FALSE일때의 값의 데이터 타입이 같아야 한다.
PIVOT 연습문제 4번을 풀 때 food_id를 PIVOT하는 과정에서 IF(key = 'food_id', IF(string_value IS NOT NULL, string_value, int_value AS STRING), NULL)와 같이 표현했었다.KEY값에 따라 string_value와 int_value중 타입에 맞는 하나의 컬럼에만 값이 있었고, 처음에는 이를 명시적으로 적기보다 범용적으로 사용될 수 있도록 하는게 더 좋지 않을까 싶었다. 값이 있는 컬럼의 값을 사용하자는 의도로 IF(string_value IS NOT NULL, string_value, int_value AS STRING)라고 표현했고 다음과 같은 에러를 만났다.
No matching signature for function IF for argument types: BOOL, STRING, INT64. Supported signature: IF(BOOL, ANY, ANY) at [55:23]
에러를 피하고자 CAST를 이용했는데 데이터 타입을 억지로 바꾸기보다는(추후에 숫자형 데이터로 대소 비교를 한다던지 나열을 한나던지의 상황이 있을 수 있으니..) 명시적으로 표현하는 것을 마냥 피할것은 아니겠다는 생각을 했다.특별한 문자(예약어, 숫자)를 ALIAS로 설정하려면 back tick(`)으로 감싸줘야한다.
퍼널 쿼리 연습문제
-- 퍼널 별 유저 수 집계(2022-08-01 ~ 2022-08-18)
-- welcome -> home -> good category -> restaurant -> cart -> 주문하기 클릭
/*
event_data | event_name_with_screen | stemp_number | cnt 의 컬럼 형태로 만들것
*/
-- 처음 작성했던 쿼리
-- SELECT
-- event_date,
-- CASE
-- WHEN event_name = 'screen_view' AND string_value = 'welcome' THEN CONCAT(event_name, '-', string_value)
-- WHEN event_name = 'screen_view' AND string_value = 'home' THEN CONCAT(event_name, '-', string_value)
-- WHEN event_name = 'screen_view' AND string_value = 'food_category' THEN CONCAT(event_name, '-', string_value)
-- WHEN event_name = 'screen_view' AND string_value = 'restaurant' THEN CONCAT(event_name, '-', string_value)
-- WHEN event_name = 'screen_view' AND string_value = 'cart' THEN CONCAT(event_name, '-', string_value)
-- WHEN event_name = 'click_payment' AND string_value = 'cart' THEN CONCAT(event_name, '-', string_value)
-- END AS event_name_with_screen,
-- CASE
-- WHEN event_name = 'screen_view' AND string_value = 'welcome' THEN 1
-- WHEN event_name = 'screen_view' AND string_value = 'home' THEN 2
-- WHEN event_name = 'screen_view' AND string_value = 'food_category' THEN 3
-- WHEN event_name = 'screen_view' AND string_value = 'restaurant' THEN 4
-- WHEN event_name = 'screen_view' AND string_value = 'cart' THEN 5
-- WHEN event_name = 'click_payment' AND string_value = 'cart' THEN 6
-- END AS step_number,
-- COUNT(event_date) AS cnt
-- FROM `advanced.app_logs_unnest`
-- WHERE
-- event_date BETWEEN '2022-08-01' AND '2022-08-18'
-- GROUP BY ALL
-- ORDER BY event_date, step_number
-- =============================================
-- 정석 쿼리
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 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.int_value, NULL)) AS session_id
FROM `advanced.app_logs`
CROSS JOIN UNNEST(event_params) AS event_param
-- WHERE
-- event_date = "2022-08-01"
-- event_date BETWEEN "2022-08-01" AND "2022-08-18"
-- AND
-- event_param.key IN ("screen_view", "click_payment")
-- AND
-- event_param.value.string_value IN ("welcome", "home", "food_category", "restaurant", "cart")
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_date BETWEEN "2022-08-01" AND "2022-08-18"
AND
event_name IN ("screen_view", "click_payment")
AND
firebase_screen IN ("welcome", "home", "food_category", "restaurant", "cart")
)
SELECT
event_date,
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, step_number기억할 것
각 컬럼에 어떤 값이 있는지 잘! 확인하자.
event_name_with_screen 부분을 CASE WHEN으로 처리하며 '이게 진정 맞을까......' 싶긴 했었었다. 왜인지 초반에 컬럼값 확인할 때 WHERE 절에 IN 구문으로 확인했을때 원하던 결과로 나오지 않아 CASE WHEN으로 직접 처리했었는데.. 실수였다. (아마 string_value만 IN연산으로 확인하고 섣부른 판단을 했던게 아닐지 싶다.)
CASE WHEN 구문에서 전체를 포함하도록 조건을 구성하지 않으면 WHEN에 해당하지 않는 부분은 NULL값으로 생성된다.
처음 쿼리를 짜고 CNT 컬럼의 값을 확인했을 때 event_name_with_screen과 step_number에 왜 NULL값이 있는지 당황스러웠다. 이미 있는 데이터에 새 컬럼을 만든 것이니 조건에 해당되지 않는 부분은 값이 없어 NULL로 남아있다는 사실...!DATETIME 함수를 이용하여 "Asia/Seoul" 처리하기
새까맣게 잊고있었다. 까먹지 말자~!항상 데이터를 뽑아 어떤 내용을 확인하고 싶은 건지를 생각하자.
문제에 맞는 쿼리를 짜고 작성된 쿼리의 결과가 잘 나오는지에만 급급했던 거 같다. 이 강의를 듣는 목적은 데이터 분석에 대한 감 잡기!지 쿼리 잘 짜기!는 아니었으니까..!(물론 SQL 실력 향상은 필요한 부분..!!) 스스로 고민해보는 시간을 꼭! 갖도록 해보자.
답변 1
0
안녕하세요, 인프런 AI 인턴입니다. inini님.
BigQuery에서 ARRAY, STRUCT, PIVOT을 활용한 다양한 문제를 해결하시는 과정은 매우 유익합니다. 이러한 작업을 통해 데이터 구조에 대한 깊은 이해와 쿼리 작성 능력을 길러나가신 것 같네요. 특히 퍼널 분석에서는 데이터의 흐름을 파악하고, 유저 행동을 이해하는 데 있어 중요한 역할을 합니다.
아래에 유사한 문제 해결과 관련된 정보를 제공할 수 있는 게시물을 추천드릴게요.
관련 질문 및 답변
구글 스프레드시트 사용 시 데이터 로그 설계에 대한 질문
- 이 질문에서는 데이터 로그의 구조 설계와 다양한 파라미터의 활용 방법에 대해 논의합니다. 퍼널 분석에서도 데이터 설계와 파라미터 사용은 중요하므로 주의 깊게 참고하시면 도움이 될 것입니다.
- 이 질문에서는 데이터 로그의 구조 설계와 다양한 파라미터의 활용 방법에 대해 논의합니다. 퍼널 분석에서도 데이터 설계와 파라미터 사용은 중요하므로 주의 깊게 참고하시면 도움이 될 것입니다.
-
- 퍼널 쿼리 작성 시 발생할 수 있는 일반적인 오류와 그 해결 방안에 대한 내용을 다룹니다. 쿼리 작성 중에 발생하는 오류에 대처하는 데 유용할 것입니다.
- 퍼널 쿼리 작성 시 발생할 수 있는 일반적인 오류와 그 해결 방안에 대한 내용을 다룹니다. 쿼리 작성 중에 발생하는 오류에 대처하는 데 유용할 것입니다.
Array 및 Struct 데이터를 다루며 UNNEST와 PIVOT을 활용하는 것은 데이터를 다차원적으로 분석할 수 있게 해줍니다. 특히, 퍼널 쿼리에서는 이벤트의 순차적인 흐름을 잘 표현하기 위해 CASE WHEN 절이나 IF 문을 적절히 사용하여 이벤트 흐름을 구조화합니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.