Arrayfunktionen in Standard-SQL

ARRAY

ARRAY(subquery)

Beschreibung

Die Funktion ARRAY gibt ein mit genau einem Element für jede Zeile in einer Unterabfrage zurück.

Wenn durch die subquery eine SQL-Tabelle erzeugt wird, muss die Tabelle genau eine Spalte haben. Jedes Element im Ausgabe-ARRAY ist der Wert der einzigen Spalte einer Zeile in der Tabelle.

Wenn durch die subquery eine Wertetabelle erzeugt wird, entspricht jedes Element im Ausgabe-ARRAY der gesamten entsprechenden Zeile der Wertetabelle.

Einschränkungen

  • Unterabfragen sind ungeordnet. Deshalb wird die Reihenfolge in der Quelltabelle für die Unterabfrage von den Elementen des Ausgabe-ARRAY nicht zwingend beibehalten. Wenn die Unterabfrage jedoch die Klausel ORDER BY enthält, gibt die Funktion ARRAY ein ARRAY zurück, das diese Klausel beachtet.
  • Wenn bei der Unterabfrage mehr als eine Spalte zurückgegeben wird, gibt die ARRAY-Funktion einen Fehler zurück.
  • Wenn bei der Unterabfrage eine Spalte des Typs ARRAY oder Zeilen des Typs zurückgegeben werden, gibt die Funktion einen Fehler zurück: BigQuery unterstützt keine s mit Elementen des Typs .
  • Wenn bei der Unterabfrage keine Zeilen zurückgegeben werden, gibt die ARRAY-Funktion ein leeres zurück. Es wird nie ein NULL- zurückgegeben.

Rückgabetyp

ARRAY

Beispiele

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+

Wenn Sie ein ARRAY aus einer Unterabfrage mit mehreren Spalten erstellen möchten, ändern Sie die Unterabfrage so, dass SELECT AS STRUCT verwendet wird. Daraufhin gibt die ARRAY-Funktion ein ARRAY von STRUCTs zurück. Das ARRAY enthält ein STRUCT für jede Zeile in der Unterabfrage und jedes dieser STRUCTs enthält wiederum ein Feld für jede Spalte in dieser Zeile.

