[빠짝스터디 1주차 과제] + UNNEST, PIVOT

UNNEST

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

SELECT title, genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) AS genre

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

SELECT title,
actor_info.actor,
actor_info.character,
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor_info;

actors는 하나의 배열이고 그 배열의 각 요소가 STRUCT(구조체)

배열 자체에서 바로 .actor나 .character로 접근할 수 없음

먼저 배열을 UNNEST로 펼친 후에 펼쳐진 각 STRUCT에서 필드값을 가져와야함

• actors는 '서류 묶음'(배열)

• 각 서류(STRUCT)에는 '배우 이름'과 '캐릭터 이름'이라는 항목이 있음

• 서류 묶음을 먼저 풀어서(UNNEST) 개별 서류로 만든 다음

• 각 서류에서 원하는 정보를 읽어야 함
  

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

SELECT 
  title, 
  actor_info.actor,
  actor_info.character,
  genre,
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor_info
CROSS JOIN UNNEST(genres) AS genre;

• actors 는 배열 인데 구조에 그 밑에 스트링, 스트링 2개 항목이 저장된거고

• genres 는 배열인데 스트링 한 계층?의 구조가 있는거라 서로 구조가 달라서 둘이 같이 unnest 불가함

  그래서 cross join, unnest 두 번 써줘야함

• actors : ARRAY<STRUCT> => UNNEST => STRUCT

• genres : ARRAY<STRING> => UNNEST => STRING

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

SELECT 
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  params.key AS key,
  params.value.string_value AS string_value,
  params.value.int_value AS int_value,
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS params
ORDER BY event_date;

2022.08.01 부터 나와있길래 그때 날짜부터 정렬인줄 알았더니 where 절에서 2022.08.01 로 필터링 한거였음;;

에러 메시지의 의미:

• "Expected end of input but got keyword UNNEST" → FROM 절 다음에 UNNEST가 바로 나오면 안 되고, JOIN이나 CROSS JOIN이 먼저 나와야 한다는 뜻

JOIN이나 CROSS JOIN이 필요한 이유:

원본 테이블(app_logs)의 행과 UNNEST로 펼친 배열(event_params)의 요소들을 어떻게 연결할지 명시해야 하기 때문

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,
  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 ASC

첫번째 풀이는 PIVOT을 하면서 바로 SUM을 한 것

다른 풀이는 집계 함수를 사용해서(SUM) 집계한 후에 PIVOT

• 컬럼 별칭(AS)에 작은따옴표를 사용함 (AS '2023-05-01') → 백틱 `` 을 사용해야 함.

• GROUP BY 절에 직접 ASC/DESC를 사용함 → ORDER BY를 따로 사용해야 함

   

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

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로 처리합니다.

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

주문 횟수별로 구해달라고 했을 땐? → MAX 대신 SUM

값이 있으면 1로 넣어달라고 했기 때문에 이걸 SUM 하면 주문 횟수 더한 값이 됨.

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

WITH base AS (
SELECT 
	event_date,
	event_name,
	user_pseudo_id,
	event_timestamp,
	user_id,
	MAX(IF(param.key = "firebase_screen", param.value.string_value, NULL)) AS firebase_screen,
  MAX(IF(param.key = "food_id", param.value.int_value, NULL)) AS food_id,
  MAX(IF(param.key = "session_id", param.value.string_value, NULL)) AS session_id
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS param
WHERE event_date = "2022-08-01"
GROUP BY ALL
)
SELECT 
	select_date,
	COUNT(user_id) AS user_cnt
FROM base
WHERE 
	event_name = "click_cart" 
GROUP BY event_date

SELECT title, genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) AS genre

SELECT title,
actor_info.actor,
actor_info.character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor_info;