# ARRAY

배열(ARRAY)은 하나의 변수에 여러 값들을 저장하는 자료형입니다.

다양한 개발 언어에서 배열(파이썬은 리스트와 유사)이 존재합니다.

# 배열

문법 :

1. 괄호를 사용
- ex) [1, 2, 3]
1. 특정 데이터 유형을 선언할 경우 꺾쇠 괄호 사용
- ex) ARRAY<INT64>[1, 2, 4]

SELECT 
  [1, 2, 3] as numbers,
  ARRAY<INT64>[1,2,4,9,10] as numbers2

# UNNEST

위 예제에서 numbers의 값 중 1인 요소를 SELECT하고 싶은 경우 다음과 같은 쿼리를 실행하면 오류를 반환합니다.

WITH base AS (
  SELECT 
    [1, 2, 3] as numbers,
    ARRAY<INT64>[1,2,4,9,10] as numbers2
)

SELECT
  *
FROM base
WHERE numbers = 1

위 오류가 발생한 이유는 배열에 바로 조건을 걸었기 때문이고, 자세히 설명하면 [1, 2, 3] = 1라는 조건을 설정한 것과 동일합니다. = 연산자는 동일한 타입에서만 동작하므로 배열을 INT로 풀어내야 합니다.

배열(ARRAY) 안에 있는 요소에 접근하고 싶은 경우, 배열을 풀어내야 합니다(Fltten) 이럴 때 사용하는 것이 UNNEST 입니다.

UNNEST : to put out of or as if out of a nest. 둥지 밖으로 나가는 것처럼 데이터를 둥지 밖으로 풀어넣습니다.

처음 배열을 공부하면 UNNEST에 대해 이해하기 어렵습니다. 배열을 풀어낸다는 것은 무엇이지? 이런 생각을 할 수 있습니다. 쉽게 말씀드리면 배열에 있는 데이터를 접근할 수 있도록 하는 작업입니다. 예시를 보면 이해가 더 수월합니다.

SELECT 
  ['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'] AS element

위 쿼리를 실행하면 아래와 같이 나옵니다.

이 쿼리의 "행"을 잘 보시면 1이라고 되어있습니다. 즉 하나의 행에 여러 데이터가 담겨져 있습니다.

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred']) AS element

위 쿼리는 배열을 배열 안이 아닌 바깥으로 풀어냈습니다. 이제 행을 보시면 하나의 행에 데이터가 여러가지 있는 것이 아닌, 각각의 행에 데이터를 가지고 있습니다.

위 예시는 FROM에 바로 UNNEST가 있었는데, 테이블에서 활용할 경우엔 다음과 같이 활용합니다.

SELECT   
  alias_col_name
FROM Table, UNNEST(배열) AS alias_col_name

FROM 절에 있는 Table, UNNEST(배열)은 사실 FROM Table CROSS JOIN UNNEST(배열)과 동일한 표현입니다. UNNEST는 CROSS JOIN하면서 데이터를 모두 연결합니다.

아래 두 GIF를 보시면 더 명확하게 이해할 수 있습니다.

UNNEST 과정

UNNEST 후 WHERE 조건 설정한 과정

TIP

Q) 왜 배열이 헷갈릴까요?
A) 헷갈릴 수 있는 포인트는 빅쿼리에서 테이블 형태로 되어있고, 우리 눈에 봤을 때 각각 데이터가 하나의 행이라고 생각해서 그럴 수 있습니다. 이 부분은 빅쿼리에서 다른 방식으로 데이터를 보여주면 더 잘 이해할 수 있지 않을까? 싶은 아쉬움이 있네요.

# 배열의 순서를 출력하고 싶은 경우

FROM 절 아래에 WITH OFFSET을 명시하면 배열의 순서를 출력해줍니다. OFFSET은 0부터 시작됩니다.

문법 : UNNEST + WITH OFFSET

SELECT  
  element, offset_value
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS element
WITH OFFSET AS offset_value
ORDER BY offset_value

# 배열의 N번째 요소에 접근하고 싶은 경우

N번째 요소에 접근하고 싶은 경우 위에서 사용한 OFFSET 또는 ORDINAL을 사용하면 됩니다.

문법 : ARRAY[OFFSET(N)] : ARRAY에서 N번째 요소(0부터 시작)

SELECT 
  ["apple", "pear", "orange"] AS fruit, 
  ["apple", "pear", "hi"][OFFSET(0)] AS one

SAFE_OFFSET을 사용하면 만약 배열의 N번째 요소에 데이터가 없는 경우 NULL을 반환합니다.

SELECT 
  ["apple", "pear", "orange"] AS fruit, 
  # ["apple", "pear", "hi"][OFFSET(4)] AS one, # Array index 4 is out of bounds (overflow) 오류 발생
  ["apple", "pear", "hi"][SAFE_OFFSET(4)] AS safe_four

OFFSET이 아닌 ORDINAL을 사용해도 가능합니다. ORDINAL은 1부터 시작됩니다(OFFSET은 0부터 시작)

ARRAY[ORDINAL(N)] : ARRAY에서 N번째 요소(1부터 시작)

SELECT 
  ["apple", "pear", "orange"] AS fruit, 
  ["apple", "pear", "hi"][ORDINAL(2)] AS second

비교를 위해 OFFSET, ORDINAL 모두 사용한 쿼리의 예를 보겠습니다.

WITH sequences AS (
  SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
  UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
  UNION ALL SELECT [5, 10] AS some_numbers
)

SELECT 
  some_numbers,
  some_numbers[SAFE_OFFSET(1)] AS offset_1,
  some_numbers[SAFE_ORDINAL(1)] AS ordinal_1
FROM sequences

그 외 더 자세한 내용은 BigQuery UNNEST, ARRAY, STRUCT 사용 방법 (opens new window) 글을 추천합니다

← 자료형 STRUCT →