오늘은 빅쿼리에서 사용하는 Array , 그리고 Array 하면 빠질 수 없는 Unnest 에 대해 알아보겠습니다

[Array 란 무엇일까]

빅쿼리 UI 상으로는 'Repeated' 라고 표현되는 것이 바로 Array 인데요,

예를 들어서 간단하게 아래 같은 구조의 도표가 있다고 생각해볼게요. 사람마다의 취미와 직업이 써져 있죠.

이렇게 각 1 사람당 1개의 취미 , 1개의 직업만 있다면 array 구조는 불필요합니다.

근데 취미가 여러개라면 ?

아래 이미지처럼 name 과 job 이 있던 행이 hobby가 늘어남에 따라

행이 5개였던게 8개로 늘어났죠.

지금은 예시이다 보니 5개 행이 8개로 늘었지만

수가 훨씬 많다면 공간 차지면에서 비효율적일수도 있고,

이후에 데이터를 추출할 때도, hobby 가 여러개 있다는걸 모르는 사람이라면

원하는 것과 다른 데이터를 가지게 될 수 도 있죠.

이럴 때 활용할 수 있는게 array 입니다.

아래 이미지를 보시면 array를 사용하면 hobby 가 여러개 있더라도

name 1명에게만 행이 생성되었습니다.

이렇게 한 개의 행에 특정 데이터들이 여러개 저장되면서, 데이터가 세로로 저장되 것이 Array 입니다.

참고로 빅쿼리에서 JSON 형식으로 아래와 같이 확인할 수 있습니다.

[Array 를 만드는 방법]

Array 를 만드는 방법은 여러가지가 있습니다.

[1] 대괄호

select 'HAYEON' name , ['십자수', '헬스장'] hobby , '공무원' job

[2] ' Array <타입>' + [대괄호]

select 'HAYEON' name , Array ['십자수', '헬스장'] hobby , '공무원' job

[3] Generate 함수

GENERATE_ARRAY (시작 숫자 , 마지막 숫자 , 간격)

select GENERATE_ARRAY(2,20,4) GENERATE_ARRAY

GENERATE_DATE_ARRAY (시작 날짜 ,마지막 날짜 ,간격)

select GENERATE_DATE_ARRAY('2024-07-01','2024-08-01',interval 1 week) GENERATE_DATE_ARRAY

[4] Array_agg 사용

ARRAY_AGG

 with hobby_table as

 (select '십자수' as hobby

  union all

  select '뜨개질' as hobby

  union all

  select '클라이밍' as hobby    

  union all

  select '게임' as hobby    

  union all

  select '요가' as hobby    

  union all

  select '영상 제작' as hobby  

 )

 select array_agg(hobby) as hobby_array

 from hobby_table

ARRAY_AGG는 나중에 자주 쓰게되는 것 같아서 좀 더 연습해볼게요 :)

위의 예시에서는 array 안에 넣을 항목들을 with 구문에 만들어 두었었는데

만약 저장되어 있는 테이블에서 array_agg 를 써야한다면 어떻게 해야할까요?

가장 처음에 가지고 있던 테이블을 보면 이렇게 되어 있는데,

만약..... 여기 있는 인물들이

직업이 바뀌는 상황이 있었다면? 연도별로 직업이 달라져야하지 않을까요?

또 중간에 취미가 바뀌거나 사라졌을 수도 있겠죠?

한번 아래처럼 표를 수정해보았어요.

2023년에서 2024년이 될 때

Hayeon , Yeji , Hyunji 는 job 에 변화가 있었고,

Jiyounh 은 hobby 가 2개에서 1개로 줄었습니다.

이 테이블에 yerar ,name,job 은 group by를 , hobby 와 job엔 array_agg 을 써볼게요

SELECT

    year,

    name,

    job,

    array_agg(hobby) hobby,

FROM `boheetest.SQL_Tableau.example`

group by year,name,job

이런식으로 array_agg 를 써줄 수 있습니다 :)

[Array 에 있는 값을 가져오는 방법 (1) 배열의 순서로 가져올 때]

array 에 있는 값은, 배열의 순서를 지정해서 가져올 수 있습니다.

이때, 숫자를 0부터 셀지 1부터 셀지에 따라 쓰이는 명령어가 다른데

Offset > 0부터

Ordinal > 1부터 입니다.

*만약 이 순서에 대해 숫자를 썼는데 그 값이 없었다면 오류가 발생하겠죠?

