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

--연습문제
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 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요
select
    title
    , genres_new
from advanced,.array_exercise AS a, UNNEST(genres) as genres_new

-- 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다
select
    title
    , actors_new.actor
    , actors_new.character
from advanced,.array_exercise AS a, UNNEST(actors) as actors_new


--3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다
--방법 1
with gen as (
    select
        title
        , genres_new
    from advanced,.array_exercise AS a, UNNEST(genres) as genres_new
)
, actors as (
    select
        title
        , actors_new.actor
        , actors_new.character
    from advanced,.array_exercise AS a, UNNEST(actors) as actors_new
)
select
from gen g join actors a
    on g.title=a.title

--방법 2
    select
        title
        , actors_new.actor
        , actors_new.character
        , genre_new
    from advanced,.array_exercise AS a, UNNEST(actors) as actors_new, UNNEST(genres) as genre_new


--방법 3
    select
        title
        , actors_new.actor
        , actors_new.character
        , genre_new
    from advanced.array_exercise
        cross join UNNEST(actors) as actors_new
        cross join UNNEST(genres) as genre_new
    where actors_new.actor 로 조건을 걸어야함 --actor(키값바로) 또는 actors_new로는 안된다 actors_new는 스트럭트 구조이고  actor는 이전 값임

--4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요
--하루 사용자 집계, 어떤 이벤트가 있는가?

select
    user_id
    , event_date
    , event_name
    , user_pseudo_id
    , event_component.key
    , event_component.value.string_value
    , event_component.value.int_value
from app_logs as app, UNNEST(event_pharams) as event_component
where event_date = '2022-08-11'


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

with raw as (
    select
        user_id
        , order_date
        , sum(amount) as amounts
    from orders
)
SELECT
    order_date
    , MAX(IF(user_id=1, amounts, NULL)) AS user_1
    , MAX(IF(user_id=2, amounts, NULL)) AS user_2
    , MAX(IF(user_id=3, amounts, NULL)) AS user_3
...
FROM raw
GROUP BY
    order_date


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

with raw as (
 --혹시나 하나의 유저가 하루에 여러 주문을 했을수도 있을것 같아서 이것 사용(MAX 쓸 예정이라서)
    select
        user_id
        , order_date
        , sum(amount) as amounts
    from orders
)
SELECT
    user_id
    , MAX(IF(order_date='2023-05-01', amounts, NULL)) AS '2023-05-01'
    , MAX(IF(order_date='2023-05-02', amounts, NULL)) AS '2023-05-02'
    , MAX(IF(order_date='2023-05-03', amounts, NULL)) AS '2023-05-03'
...
FROM raw
GROUP BY
    user_id

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

with raw as (
    select
        user_id
        , order_date
        , sum(amount) as amounts
        , count(distinct order_id) as order_cnt
    from orders
)
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'
    --second case
    , MAX(IF(order_date='2023-05-01', order_cnt, 0)) AS '2023-05-01'
    , MAX(IF(order_date='2023-05-02', order_cnt, 0)) AS '2023-05-02'
    , MAX(IF(order_date='2023-05-03', order_cnt, 0)) AS '2023-05-03'
...
FROM raw
GROUP BY
    user_id


-- 4)user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?

-- ##데이터 조회할때 유용한 except(column):특정 컬럼 제외하고 모두 다
-- select * except(event_params)
-- GROUP BY ALL
with raw as (
    select
        user_id
        , event_date
        , event_name
        , user_pseudo_id
        , event_component.key
        , event_component.value.int_value as food_id
    from app_logs as app, UNNEST(event_pharams) as event_component
    where user_id = 32888
        and event_name = 'click_cart'
        and event_component.key = 'food_id'
)
select
    distinct
    event_time --나는 일별로 보고 싶어서 추가
    , food_id
from raw

--성윤님 강의 내용
select
    user_id
    , event_name
    , event_date
    , event_timestamp --같은일에 중복 주문이 있을까봐서
    , MAX(IF(event_component.key='food_id', event_component.value.int_value, NULL)) AS food_id
from app_logs as app, UNNEST(event_pharams) as event_component
where user_id = 32888
        and event_name = 'click_cart'
        and event_component.key = 'food_id'
