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

김세현님의 프로필 이미지
김세현

작성한 질문수

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

[바짝스터디 1주차 과제] ARRAY, STRUCT 연습 문제 / PIVOT 연습 문제 / 퍼널 쿼리 연습 문제

작성

·

42

0

ARRAY, STRUCT, UNNEST 연습 문제

 

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

SELECT
  title,
  genres  -- 오답: genres 배열을 그대로 선택하려 함.
FROM `advanced.array_exercises` AS ae
JOIN UNNEST(genres) AS genre;  -- 오답: UNNEST와 잘못된 JOIN 방식 사용

SELECT
  title,  -- 각 영화의 제목을 선택합니다. 이를 통해 각 장르가 어떤 영화에 속하는지 확인할 수 있습니다.
  genre   -- UNNEST를 통해 평면화한 개별 장르를 선택합니다.
FROM `advanced.array_exercises` AS ae  -- advanced.array_exercises 테이블에서 데이터를 가져옵니다.
CROSS JOIN UNNEST(genres) AS genre;  -- genres 배열을 평면화하여 각 장르를 개별 행으로 만듭니다.


genres라는 배열을 그대로 선택하려 했습니다.
genres는 ARRAY 자료형이기 때문에 이 상태로는 각 장르를 개별적으로 볼 수 없습니다.

UNNEST를 사용할 때 단순 JOIN을 사용했습니다. UNNEST를 통해 배열을 개별적인 행으로 변환할 때는 CROSS JOIN을 사용해야 합니다.

 

 


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

SELECT
  title,
  actors.actor,  -- 잘못된 접근 방식: 'actors'는 배열 형태이므로 직접 필드에 접근할 수 없음.
  actors.character  -- 잘못된 접근 방식: 'actors'의 필드에 바로 접근하려고 하였음.
FROM `advanced.array_exercises` AS ae
CROSS JOIN UNNEST(actors) AS actor;  -- 배열을 평면화하기 위해 UNNEST 사용, 하지만 이후 필드 접근 방식에서 실수가 있었음.
SELECT
  title,  -- 각 영화의 제목을 선택합니다.
  actor.actor,  -- 배우의 이름을 선택합니다. UNNEST를 통해 평면화한 결과입니다.
  actor.character  -- 배우가 맡은 배역을 선택합니다. UNNEST를 통해 평면화한 결과입니다.
FROM `advanced.array_exercises` AS ae  -- advanced.array_exercises 테이블에서 데이터를 가져옵니다.
CROSS JOIN UNNEST(actors) AS actor;  -- actors 배열을 평면화하여 각 배우와 배역 정보를 개별 행으로 만듭니다.


actors 배열의 각 필드 (actor, character)에 접근하기 위해 단순히 배열을 호출했습니다. 그러나 배열은 UNNEST를 통해 각 요소를 개별 행으로 확장하지 않으면 필드에 접근할 수 없습니다.
actors는 구조체(STRUCT)로 배열 안에 존재하기 때문에, 각 필드에 접근하려면 UNNEST 이후 명확한 필드명을 사용해야 했습니다.

  • actor.actor: UNNEST된 배열의 actor 필드에 접근하여 배우의 이름을 가져옵니다.

  • actor.character: UNNEST된 배열의 character 필드에 접근하여 각 배우의 배역 정보를 가져옵니다.

 

 

 


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

SELECT
  title,  -- 각 영화의 제목을 선택합니다. 영화별 배우와 배역, 그리고 장르 정보를 확인할 수 있습니다.
  actor.actor AS actor,  -- 배우의 이름을 선택합니다. UNNEST를 통해 평면화한 결과로 각 영화마다 배우의 이름을 가져옵니다.
  actor.character AS character,  -- 배우가 맡은 배역을 선택합니다. UNNEST를 통해 평면화한 결과로 각 영화마다 배역 정보를 제공합니다.
  genre  -- 영화의 장르를 선택합니다. UNNEST를 통해 평면화한 결과로 각 영화마다 해당하는 장르를 표시합니다.
