Diese Seite wurde von der Cloud Translation API übersetzt.
Switch to English

Mit Arrays arbeiten

In Cloud Spanner SQL ist ein Array eine geordnete Liste, die aus null oder mehr Werten desselben Datentyps besteht. Sie können Arrays mit einfachen Datentypen wie INT64 und komplexen Datentypen wie STRUCTs erstellen. Ausgenommen ist derzeit der Datentyp ARRAY. Arrays von Arrays werden nicht unterstützt. Arrays können NULL-Werte enthalten.

Mit Cloud Spanner SQL können Sie Arrayliterale erstellen, mit der Funktion ARRAY Arrays aus Unterabfragen erstellen und Werte mit der Funktion ARRAY_AGG zu einem Array zusammenfassen.

Sie können Arrays mit Funktionen wie ARRAY_CONCAT() kombinieren und Arrays mit ARRAY_TO_STRING() in Strings konvertieren.

Arrays erstellen

Arrayliterale verwenden

Sie können in Cloud Spanner SQL ein Arrayliteral mit Klammern erstellen ([ und ]). Die einzelnen Elemente in einem Array werden durch Kommas getrennt.

SELECT [1, 2, 3] as numbers;

SELECT ["apple", "pear", "orange"] as fruit;

SELECT [true, false, true] as booleans;

Sie können Arrays auch aus Ausdrücken erstellen, die kompatible Typen haben. Beispiel:

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);

Beachten Sie, dass das zweite Beispiel drei Ausdrücke enthält, einen Ausdruck, der INT64 zurückgibt, einen Ausdruck, der FLOAT64 zurückgibt und einen Ausdruck, der ein Literal deklariert. Dieser Ausdruck funktioniert, weil alle drei Ausdrücke FLOAT64 als Supertyp haben.

Verwenden Sie spitze Klammern (< und >), um einen bestimmten Datentyp für ein Array zu deklarieren. Beispiel:

SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;

Sie müssen Arrays der meisten Datentypen, wie INT64 oder STRING, nicht zuerst deklarieren.

SELECT [1, 2, 3] as numbers;

Mit ARRAY<type>[] können Sie ein leeres Array eines bestimmten Typs schreiben. Außerdem ist es möglich, ein leeres Array ohne Typ mit [] zu schreiben. In diesem Fall versucht Cloud Spanner SQL, den Arraytyp aus dem umgebenden Kontext abzuleiten. Wenn Cloud Spanner SQL keinen Typ ableiten kann, wird der Standardtyp ARRAY<INT64> verwendet.

Generierte Werte verwenden

Sie können auch ein ARRAY mit generierten Werten erstellen.

Arrays mit Ganzzahlen erstellen

GENERATE_ARRAY generiert ein Array mit Werten aus einem Start- und einem Endwert sowie einem Schrittwert. Die folgende Abfrage generiert beispielsweise ein Array, das alle ungeraden Ganzzahlen von 11 bis einschließlich 33 enthält:

SELECT GENERATE_ARRAY(11, 33, 2) AS odds;

+--------------------------------------------------+
| odds                                             |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
+--------------------------------------------------+

Sie können auch ein Array von Werten in absteigender Reihenfolge generieren, indem Sie einen negativen Schrittwert angeben:

SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;

+----------------------------------+
| countdown                        |
+----------------------------------+
| [21, 20, 19, 18, 17, 16, 15, 14] |
+----------------------------------+

Arrays mit Daten generieren

GENERATE_DATE_ARRAY generiert ein Array von DATE-Werten aus einem Start- und einem End-DATE sowie einem Schrittwert INTERVAL.

Sie können eine Gruppe von DATE-Werten mit GENERATE_DATE_ARRAY erstellen. Die folgende Abfrage gibt z. B. das aktuelle Datum als DATE und die folgenden DATE-Werte in Intervallen von 1 Woche (WEEK) bis einschließlich eines späteren Datums als DATE aus:

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] |
+--------------------------------------------------------------------------+

Auf Arrayelemente zugreifen

Sehen Sie sich die folgende Tabelle sequences an:

CREATE TABLE sequences (
  id INT64 NOT NULL,
  some_numbers ARRAY<INT64> NOT NULL
) PRIMARY KEY(id);

Angenommen, die Tabelle wird mit den folgenden Zeilen gefüllt:

+----+---------------------+
| id | some_numbers        |
+----+---------------------+
| 1  | [0, 1, 1, 2, 3, 5]  |
| 2  | [2, 4, 8, 16, 32]   |
| 3  | [5, 10]             |
+----+---------------------+

