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

이은경 - 빠짝스터디 1주차 과제 제출합니다.

-- ARRAY_STRUCT 연습 문제
-- 연습문제 데이터 생성
CREATE OR REPLACE TABLE advanced.array_exercises as
SELECT movie_id, title, actors, genres
FROM(
  SELECT 
    1 as movie_id,
    'Avengers:Endgame' as title,
    ARRAY<STRUCT<actor STRING, character STRING>>[
      STRUCT('Robert Downey Jr.','Tony Stark'),
      STRUCT('Chris Evans','Steve Rogers')
    ] as actors, 
    ARRAY<STRING>['Action','Adventure','Drama'] as genres
    UNION ALL
    SELECT
      2,
      'Inception',
      ARRAY<STRUCT<actor STRING, character STRING>>[
        STRUCT('Leonardo Dicaprio','Cobb'),
        STRUCT('Joseph Gordon-Levitt','Arthur')
    ], 
    ARRAY<STRING>['Action','Adventure','Sci-Fi']
    UNION ALL
    SELECT
      3,
      'The Dark Knight',
      ARRAY<STRUCT<actor STRING, character STRING>>[
        STRUCT('Christian Bale','Bruce Wayne'),
        STRUCT('Heath Ledger','Joker')
    ], 
    ARRAY<STRING>['Action','Crime','Drama']

);

-- 연습문제 1) array_exercises table에서 title 별로 영화 genres를 UNNEST해서 보여주세요.
# UNNEST(ARRAY_COLUMN) AS 새로운 이름
# SELECT 절에서 새로운 이름으로 사용한다. 기존의 ARRAY_COLUMN은 사용하지 않는다.
SELECT
  a.title, genre
FROM
  advanced.array_exercises as a
CROSS JOIN
  UNNEST(genres) as genre;


-- 연습문제 2) array_exercises table에서 title 별로 배우(actor)와 배역(character)을 UNNEST해서 보여주세요. 배우와 배역은 별도의 column으로 나와야 합니다.
SELECT a.title, person.actor as actor, person.character as character
FROM 
  advanced.array_exercises as a
CROSS JOIN
    UNNEST(actors) as person;


-- 연습문제 3) array_exercises table에서 title 별로 배우(actor), 배역(character), 장르를 한 row에 표시되도록 출력하세요.
# CROSS JOIN 하면 데이터의 중복이 어느 정도 발생, 어쩔 수 없는 이슈이다.
SELECT
  a.title, person.actor as actor, person.character as character, genre
FROM
  advanced.array_exercises as a
CROSS JOIN
  UNNEST(actors) as person
CROSS JOIN
  UNNEST(genres) as genre;


-- 3)번 문제 관련 추가
# 3) 결과를 기준으로, 조건문을 사용하고 싶다.
# "WHERE"를 바로 사용하면 error 발생 : 실행 순서가 FROM -> JOIN -> SELECT
# actors -> UNNEST(actors) : STRUCT<actor, character>
# genres -> UNNEST(genres) : STRING
SELECT
  a.title, person.actor as actor, person.character as character, genre
FROM
  advanced.array_exercises as a
CROSS JOIN
  UNNEST(actors) as person
CROSS JOIN
  UNNEST(genres) as genre
WHERE
  person.actor='Chris Evans' -- 그냥 actor 사용하면 error 발생함
  and
  genre='Action';


-- 연습문제 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요
SELECT
  a.user_id, a.event_date, a.event_name, a.user_pseudo_id, b.key as key,
  b.value.string_value as string_value, b.value.int_value as int_value
FROM
  advanced.app_log as a
CROSS JOIN
  UNNEST(event_params) as b;

-- 4)번 문제 관련, 추가 사항(일 별, 사용자 수 집계)
WITH base as(
  SELECT
  a.user_id, a.event_date, a.event_name, a.user_pseudo_id, b.key as key,
  b.value.string_value as string_value, b.value.int_value as int_value
FROM
  advanced.app_log as a
CROSS JOIN
  UNNEST(event_params) as b
)

SELECT
  event_date,count(user_id) as cnt
FROM
  base
GROUP BY
  event_date
ORDER BY
  event_date;


#############################
-- PIVOT 연습문제

