표준 SQL의 배열 작업

BigQuery에서 배열은 데이터 유형이 동일한 0개 이상의 값으로 구성된 순서가 지정된 목록을 의미합니다. INT64와 같은 간단한 데이터 유형과 STRUCT와 같은 복합 데이터 유형의 배열을 생성할 수 있습니다. 현재 ARRAY 데이터 유형은 예외입니다. 즉, 배열의 배열은 지원되지 않습니다.

BigQuery에서는 배열 리터럴을 생성하고 ARRAY 함수를 사용하여 하위 쿼리에서 배열을 구성하고 ARRAY_AGG 함수를 사용하여 값을 배열로 집계할 수 있습니다.

ARRAY_CONCAT() 등의 함수를 사용해 배열을 결합하고 ARRAY_TO_STRING()을 통해 배열을 문자열로 변환할 수 있습니다.

배열 생성

배열 리터럴 사용

BigQuery에서 괄호 ([])를 사용하여 배열 리터럴을 구성할 수 있습니다. 배열의 각 요소는 쉼표로 구분됩니다.

SELECT [1, 2, 3] as numbers;

SELECT ["apple", "pear", "orange"] as fruit;

SELECT [true, false, true] as booleans;

호환 가능한 유형의 표현식에서 배열을 만들 수도 있습니다. 예를 들면 다음과 같습니다.

SELECT [a, b, c]
FROM
  (SELECT 5 AS a,
          37 AS b,
          406 AS c);

SELECT [a, b, c]
FROM
  (SELECT CAST(5 AS INT64) AS a,
          CAST(37 AS FLOAT64) AS b,
          406 AS c);

두 번째 예에는 INT64를 반환하는 표현식과 FLOAT64를 반환하는 표현식, 리터럴을 선언하는 표현식 등 3가지 표현식이 있습니다. 세 가지 표현식 모두 FLOAT64를 상위 유형으로 공유하므로 이 표현식이 작동합니다.

배열에 대한 특정 데이터 유형을 선언하려면 꺾쇠괄호(<>)를 사용합니다. 예:

SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;

INT64 또는 STRING과 같은 대부분 데이터 유형의 배열을 먼저 선언할 필요가 없습니다.

SELECT [1, 2, 3] as numbers;

ARRAY<type>[]을 사용하여 특정 유형의 빈 배열을 작성할 수 있습니다. 또한 []를 사용하여 유형이 없는 빈 배열을 작성할 수도 있습니다. 이 경우, BigQuery가 주변 컨텍스트에서 배열 유형을 유추하려 합니다. BigQuery에서 유형을 유추할 수 없으면 기본 유형 ARRAY<INT64>가 사용됩니다.

생성된 값 사용

생성된 값으로 ARRAY를 구성할 수도 있습니다.

정수 배열 생성

GENERATE_ARRAY는 시작 및 종료 값과 단계 값으로 값의 배열을 생성합니다. 예를 들어 다음 쿼리에서는 다음과 같은 11부터 33까지의 모든 홀수 정수를 포함하는 배열을 생성합니다.

SELECT GENERATE_ARRAY(11, 33, 2) AS odds;

+--------------------------------------------------+
| odds                                             |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
+--------------------------------------------------+

음의 단계 값을 제공해 내림차순의 배열을 생성할 수도 있습니다.

SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;

+----------------------------------+
| countdown                        |
+----------------------------------+
| [21, 20, 19, 18, 17, 16, 15, 14] |
+----------------------------------+

날짜 배열 생성

GENERATE_DATE_ARRAY는 시작 및 종료 DATE와 단계 INTERVALDATE의 배열을 생성합니다.

GENERATE_DATE_ARRAY를 사용해 DATE 값 집합을 생성할 수 있습니다. 예를 들어 이 쿼리는 1 WEEK 간격으로 나중 DATE를 포함한 현재 DATE와 이어지는 DATE를 반환합니다.

SELECT
  GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK)
    AS date_array;