FROM `advanced.array_exercises` AS ae  -- advanced.array_exercises 테이블에서 데이터를 가져옵니다.
CROSS JOIN UNNEST(actors) AS actor  -- actors 배열을 평면화하여 각 배우와 배역 정보를 개별 행으로 만듭니다.
CROSS JOIN UNNEST(genres) AS genre  -- genres 배열을 평면화하여 각 장르를 개별 행으로 만듭니다.
WHERE
  actor.actor = "Chris Evans"  -- 특정 배우의 이름을 기준으로 필터링합니다. 이 조건을 통해 Chris Evans가 출연한 영화만 선택합니다.
  AND genre = "Action"  -- 특정 장르를 기준으로 필터링합니다. Action 장르의 영화만 선택합니다.

  • UNNEST(actors) 및 UNNEST(genres): 각각의 배열을 평면화하여 필요한 정보에 접근하기 쉽게 만들어 줍니다. 평면화된 배열의 데이터를 각 필드별로 나누어 분석하고 이해하기가 쉬워집니다.

  • AS actor, AS genre: 평면화된 결과에 별칭을 부여하여 이후 쿼리에서 이를 명확하게 사용할 수 있도록 합니다. 별칭을 사용함으로써 코드의 가독성이 향상되고, 각 필드를 참조할 때 혼동을 줄일 수 있습니다.


  1. 앱 로그 데이터(app_logs)의 배열을 풀어주세요.

SELECT
  user_id, event_date, event_name, user_pseudo_id, ae.key, ae.value.string_value, ae.value.int_value
FROM `advanced.app_logs`
CROSS JOIN UNNEST(event_params) AS ae
WITH base AS (
  SELECT
    event_date,
    event_timestamp,
    event_name,
    event_param.key AS key,  -- event_params 배열의 key 값을 가져옵니다. 각 이벤트의 파라미터 키를 명확히 확인할 수 있도록 합니다.
    event_param.value AS value,  -- event_params 배열의 value 구조체를 가져옵니다. value에는 여러 필드가 포함될 수 있으므로 전체 구조체를 가져옵니다.
    event_param.value.string_value AS string_value,  -- value 구조체 내의 string_value 필드에 접근합니다. 문자열 형태의 파라미터 값을 직접 추출합니다.
    event_param.value.int_value AS int_value,  -- value 구조체 내의 int_value 필드에 접근합니다. 정수 형태의 값을 명확히 추출하여 분석합니다.
    user_id  -- 이벤트를 발생시킨 사용자 ID입니다. 사용자별 이벤트 참여를 분석하기 위해 필요합니다.
  FROM `advanced.app_logs`
  CROSS JOIN UNNEST(event_params) AS event_param  -- event_params 배열을 평면화하여 각 파라미터를 독립된 행으로 만듭니다.
  WHERE
    event_date = "2022-08-01"  -- 특정 날짜의 데이터를 선택합니다. 이 날짜에 발생한 이벤트만 필터링하여 분석합니다.
)

SELECT
  event_date,  -- 이벤트가 발생한 날짜를 선택합니다. 이를 통해 특정 날짜에 발생한 이벤트 정보를 확인할 수 있습니다.
  event_name,  -- 이벤트의 이름을 선택합니다. 이벤트 종류별로 데이터를 구분하여 분석할 수 있도록 합니다.
  COUNT(DISTINCT user_id) AS cnt  -- 중복되지 않는 사용자 수를 집계하여 이벤트별 사용자 수를 계산합니다. 각 이벤트에 참여한 고유 사용자의 수를 계산함으로써 이벤트의 인기도를 파악할 수 있습니다.
FROM base
GROUP BY event_date, event_name  -- 날짜와 이벤트 이름으로 그룹화하여 집계합니다. 이를 통해 각 날짜의 이벤트별 참여도를 명확히 알 수 있습니다.
ORDER BY cnt DESC;  -- 사용자 수를 기준으로 내림차순 정렬하여 가장 많은 사용자가 참여한 이벤트를 먼저 보여줍니다. 이를 통해 인기 이벤트를 한눈에 파악할 수 있습니다.






PIVOT 연습 문제

 

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

