Dalam GoogleSQL untuk BigQuery, array adalah daftar berurutan yang terdiri dari nol atau beberapa
nilai dari jenis data yang sama. Anda dapat membuat array jenis data sederhana,
seperti INT64, dan jenis data kompleks, seperti STRUCT. Pengecualian
saat ini untuk jenis data ini adalah ARRAY karena array dari array
tidak didukung. Untuk mempelajari jenis data ARRAY
lebih lanjut, termasuk
penanganan NULL, baca Jenis array.
Dengan GoogleSQL, Anda dapat membuat literal array,
membangun array dari subkueri menggunakan
fungsi ARRAY,
dan menggabungkan nilai ke dalam array menggunakan
fungsi
ARRAY_AGG.
Anda dapat menggabungkan array menggunakan fungsi seperti
ARRAY_CONCAT(), dan mengonversi array menjadi string menggunakan ARRAY_TO_STRING().
Mengakses elemen array
Pertimbangkan tabel yang diemulasikan berikut yang disebut Sequences. Tabel ini berisi
kolom some_numbers dari jenis data ARRAY.
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] |
*---------------------*/
Untuk mengakses elemen array disome_numbers menentukan jenis pengindeksan
yang ingin Anda gunakan:
baikindex
atauOFFSET(index) untuk indeks
berbasis nol, atauORDINAL(index) untuk
indeks berbasis satu.
Contoh:
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 |
*--------------------+---------+----------+-----------*/
Mencari panjang
Fungsi ARRAY_LENGTH menampilkan panjang array.
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 |
*--------------------+--------*/
Mengonversi elemen dalam array menjadi baris dalam tabel
Untuk mengonversi ARRAY menjadi serangkaian baris, yang juga dikenal sebagai "perataan", gunakan operator
UNNEST. UNNEST menggunakan ARRAY dan menampilkan tabel dengan satu baris untuk
setiap elemen dalam ARRAY.
Karena UNNEST menghancurkan urutan elemen ARRAY, Anda mungkin
ingin memulihkan urutan ke tabel. Untuk melakukannya, gunakan klausa WITH OFFSET
opsional untuk menampilkan kolom tambahan dengan offset untuk setiap elemen array,
lalu gunakan klausa ORDER BY untuk mengurutkan baris berdasarkan offsetnya.
Contoh
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 |
*----------+--------*/
Untuk meratakan seluruh kolom ARRAY sekaligus mempertahankan nilai
kolom lain di setiap baris, gunakan
cross join yang dikorelasikan untuk menggabungkan tabel yang berisi
ARRAY ke output UNNEST dari kolom ARRAY tersebut.
Dengan penggabungan correlated, operator UNNEST
mereferensikan kolom berjenis ARRAY dari setiap baris dalam tabel sumber, yang
muncul sebelumnya dalam klausa FROM. Untuk setiap baris N dalam tabel sumber,
UNNEST meratakan ARRAY dari baris N menjadi kumpulan baris yang berisi
elemen ARRAY, lalu cross join menggabungkan kumpulan baris baru ini dengan
satu baris N dari tabel sumber.
Contoh
Contoh berikut menggunakan UNNEST
untuk menampilkan baris bagi setiap elemen di kolom array. Karena
CROSS JOIN, kolom id berisi nilai id untuk baris dalam
Sequences yang berisi setiap angka.
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 |
*------+-------------------*/
Perhatikan bahwa untuk cross join yang berkorelasi, operator UNNEST bersifat opsional dan
CROSS JOIN dapat dinyatakan sebagai cross join koma. Dengan menggunakan notasi
singkat ini, contoh sebelumnya digabungkan sebagai berikut:
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 |
*------+-------------------*/
Mengkueri array bertingkat
Jika tabel berisi ARRAY dari STRUCT, Anda dapat
meratakan ARRAY untuk mengkueri kolom STRUCT.
Anda juga dapat meratakan kolom jenis ARRAY dari nilai STRUCT.
Mengkueri elemen STRUCT dalam array
Contoh berikut menggunakan UNNEST dengan CROSS JOIN untuk meratakan ARRAY dari
STRUCT.
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]} |
*------+---------------------------------------*/
Anda dapat menemukan informasi spesifik dari kolom berulang. Misalnya, kueri berikut menampilkan pembalap tercepat dalam balapan 800M.
Contoh
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 |
*------+---------------*/
Mengkueri kolom jenis ARRAY dalam sebuah struct
Anda juga bisa mendapatkan informasi dari kolom berulang bertingkat. Misalnya, pernyataan berikut menampilkan pelari yang memiliki putaran tercepat dalam balapan 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 |
*------+-------------------------*/
Perhatikan bahwa kueri sebelumnya menggunakan operator koma (,) untuk menjalankan
CROSS JOIN implisit. Ini setara dengan contoh berikut, yang menggunakan
CROSS JOIN eksplisit.
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 |
*------+-------------------------*/
Meratakan array dengan CROSS JOIN mengecualikan baris yang memiliki array
kosong atau NULL. Jika Anda ingin menyertakan baris ini, gunakan 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 |
*-------------+--------------------*/
Membuat array
Anda dapat membuat array menggunakan literal array atau fungsi array. Untuk mempelajari lebih lanjut cara membuat array, lihat Jenis array.
Membuat array dari subkueri
Tugas umum saat menggunakan array adalah mengubah hasil subkueri menjadi
array. Di GoogleSQL, Anda dapat melakukannya menggunakan
fungsi ARRAY().
Misalnya, pertimbangkan operasi berikut pada tabel 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] |
*--------------------+---------------------*/
Contoh ini dimulai dengan tabel bernama Urutan. Tabel ini berisi kolom,
some_numbers, dengan jenis ARRAY<INT64>.
Kueri itu sendiri berisi subkueri. Subkueri ini memilih setiap baris dalam
kolom some_numbers dan menggunakan
UNNEST untuk menampilkan
array sebagai satu set baris. Selanjutnya, metode ini mengalikan setiap nilai dengan dua, lalu
menggabungkan kembali baris tersebut ke dalam array menggunakan operator ARRAY().
Memfilter array
Contoh berikut menggunakan klausa WHERE di subkueri operator ARRAY()
untuk memfilter baris yang ditampilkan.
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] |
| [] |
*------------------------*/
Perhatikan bahwa baris ketiga berisi array kosong, karena elemen dalam
baris asli yang sesuai ([5, 10]) tidak memenuhi persyaratan filter
x < 5.
Anda juga dapat memfilter array menggunakan SELECT DISTINCT untuk hanya menampilkan
elemen unik dalam array.
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] |
*-----------------*/
Anda juga dapat memfilter baris array menggunakan
kata kunci IN. Kata kunci
ini memfilter baris yang berisi array dengan menentukan apakah nilai
tertentu cocok dengan elemen dalam array.
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] |
| [] |
*--------------------*/
Perhatikan kembali bahwa baris ketiga berisi array kosong, karena array dalam
baris asli yang sesuai ([5, 10]) tidak berisi 2.
Memindai array
Untuk memeriksa apakah array berisi nilai tertentu, gunakan operator IN
dengan UNNEST. Untuk memeriksa apakah array berisi nilai
yang cocok dengan suatu kondisi, gunakan operator EXISTS dengan
UNNEST.
Memindai nilai-nilai tertentu
Untuk memindai array yang berisi nilai tertentu, gunakan operator IN dengan UNNEST.
Contoh
Contoh berikut menampilkan true jika array berisi angka 2.
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;
/*----------------*
| contains_value |
+----------------+
| true |
*----------------*/
Untuk menampilkan baris tabel tempat kolom array berisi nilai tertentu,
filter hasil IN UNNEST menggunakan klausa WHERE.
Contoh
Contoh berikut menampilkan nilai id untuk baris yang kolom
array-nya berisi nilai 2.
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 |
*---------------*/
Memindai nilai yang memenuhi suatu kondisi
Untuk memindai nilai yang cocok dengan kondisi pada array, gunakan UNNEST untuk menampilkan
tabel berisi elemen dalam array, gunakan WHERE untuk memfilter tabel yang dihasilkan di
subkueri, dan gunakan EXISTS untuk memeriksa apakah tabel yang difilter berisi baris.
Contoh
Contoh berikut menampilkan nilai id untuk baris yang kolom
array-nya berisi nilai yang lebih besar dari 5.
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 |
*---------------*/
Memindai nilai kolom STRUCT yang memenuhi kondisi
Guna menelusuri array STRUCT untuk kolom yang nilainya cocok dengan sebuah kondisi, gunakan
UNNEST untuk menampilkan tabel dengan kolom untuk setiap kolom STRUCT, lalu filter
baris yang tidak cocok dari tabel menggunakan WHERE EXISTS.
Contoh
Contoh berikut menampilkan baris yang kolom array-nya berisi
STRUCT yang kolom b-nya memiliki nilai lebih besar dari 3.
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 |
*---------------*/
Array dan agregasi
Dengan GoogleSQL, Anda dapat menggabungkan nilai ke dalam array menggunakan
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 yang ditampilkan oleh ARRAY_AGG() berada dalam urutan arbitrer, karena urutan
penyambungan nilai oleh fungsi tidak dijamin. Untuk mengurutkan elemen
array, gunakan ORDER BY. Contoh:
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] |
*-----------------------*/
Anda juga dapat menerapkan fungsi agregat seperti SUM() ke elemen dalam
array. Misalnya, kueri berikut menampilkan jumlah elemen array untuk
setiap baris dalam tabel 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 juga mendukung fungsi agregat, ARRAY_CONCAT_AGG(),
yang menyambungkan elemen kolom array di seluruh baris.
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] |
*--------------------------------------------------*/
Mengonversi array ke string
Fungsi ARRAY_TO_STRING() memungkinkan Anda mengonversi ARRAY<STRING> menjadi
satu nilai STRING atau ARRAY<BYTES> menjadi nilai BYTES tunggal dengan
nilai yang dihasilkan merupakan penyambungan elemen array secara berurutan.
Argumen kedua adalah pemisah yang akan disisipkan fungsi di antara input untuk menghasilkan output; argumen kedua ini harus dari jenis yang sama dengan elemen dari argumen pertama.
Contoh:
WITH Words AS
(SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM Words;
/*-------------*
| greetings |
+-------------+
| Hello World |
*-------------*/
Argumen ketiga opsional akan menggantikan nilai NULL dalam array
input.
Jika Anda menghilangkan argumen ini, fungsi tersebut akan mengabaikan elemen array
NULL.Jika Anda memberikan string kosong, fungsi tersebut akan menyisipkan pemisah untuk elemen array
NULL.
Contoh:
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 |
*------------------+--------------+---------*/
Menggabungkan array
Dalam beberapa kasus, Anda mungkin ingin menggabungkan beberapa array ke dalam satu array.
Anda dapat melakukannya menggunakan fungsi ARRAY_CONCAT().
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) AS count_to_six;
/*--------------------------------------------------*
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
*--------------------------------------------------*/
Memperbarui array
Pertimbangkan tabel berikut yang bernama arrays_table. Kolom pertama dalam
tabel adalah array bilangan bulat dan kolom kedua berisi dua array
bilangan bulat bertingkat.
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] |
*---------------*---------------------------*----------------------------*/
Anda dapat memperbarui array dalam tabel menggunakan pernyataan UPDATE. Contoh berikut
memasukkan angka 5 ke dalam kolom regular_array,
dan menyisipkan elemen dari kolom first_array kolom nested_arrays
ke dalam kolom 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] |
*---------------*---------------------------*----------------------------*/
Membuat zip array
Dengan mempertimbangkan dua array yang berukuran sama, Anda dapat menggabungkannya ke dalam satu array yang terdiri dari pasangan elemen dari array input, yang diambil dari posisinya yang sesuai. Operasi ini terkadang disebut zipping.
Anda dapat membuat zip array dengan UNNEST dan WITH OFFSET. Dalam contoh ini, setiap
pasangan nilai disimpan sebagai STRUCT dalam array.
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 }] |
*------------------------------*/
Anda dapat menggunakan array input dengan panjang yang berbeda asalkan array pertama sama dengan atau kurang dari panjang array kedua. Array yang di-zip akan menjadi panjang dari array input terpendek.
Untuk mendapatkan array yang di-zip yang menyertakan semua elemen meskipun array input
memiliki panjang yang berbeda, ubah LEAST menjadi GREATEST. Elemen dari kedua array
yang tidak memiliki elemen terkait dalam array lain akan dipasangkan dengan 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 }] |
*-------------------------------*/
Membuat array dari array
GoogleSQL tidak mendukung pembuatan
array dari array
secara langsung. Sebagai gantinya, Anda harus membuat array struct, dengan setiap struct
berisi kolom jenis ARRAY. Untuk mengilustrasikan hal ini, pertimbangkan tabel
Points berikut:
/*----------*
| point |
+----------+
| [1, 5] |
| [2, 8] |
| [3, 7] |
| [4, 1] |
| [5, 7] |
*----------*/
Sekarang, misalkan Anda ingin membuat array yang terdiri dari setiap point dalam
tabel Points. Untuk melakukannya, gabungkan array yang ditampilkan dari setiap baris dalam
STRUCT, seperti yang ditunjukkan di bawah ini.
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]}] |
*-------------------*/