+--------------------------------------------------------------------------+
| date_array                                                               |
+--------------------------------------------------------------------------+
| [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] |
+--------------------------------------------------------------------------+

배열 요소 액세스

sequences 테이블을 살펴봅니다.

+---------------------+
| some_numbers        |
+---------------------+
| [0, 1, 1, 2, 3, 5]  |
| [2, 4, 8, 16, 32]   |
| [5, 10]             |
+---------------------+

이 테이블에는 ARRAY 데이터 유형의 some_numbers 열이 있습니다. 이 열의 배열 요소에 액세스하려면 0부터 시작하는 색인의 경우 OFFSET, 1부터 시작하는 색인의 경우 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[OFFSET(1)] AS offset_1,
       some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;

+--------------------+----------+-----------+
| some_numbers       | offset_1 | ordinal_1 |
+--------------------+----------+-----------+
| [0, 1, 1, 2, 3, 5] | 1        | 0         |
| [2, 4, 8, 16, 32]  | 4        | 2         |
| [5, 10]            | 10       | 5         |
+--------------------+----------+-----------+

길이 확인

ARRAY_LENGTH() 함수는 배열 길이를 반환합니다.

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,
       ARRAY_LENGTH(some_numbers) AS len
FROM sequences;

+--------------------+--------+
| some_numbers       | len    |
+--------------------+--------+
| [0, 1, 1, 2, 3, 5] | 6      |
| [2, 4, 8, 16, 32]  | 5      |
| [5, 10]            | 2      |
+--------------------+--------+

배열 결합

ARRAY를 일련의 행으로 변환하려면('결합'이라고도 함) UNNEST 연산자를 사용합니다. UNNEST에서 ARRAY를 가져와 ARRAY의 요소마다 단일 행이 포함된 테이블을 반환합니다.

UNNEST에서는 ARRAY 요소의 순서를 무시하므로 테이블에 순서를 복원해야 하는 경우가 있습니다. 이렇게 하려면 선택사항인 WITH OFFSET 절을 사용하여 배열 요소마다 오프셋이 포함된 추가 열을 반환한 후 ORDER BY 절을 사용하여 오프셋을 기준으로 행을 정렬합니다.

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

+----------+--------+
| element  | offset |
+----------+--------+
| foo      | 0      |
| bar      | 1      |
| baz      | 2      |
| qux      | 3      |
| corge    | 4      |
| garply   | 5      |
| waldo    | 6      |
| fred     | 7      |
+----------+--------+

각 행의 다른 열 값을 보존하면서 ARRAY의 전체 열을 결합하려면 CROSS JOIN을 사용하여 ARRAY 열이 포함된 테이블을 해당 ARRAY 열의 UNNEST 출력에 조인합니다.

이는 상관 교차 조인입니다. 즉, UNNEST 연산자가 앞서 ARRAY 절에 표시된 소스 테이블의 각 행에 있는 FROM 열을 참조합니다. 소스 테이블의 N 행에서 UNNESTN 행의 ARRAYARRAY 요소가 포함된 행 집합으로 결합한 후 CROSS JOIN에서 이 새로운 행 집합을 소스 테이블의 단일 N 행과 연결합니다.

다음 예에서는 UNNEST를 사용하여 배열 열의 각 요소에 대한 행을 반환합니다. CROSS JOIN으로 인해 id 열은 각 숫자가 포함된 sequences에 있는 행의 id 값을 포함합니다.

WITH sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers;

+------+-------------------+
| id   | flattened_numbers |
+------+-------------------+
|    1 |                 0 |
|    1 |                 1 |
|    1 |                 1 |
|    1 |                 2 |
|    1 |                 3 |
|    1 |                 5 |
|    2 |                 2 |
|    2 |                 4 |
|    2 |                 8 |
|    2 |                16 |
|    2 |                32 |
|    3 |                 5 |
|    3 |                10 |
+------+-------------------+

중첩 배열 쿼리

