SQL 중급: 3일차

2일차에 이어서 ARRAY, STRUCT를 평면화하는 쿼리 연습 문제를 풀어봅시다.

https://www.inflearn.com/course/bigquery-%ED%99%9C%EC%9A%A9%ED%8E%B8/dashboard

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석) 강의 | 카일스쿨 - 인프런

 

시작하기 전에, 어제 2 1 3 순서로 저장되었던 id 값을 1 2 3 순서로 바꾸기 위해 아래 쿼리를 날려준다.

SELECT *

FROM `advanced.array_test`

ORDER BY id

 

1. 브랜드별로 매니저 이름, 연락처, 제품명이 모두 나오도록 출력 해보자

아래와 같은 데이터가 있다.

 

결과적으로 아래와 같은 데이터를 얻고 싶다.

 

색깔은 내가 이해하기 쉽게 구분하려고 추가한 것이다.

데이터를 잘 보면 브랜드 A B C 별로 판매하는 제품이 3개씩인데, 각각 브랜드명, 담당자 정보를 모두 추가하려는 것이다.

 

예를 들어, 지우개를 판매하는 매니저는 총 몇명인가? 같은 데이터를 구하기 위해서는 (엑셀에서는 피벗이 되겠지)

ARRAY, STRUCT 구조체를 UNNEST 해서 위와 같은 데이터 테이블을 만드는 것이 1차적으로 필요하다.

 

내가 이 강의를 왜 듣는지, 쿼리 공부를 왜 하는지, 실무에서 어떤 목적으로 활용할 수 있을지 생각하면서 나아가면 좋다.

목적 없이 SQL이 대세라고 하던데, 빅쿼리를 쓴다고 하던데, 라면서 남을 따라하는 것은 의미없다.

 

 

강의로 돌아가서, 결과물을 얻기 위해 쿼리를 구상해 날려본다.

SELECT

 brand,

 name,

 mobile,

 products

FROM advanced.array_test AS atest

CROSS JOIN UNNEST(atest.manager) AS managers

 

찢어지긴 했는데.. 행별로 브랜드, 이름, 연락처가 기입이 안됐다.

다시 고민..

 

제품명쪽이 아직 UNNEST 안 된 것 같아 두줄로 작성했더니 성공!

SELECT

 brand,

 name,

 mobile,

 product_dt

FROM advanced.array_test AS atest

CROSS JOIN UNNEST(atest.manager) AS managers

CROSS JOIN UNNEST(atest.products) AS product_dt

 

 

정답이 맞는지, 혹시 저렇게 두 줄로 안하고 다른 방법이 있는지 강의를 틀어 맞춰본다.

결론적으로 UNNEST를 두 번 중첩해서 사용할 수 있다는 것을 아는 것이 핵심이기 때문에, 정답은 맞다.

하지만 강의에서 알려준 쿼리 작성 방법을 참고해두면 좋을 것 같아 메모해 둔다.

 

정답을 맞추는 것보다 데이터 구조를 이해하고 쿼리를 작성해 풀어나가는 방식을 이해하려고 하자

 

array_test 라는 테이블에서(FROM) 내가 보고 싶은 열을 적어준다(SELECT)

 

SELECT

 brand,

 manager,

 products

FROM array_test

 

오류가 난다. advanced라는 데이터 세트 안에 있는 것이므로 쿼리를 고친다.

 

SELECT

 brand,

 manager,

 products

FROM advanced.array_test 

 

데이터의 구조를 파악한다.

 

SELECT

 brand,

 manager, # ARRAY<STRUCT(STRING, STRING)>

 products # ARRAY<STRING>

FROM advanced.array_test

 

구조체(STRUCT)를 풀어주고(UNNEST), 풀어준 데이터(managers)만 선택한다(SELECT)

 

SELECT

 brand,

-- manager, # ARRAY<STRUCT(STRING, STRING)>

 managers,

 products # ARRAY<STRING>

FROM advanced.array_test

CROSS JOIN UNNEST(manager) AS managers

 

여기서 제일 헤깔리는게 UNNEST 괄호 안에 있는 manager = name, mobile 저장된 STRUCT 이고

이걸 풀어서 managers 라고 저장한 상황인 것이다. manager / managers 잘 구분하자!

 

실제로 SELECT 쪽에서 제대로 안바꾸면, 데이터 조회 결과가 다르다.

 

 

 

SELECT  brand, -- manager, # ARRAY<STRUCT(STRING, STRING)>  managers,  products # ARRAY<STRING> FROM advanced.array_test CROSS JOIN UNNEST(manager) AS managers	SELECT  brand,  manager, # ARRAY<STRUCT(STRING, STRING)> -- managers,  products # ARRAY<STRING> FROM advanced.array_test CROSS JOIN UNNEST(manager) AS managers
UNNEST 완료된 managers를 올바르게 SELECT 한 경우 브랜드A 담당자인 홍길동, 김철수 name 별로 mobile 값이 잘 나온다.	구조체(STRUCT)의 이름인 manager를 SELECT 한 경우 최초 원본 데이터를 선택한 것과 같다. 열 헤더를 보면 manager.name과 manager.mobile이 보인다.

 

 

아래 내용은 잘 이해가 안가는데, 강의에서는

managers.name 그리고 managers.mobile 이렇게 명시를 해주고 각각 AS 해서 명칭을 정했다. 

 

나중에 헤깔리지 않기 위해 그런건가..

나는 SELECT managers 라고만 해도 name, mobile 열이 각각 잘 나왔는데.. 이유를 알 수 없어 답답하다.

 

 

SELECT

 brand,

-- manager, # ARRAY<STRUCT(STRING, STRING)>

 managers.name AS name_dt,

 managers.mobile AS mobile_dt,

 products # ARRAY<STRING>

FROM advanced.array_test

CROSS JOIN UNNEST(manager) AS managers

 

 

 

이제 다음 ARRAY인 제품명(products)를 풀어준다(UNNEST)

CROSS JOIN UNNEST(products) 내용을 한 줄 더 쓰고, SELECT 쪽에서 알맞게 선택한다.

 

 

SELECT

 brand,

-- manager, # ARRAY<STRUCT(STRING, STRING)>

 managers.name AS name_dt,

 managers.mobile AS mobile_dt,

-- products # ARRAY<STRING>

 product_dt

FROM advanced.array_test

CROSS JOIN UNNEST(manager) AS managers

CROSS JOIN UNNEST(products) AS product_dt

 

 

그럼 원하는 데이터 준비가 완료된다.

 

 

 

 

2. 빠른 쿼리 작성을 위한 단축키 2가지

 

 

ctrl + / 또는 command + / 입력하면 빠른 주석 처리가 가능하다.

 

문장 앞에 -- 글자를 넣어서 쿼리를 실행할 때 포함되지 않도록 하는 방법이다.

예를 들면 이렇게 빨간 글씨가 되는 것이다.

 

-- manager, # ARRAY<STRUCT(STRING, STRING)>

 

 

 

원하는 단어 중간에 커서 위치하고 ctrl + d 또는 command | d 버튼을 계속 누르면, 똑같은 단어 한 번에 수정이 가능하다.

 

예를 들면 manager 단어 전부 선택하고 수정할 수 있는 것이다.

 

 

 

오늘의 교훈은 글 중간에도 적었지만, 한번 더 반복 학습하자.

 

정답을 맞추는 것보다 데이터 구조를 이해하고 쿼리를 작성해 풀어나가는 방식을 이해하려고 하자