Diese Tabelle enthält die Spalte some_numbers des Datentyps ARRAY. Für den Zugriff auf Elemente über Arrays in dieser Spalte müssen Sie angeben, welchen Indexierungstyp Sie verwenden möchten: entweder OFFSET für auf null basierende Indexe oder ORDINAL für auf eins basierende Indexe.

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         |
+--------------------+----------+-----------+

Diese Abfrage zeigt, wie OFFSET() und ORDINAL() verwendet werden:

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 |
+---------------+----------+-----------+

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      |
+--------------------+--------+

Es folgt eine Beispielabfrage, bei der dieselbe Definition der Tabelle sequences wie oben angenommen wird. Sie enthält die folgenden Beispielzeilen:

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 umwandeln

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 ARRAY vereinfachen möchten, ohne in jeder Zeile die Werte der anderen Spalten zu verlieren, verwenden Sie CROSS JOIN. So verknüpfen Sie die Tabelle, die die ARRAY-Spalte enthält, mit der UNNEST-Ausgabe dieser ARRAY-Spalte.

So funktioniert ein korrelierter Cross Join: Der UNNEST-Operator referenziert die Spalte mit den ARRAY-Elementen einer jeden Zeile der Quelltabelle aus der vorherigen FROM-Klausel. 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.

Beispiel

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 durch Kommas getrenntes Join ausgedrückt werden. Mit dieser Kurzschreibweise wird aus dem obigen Beispiel Folgendes:

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.

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]   |
+------+-------------+-----------------------+

In wiederkehrenden Feldern finden Sie spezifische Informationen. Beispiel: Die folgende Abfrage gibt den schnellsten Läufer in einem Rennen über 800 Meter zurück.

In diesem Beispiel wird keine Vereinfachung eines Arrays verwendet, sondern wird dargestellt, wie Informationen aus einem wiederkehrenden Feld im Allgemeinen abgerufen werden.

Beispiel

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       |
+------+---------------+

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 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                |
+------+-------------------------+

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 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                |
+------+-------------------------+

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 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              |
+-------------+--------------------+

Arrays aus Unterabfragen erstellen

Eine häufige Aufgabe bei der Arbeit mit Arrays besteht darin, ein Unterabfrageergebnis zu einem Array zu machen. In Cloud Spanner SQL erreichen Sie dies mit der Funktion ARRAY().

Betrachten Sie zum Beispiel den folgenden 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]            |
+--------------------+---------------------+
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.

Hinweis: In den folgenden Beispielen sind die Ergebniszeilen nicht sortiert.

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]                  |
+------------------------+
| []                     |
+------------------------+

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] |
+-----------------+
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]    |
+----------------+

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]  |
| []                 |
+--------------------+
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 die Funktion 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 ein Array nach Werten durchsuchen 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             |
+---------------+

Nach STRUCT-Feldwerten suchen, 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 Cloud Spanner SQL 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] |
+-----------------------+

Sehen Sie sich die folgende Tabelle an, fruits:

CREATE TABLE fruits (
  fruit STRING(MAX),
  id INT64 NOT NULL
) PRIMARY KEY(id);

Angenommen, die Tabelle wird mit den folgenden Daten gefüllt:

+----+--------------+
| id | fruit        |
+----+--------------+
| 1  | "apple"      |
| 2  | "pear"       |
| 3  | "banana"     |
+----+--------------+

Diese Abfrage zeigt, wie ARRAY_AGG() verwendet wird:

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.

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) 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 unterstützt auch die Aggregatfunktion ARRAY_CONCAT_AGG(), die die Elemente einer Arrayspalte über Zeilen hinweg verkettet.

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]                               |
+--------------------------------------------------+

Hinweis: Das von ARRAY_CONCAT_AGG() zurückgegebene Array ist nicht deterministisch, da die Reihenfolge, in der die Funktion Werte verkettet, nicht garantiert ist.

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 greetings AS
  (SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM greetings;

+-------------+
| 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 von Arrays erstellen

In Cloud Spanner SQL 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:

CREATE TABLE points (
  point ARRAY<INT64>,
  id INT64 NOT NULL
) PRIMARY KEY(id);

Angenommen, die Tabelle wird mit den folgenden Zeilen gefüllt:

+----+----------+
| id | point    |
+----+----------+
| 1  | [1, 5]   |
| 2  | [2, 8]   |
| 3  | [3, 7]   |
| 4  | [4, 1]   |
| 5  | [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]}]  |
+--------------------+
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] |
+--------------+