Cloud Spanner SQL에서 배열은 데이터 유형이 동일한 0개 이상의 값으로 구성된 순서가 지정된 목록입니다. INT64와 같은 간단한 데이터 유형과 STRUCT와 같은 복합 데이터 유형의 배열을 생성할 수 있습니다. 현재 ARRAY 데이터 유형은 예외입니다. 즉, 배열의 배열은 지원되지 않습니다. 배열에는 NULL 값이 포함될 수 있습니다.
Cloud Spanner SQL에서는 배열 리터럴을 생성하고, ARRAY 함수를 사용하여 하위 쿼리에서 배열을 구성하고, ARRAY_AGG 함수를 사용하여 값을 배열로 집계할 수 있습니다.
ARRAY_CONCAT() 등의 함수를 사용하여 배열을 결합하고 ARRAY_TO_STRING()을 통해 배열을 문자열로 변환할 수 있습니다.
배열 생성
배열 리터럴 사용
Cloud Spanner SQL에서 괄호([ 및 ])를 사용하여 배열 리터럴을 구성할 수 있습니다. 배열의 각 요소는 쉼표로 구분됩니다.
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>[]을 사용하여 특정 유형의 빈 배열을 작성할 수 있습니다. 또한 []를 사용하여 유형이 없는 빈 배열을 작성할 수도 있습니다. 이 경우, Cloud Spanner SQL은 주변 컨텍스트에서 배열 유형을 유추하려 합니다. Cloud Spanner SQL에서 유형을 유추할 수 없으면 기본 유형 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와 단계 INTERVAL에서 DATE의 배열을 생성합니다.
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 테이블을 참조하세요.
CREATE TABLE sequences (
id INT64 NOT NULL,
some_numbers ARRAY<INT64> NOT NULL
) PRIMARY KEY(id);
테이블에 다음 행이 채워져 있다고 가정합니다.
+----+---------------------+
| id | some_numbers |
+----+---------------------+
| 1 | [0, 1, 1, 2, 3, 5] |
| 2 | [2, 4, 8, 16, 32] |
| 3 | [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 |
+--------------------+----------+-----------+
이 쿼리는 OFFSET() 및 ORDINAL()을 사용하는 방법을 보여줍니다.
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 |
+--------------------+--------+
다음은 위의 sequences 테이블과 동일한 샘플 행을 포함하는 동일한 정의를 가정하는 예시 쿼리입니다.
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 연산자는 이전에 FROM 절에 표시된 소스 테이블의 각 행에 있는 ARRAY 열을 참조합니다. 소스 테이블의 N 행에서 UNNEST가 N 행의 ARRAY를 ARRAY 요소가 포함된 행 집합으로 평면화한 후 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 |
+------+-------------------+
상관 교차 조인의 경우 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 유형 필드도 평면화할 수 있습니다.
ARRAY의 STRUCT 요소 쿼리
다음 예시에서는 UNNEST를 CROSS JOIN과 함께 사용하여 STRUCT의 ARRAY를 평면화합니다.
SELECT race,
participant.name,
participant.splits
FROM
(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
) AS r
CROSS JOIN UNNEST(r.participants) AS participant;
+------+-------------+-----------------------+
| race | name | splits |
+------+-------------+-----------------------+
| 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 |
+------+---------------+
SELECT race,
(SELECT name
FROM UNNEST(participants)
ORDER BY (
SELECT SUM(duration)
FROM UNNEST(splits) AS duration) ASC
LIMIT 1) AS fastest_racer
FROM
(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
) AS r;
+------+---------------+
| 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 |
+------+-------------------------+
SELECT race,
(SELECT name
FROM UNNEST(participants),
UNNEST(splits) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM
(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
) AS r;
+------+-------------------------+
| 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 |
+------+-------------------------+
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
(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
) AS r;
+------+-------------------------+
| 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 |
+-------------+--------------------+
SELECT
name, sum(duration) as duration
FROM
(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("Nathan" as name, ARRAY<FLOAT64>[] as splits),
STRUCT("David" as name, NULL as splits)]
AS participants) AS races,
races.participants LEFT JOIN participants.splits duration
GROUP BY name;
+-------------+--------------------+
| name | duration |
+-------------+--------------------+
| Murphy | 102.9 |
| Rudisha | 102.19999999999999 |
| David | NULL |
| Rotich | 103.6 |
| Makhloufi | 102.6 |
| Bosse | 103.4 |
| Kipketer | 106 |
| Nathan | NULL |
| Lewandowski | 104.2 |
+-------------+--------------------+
서브 쿼리에서 배열 생성
배열을 다룰 때는 일반적으로 서브 쿼리 결과를 배열로 변환하는 작업을 거칩니다. Cloud Spanner SQL에서는 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] |
+--------------------+---------------------+
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] |
| [] |
+------------------------+
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] |
+-----------------+
SELECT ARRAY(SELECT DISTINCT x
FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences
WHERE id = 1;
+----------------+
| 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] |
| [] |
+--------------------+
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 |
+---------------+
배열과 집계
Cloud Spanner SQL에서 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] |
+-----------------------+
fruits 테이블을 참조하세요.
CREATE TABLE fruits (
fruit STRING(MAX),
id INT64 NOT NULL
) PRIMARY KEY(id);
테이블에 다음 데이터가 채워져 있다고 가정합니다.
+----+--------------+
| id | fruit |
+----+--------------+
| 1 | "apple" |
| 2 | "pear" |
| 3 | "banana" |
+----+--------------+
이 쿼리는 ARRAY_AGG()을 사용하는 방법을 보여줍니다.
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM fruits;
+---------------------+
| fruit_basket |
+---------------------+
| [apple,pear,banana] |
+---------------------+
이 함수는 값을 연결하는 순서를 보장하지 않으므로 ARRAY_AGG()에 의해 반환된 배열은 임의의 순서로 되어 있습니다.
또한 배열의 요소에 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 |
+--------------------+------+
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 |
+---------------+------+
Cloud Spanner SQL에서는 여러 행에 걸친 한 배열 열의 여러 요소를 연결하는 집계 함수인 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] |
+--------------------------------------------------+
배열의 배열 구성
Cloud Spanner SQL은 배열의 배열 직접 구성을 지원하지 않습니다. 대신 각 구조체에 ARRAY 유형의 필드를 포함하는 구조체 배열을 만듭니다. 이를 설명하기 위해 다음 points 테이블을 살펴보세요.
CREATE TABLE points (
point ARRAY<INT64>,
id INT64 NOT NULL
) PRIMARY KEY(id);
테이블에 다음 행이 채워져 있다고 가정합니다.
+----+----------+
| id | point |
+----+----------+
| 1 | [1, 5] |
| 2 | [2, 8] |
| 3 | [3, 7] |
| 4 | [4, 1] |
| 5 | [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]}] |
+--------------------+
SELECT ARRAY(
SELECT STRUCT(point)
FROM points)
AS coordinates;
+--------------+
| coordinates |
+--------------+
| point: [1,5] |
| point: [2,8] |
| point: [3,7] |
| point: [4,1] |
| point: [5,7] |
+--------------+