테이블에 STRUCTARRAY가 포함된 경우 ARRAY를 결합하여 STRUCT의 필드를 쿼리할 수 있습니다. STRUCT 값의 ARRAY 유형 필드도 결합할 수 있습니다.

ARRAY의 STRUCT 요소 쿼리

다음 예에서는 UNNESTCROSS JOIN을 함께 사용하여 STRUCTARRAY를 결합합니다.

WITH races AS (
  SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
     STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
     STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
     STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
     STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
     STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
     STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
     STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
       AS participants)
SELECT
  race,
  participant
FROM races r
CROSS JOIN UNNEST(r.participants) as participant;

+------+---------------------------------------+
| race | participant                           |
+------+---------------------------------------+
| 800M | {Rudisha, [23.4, 26.3, 26.4, 26.1]}   |
| 800M | {Makhloufi, [24.5, 25.4, 26.6, 26.1]} |
| 800M | {Murphy, [23.9, 26, 27, 26]}          |
| 800M | {Bosse, [23.6, 26.2, 26.5, 27.1]}     |
| 800M | {Rotich, [24.7, 25.6, 26.9, 26.4]}    |
| 800M | {Lewandowski, [25, 25.7, 26.3, 27.2]} |
| 800M | {Kipketer, [23.2, 26.1, 27.3, 29.4]}  |
| 800M | {Berian, [23.7, 26.1, 27, 29.3]}      |
+------+---------------------------------------+

반복되는 필드에서 특정 정보를 찾을 수 있습니다. 예를 들어, 다음 쿼리는 800m 경주에서 가장 빠른 주자를 반환합니다.

이 예에서는 배열을 결합하지는 않지만 반복되는 필드에서 정보를 가져오는 일반적인 방법을 보여줍니다.

WITH races AS (
  SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
     STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
     STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
     STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
     STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
     STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
     STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
     STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
       AS participants)
SELECT
  race,
  (SELECT name
   FROM UNNEST(participants)
   ORDER BY (
     SELECT SUM(duration)
     FROM UNNEST(splits) AS duration) ASC
   LIMIT 1) AS fastest_racer
FROM races;

+------+---------------+
| race | fastest_racer |
+------+---------------+
| 800M | Rudisha       |
+------+---------------+

STRUCT의 ARRAY 유형 필드 쿼리

또한 중첩된 반복 필드에서 정보를 가져올 수도 있습니다. 예를 들어, 다음 문은 800m 경주에서 랩타임이 가장 빠른 주자를 반환합니다.

