표준 SQL의 배열 함수

ARRAY

ARRAY(subquery)

설명

ARRAY 함수는 하위 쿼리의 각 행별로 하나의 요소를 가진 ARRAY를 반환합니다.

subquery표준 SQL 테이블을 생성할 경우 이 테이블에는 정확히 1개의 열이 있어야 합니다. 출력 ARRAY의 각 요소는 표에 있는 행의 단일 열 값입니다.

subquery값 테이블을 생성할 경우 출력 ARRAY의 각 요소는 값 테이블의 해당 행 전체입니다.

제약 조건

  • 하위 쿼리는 정렬되지 않으므로 하위 쿼리의 원본 표에서 출력 ARRAY의 요소 순서가 지켜진다는 보장은 없습니다. 그러나 하위 쿼리에 ORDER BY 절이 포함되어 있는 경우 ARRAY 함수는 해당 절을 반영하는 ARRAY를 반환합니다.
  • 하위 쿼리가 둘 이상의 열을 반환하면 ARRAY 함수는 오류를 반환합니다.
  • 하위 쿼리가 ARRAY 유형의 열 또는 ARRAY 유형의 행을 반환할 경우, ARRAY 함수는 오류를 반환합니다. BigQuery는 ARRAY 유형의 요소가 있는 ARRAY를 지원하지 않습니다.
  • 하위 쿼리가 0개의 행을 반환하면 ARRAY 함수는 빈 ARRAY를 반환합니다. 절대 NULL ARRAY를 반환하지 않습니다.

반환 유형

ARRAY

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+

여러 개의 열이 포함된 하위 쿼리에서 ARRAY를 구성하려면 SELECT AS STRUCT를 사용하도록 하위 쿼리를 변경합니다. 그러면 ARRAY 함수가 STRUCTARRAY를 반환합니다. ARRAY는 하위 쿼리의 각 행에 하나의 STRUCT를 포함하며, 각각의 STRUCT는 해당 행의 각 열당 하나의 필드를 포함합니다.

SELECT
  ARRAY
    (SELECT AS STRUCT 1, 2, 3
     UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;

+------------------------+
| new_array              |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+

마찬가지로, 하나 이상의 ARRAY를 포함하는 하위 쿼리에서 ARRAY를 구성하려면 SELECT AS STRUCT를 사용하도록 하위 쿼리를 변경합니다.

SELECT ARRAY
  (SELECT AS STRUCT [1, 2, 3] UNION ALL
   SELECT AS STRUCT [4, 5, 6]) AS new_array;

+----------------------------+
| new_array                  |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+

ARRAY_CONCAT

ARRAY_CONCAT(array_expression_1 [, array_expression_n])

설명

요소 유형이 같은 하나 이상의 배열을 단일 배열로 연결합니다.

반환 유형

ARRAY

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

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

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

설명

배열의 크기를 반환합니다. 빈 배열에는 0을 반환합니다. array_expressionNULL이면 NULL을 반환합니다.

반환 유형

INT64


WITH items AS
  (SELECT ["apples", "bananas", NULL, "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [apples, bananas, NULL, grapes] | 4    |
| [coffee, tea, milk]             | 3    |
| [cake, pie]                     | 2    |
+---------------------------------+------+

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])

설명

array_expression에 있는 요소의 연결을 STRING으로 반환합니다. array_expression 값은 STRING 배열이거나 BYTES 데이터 유형일 수 있습니다.

null_text 매개변수를 사용하는 경우 함수는 배열에 있는 모든 NULL 값을 null_text 값으로 바꿉니다.

null_text 매개변수를 사용하지 않으면 함수는 NULL 값과 앞의 구분 기호를 생략합니다.


WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

설명

값 배열을 반환합니다. start_expressionend_expression 매개변수는 배열의 시작과 끝을 결정합니다(해당 값 포함).

GENERATE_ARRAY 함수에는 다음 데이터 유형을 입력할 수 있습니다.

  • INT64
  • NUMERIC
  • FLOAT64

step_expression 매개변수는 배열 값을 생성하는 데 사용되는 증분을 결정합니다. 이 매개변수의 기본값은 1입니다.

이 함수는 step_expression이 0으로 설정되거나 NaN인 입력이 있는 경우 오류를 반환합니다.

NULL인 인수가 있는 경우 함수는 NULL 배열을 반환합니다.

반환 데이터 유형

ARRAY

다음은 기본 단계가 1인 정수 배열을 반환합니다.

SELECT GENERATE_ARRAY(1, 5) AS example_array;

+-----------------+
| example_array   |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+

다음은 사용자가 지정한 단계 크기를 사용하여 배열을 반환합니다.

SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9]  |
+---------------+

