In GoogleSQL for BigQuery ist ein Array eine sortierte Liste aus null oder mehr Werten des gleichen Datentyps. Sie können Arrays mit einfachen Datentypen wie INT64 und komplexen Datentypen wie STRUCTs erstellen. Die aktuelle Ausnahme ist der ARRAY-Datentyp, denn Arrays von Arrays werden nicht unterstützt. Weitere Informationen zum Datentyp ARRAY, einschließlich der Behandlung von NULL, finden Sie unter Array-Typ.
Mit GoogleSQL können Sie Arrayliterale anlegen, mit der Funktion ARRAY Arrays aus Unterabfragen erstellen und mit der Funktion ARRAY_AGG Werte zu einem Array zusammenfassen.
Sie können Arrays mit Funktionen wie ARRAY_CONCAT() kombinieren und Arrays mit ARRAY_TO_STRING() in Strings konvertieren.
Auf Arrayelemente zugreifen
Betrachten Sie die folgende emulierte Tabelle mit dem Namen Sequences. Diese Tabelle enthält die Spalte some_numbers des Datentyps 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] |
*---------------------*/
Für den Zugriff auf Arrayelemente in der Spalte some_numbers müssen Sie angeben, welchen Indexierungstyp Sie verwenden möchten: entweder index oder OFFSET(index) für auf null basierende Indexe oder ORDINAL(index) für einsbasierte Indexe.
Beispiel:
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 |
*--------------------+---------+----------+-----------*/
Längenwerte suchen
Die Funktion ARRAY_LENGTH gibt die Länge eines Arrays zurück.
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 |
*--------------------+--------*/
Elemente in einem Array in Zeilen einer Tabelle konvertieren
Verwenden Sie den Operator UNNEST, um ein ARRAY in einen Zeilensatz zu konvertieren. Dies wird auch als Vereinfachung bezeichnet. UNNEST gibt dann für das ARRAY eine Tabelle mit jeweils einer Zeile für jedes Element im ARRAY zurück.
Allerdings verändert UNNEST die Anordnung der ARRAY-Elemente. Falls Sie die ursprüngliche Anordnung innerhalb der Tabelle wiederherstellen möchten, tun Sie Folgendes: Verwenden Sie die optionale Klausel WITH OFFSET, um eine zusätzliche Spalte mit dem Versatz für jedes Arrayelement zurückzugeben. Verwenden Sie dann die Klausel ORDER BY, um die Zeilen nach deren Versatz zu ordnen.
Beispiel
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 |
*----------+--------*/
Wenn Sie eine komplette Spalte von ARRAYs vereinfachen möchten, ohne in jeder Zeile die Werte der anderen Spalten zu verlieren, verwenden Sie einen korrelierten Cross Join, um die Tabelle, in der die ARRAY-Spalte enthalten ist, mit der UNNEST-Ausgabe dieser ARRAY-Spalte zu verknüpfen.
Bei einem korrelierten Join verweist der Operator UNNEST auf die typisierte ARRAY-Spalte aus jeder Zeile in der Quelltabelle, die zuvor in der FROM-Klausel angezeigt wurde. Für jede Zeile N in der Quelltabelle vereinfacht UNNEST das ARRAY aus Zeile N zu einem Zeilensatz, der die ARRAY-Elemente enthält. Anschließend wird dieser neue Zeilensatz mithilfe von Cross Join mit der einzelnen Zeile N der Quelltabelle zusammengeführt.
Beispiele
Im folgenden Beispiel wird mithilfe von UNNEST für jedes Element in der Arrayspalte eine Zeile zurückgegeben. Durch den CROSS JOIN enthält die Spalte id die id-Werte für die Zeile in Sequences, die jede Zahl enthält.
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 |
*------+-------------------*/
Der Operator UNNEST bei korrelierten Cross Joins ist optional und CROSS JOIN kann als ein Komma Cross Join ausgedrückt werden. Mit dieser Kurzschreibweise wird das vorherige Beispiel so konsolidiert:
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 |
*------+-------------------*/
Verschachtelte Arrays abfragen
Enthält eine Tabelle ein ARRAY aus STRUCT-Werten, können Sie das ARRAY vereinfachen, um die Felder von STRUCT abzufragen.
Sie können auch Felder vom Typ ARRAY mit Werten von STRUCT vereinfachen.
STRUCT-Elemente in einem Array abfragen
Im folgenden Beispiel wird UNNEST mit CROSS JOIN verwendet, um ein ARRAY von STRUCT-Werten zu vereinfachen.
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]} |
*------+---------------------------------------*/
In wiederkehrenden Feldern finden Sie spezifische Informationen. Beispiel: Die folgende Abfrage gibt den schnellsten Läufer in einem Rennen über 800 Meter zurück.
Beispiel
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 |
*------+---------------*/
Felder des Typs ARRAY in einem STRUCT abfragen
Sie können auch Informationen aus verschachtelten wiederkehrenden Feldern abrufen. Beispiel: Die folgende Anweisung gibt den Läufer zurück, der die schnellste Runde in dem 800 Meter-Lauf gelaufen ist.
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 |
*------+-------------------------*/
Beachten Sie, dass bei der vorherigen Abfrage der Kommaoperator verwendet wird (,), um eine implizite CROSS JOIN auszuführen. Dies entspricht dem folgenden Beispiel, in dem ein expliziter CROSS JOIN verwendet wird.
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 |
*------+-------------------------*/
Durch das Vereinfachen von Arrays mit einem CROSS JOIN werden Zeilen mit leeren oder NULL-Arrays ausgeschlossen. Wenn Sie diese Zeilen einbinden möchten, verwenden Sie 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 |
*-------------+--------------------*/
Arrays erstellen
Sie können ein Array mit Arrayliteralen oder Arrayfunktionen erstellen. Weitere Informationen zum Erstellen von Arrays finden Sie unter Array-Typ.
Arrays aus Unterabfragen erstellen
Eine häufige Aufgabe bei der Arbeit mit Arrays besteht darin, ein Unterabfrageergebnis zu einem Array zu machen. In GoogleSQL können Sie dies mithilfe von ARRAY() erreichen.
Ein Beispiel dafür ist der folgende Vorgang für die Tabelle 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] |
*--------------------+---------------------*/
Dieses Beispiel beginnt mit einer Tabelle namens „sequences“. Diese Tabelle enthält eine Spalte some_numbers vom Typ ARRAY<INT64>.
Die Abfrage selbst enthält eine Unterabfrage. Bei dieser Unterabfrage wird jede Zeile in der Spalte some_numbers ausgewählt und UNNEST verwendet, um das Array als Zeilensatz zurückzugeben. Anschließend wird jeder Wert mit zwei multipliziert und dann werden die Zeilen mithilfe des Operators ARRAY() wieder zu einem Array zusammengesetzt.
Arrays filtern
Im folgenden Beispiel wird in der Unterabfrage des Operators ARRAY() eine WHERE-Klausel verwendet, um die zurückgegebenen Zeilen zu filtern.
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] |
| [] |
*------------------------*/
Beachten Sie, dass die dritte Zeile ein leeres Array enthält, da die Elemente in der entsprechenden ursprünglichen Zeile ([5, 10]) nicht die Filteranforderung von x < 5 erfüllen.
Sie können Arrays auch mit SELECT DISTINCT filtern, um nur einzelne Elemente innerhalb eines Arrays zurückzugeben.
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] |
*-----------------*/
Sie können Zeilen von Arrays auch mithilfe des Schlüsselworts IN filtern. Dieses Schlüsselwort filtert Zeilen, die Arrays enthalten, indem bestimmt wird, ob ein spezifischer Wert mit einem Element in dem Array übereinstimmt.
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] |
| [] |
*--------------------*/
Wieder enthält die dritte Zeile ein leeres Array, weil das Array in der entsprechenden Originalzeile ([5, 10]) 2 nicht enthielt.
In Arrays suchen
Um in einem Array nach einem bestimmten Wert zu suchen, verwenden Sie den Operator IN mit UNNEST. Wenn Sie prüfen möchten, ob ein Array einen Wert enthält, der eine bestimmte Bedingung erfüllt, verwenden Sie den Operator EXISTS mit UNNEST.
Nach bestimmten Werten suchen
Verwenden Sie den Operator IN mit UNNEST, um ein Array nach einem bestimmten Wert zu durchsuchen.
Beispiel
Im folgenden Beispiel wird true zurückgegeben, wenn das Array die Zahl 2 enthält.
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;
/*----------------*
| contains_value |
+----------------+
| true |
*----------------*/
Wenn die Zeilen einer Tabelle zurückgegeben werden sollen, deren Arrayspalte einen bestimmten Wert enthält, müssen Sie die Ergebnisse von IN UNNEST mit der Klausel WHERE filtern.
Beispiel
Im folgenden Beispiel wird der Wert id für die Zeilen zurückgegeben, in denen die Arrayspalte den Wert 2 enthält.
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 |
*---------------*/
Nach Werten suchen, die eine Bedingung erfüllen
Wenn Sie in einem Array Werte suchen möchten, die eine Bedingung erfüllen, können Sie UNNEST verwenden, um eine Tabelle der Elemente im Array zurückzugeben. Mithilfe von WHERE können Sie die resultierende Tabelle über eine Unterabfrage filtern und mithilfe von EXISTS können Sie überprüfen, ob die gefilterte Tabelle Zeilen enthält.
Beispiel
Im folgenden Beispiel wird der Wert id für die Zeilen zurückgegeben, in denen die Arrayspalte Werte größer als 5 enthält.
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 |
*---------------*/
Scannen nach STRUCT-Feldwerten, die eine Bedingung erfüllen
Wenn Sie ein Array von STRUCT nach Feldern durchsuchen möchten, deren Wert eine bestimmte Bedingung erfüllt, verwenden Sie UNNEST, um eine Tabelle mit jeweils einer Spalte für jedes STRUCT-Feld zurückzugeben, und filtern Sie dann nicht übereinstimmende Zeilen mit WHERE EXISTS aus der Tabelle heraus.
Beispiel
Im folgenden Beispiel werden die Zeilen zurückgegeben, in denen die Arrayspalte ein STRUCT enthält, dessen Feld b einen Wert größer als 3 hat.
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 |
*---------------*/
Arrays und Aggregation
Mithilfe von ARRAY_AGG() können Sie in GoogleSQL Werte in einem Array zusammenfassen.
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] |
*-----------------------*/
Das von ARRAY_AGG() zurückgegebene Array ist in einer beliebigen Reihenfolge angeordnet, weil die Reihenfolge, in der die Funktion Werte verkettet, nicht garantiert ist. Verwenden Sie zum Sortieren der Arrayelemente ORDER BY. Beispiel:
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] |
*-----------------------*/
Sie können auch Aggregatfunktionen wie SUM() auf die Elemente in einem Array anwenden. Die folgende Abfrage gibt beispielsweise die Summe der Arrayelemente für jede Zeile der Tabelle Sequences zurück.
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 unterstützt auch eine Aggregatfunktion ARRAY_CONCAT_AGG(), die die Elemente einer Arrayspalte zeilenübergreifend verkettet.
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] |
*--------------------------------------------------*/
Arrays in Strings umwandeln
Mit der Funktion ARRAY_TO_STRING() können Sie ein ARRAY<STRING> in einen einzelnen STRING-Wert oder ein ARRAY<BYTES> in einen einzelnen BYTES-Wert umwandeln, wobei der resultierende Wert die geordnete Verkettung der Arrayelemente ist.
Das zweite Argument ist das Trennzeichen, das für die Ausgabe zwischen den Eingaben einfügt wird. Dieses zweite Argument muss vom gleichen Typ wie die Elemente des ersten Arguments sein.
Beispiel:
WITH Words AS
(SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM Words;
/*-------------*
| greetings |
+-------------+
| Hello World |
*-------------*/
Das optionale dritte Argument wird im Eingabearray für NULL-Werte verwendet.
Wenn Sie dieses Argument weglassen, werden
NULL-Arrayelemente ignoriert.Wenn Sie eine leere Zeichenfolge angeben, wird für
NULL-Arrayelemente ein Trennzeichen eingefügt.
Beispiel:
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 |
*------------------+--------------+---------*/
Arrays kombinieren
Gelegentlich sollen mehrere Arrays zu einem einzelnen Array kombiniert werden.
Dies erreichen Sie mit der Funktion 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 aktualisieren
Betrachten Sie die folgende Tabelle mit dem Namen arrays_table. Die erste Spalte in der Tabelle ist ein Array von Ganzzahlen und die zweite Spalte zwei verschachtelte Arrays mit Ganzzahlen.
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] |
*---------------*---------------------------*----------------------------*/
Sie können Arrays in einer Tabelle mit der Anweisung UPDATE aktualisieren. Im folgenden Beispiel wird die Zahl 5 in die Spalte regular_array und die Elemente aus dem Feld first_array der Spalte nested_arrays in das Feld second_array eingefügt:
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] |
*---------------*---------------------------*----------------------------*/
Arrays komprimieren
Zwei Arrays gleicher Größe können Sie zu einem einzigen Array zusammenführen, das dann aus Paaren von Elementen aus Eingabearrays besteht, die aus den entsprechenden Positionen stammen. Dieser Vorgang wird manchmal als Komprimieren bezeichnet.
Sie können Arrays mit UNNEST und WITH OFFSET komprimieren. In diesem Beispiel wird jedes Wertepaar als STRUCT in einem Array gespeichert.
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 }] |
*------------------------------*/
Sie können Eingabearrays unterschiedlicher Länge verwenden, solange das erste Array gleich oder kleiner als das zweite Array ist. Das komprimierte Array entspricht dann der Länge des kürzesten Eingabearrays.
Wenn Sie ein komprimiertes Array mit allen Elementen erhalten möchten, selbst wenn die Eingabearrays unterschiedlicher Länge sind, ändern Sie LEAST in GREATEST. Elemente in einem der Arrays ohne verknüpfte Elemente im jeweils anderen Array werden mit NULL gekoppelt.
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 }] |
*-------------------------------*/
Arrays von Arrays erstellen
In GoogleSQL können Arrays von Arrays nicht direkt erstellt werden. Stattdessen können Sie aber ein Array von Structs erstellen, in dem jedes Struct ein Feld vom Typ ARRAY enthält. Betrachten Sie zur Veranschaulichung die folgende Points-Tabelle:
/*----------*
| point |
+----------+
| [1, 5] |
| [2, 8] |
| [3, 7] |
| [4, 1] |
| [5, 7] |
*----------*/
Angenommen, Sie möchten ein Array aus allen point in der Tabelle Points erstellen. Fassen Sie zu diesem Zweck das aus jeder Zeile zurückgegebene Array wie unten dargestellt in einem STRUCT zusammen.
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]}] |
*-------------------*/