GROUP BY ALL --알아서 컬럼들 픽


--퍼널 별 유저 수 집계

with raw as (
    select
        user_id
        , event_date
        , event_timestamp
        , event_name
        , user_pseudo_id
        , platform
        , event_component.key
        , event_component.value.string_value
        , event_component.value.int_value
        , MAX(IF(event_component.key = "firebase_screen", event_component.value.string_value, NULL)) AS firebase_screen
--         , MAX(IF(event_component.key = "food_id", event_component.value.int_value, NULL)) AS food_id
        , MAX(IF(event_component.key = "session_id", event_component.value.int_value, NULL)) AS session_id

    from app_logs as app, UNNEST(event_pharams) as event_component
    where event_date BETWEEN "2022-08-01" AND "2022-08-18"
    group by all
)
, 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")
)
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 [0,1,1,2,3,4] as array_practice
array<int64>[0,1,3] as array_practice
generate_array(1,5,2)
generate_date_array('2024-01-01', '2024-02-01', interval 1 week)
WITH programming_languages AS (
  SELECT "python" AS programming_language
  UNION ALL
  SELECT "go"
  UNION ALL
  SELECT "scala"
)
select array_agg(programming_languages) as output
from programming_languages

--배열에 접근하기
offset: #0
ordinal: #1
#out of range를 방지하기 위해서 safe_ 추가하기

--사용 예시
select some_numbers[safe_offset(1)] as second_value
컬럼명[safe_offset(가져오고 싶은 위치)]


Array(like list): 비슷한 카테고리에 대해 데이터를 저장할때 예시) 메뉴(컬럼): 돼지국밥, 떡볶이, 치킨
Struct(like dict): 다양한 속성에 대해 데이터를 한 컬럼에 다 넣고 싶을때 예시) 주소록(컬럼): 이름, 전화번호,이메일, 생일 등등

SELECT (1,2,3) AS struct_test
SELECT STRUCT<hi INT64, hello INT64, awesome STRING>(1, 2, 'HI') AS struct_test
SELECT struct_test.hi, struct_test.hello
    FROM ( SELECT
    STRUCT<hi INT64, hello INT64,
    awesome STRING>(1, 2, 'HI') AS struct_test )


-- UNNEST를 사용해 중첩된 데이터 구조 풀기(평면화, Flatten)
WITH example_data AS( SELECT
'kyle' AS name,
['Python', 'SQL', 'R', 'Julia', 'Go'] AS preferred_language, 'Incheon' AS hometown
UNION ALL SELECT
'max' AS name,
['Python', 'SQL', 'Scala', 'Java', 'Kotlin'] AS preferred_language, 'Seoul' AS hometown
UNION ALL SELECT
'yun' AS name,
['Python', 'SQL'] AS preferred_language, 'Incheon' AS hometown
)
SELECT
name, pref_lang, hometown
FROM example_data CROSS JOIN UNNEST(preferred_language) AS pref_lang
FROM exaple_data AS a, UNNEST(preferred_language) AS pref_lang
--그럼 unnest안에는 array만? struct는?


SELECT
    student
    , MAX(IF(subject="수학", score, NULL)) AS 수학
    , MAX(IF(subject="영어", score, NULL)) AS 영어
    , MAX(IF(subject="과학", score, NULL)) AS 과학
FROM Table
GROUP BY
    student


###팁
#같은 단어를 수정할 때,빨리하고 싶은 - 단어를 커서위에 올리고 커맨드 디 범위설정하고 수정하면 일괄수정 -> 인텔리데이에서는 어떻게 하지?
#기대하는 아웃풋의 형태를 적어보는것 좋다 -> 쉐어포인트 컬럼에 만들기 프로젝트 시작전
- 어떤 업무를 함에 있어서 흐름을 아는 것이 중요하다(흐름을 모르면 어떤것을 왜 해야하는지 모를 수 있음)
- 맥락 -> 목적 -> 퍼널 -> 가설 -> 분석

서비스의 목표 파악(어떤 문제를 해결하려고 하는지)
문제 정의: 핵심 문제 목표 정의
퍼널 정의 -> 우리도 이 데이터가 있는지 물어보기