다음은 단계 크기로 음수 값 -3을 사용하여 배열을 반환합니다.

SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+

다음은 start_expressionend_expression에 같은 값을 사용하여 배열을 반환합니다.

SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

+---------------+
| example_array |
+---------------+
| [4]           |
+---------------+

다음은 빈 배열을 반환합니다. start_expressionend_expression보다 크고 step_expression 값이 양수이기 때문입니다.

SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| []            |
+---------------+

다음은 end_expressionNULL이므로 NULL 배열을 반환합니다.

SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;

+---------------+
| example_array |
+---------------+
| NULL          |
+---------------+

다음은 여러 배열을 반환합니다.

SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;

+---------------+
| example_array |
+---------------+
| [3, 4, 5]     |
| [4, 5]        |
| [5]           |
+---------------+

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

설명

날짜 배열을 반환합니다. start_dateend_date 매개변수는 배열의 시작과 끝을 결정합니다(해당 값 포함).

GENERATE_DATE_ARRAY 함수에는 다음 데이터 유형을 입력할 수 있습니다.

  • start_date는 DATE여야 합니다
  • end_date는 DATE여야 합니다
  • INT64_expr은 INT64여야 합니다
  • date_part는 DAY, WEEK, MONTH, QUARTER 또는 YEAR여야 합니다.

INT64_expr 매개변수는 날짜를 생성하는 데 사용되는 증분을 결정합니다. 이 매개변수의 기본값은 1일입니다.

INT64_expr이 0으로 설정된 경우 이 함수는 오류를 반환합니다.

반환 데이터 유형

0 이상의 DATE 값이 포함된 ARRAY.

다음은 기본 단계가 1인 날짜의 배열을 반환합니다.

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

+--------------------------------------------------+
| example                                          |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+

다음은 사용자가 지정한 단계 크기를 사용하여 배열을 반환합니다.

SELECT GENERATE_DATE_ARRAY(
 '2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;

+--------------------------------------+
| example                              |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+

다음은 단계 크기로 음수 값 -3을 사용하여 배열을 반환합니다.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL -3 DAY) AS example;

+--------------------------+
| example                  |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+

다음은 start_dateend_date에 같은 값을 사용하여 배열을 반환합니다.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-05', INTERVAL 8 DAY) AS example;

+--------------+
| example      |
+--------------+
| [2016-10-05] |
+--------------+

다음은 빈 배열을 반환합니다. start_dateend_date보다 크고 step 값이 양수이기 때문입니다.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL 1 DAY) AS example;

+---------+
| example |
+---------+
| []      |
+---------+

입력 중 하나가 NULL이므로 다음은 NULL 배열을 반환합니다.

SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

+---------+
| example |
+---------+
| NULL    |
+---------+

다음은 MONTH를 date_part 간격으로 사용하여 날짜 배열을 반환합니다.

SELECT GENERATE_DATE_ARRAY('2016-01-01',
  '2016-12-31', INTERVAL 2 MONTH) AS example;

+--------------------------------------------------------------------------+
| example                                                                  |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+

다음은 상수가 아닌 날짜를 사용하여 배열을 생성합니다.

WITH StartsAndEnds AS (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
)
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM StartsAndEnds;

+--------------------------------------------------------------+
| date_range                                                   |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+

GENERATE_TIMESTAMP_ARRAY

GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
                         INTERVAL step_expression date_part)

설명

주어진 간격으로 구분된 TIMESTAMPSARRAY를 반환합니다. start_timestampend_timestamp 매개변수는 ARRAY의 하위 경계 및 상위 경계를 결정합니다(해당 값 포함).

GENERATE_TIMESTAMP_ARRAY 함수에는 다음 데이터 유형을 입력할 수 있습니다.

  • start_timestamp: TIMESTAMP
  • end_timestamp: TIMESTAMP
  • step_expression: INT64
  • 허용되는 date_part 값은 MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY입니다.