WITH races AS (
 SELECT "800M" AS race,
   [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
    STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
    STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
    STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
    STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
    STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
    STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
    STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
    AS participants)
SELECT
race,
(SELECT name
 FROM UNNEST(participants),
   UNNEST(splits) AS duration
 ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM races;

+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer                |
+------+-------------------------+

이전 쿼리는 쉼표 연산자(,)를 사용하여 묵시적 CROSS JOIN을 수행합니다. 이는 명시적 CROSS JOIN을 사용하는 다음 예와 같습니다.

WITH races AS (
 SELECT "800M" AS race,
   [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
    STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
    STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
    STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
    STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
    STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
    STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
    STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
    AS participants)
SELECT
race,
(SELECT name
 FROM UNNEST(participants)
 CROSS JOIN UNNEST(splits) AS duration
 ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM races;

+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer                |
+------+-------------------------+

CROSS JOIN을 사용하여 배열을 결합하면 빈 배열이나 NULL 배열이 있는 행은 제외됩니다. 이러한 행을 포함하려면 LEFT JOIN을 사용합니다.

WITH races AS (
 SELECT "800M" AS race,
   [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
    STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
    STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
    STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
    STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
    STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
    STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
    STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits),
    STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as splits),
    STRUCT("David" as name, NULL as splits)]
    AS participants)
SELECT
  name, sum(duration) AS finish_time
FROM races, races.participants LEFT JOIN participants.splits duration
GROUP BY name;

+-------------+--------------------+
| name        | finish_time        |
+-------------+--------------------+
| Murphy      | 102.9              |
| Rudisha     | 102.19999999999999 |
| David       | NULL               |
| Rotich      | 103.6              |
| Makhloufi   | 102.6              |
| Berian      | 106.1              |
| Bosse       | 103.4              |
| Kipketer    | 106                |
| Nathan      | NULL               |
| Lewandowski | 104.2              |
+-------------+--------------------+

하위 쿼리에서 배열 생성

배열을 다룰 때는 일반적으로 하위 쿼리 결과를 배열로 변환하는 작업을 거칩니다. BigQuery에서는

ARRAY() 함수를 사용하여 이를 수행할 수 있습니다.

예를 들어, sequences 테이블에 다음 작업을 수행한다고 가정합니다.

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,
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x) AS doubled
FROM sequences;

+--------------------+---------------------+
| some_numbers       | doubled             |
+--------------------+---------------------+
| [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] |
| [2, 4, 8, 16, 32]  | [4, 8, 16, 32, 64]  |
| [5, 10]            | [10, 20]            |
+--------------------+---------------------+

이 예는 sequences라는 이름의 테이블로 시작합니다. 이 테이블에는 ARRAY<INT64> 유형의 some_numbers 열이 있습니다.

쿼리 자체에 하위 쿼리가 포함되어 있습니다. 이 하위 쿼리는 some_numbers 열의 각 행을 선택하고 UNNEST를 사용하여 배열을 행 집합으로 반환합니다. 그런 다음 각 값에 2를 곱한 후 ARRAY() 연산자를 사용하여 행을 다시 배열로 다시 결합합니다.

배열 필터링

다음 예는 ARRAY() 연산자의 하위 쿼리에서 WHERE 절을 사용하여 반환된 행을 필터링합니다.

참고: 다음 예에서는 결과 행이 정렬되지 않습니다.

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
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x
        WHERE x < 5) AS doubled_less_than_five
FROM sequences;

+------------------------+
| doubled_less_than_five |
+------------------------+
| [0, 2, 2, 4, 6]        |
| [4, 8]                 |
| []                     |
+------------------------+

해당하는 원래 행의 요소([5, 10])가 필터링 요구사항인 x < 5을 충족하지 않아 세 번째 행에는 빈 배열이 표시된다는 점에 주목하세요.

SELECT DISTINCT를 사용하여 배열 내에서 고유한 요소만 반환하도록 배열을 필터링할 수도 있습니다.

WITH sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers)
SELECT ARRAY(SELECT DISTINCT x
             FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences;

+-----------------+
| unique_numbers  |
+-----------------+
| [0, 1, 2, 3, 5] |
+-----------------+

또한 IN 키워드를 사용하여 배열 행을 필터링할 수도 있습니다. 이 키워드는 특정 값이 배열의 요소와 일치하는지 여부를 판단하여 배열이 포함된 행을 필터링합니다.

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
   ARRAY(SELECT x
         FROM UNNEST(some_numbers) AS x
         WHERE 2 IN UNNEST(some_numbers)) AS contains_two
FROM sequences;

+--------------------+
| contains_two       |
+--------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32]  |
| []                 |
+--------------------+

마찬가지로 해당하는 원래 행의 요소([5, 10])에 2가 포함되어 있지 않아 세 번째 행에는 빈 배열이 표시된다는 점에 주목하세요.

배열 검색

배열에 특정 값이 있는지 확인하려면 UNNEST와 함께 IN 연산자를 사용합니다. 배열에 조건과 일치하는 값이 있는지 확인하려면 UNNEST와 함께 EXISTS 함수를 사용합니다.

특정 값 검색

배열에서 특정 값을 검색하려면 UNNEST와 함께 IN 연산자를 사용합니다.

다음 예는 배열에 숫자 2가 있으면 true를 반환합니다.

SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;

+----------------+
| contains_value |
+----------------+
| true           |
+----------------+