이런 경우를 대비해선 SAFE_OFFSET, SAFE_ORDINAL 을 사용할 수 있고, 값이 없으면 NULL 로 채워집니다.

아래와 같은 ARRAY 가 있다고 하면

Offset > 0부터/ Ordinal > 1부터니까

array_agg(hobby)[SAFE_OFFSET(0)] as hobby_array > '십자수'

array_agg(hobby)[SAFE_OFFSET(1)] as hobby_array > '뜨개질'

array_agg(hobby)[SAFE_OFFSET(3)] as hobby_array >'게임'

array_agg(hobby)[SAFE_ORDINAL(0)] as hobby_array > 'NULL'

array_agg(hobby)[SAFE_ORDINAL(1)] as hobby_array > '십자수'

array_agg(hobby)[SAFE_ORDINAL(3)] as hobby_array > '클라이밍'

여기까지 이렇게 array 에 대해 알아보았는데요,

array 하면 빠질 수 없는게 바로 unnest 이죠, 이어서 같이 알아보겠습니다!

[Array 에 있는 값을 가져오는 방법 (2) Unnest]

위에서 있었던 테이블은 사람마다의 취미와 직업이 있었죠,

그럼 이 중에서 '취미가 게임인 사람' 을 조회하고 싶어서 where 절로 필터를 걸었다면 어떻게 될까요?

<오류 발생 화면>

No matching signature for operator IN for argument types ARRAY and {STRING} at [13:13]

No matching signature for operator = for argument types: ARRAY, STRING. Supported signature: ANY = ANY at [14:7]

아래 쿼리에서는 'in' 을 써도 '=' 를 써도 비슷한 오류가 발생합니다.

with table as (

    select 'HAYEON' name , Array ['십자수', '헬스장'] hobby , '공무원' job

    Union all

    select 'YEJI' name , ['뜨개질','게임'] hobby , '주부' job

    Union all

    select 'MINJI' name, ['게임'] hobby , '직장인' job

    Union all

    select 'HYUNJI' name , ['필라테스','영상제작'] hobby , '대학생' job

    Union all

    select 'JIYOUNG' name , ['요가','클라이밍'] hobby , '직장인' job

)

select * from table

where hobby in ('게임')

그 이유는 지금 hobby 는 array 에 있는데, '게임' 이라는 string 값과 같은지 다른지조차 비교할 수 없기 때문이에요.

이럴때 필요한 것이 바로 unnest 입니다.

unnest 는'평면화'라고도 불리는데요,

뜬금없을 수 있지만 한번 엑셀의 셀 병합을 떠올려볼까요?

좌측이 일반적인 도표이고, 우측은 제가 같은 값들로 셀 병합을 한 상태인데요, 종종 병합된 셀에서 엑셀의 특정 기능을 실행할 때

이런 경고창과 함께 실행이 되지 않았던 경험 한번씩은 있으시죠?

이게 어떻게 보면 unnest 의 필요성 이에요.

현재 셀들이 같은 기준점에서 처리가 불가능한 상태이기 때문에

다 같은 구조로 만들어줘야만 (엑셀에서는 셀의 크기가 동일해야만) 하는 것이죠.

병합된 셀을 다시 기본 형태처럼 ,

즉, array 로 묶인 형태 ( = 병합된 셀) 를 푸는 것이 unnest 입니다.

[Unnest 사용 시 주의할 점]

unnst 를 사용할 때 꼭 유의하셔야 하는 부분은 '행의 갯수가 늘어날 수 있다' 는 점 이에요.

예를 들어서 위에 나온 인물들이 물건을 구매했고,

그 구매한 물건들이 array 형태인 테이블이 있다고 생각해볼게요.

이렇게 보면 각 사람별로 product_price 의 총 합과 total_price 값이 동일하죠?

근데 이 테이블이 만약 unnest 되었다면

아래처럼  total price 가 모든 행마다 붙게 되면서 원치 않는 total_price 값을 얻게될 수도 있어요.

실제로 ga 를 다룰 때 자주 발생하는 이슈이기도 하니 데이터 구조를 잘 파악하면서 사용해야 합니다 :)

그럼 오늘은 여기까지 array 와 unnest 에 대해 알아보았는데요,

다음 글에서는 struct 에 대해 정리해보고,

그 용법들을 ga 데이터에서 활용하는 방법에 대해 공유드리도록 할게요!

함께 소통하면 좋은 부분 있으시면 언제든 댓글 남겨주세요!

감사합니다 :)