step_expression 매개변수는 타임스탬프를 생성하는 데 사용되는 증분을 결정합니다.

반환 데이터 유형

0 이상의 TIMESTAMP 값이 포함된 ARRAY.

다음 예시에서는 1초 간격으로 TIMESTAMPARRAY를 반환합니다.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
                                INTERVAL 1 SECOND) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
+--------------------------------------------------------------------------+

다음 예시에서는 음수 간격으로 TIMESTAMPSARRAY를 반환합니다.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-01 00:00:00',
                                INTERVAL -2 DAY) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] |
+--------------------------------------------------------------------------+

다음 예시에서는 하나의 요소로 ARRAY를 반환합니다. start_timestampend_timestamp의 값이 같기 때문입니다.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+--------------------------+
| timestamp_array          |
+--------------------------+
| [2016-10-05 00:00:00+00] |
+--------------------------+

다음 예시에서는 빈 ARRAY를 반환합니다. start_timestampend_timestamp보다 이후이기 때문입니다.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| []              |
+-----------------+

다음 예시에서는 null ARRAY를 반환합니다. 입력 값 중 하나가 NULL이기 때문입니다.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', NULL, INTERVAL 1 HOUR)
  AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| NULL            |
+-----------------+

다음 예시에서는 start_timestampend_timestamp 값이 포함된 열에서 TIMESTAMPARRAY를 생성합니다.

SELECT GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL 1 HOUR)
  AS timestamp_array
FROM
  (SELECT
    TIMESTAMP '2016-10-05 00:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 02:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 12:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 14:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 23:59:00' AS start_timestamp,
    TIMESTAMP '2016-10-06 01:59:00' AS end_timestamp);

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 01:00:00+00, 2016-10-05 02:00:00+00] |
| [2016-10-05 12:00:00+00, 2016-10-05 13:00:00+00, 2016-10-05 14:00:00+00] |
| [2016-10-05 23:59:00+00, 2016-10-06 00:59:00+00, 2016-10-06 01:59:00+00] |
+--------------------------------------------------------------------------+

OFFSET과 ORDINAL

array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]

설명

위치별로 ARRAY 요소에 액세스하고 해당 요소를 반환합니다. OFFSET은 0부터 번호가 시작된다는 뜻이고, ORDINAL은 1부터 번호가 시작된다는 뜻입니다.

주어진 배열은 0이나 1부터 시작되는 것으로 해석될 수 있습니다. 배열 요소에 액세스할 때 배열 위치를 각각 OFFSET 또는 ORDINAL로 시작해야 합니다. 기본 동작은 없습니다.

색인이 범위를 벗어날 경우 OFFSETORDINAL 모두 오류를 생성합니다.

반환 유형

ARRAY 내의 요소에 따라 다릅니다.


WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;

+----------------------------------+-----------+-----------+
| list                             | offset_1  | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas   | apples    |
| [coffee, tea, milk]              | tea       | coffee    |
| [cake, pie]                      | pie       | cake      |
+----------------------------------+-----------+-----------+

ARRAY_REVERSE

ARRAY_REVERSE(value)

설명

요소가 있는 입력 ARRAY를 역순으로 반환합니다.

반환 유형

ARRAY

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr
)
SELECT
  arr,
  ARRAY_REVERSE(arr) AS reverse_arr
FROM example;

+-----------+-------------+
| arr       | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1]   |
| [4, 5]    | [5, 4]      |
| []        | []          |
+-----------+-------------+

SAFE_OFFSET 및 SAFE_ORDINAL

array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]

설명

OFFSETORDINAL과 동일하지만 색인이 범위를 벗어나면 NULL을 반환한다는 점이 다릅니다.

반환 유형

ARRAY 내의 요소에 따라 다릅니다.


WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list,
  list[SAFE_OFFSET(3)] as safe_offset_3,
  list[SAFE_ORDINAL(3)] as safe_ordinal_3
FROM items;

+----------------------------------+---------------+----------------+
| list                             | safe_offset_3 | safe_ordinal_3 |
+----------------------------------+---------------+----------------+
| [apples, bananas, pears, grapes] | grapes        | pears          |
| [coffee, tea, milk]              | NULL          | milk           |
| [cake, pie]                      | NULL          | NULL           |
+----------------------------------+---------------+----------------+
이 페이지가 도움이 되었나요? 평가를 부탁드립니다.

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

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