BigQuery용 GoogleSQL에서 배열은 데이터 유형이 동일한 0개 이상의 값으로 구성된 순서가 지정된 목록을 의미합니다. INT64와 같은 간단한 데이터 유형과 STRUCT와 같은 복합 데이터 유형의 배열을 생성할 수 있습니다. 현재 배열의 배열은 지원되지 않으므로 ARRAY 데이터 유형은 예외입니다. NULL 처리를 포함한 ARRAY 데이터 유형에 대한 자세한 내용은 배열 유형을 참조하세요.
GoogleSQL에서는 배열 리터럴을 구성하고, ARRAY 함수를 사용하여 하위 쿼리에서 배열을 빌드하고, ARRAY_AGG 함수를 사용하여 값을 배열로 집계할 수 있습니다.
ARRAY_CONCAT() 등의 함수를 사용하여 배열을 결합하고 ARRAY_TO_STRING()을 통해 배열을 문자열로 변환할 수 있습니다.
배열 요소 액세스
Sequences라는 다음의 에뮬레이션된 테이블을 살펴봅니다. 이 테이블에는 ARRAY 데이터 유형의 some_numbers 열이 있습니다.
WITH
Sequences AS (
SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL
SELECT [2, 4, 8, 16, 32] UNION ALL
SELECT [5, 10]
)
SELECT * FROM Sequences
/*---------------------*
| some_numbers |
+---------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32] |
| [5, 10] |
*---------------------*/
some_numbers 열의 배열 요소에 액세스하려면 0부터 시작하는 색인의 경우 index
또는OFFSET(index), 1부터 시작하는 색인의 경우 ORDINAL(index) 중 사용할 색인 유형을 지정합니다.
예를 들면 다음과 같습니다.
SELECT
some_numbers,
some_numbers[0] AS index_0,
some_numbers[OFFSET(1)] AS offset_1,
some_numbers[ORDINAL(1)] AS ordinal_1
FROM Sequences
/*--------------------+---------+----------+-----------*
| some_numbers | index_0 | offset_1 | ordinal_1 |
+--------------------+---------+----------+-----------+
| [0, 1, 1, 2, 3, 5] | 0 | 1 | 0 |
| [2, 4, 8, 16, 32] | 2 | 4 | 2 |
| [5, 10] | 5 | 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의 전체 열을 평면화하려면 상관 교차 조인을 사용하여 ARRAY 열이 포함된 테이블을 ARRAY 열의 UNNEST 출력에 조인합니다.
상관 조인을 사용하면 UNNEST 연산자는 이전에 FROM 절에 표시된 소스 테이블의 각 행에 있는 ARRAY 유형 열을 참조합니다. 소스 테이블의 N 행에서 UNNEST가 N 행의 ARRAY를 ARRAY 요소가 포함된 행 집합으로 결합한 후 교차 조인에서 이 새로운 행 집합을 소스 테이블의 단일 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 |
*------+-------------------*/
참고로, 상관 교차 조인의 경우 UNNEST 연산자는 선택사항이며 CROSS JOIN은 쉼표 교차 조인으로 표현할 수 있습니다. 이 약식 표기법을 사용하면 이전 예시는 다음과 같이 통합됩니다.
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, 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 |
*------+-------------------*/
중첩 배열 쿼리
테이블에 STRUCT의 ARRAY가 포함된 경우 ARRAY를 평면화하여 STRUCT의 필드를 쿼리할 수 있습니다.
STRUCT 값의 ARRAY 유형 필드도 평면화할 수 있습니다.
배열의 STRUCT 요소 쿼리
다음 예시에서는 UNNEST를 CROSS JOIN과 함께 사용하여 STRUCT의 ARRAY를 평면화합니다.
WITH Races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)]
AS participants)
SELECT
race,
participant
FROM Races AS 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 laps),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants)
ORDER BY (
SELECT SUM(duration)
FROM UNNEST(laps) AS duration) ASC
LIMIT 1) AS fastest_racer
FROM Races;
/*------+---------------*
| race | fastest_racer |
+------+---------------+
| 800M | Rudisha |
*------+---------------*/
구조체의 ARRAY 유형 필드 쿼리
또한 중첩된 반복 필드에서 정보를 가져올 수도 있습니다. 예를 들어 다음 문은 800m 경주에서 랩타임이 가장 빠른 주자를 반환합니다.
WITH Races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants),
UNNEST(laps) 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 laps),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants)
CROSS JOIN UNNEST(laps) 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 laps),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps),
STRUCT("Nathan" AS name, ARRAY<FLOAT64>[] AS laps),
STRUCT("David" AS name, NULL AS laps)]
AS participants)
SELECT
name, sum(duration) AS finish_time
FROM Races CROSS JOIN Races.participants LEFT JOIN participants.laps AS 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 |
*-------------+--------------------*/
배열 생성
배열 리터럴 또는 배열 함수를 사용해서 배열을 구성할 수 있습니다. 배열 구성에 대한 자세한 내용은 배열 유형을 참조하세요.
서브 쿼리에서 배열 생성
배열을 다룰 때는 일반적으로 서브 쿼리 결과를 배열로 변환하는 작업을 거칩니다. GoogleSQL에서는 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 |
*---------------*/
배열과 집계
GoogleSQL에서 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) AS x) AS sums
FROM Sequences AS s;
/*--------------------+------*
| some_numbers | sums |
+--------------------+------+
| [0, 1, 1, 2, 3, 5] | 12 |
| [2, 4, 8, 16, 32] | 62 |
| [5, 10] | 15 |
*--------------------+------*/
GoogleSQL에서는 여러 행에 걸친 한 배열 열의 여러 요소를 연결하는 집계 함수인 ARRAY_CONCAT_AGG()도 지원됩니다.
WITH Aggregates 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 Aggregates;
/*--------------------------------------------------*
| count_to_six_agg |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
*--------------------------------------------------*/
배열의 문자열 변환
ARRAY_TO_STRING() 함수를 사용하면 ARRAY<STRING>을 단일 STRING 값으로 변환하거나 ARRAY<BYTES>를 단일 BYTES 값으로 변환할 수 있습니다. 그러면 결과 값으로 순서에 따라 연결된 배열 요소를 얻을 수 있습니다.
두 번째 인수는 함수에서 출력을 생성하기 위해 입력 사이에 삽입하는 구분자입니다. 이 두 번째 인수는 첫 번째 인수의 요소와 유형이 동일해야 합니다.
예시:
WITH Words AS
(SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM Words;
/*-------------*
| 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] |
*--------------------------------------------------*/
배열 업데이트
arrays_table이라는 테이블을 살펴보겠습니다. 테이블의 첫 번째 열은 정수 배열이고 두 번째 열은 두 개의 중첩된 정수 배열을 포함합니다.
WITH arrays_table AS (
SELECT
[1, 2] AS regular_array,
STRUCT([10, 20] AS first_array, [100, 200] AS second_array) AS nested_arrays
UNION ALL SELECT
[3, 4] AS regular_array,
STRUCT([30, 40] AS first_array, [300, 400] AS second_array) AS nested_arrays
)
SELECT * FROM arrays_table;
/*---------------*---------------------------*----------------------------*
| regular_array | nested_arrays.first_array | nested_arrays.second_array |
+---------------+---------------------------+----------------------------+
| [1, 2] | [10, 20] | [100, 200] |
| [3, 4] | [30, 40] | [130, 400] |
*---------------*---------------------------*----------------------------*/
UPDATE 문을 사용하여 테이블의 배열을 업데이트할 수 있습니다. 다음 예시에서는 숫자 5를 regular_array 열에 삽입하고 nested_arrays 열의 first_array 필드의 요소를 second_array 필드에 삽입합니다.
UPDATE
arrays_table
SET
regular_array = ARRAY_CONCAT(regular_array, [5]),
nested_arrays.second_array = ARRAY_CONCAT(nested_arrays.second_array,
nested_arrays.first_array)
WHERE TRUE;
SELECT * FROM arrays_table;
/*---------------*---------------------------*----------------------------*
| regular_array | nested_arrays.first_array | nested_arrays.second_array |
+---------------+---------------------------+----------------------------+
| [1, 2, 5] | [10, 20] | [100, 200, 10, 20] |
| [3, 4, 5] | [30, 40] | [130, 400, 30, 40] |
*---------------*---------------------------*----------------------------*/
배열 압축
크기가 동일한 두 개의 배열이 제공되면 해당 위치에서 가져온 입력 배열의 요소 쌍으로 구성된 단일 배열로 병합할 수 있습니다. 이 작업을 압축이라고도 합니다.
UNNEST 및 WITH OFFSET으로 배열을 압축할 수 있습니다. 이 예시에서 각 값 쌍은 배열에 STRUCT로 저장됩니다.
WITH
Combinations AS (
SELECT
['a', 'b'] AS letters,
[1, 2, 3] AS numbers
)
SELECT
ARRAY(
SELECT AS STRUCT
letters[SAFE_OFFSET(index)] AS letter,
numbers[SAFE_OFFSET(index)] AS number
FROM Combinations
CROSS JOIN
UNNEST(
GENERATE_ARRAY(
0,
LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
ORDER BY index
);
/*------------------------------*
| pairs |
+------------------------------+
| [{ letter: "a", number: 1 }, |
| { letter: "b", number: 2 }] |
*------------------------------*/
첫 번째 배열의 길이가 두 번째 배열의 길이와 동일하거나 짧은 경우 길이가 다른 입력 배열을 사용할 수 있습니다. 압축된 배열은 가장 짧은 입력 배열의 길이입니다.
입력 배열의 길이가 다르더라도 모든 요소를 포함하는 압축된 배열을 가져오려면 LEAST를 GREATEST로 변경합니다. 다른 배열에 연결된 요소가 없는 배열의 요소는 NULL과 페어링됩니다.
WITH
Combinations AS (
SELECT
['a', 'b'] AS letters,
[1, 2, 3] AS numbers
)
SELECT
ARRAY(
SELECT AS STRUCT
letters[SAFE_OFFSET(index)] AS letter,
numbers[SAFE_OFFSET(index)] AS number
FROM Combinations
CROSS JOIN
UNNEST(
GENERATE_ARRAY(
0,
GREATEST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
ORDER BY index
);
/*-------------------------------*
| pairs |
+-------------------------------+
| [{ letter: "a", number: 1 }, |
| { letter: "b", number: 2 }, |
| { letter: null, number: 3 }] |
*-------------------------------*/
배열의 배열 구성
GoogleSQL은 배열의 배열 직접 구성을 지원하지 않습니다. 대신 각 구조체에 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 |
+-------------------+
| [{point: [1,5]}, |
| {point: [2,8]}, |
| {point: [5,7]}, |
| {point: [3,7]}, |
| {point: [4,1]}] |
*-------------------*/