SELECT
  ARRAY
    (SELECT AS STRUCT 1, 2, 3
     UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;

+------------------------+
| new_array              |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+

Analog können Sie zum Erstellen eines ARRAY aus einer Unterabfrage, die mindestens ein enthält, die Unterabfrage so ändern, dass SELECT AS STRUCT verwendet wird.

SELECT ARRAY
  (SELECT AS STRUCT [1, 2, 3] UNION ALL
   SELECT AS STRUCT [4, 5, 6]) AS new_array;

+----------------------------+
| new_array                  |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+

ARRAY_CONCAT

ARRAY_CONCAT(array_expression_1 [, array_expression_n])

Beschreibung

Verkettet eine oder mehrere Arrays mit demselben Elementtyp zu einem einzelnen Array.

Rückgabetyp

ARRAY

Beispiele

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

+--------------------------------------------------+
| count_to_six                                     |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                               |
+--------------------------------------------------+

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

Beschreibung

Gibt die Größe des Arrays zurück. Gibt 0 für ein leeres Array zurück. Gibt NULL zurück, wenn array_expression gleich NULL ist.

Rückgabetyp

INT64

Beispiele

WITH items AS
  (SELECT ["coffee", NULL, "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [coffee, NULL, milk]            | 3    |
| [cake, pie]                     | 2    |
+---------------------------------+------+

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])

Beschreibung

Gibt eine Verkettung der Elemente in array_expression als STRING zurück. Der Wert für array_expression kann entweder ein Array von STRING- oder BYTES-Datentypen sein.

Wenn der Parameter null_text verwendet wird, ersetzt die Funktion alle NULL-Werte im Array durch den Wert von .

Wird der Parameter null_text nicht verwendet, lässt die Funktion den NULL-Wert und sein vorangehendes Trennzeichen weg.

Beispiele

WITH items AS
  (SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+

WITH items AS
  (SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

Beschreibung

Gibt ein Array von Werten zurück. Die Parameter start_expression und end_expression bestimmen den einzubeziehenden Start und das einzubeziehende Ende des Arrays.

Die Funktion GENERATE_ARRAY akzeptiert folgende Datentypen als Eingaben:

  • INT64
  • NUMERIC
  • FLOAT64

Der Parameter step_expression bestimmt das Inkrement, das zum Erzeugen von Arraywerten verwendet wird. Der Standardwert für diesen Parameter ist 1.

Diese Funktion gibt einen Fehler zurück, wenn step_expression auf 0 festgelegt oder eine Eingabe NaN ist.

Wenn eines der Argumente NULL ist, gibt die Funktion ein -Array zurück.

Rückgabedatentyp

ARRAY

Beispiele

Im folgenden Beispiel wird ein Array von Ganzzahlen mit einem Standardschritt von 1 zurückgegeben.

SELECT GENERATE_ARRAY(1, 5) AS example_array;

+-----------------+
| example_array   |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+

Im folgenden Beispiel wird ein Array mit einer benutzerdefinierten Schrittgröße zurückgegeben.

SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9]  |
+---------------+

Im folgenden Beispiel wird ein Array mit dem negativen Wert -3 als Schrittgröße zurückgegeben.

SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+

Im folgenden Beispiel wird ein Array mit demselben Wert für start_expression und end_expression zurückgegeben.

SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

+---------------+
| example_array |
+---------------+
| [4]           |
+---------------+

Im folgenden Beispiel wird ein leeres Array zurückgegeben, da start_expression größer als end_expression und der Wert für step_expression positiv ist.

SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| []            |
+---------------+

Im folgenden Beispiel wird ein NULL-Array zurückgegeben, da end_expression gleich ist.

SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;

+---------------+
| example_array |
+---------------+
| NULL          |
+---------------+

Im folgenden Beispiel werden mehrere Arrays zurückgegeben.

SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;

+---------------+
| example_array |
+---------------+
| [3, 4, 5]     |
| [4, 5]        |
| [5]           |
+---------------+

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

Beschreibung

Gibt ein Array von Daten zurück. Die Parameter start_date und end_date bestimmen den einzubeziehenden Start und das einzubeziehende Ende des Arrays.

Die Funktion GENERATE_DATE_ARRAY akzeptiert folgende Datentypen als Eingaben:

  • start_date muss ein DATE-Wert sein.
  • end_date muss ein DATE-Wert sein.
  • INT64_expr muss ein INT64-Wert sein.
  • date_part muss ein DAY-, WEEK-, MONTH-, QUARTER- oder YEAR-Wert sein.

Der Parameter INT64_expr bestimmt das Inkrement, das zum Erzeugen von Datumswerten verwendet wird. Der Standardwert für diesen Parameter ist 1 Tag.

Diese Funktion gibt einen Fehler zurück, wenn INT64_expr auf 0 festgelegt ist.

Rückgabedatentyp

Ein ARRAY mit mindestens 0 DATE-Werten.

Beispiele

Im folgenden Beispiel wird ein Array von Daten mit einem Standardschritt von 1 zurückgegeben.

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

+--------------------------------------------------+
| example                                          |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+

Im folgenden Beispiel wird ein Array mit einer benutzerdefinierten Schrittgröße zurückgegeben.

SELECT GENERATE_DATE_ARRAY(
 '2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;

+--------------------------------------+
| example                              |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+

Im folgenden Beispiel wird ein Array mit dem negativen Wert -3 als Schrittgröße zurückgegeben.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL -3 DAY) AS example;

+--------------------------+
| example                  |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+

Im folgenden Beispiel wird ein Array mit demselben Wert für start_date und end_date zurückgegeben.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-05', INTERVAL 8 DAY) AS example;

+--------------+
| example      |
+--------------+
| [2016-10-05] |
+--------------+

Im folgenden Beispiel wird ein leeres Array zurückgegeben, da start_date größer als end_date und der Wert für step positiv ist.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL 1 DAY) AS example;

+---------+
| example |
+---------+
| []      |
+---------+

Im folgenden Beispiel wird ein NULL-Array zurückgegeben, da eine seiner Eingaben ist.

SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

+---------+
| example |
+---------+
| NULL    |
+---------+

Im folgenden Beispiel wird ein Array von Datumswerten mit MONTH als date_part-Intervall zurückgegeben.

SELECT GENERATE_DATE_ARRAY('2016-01-01',
  '2016-12-31', INTERVAL 2 MONTH) AS example;

+--------------------------------------------------------------------------+
| example                                                                  |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+

Im folgenden Beispiel werden nicht konstante Daten zur Generierung eines Arrays verwendet.

SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;

+--------------------------------------------------------------+
| date_range                                                   |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+

GENERATE_TIMESTAMP_ARRAY

GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
                         INTERVAL step_expression date_part)

Beschreibung

Gibt ein ARRAY von TIMESTAMPS zurück, getrennt durch ein bestimmtes Intervall. Die Parameter start_timestamp und end_timestamp bestimmen die einzubeziehende untere und obere Grenze des ARRAY.

Die Funktion GENERATE_TIMESTAMP_ARRAY akzeptiert folgende Datentypen als Eingaben:

  • start_timestamp: TIMESTAMP
  • end_timestamp: TIMESTAMP
  • step_expression: INT64

  • Zulässige Werte für date_part:

    MICROSECOND, MILLISECOND,

    SECOND, MINUTE, HOUR oder DAY.