SELECT
  order_date,
  SUM(IF(user_id = 1, amount, 0)) AS user_1,  -- user_id가 1인 유저의 금액 합계를 해당 열로 표현합니다.
  SUM(IF(user_id = 2, amount, 0)) AS user_2,  -- user_id가 2인 유저의 금액 합계를 해당 열로 표현합니다.
  SUM(IF(user_id = 3, amount, 0)) AS user_3   -- user_id가 3인 유저의 금액 합계를 해당 열로 표현합니다.
FROM advanced.orders
GROUP BY order_date  -- 각 날짜(order_date)별로 그룹화하여 유저별 합계를 구합니다.
ORDER BY order_date;  -- 결과를 날짜 순으로 정렬합니다.

 

 

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

SELECT
  user_id,
  SUM(IF(order_date = DATE '2023-05-01', amount, 0)) AS `2023-05-01`,  -- 2023-05-01에 해당하는 주문 금액 합계
  SUM(IF(order_date = DATE '2023-05-02', amount, 0)) AS `2023-05-02`,  -- 2023-05-02에 해당하는 주문 금액 합계
  SUM(IF(order_date = DATE '2023-05-03', amount, 0)) AS `2023-05-03`,  -- 2023-05-03에 해당하는 주문 금액 합계
  SUM(IF(order_date = DATE '2023-05-04', amount, 0)) AS `2023-05-04`,  -- 2023-05-04에 해당하는 주문 금액 합계
  SUM(IF(order_date = DATE '2023-05-05', amount, 0)) AS `2023-05-05`   -- 2023-05-05에 해당하는 주문 금액 합계
FROM advanced.orders
GROUP BY user_id  -- 각 유저별로 그룹화하여 날짜별 주문 금액을 피벗 형태로 만듭니다.
ORDER BY user_id;  -- 결과를 user_id 순으로 정렬합니다.

서브쿼리를 사용하는 대신, orders 테이블에서 직접 필요한 데이터를 집계하는 방식으로 간소화합니다.



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

SELECT
  user_id,  -- 각 사용자 ID를 행으로 표시합니다.
  MAX(IF(order_date = DATE '2023-05-01', 1, 0)) AS `2023-05-01`,  -- 2023-05-01에 주문이 있으면 1, 없으면 0을 반환합니다.
  MAX(IF(order_date = DATE '2023-05-02', 1, 0)) AS `2023-05-02`,  -- 2023-05-02에 주문이 있으면 1, 없으면 0을 반환합니다.
  MAX(IF(order_date = DATE '2023-05-03', 1, 0)) AS `2023-05-03`,  -- 2023-05-03에 주문이 있으면 1, 없으면 0을 반환합니다.
  MAX(IF(order_date = DATE '2023-05-04', 1, 0)) AS `2023-05-04`,  -- 2023-05-04에 주문이 있으면 1, 없으면 0을 반환합니다.
  MAX(IF(order_date = DATE '2023-05-05', 1, 0)) AS `2023-05-05`   -- 2023-05-05에 주문이 있으면 1, 없으면 0을 반환합니다.
FROM `advanced.orders`
GROUP BY user_id  -- 각 사용자별로 그룹화합니다.
ORDER BY user_id;  -- 사용자 ID 순으로 결과를 정렬하여 표시합니다.

amount를 이용하여 MAX()를 사용한 부분에서 금액의 크기를 반환하게 됩니다. 하지만 이 문제에서는 금액이 아닌 주문 여부를 판별해야 합니다. 따라서 amount 대신 주문 여부를 확인하는 1이나 0을 사용해야 합니다.
반환 값으로 amount를 사용해 최대값을 찾는 것은 주문이 여러 번 발생했을 때 의미가 없습니다. 우리는 단순히 주문이 있었는지 여부만을 확인하고자 하므로, 1 또는 0을 반환하는 것이 필요합니다. 주문이 한 번이라도 있었다면 1을, 없었다면 0을 반환하는 형태가 되어야 합니다.

퍼널쿼리 연습문제

  1. 각 퍼널의 유저 수를 집계
    데이터 : 2022-08-01 ~ 2022-08-18

 