배열 열에 특정 값이 포함된 테이블 행을 반환하려면 WHERE 절을 사용하여 IN UNNEST의 결과를 필터링합니다.

다음 예는 배열 열에 값 2가 있는 행의 id 값을 반환합니다.

WITH sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows
FROM sequences
WHERE 2 IN UNNEST(sequences.some_numbers)
ORDER BY matching_rows;

+---------------+
| matching_rows |
+---------------+
| 1             |
| 2             |
+---------------+

조건을 충족하는 값 검색

배열에서 조건과 일치하는 값을 검색하려면 UNNEST를 사용하여 배열의 요소 테이블을 반환하고, WHERE를 사용하여 하위 쿼리에서 결과 테이블을 필터링한 후 EXISTS를 사용하여 필터링된 테이블에 행이 포함되어 있는지 확인합니다.

다음 예는 배열 열에 5보다 큰 값이 포함된 행의 id 값을 반환합니다.

WITH sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows FROM sequences
WHERE EXISTS (SELECT *
              FROM UNNEST(some_numbers) AS x
              WHERE x > 5);

+---------------+
| matching_rows |
+---------------+
| 2             |
| 3             |
+---------------+

조건을 충족하는 STRUCT 필드 값 검색

STRUCT의 배열에서 값이 조건과 일치하는 필드를 검색하려면 UNNEST를 사용하여 각 STRUCT 필드에 대한 열이 있는 테이블을 반환한 후 WHERE EXISTS를 사용하여 이 테이블에서 일치하지 않는 행을 필터링합니다.

다음 예는 배열 열에 STRUCT 필드의 값이 3보다 큰 b가 포함된 행을 반환합니다.

WITH sequences AS
  (SELECT 1 AS id, [STRUCT(0 AS a, 1 AS b)] AS some_numbers
   UNION ALL SELECT 2 AS id, [STRUCT(2 AS a, 4 AS b)] AS some_numbers
   UNION ALL SELECT 3 AS id, [STRUCT(5 AS a, 3 AS b), STRUCT (7 AS a, 4 AS b)]
     AS some_numbers)
SELECT id AS matching_rows
FROM sequences
WHERE EXISTS (SELECT 1
              FROM UNNEST(some_numbers)
              WHERE b > 3);

+---------------+
| matching_rows |
+---------------+
| 2             |
| 3             |
+---------------+

배열과 집계

BigQuery에서 ARRAY_AGG()를 사용하여 값을 배열로 집계할 수 있습니다.