-- 연습문제 1) orders 테이블에서 유저(user_id) 별로 주문 금액(amount)의 합게를 PIVOT 해주세요.
-- 날짜(order_date)를 행(Row)으로, user_id를 열로 만들어야 합니다.
SELECT
  order_date,
  MAX(IF(user_id=1,amount,0)) as user_1,
  MAX(IF(user_id=2,amount,0)) as user_2,
  MAX(IF(user_id=3,amount,0)) as user_3
FROM
  advanced.orders
GROUP BY
  order_date
ORDER BY
  order_date;


-- 연습문제 2) orders 테이블에서 날짜(order_date) 별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요.
-- user_id를 행으로, order_date를 열으로 만들어야 합니다.
# column name을 어떻게 지정? -- backtick(`) 사용

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) orders 테이블에서 사용자(user_id) 별, 날짜(order_date) 별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행으로, order_date를 열로 만들고 주문을 많이 해도 1로 처리합니다.
# column name을 어떻게 지정? (2번 문제와 연관) -- backtick(`) 사용
SELECT
  user_id,
  IF(SUM(IF(order_date='2023-05-01',amount,0))>0,1,0) as `2023-05-01`,
  IF(SUM(IF(order_date='2023-05-02',amount,0))>0,1,0) as `2023-05-02`,
  IF(SUM(IF(order_date='2023-05-03',amount,0))>0,1,0) as `2023-05-03`,
  IF(SUM(IF(order_date='2023-05-04',amount,0))>0,1,0) as `2023-05-04`,
  IF(SUM(IF(order_date='2023-05-05',amount,0))>0,1,0) as `2023-05-05`
FROM
  advanced.orders
GROUP BY
  user_id
ORDER BY
  user_id;


-- 연습문제 3번 다른 풀이
-- 특정 column 대신 "1"을 사용할 수 있다.(유무에 따라서)
-- 만약, 횟수를 구해야 할 경우에는 MAX대신 SUM을 사용하면 된다.
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) 앱 로그 데이터 배열 PIVOT 하기
WITH example as (
  SELECT
    a.user_id, a.event_date, a.event_timestamp, a.event_name, a.user_pseudo_id,
    b.key as key, b.value.string_value as string_value,
    b.value.int_value as int_value
  FROM
    advanced.app_log as a
  CROSS JOIN
    UNNEST(event_params) as b
  WHERE
    event_date='2022-08-01'
)

SELECT
  user_id, event_date, event_timestamp, event_name, user_pseudo_id,
  ANY_VALUE(IF(key='firebase_screen',string_value,NULL)) as firebase_screen,
  ANY_VALUE(IF(key='food_id',int_value,NULL)) as food_id,
  ANY_VALUE(IF(key='session_id',string_value,NULL)) as session_id
FROM
  example
GROUP BY ALL
ORDER BY
  event_date, event_name;


-- 퍼널 SQL 연습문제
-- 데이터 PIVOT 한 형태로 변환(시각화 용이)
# with 구문으로 만든 데이터들을 저장하는 방법은...?
with base_data as(
  SELECT
    event_date, event_timestamp, event_name, user_id,
    user_pseudo_id,platform,
    MAX(IF(event_params.key="firebase_screen",event_params.value.   string_value,NULL)) as firebase_screen,
    MAX(IF(event_params.key="food_id",event_params.value.int_value,NULL)) as food_id,
    MAX(IF(event_params.key="session_id",event_params.value.string_value,NULL)) as session_id
  FROM
    `advanced.app_log`
  CROSS JOIN
    UNNEST(event_params) as event_params
  WHERE
    event_date between '2022-08-01' and '2022-08-18'
  GROUP BY ALL
), filter_event_and_concat_event_and_screen as(
  -- event_name + screen (필요한 이벤트만 가져올 필요가 있음)
  SELECT
    * EXCEPT(event_name, firebase_screen, food_id, event_timestamp),
    CONCAT(event_name,"-", firebase_screen) as event_name_with_screen,
    datetime(timestamp_micros(event_timestamp),"Asia/Seoul") as event_datetime
  FROM
    base_data
  WHERE
    event_name in ("screen_view","click_payment")
), funnel_data 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
    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
)

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
  funnel_data
GROUP BY ALL
ORDER BY event_date;

• 느낀 점

  • 새로운 자료형은 늘 어렵다...

  • 퍼널 분석의 큰 흐름을 배울 수 있어서 좋았습니다!

    • 의사 결정 관련 부분은 정말 많은 도움이 될 것 같습니다.

  • connected sheet 기능 신기하네요..