-- Step 1: 초기 데이터 추출 - 기본 테이블(base)을 생성합니다.
WITH base AS (
  SELECT
    event_timestamp,
    event_date,
    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.string_value, NULL)) AS session_id
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
  WHERE
    event_date BETWEEN "2022-01-01" AND "2022-12-31"
  GROUP BY event_timestamp, event_date, event_name, user_id, user_pseudo_id, platform  -- 필요한 필드만 명시적으로 그룹화합니다.
),
-- Step 2: 이벤트 이름과 화면 이름을 결합하여 새로운 형태로 가공합니다.
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 3: 퍼널 단계 정의 및 각 단계별 유저 수를 집계합니다.
SELECT
  event_name_with_screen,
  event_date,
  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  -- 중복 유저를 제거하기 위해 DISTINCT를 사용합니다.
FROM filter_event_and_concat_event_and_screen
GROUP BY event_name_with_screen, event_date, step_number  -- 필요한 열만 명시적으로 그룹화합니다.
HAVING step_number IS NOT NULL
ORDER BY event_date;


sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

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

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

BigQuery에서 ARRAY, STRUCT, UNNEST, 그리고 퍼널 분석 쿼리에 대한 실습을 진행하면서 겪으신 여러 문제들에 대해 답변을 드리겠습니다. 각각의 문제에서 어떤 부분이 잘못되었는지와 올바른 접근 방법에 대해 간략히 설명드리겠습니다.

ARRAY, STRUCT, UNNEST 문제

  1. 영화별 장르 UNNEST 문제:

    • genres는 인터프리터가 접근할 수 없는 배열이기 때문에, 배열을 개별적인 행으로 변환하는 UNNEST를 CROSS JOIN으로 사용하여 각각의 장르를 개별 행으로 보여주어야 합니다. 올바른 쿼리 예시처럼 CROSS JOIN UNNEST(genres) AS genre를 사용하면 됩니다.

  2. 배우와 배역 정보 출력 문제:

    • actors는 STRUCT 타입의 배열이므로 배열을 확장해주기 위해 UNNEST를 사용해야 하며, actor 및 character 필드에 접근하려면 별칭을 활용하여 actor.actor와 actor.character로 접근이 필요합니다. 올바른 방법을 사용하여 각 필드를 추출해야 합니다.

  3. 배우, 배역, 장르 한 Row에 보여주기 문제:

    • 이 문제에서는 여러 UNNEST 연산이 동시에 이루어져 데이터 중복이 발생할 수 있습니다. 따라서, CROSS JOIN UNNEST(actors) AS actor와 CROSS JOIN UNNEST(genres) AS genre를 올바르게 사용했습니다.

PIVOT 문제

  1. 유저별 주문 금액 합계 PIVOT 문제:

    • WHERE 조건 없이 유저 ID에 따라 숫자 값들을 피벗팅하여 열로 변환하였습니다. 날짜별로 그룹화한 후 해보시길 바랍니다.

  2. 주문 주문 여부 판단 문제:

    • 주문 여부를 판별하는 데 있어 MAX()와 IF()를 사용하는 방식으로 데이터가 단순한 샘플이 될 수 있어요. 주문이 있다는 것을 1, 없는 것을 0으로 표기하는 방식은 참고해보세요.

퍼널 쿼리 문제

  • 퍼널 각 단계의 유저 수 집계:
    • 여기서는 각 이벤트와 화면 이름을 결합하여 각 단계별 순서를 정의하고 그에 따라 사용자 수를 집계하도록 문제를 풀었습니다. WITH 구문을 활용해 데이터 전처리를 수행한 후 각 단계별로 사용자 수를 집계하는 것이 요령입니다.

이와 관련된 기존 답변이나 유사한 질문 및 솔루션은 아래와 같은 자료를 참고해보세요:
- 퍼널 쿼리 작성 시 발생할 수 있는 오류들과 해결 방안 사례
- 퍼널별 유저 수 집계 관련 SQL 쿼리 사례 및 피드백

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

김세현님의 프로필 이미지
김세현

작성한 질문수

전체 Q&A
질문하기