[빠짝스터디 1주차 과제] ARRAY, STRUCT 연습 문제/ PIVOT 연습문제/ 퍼널 쿼리 연습 문제
120
작성한 질문수 1
[PART 1] ARRAY, STRUCT 연습문제
Q1. array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요.
-- CROSS JOIN UNNEST(ARRARY_COKUMN) AS 새로운 이름 -> 이후 SELECT 절에서 새로운 이름만 포함하여 쿼리를 실행하여 평면화 가능
SELECT
title,
-- genres,
genre
FROM advanced.array_exercises AS ae
CROSS JOIN UNNEST(genres) AS genre;
Q2. array_exercise 테이블에서 각 영화(title)별로 배우(actors)와 배역(character)을 보여주세요 배우와 배역은 별도의 컬럼으로 나와야 합니다.
SELECT
title,
aa.actor,
aa.character
FROM advanced.array_exercises AS ae
CROSS JOIN UNNEST(actors) AS aa;
Q3. array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다.
SELECT
title,
aa.actor,
aa.character,
gg AS genre
FROM advanced.array_exercises AS ae
CROSS JOIN UNNEST(actors) AS aa
CROSS JOIN UNNEST(genres) AS gg;
Q4. 앱 로그 데이터(app_logs)의 배열을 풀어주세요.
SELECT
event_date,
event_timestamp,
event_name,
ep.key,
ep.value.string_value,
ep.value.int_value,
user_id,
user_pseudo_id,
platform
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS ep
WHERE
event_date = "2022-08-01";
[PART 2] PIVOT 연습문제
Q1. 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;
Q2. 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;이렇게 쿼리를 짜고 실행해보니 다음과 같은 오류 문구가 나옴
Syntax error: Unexpected integer literal "2023" at [3:52]
SELECT 문에서 AS 다음에 정의한 새로운 컬럼 명칭에 오류가 있는 것 같은데, “ “ 로 감싸도 오류가 나오고 + 아예 AS 를 빼고 실행했더니 f0/f1/f2 와 같은 임의의 컬럼명이 지정됨
-- 강의를 듣고 고친 쿼리
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;alias 로 영어가 아닌 컬럼의 이름을 새로 지정할 때에는, 반드시 backtick(`) 으로 감싸줘야 한다는 점!
(c.f.) MAC 에서 backtick 은 영문인 상태로 ₩ 단축키를 누르면 나온다
강의에서는 SUM 함수가 아닌 MAX 함수 or ANY_VALUE 함수로 감싸서 쿼리를 작성해주신 점 확인
강의 진행 시에는 IF 문으로 먼저 데이터를 확인하고 user_id x order_date 별로 1개의 데이터만 있다는 점을 중간확인 했기 때문
단, 실무 상으로는 user_id 와 order_date 가 무수히 많거나 or 데이터 양이 너무 많아 중간 조회를 할 수 없는 상황도 있기 때문에 → 이런 경우에는 MAX 로 가져가는 것이 안전하지 않을까? 하는 생각도 들었음
데이터를 보고자 하는 목적이 “각 user_id x order_date 별로 주문금액의 합산” 을 보기 위함이었기 때문!
Q3. orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다.
-- 처음 시도에 짠 쿼리
SELECT
user_id,
IF((SUM(IF(order_date = "2023-05-01", amount, NULL))) IS NOT NULL, 1, 0) AS `2023-05-01`,
IF((SUM(IF(order_date = "2023-05-02", amount, NULL))) IS NOT NULL, 1, 0) AS `2023-05-02`,
IF((SUM(IF(order_date = "2023-05-03", amount, NULL))) IS NOT NULL, 1, 0) AS `2023-05-03`,
IF((SUM(IF(order_date = "2023-05-04", amount, NULL))) IS NOT NULL, 1, 0) AS `2023-05-04`,
IF((SUM(IF(order_date = "2023-05-05", amount, NULL))) IS NOT NULL, 1, 0) AS `2023-05-05`,
FROM advanced.orders
GROUP BY
user_id;SUM 과 IF 문으로 먼저 쿼리를 짜서 user_id 별 합산값을 확인하고 → 합산값이 NULL 이 아닌 경우에만 1을 다시 값으로 지정할 수 있도록 IF 문으로 감싸둠
이렇게 쿼리를 짜도 결과값은 동이하게 나오지만, SELECT 문이 길어진 것 같아서 좀 더 효율적으로 쿼리를 짤 수 있는 방법은 없을지 고민되었음 ㅠ
-- 강의를 듣고 고친 쿼리
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;IF 문 안에 들어있는 TRUE 조건이었던 amount 를 1로 바꿔주면 간단히 해결되었을 문제!
IF 문 안에 TRUE 일 때 값이 항상 특정 컬럼이 아니라 1이라고도 할 수 있다는 것을 보여주기 위한 문제
다만, PIVOT 2번 문제에서 언급했던 바와 같이 만약 user_id x order_date 별로 1개의 값만 존재하는 것이 아니라 여러 값이 존재했더라면 → 3번 쿼리에서 MAX를 썼을 때와 SUM 을 썼을 때의 결과값은 달라졌을 것이라 생각함
만약 user_id x order_date 별로 N개의 값이 존재했더라면 → SUM 함수로 감쌌을 때 1의 값이 x N개 합산되어서 나왔을 것이기 때문
따라서, 3번 문제의 경우 1 혹은 0 2개의 값으로만 표현해야 했기 때문에, SUM 이 아닌 MAX 함수로 쿼리를 작성하는 것이 필요하다고 생각함
단, 만약 “횟수” 를 알고 싶었다면 기존대로 SUM 함수를 사용하면 됨!
Q4. 앱로그 데이터 배열 PIVOT 하기 → user_id = 32888 이 카트 추가하기(click_cart)를 누를 때 어떤 음식 (food_id)을 담았는지 구해주세요. key 를 Column 으로 두고, string_value 나 int_value를 Column의 값으로 설정해서 풀어주세요.
SELECT
event_date,
event_timestamp,
event_name,
user_id,
user_pseudo_id,
MAX(IF(ep.key = "firebase_screen", ep.value.string_value, NULL)) AS firebase_screen,
MAX(IF(ep.key = "food_id", ep.value.int_value, NULL)) AS food_id,
MAX(IF(ep.key = "session_id", ep.value.string_value, NULL)) AS session_id
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS ep
GROUP BY ALL;
[PART 3] 퍼널 연습문제
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 = "2022-08-01"
event_date BETWEEN "2022-08-01" AND "2022-08-18"
GROUP BY ALL
), filter_event_and_concat_event_and_screen AS (
-- event_name + screen (필요한 이벤트만 WHERE 조건에 걸어서 사용)
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
-- step_number : 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
답변 1
0
안녕하세요, 인프런 AI 인턴입니다. 이주영님.
BigQuery에서 ARRAY, STRUCT, PIVOT 등 다양한 SQL 연습 문제를 풀면서 실제 데이터 분석에 어떻게 활용할 수 있는지 학습하는 경험은 매우 중요합니다. 이 과정에서 발생할 수 있는 오류와 그 해법 및 수행되야 할 작업을 이해한다면, 더욱 효과적인 데이터 분석을 수행할 수 있을 것입니다.
ARRAY 및 STRUCT 연습문제 관련:
UNNEST 사용: ARRAY 데이터를 UNNEST하여 각 배열 요소를 개별 레코드로 분리함으로써 데이터와의 상호작용이 더 용이해집니다. 예를 들어, 영화 데이터에서 장르를 UNNEST한 후 영화 제목과 함께 나열하여 간단히 장르는 영화별로 나열할 수 있습니다.
배우와 배역 표시: 배열에서 개인 요소로 데이터를 변환하여 다양한 속성을 다른 컬럼에서 표시할 수 있습니다.
CROSS JOIN UNNEST(actors)를 사용하면, 각 평가 항목에 대해 개별 레코드를 작성할 수 있습니다.
PIVOT 연습문제:
일별로 지정된 포맷에 따라 유저와 일정에 기반해 데이터를 피봇팅하는 것은 데이터를 더 명확하게 볼 수 있게 도와줍니다. 특히 특정 문자열 포맷의 날짜 등을 사용해야 할 때는 백틱()을 사용하여 쿼리 오류를 방지하실 수 있습니다. PIVOT 연습문제에서는 MAX 함수 대신 SUM 등을 사용한 이유에 대해 고민하는 것이 중요합니다. 양쪽을 비교하여 각 상황에 맞는 사용 방법을 학습하는 것이 좋습니다.
이 두 가지 자료는 특히 BigQuery와 관련된 다양한 쿼리 문제를 해결하는 데 유용할 것입니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.
최종 과제 제출
0
82
3
BigQuery 활용편 18강 질문있습니다!
0
84
1
리텐션 공부하다가 궁금한게 생겨 질문드립니다
0
83
2
안녕하세요 강사님 코호트 쿼리 공부하다가 의문점이 생겨서 문의드립니다
0
75
2
biquery 테이블 생성 오류 이슈
0
55
2
동일하게 쿼리를 작성했는데 화면과 다른 값이 나옵니다
0
73
2
[과제] 퍼널 PIVOT 테이블 작성하기
0
67
2
array 등
0
63
2
N day 리텐션 쿼리 관련 질문
0
63
2
이동평균 계산 시 order by 기본값은 뭔가요?
0
69
2
윈도우 연습문제 1번 질문
0
65
1
user_id에 NULL이 나오는데 정상인가요?
0
72
2
3-13 리텐션 과제 제출
0
94
2
최종 과제 제출
0
130
3
weekly retention 구하기 과제
0
90
2
1-9. 피벗 쿼리 작성
0
81
2
app_logs 테이블 생성 문제
0
73
2
Weekly Retention 구하기 완성하였습니다.
0
77
2
[과제] 퍼널 쿼리(피벗테이블 적용) 작성 완료
0
106
2
3-7 Weekly, Monthly Retention 쿼리 작성
0
92
2
정성 데이터 분석 방법 문의
0
165
1
최종 과제 제출
0
108
3
1-6 예시 문제 풀이
0
69
2
최종과제 제출
0
145
2