Der Parameter step_expression bestimmt das Inkrement, das zum Erzeugen von Zeitstempeln verwendet wird.

Rückgabedatentyp

Ein ARRAY mit 0 oder mehr TIMESTAMP-Werten.

Beispiele

Im folgenden Beispiel wird ein ARRAY von TIMESTAMPs in Intervallen von 1 Tag zurückgegeben.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
                                INTERVAL 1 DAY) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
+--------------------------------------------------------------------------+

Im folgenden Beispiel wird ein ARRAY von TIMESTAMPs in Intervallen von 1 Sekunde zurückgegeben.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
                                INTERVAL 1 SECOND) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
+--------------------------------------------------------------------------+

Im folgenden Beispiel wird ein ARRAY von TIMESTAMPS mit einem negativen Intervall zurückgegeben.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-01 00:00:00',
                                INTERVAL -2 DAY) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] |
+--------------------------------------------------------------------------+

Im folgenden Beispiel wird ein ARRAY mit einem einzelnen Element zurückgegeben, da start_timestamp und end_timestamp denselben Wert haben.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+--------------------------+
| timestamp_array          |
+--------------------------+
| [2016-10-05 00:00:00+00] |
+--------------------------+

Im folgenden Beispiel wird ein leeres ARRAY zurückgegeben, da start_timestamp nach end_timestamp liegt.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| []              |
+-----------------+

Im folgenden Beispiel wird ein Null-ARRAY zurückgegeben, da eine der Eingaben NULL ist.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', NULL, INTERVAL 1 HOUR)
  AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| NULL            |
+-----------------+

Im folgenden Beispiel werden ARRAYs von TIMESTAMPs aus Spalten erzeugt, die Werte für start_timestamp und end_timestamp enthalten.

SELECT GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL 1 HOUR)
  AS timestamp_array
FROM
  (SELECT
    TIMESTAMP '2016-10-05 00:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 02:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 12:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 14:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 23:59:00' AS start_timestamp,
    TIMESTAMP '2016-10-06 01:59:00' AS end_timestamp);

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 01:00:00+00, 2016-10-05 02:00:00+00] |
| [2016-10-05 12:00:00+00, 2016-10-05 13:00:00+00, 2016-10-05 14:00:00+00] |
| [2016-10-05 23:59:00+00, 2016-10-06 00:59:00+00, 2016-10-06 01:59:00+00] |
+--------------------------------------------------------------------------+

OFFSET und ORDINAL

array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]

Beschreibung

Greift auf ein ARRAY-Element nach Position zu und gibt das Element zurück. OFFSET bedeutet, dass die Nummerierung bei null beginnt, ORDINAL bedeutet, dass die Nummerierung bei eins beginnt.

Ein bestimmtes Array kann entweder als 0-basiert oder 1-basiert interpretiert werden. Beim Zugriff auf ein Arrayelement müssen Sie die Arrayposition mit OFFSET oder ORDINAL einleiten. Es gibt kein Standardverhalten.

Sowohl OFFSET als auch ORDINAL erzeugen einen Fehler, wenn der Index außerhalb des Bereichs liegt.

Rückgabetyp

Variiert je nach den Elementen im ARRAY.

Beispiele

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;

+----------------------------------+-----------+-----------+
| list                             | offset_1  | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas   | apples    |
| [coffee, tea, milk]              | tea       | coffee    |
| [cake, pie]                      | pie       | cake      |
+----------------------------------+-----------+-----------+

ARRAY_REVERSE

ARRAY_REVERSE(value)

Beschreibung

Gibt den Eingabe-ARRAY mit Elementen in umgekehrter Reihenfolge zurück.

Rückgabetyp

ARRAY

Beispiele

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr
)
SELECT
  arr,
  ARRAY_REVERSE(arr) AS reverse_arr
FROM example;

+-----------+-------------+
| arr       | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1]   |
| [4, 5]    | [5, 4]      |
| []        | []          |
+-----------+-------------+

SAFE_OFFSET und SAFE_ORDINAL

array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]

Beschreibung

Identisch mit OFFSET und ORDINAL, außer dass NULL zurückgegeben wird, wenn der Index außerhalb des Bereichs liegt.

Rückgabetyp

Variiert je nach den Elementen im ARRAY.

Beispiel

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list,
  list[SAFE_OFFSET(3)] as safe_offset_3,
  list[SAFE_ORDINAL(3)] as safe_ordinal_3
FROM items;

+----------------------------------+---------------+----------------+
| list                             | safe_offset_3 | safe_ordinal_3 |
+----------------------------------+---------------+----------------+
| [apples, bananas, pears, grapes] | grapes        | pears          |
| [coffee, tea, milk]              | NULL          | milk           |
| [cake, pie]                      | NULL          | NULL           |
+----------------------------------+---------------+----------------+