ARRAY
ARRAY(subquery)
Beschreibung
Die Funktion ARRAY
gibt ein ARRAY
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 nicht geordnet. Deshalb wird von den Elementen des Ausgabe-
ARRAY
die Reihenfolge in der Quelltabelle für die Unterabfrage nicht zwingend beibehalten. Wenn die Unterabfrage jedoch die KlauselORDER BY
enthält, gibt die FunktionARRAY
einARRAY
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 TypsARRAY
zurückgegeben werden, gibt die FunktionARRAY
einen Fehler zurück: BigQuery unterstützt keineARRAY
s mit Elementen des TypsARRAY
. - Wenn bei der Unterabfrage keine Zeilen zurückgegeben werden, gibt die
ARRAY
-Funktion ein leeresARRAY
zurück. Es wird nie einNULL
-ARRAY
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 STRUCT
s zurück. Das ARRAY
enthält ein STRUCT
für jede Zeile in der Unterabfrage und jedes dieser STRUCT
s 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 ARRAY
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[, ...])
Beschreibung
Verkettet eine oder mehrere Arrays mit demselben Elementtyp zu einem einzelnen Array.
Die Funktion gibt NULL
zurück, wenn eines der Eingabeargumente NULL
ist.
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 ARRAY_TO_STRING(list, ', ', 'NULL'), 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 null_text
.
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
BIGNUMERIC
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 NULL
-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 NULL
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 Generieren 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 NULL
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
date_part
-Werte sindMICROSECOND
,MILLISECOND
,SECOND
,MINUTE
,HOUR
undDAY
.
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 TIMESTAMP
s 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 TIMESTAMP
s 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 ARRAY
s von TIMESTAMP
s 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] |
+--------------------------------------------------------------------------+
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] |
| [] | [] |
+-----------+-------------+