WITH fruits AS
  (SELECT "apple" AS fruit
   UNION ALL SELECT "pear" AS fruit
   UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM fruits;

+-----------------------+
| fruit_basket          |
+-----------------------+
| [apple, pear, banana] |
+-----------------------+

이 함수는 값을 연결하는 순서를 보장하지 않으므로, ARRAY_AGG()에 의해 반환된 배열은 임의의 순서로 되어 있습니다. 배열 요소를 정렬하려면 ORDER BY를 사용합니다. 예:

WITH fruits AS
  (SELECT "apple" AS fruit
   UNION ALL SELECT "pear" AS fruit
   UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit ORDER BY fruit) AS fruit_basket
FROM fruits;

+-----------------------+
| fruit_basket          |
+-----------------------+
| [apple, banana, pear] |
+-----------------------+

또한 배열의 요소에 SUM()과 같은 집계 함수를 적용할 수 있습니다. 예를 들어, 다음 쿼리는 sequences 테이블의 각 행에 대한 배열 요소의 합계를 반환합니다.

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,
  (SELECT SUM(x)
   FROM UNNEST(s.some_numbers) x) AS sums
FROM sequences s;

+--------------------+------+
| some_numbers       | sums |
+--------------------+------+
| [0, 1, 1, 2, 3, 5] | 12   |
| [2, 4, 8, 16, 32]  | 62   |
| [5, 10]            | 15   |
+--------------------+------+

BigQuery에서는 여러 행에 걸친 한 배열 열의 여러 요소를 연결하는 집계 함수, ARRAY_CONCAT_AGG()도 지원됩니다.

WITH aggregate_example AS
  (SELECT [1,2] AS numbers
   UNION ALL SELECT [3,4] AS numbers
   UNION ALL SELECT [5, 6] AS numbers)
SELECT ARRAY_CONCAT_AGG(numbers) AS count_to_six_agg
FROM aggregate_example;

+--------------------------------------------------+
| count_to_six_agg                                 |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                               |
+--------------------------------------------------+

참고: 함수에서 값을 연결하는 순서가 보장되지 않으므로 ARRAY_CONCAT_AGG()에서 반환되는 배열은 확정값이 아닙니다.

배열의 문자열 변환

ARRAY_TO_STRING() 함수를 사용하면 ARRAY<STRING>을 단일 STRING 값으로 변환하거나 ARRAY<BYTES>를 단일 BYTES 값으로 변환할 수 있습니다. 그러면 순서에 따라 연결된 배열 요소를 그 결과로 얻을 수 있습니다.

두 번째 인수는 함수에서 출력을 생성하기 위해 입력 사이에 삽입하는 구분자입니다. 이 두 번째 인수는 첫 번째 인수의 요소와 유형이 동일해야 합니다.

예:

WITH greetings AS
  (SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM greetings;

+-------------+
| greetings   |
+-------------+
| Hello World |
+-------------+

선택사항인 세 번째 인수는 입력 배열에서 NULL 값 대신 사용됩니다.

  • 이 인수를 생략하면 함수에서 NULL 배열 요소를 무시합니다.

  • 빈 문자열을 제공하면 함수에서 NULL 배열 요소에 구분자를 삽입합니다.

예:

SELECT
  ARRAY_TO_STRING(arr, ".", "N") AS non_empty_string,
  ARRAY_TO_STRING(arr, ".", "") AS empty_string,
  ARRAY_TO_STRING(arr, ".") AS omitted
FROM (SELECT ["a", NULL, "b", NULL, "c", NULL] AS arr);

+------------------+--------------+---------+
| non_empty_string | empty_string | omitted |
+------------------+--------------+---------+
| a.N.b.N.c.N      | a..b..c.     | a.b.c   |
+------------------+--------------+---------+

배열 결합

여러 배열을 하나의 배열로 결합해야 하는 경우가 있습니다. 이 때는 ARRAY_CONCAT() 함수를 사용하면 됩니다.

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

+--------------------------------------------------+
| count_to_six                                     |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                               |
+--------------------------------------------------+

배열의 배열 구성

BigQuery는 배열의 배열 직접 구성을 지원하지 않습니다. 대신, ARRAY 유형의 필드가 포함된 각 구조체로 구조체 배열을 만들어야 합니다. 이에 대한 설명에 앞서 다음 points 테이블을 살펴보세요.

+----------+
| point    |
+----------+
| [1, 5]   |
| [2, 8]   |
| [3, 7]   |
| [4, 1]   |
| [5, 7]   |
+----------+

이제 points 테이블의 각 point로 구성된 배열을 만들려고 합니다. 이를 수행하려면 아래와 같이 STRUCT의 각 행에서 반환된 배열을 래핑합니다.

WITH points AS
  (SELECT [1, 5] as point
   UNION ALL SELECT [2, 8] as point
   UNION ALL SELECT [3, 7] as point
   UNION ALL SELECT [4, 1] as point
   UNION ALL SELECT [5, 7] as point)
SELECT ARRAY(
  SELECT STRUCT(point)
  FROM points)
  AS coordinates;

+----------------------------------------------------+
| coordinates                                        |
+----------------------------------------------------+
| [{[1, 5]}, {[2, 8]}, {[3, 7]}, {[4, 1]}, {[5, 7]}] |
+----------------------------------------------------+
이 페이지가 도움이 되었나요? 평가를 부탁드립니다.

다음에 대한 의견 보내기...

도움이 필요하시나요? 지원 페이지를 방문하세요.