Standard-SQL-Funktionen und -Operatoren

Auf dieser Seite werden BigQuery-Ausdrücke einschließlich ihrer Funktionen und Operatoren beschrieben.

Funktionsaufrufregeln

Die folgenden Regeln gelten für alle Funktionen, sofern nicht in der Funktionsbeschreibung ausdrücklich anders angegeben:

  • Bei Funktionen, die numerische Typen akzeptieren, wenn ein Operand ein Gleitkomma-Operand und der andere Operand ein anderer numerischer Typ ist, werden beide Operanden in FLOAT64 umgewandelt, bevor die Funktion ausgewertet wird.
  • Ist ein Operand NULL, ist das Ergebnis mit Ausnahme des IS-Operators NULL.

  • Bei Funktionen, bei denen die Zeitzone beachtet wird (wie in der Funktionsbeschreibung angegeben), wird, wenn nicht explizit anders angegeben, UTC als Standardzeitzone verwendet.

SAFE.-Präfix

Syntax:

SAFE.function_name()

Beschreibung

Wenn Sie eine Funktion mit dem Präfix SAFE. beginnen, wird anstelle eines Fehlers NULL zurückgeben. Das Präfix SAFE. verhindert nur Fehler bei der mit dem Präfix versehenen Funktion selbst. Es verhindert keine Fehler, die bei der Auswertung von Argumentausdrücken auftreten. Mit dem Präfix SAFE. werden nur Fehler verhindert, die aufgrund des Werts der Funktionseingaben auftreten, z. B. wenn der Wert außerhalb des zulässigen Bereichs liegt. Andere Fehler, wie interne Fehler oder Systemfehler, können weiterhin auftreten. Wenn die Funktion keinen Fehler zurückgibt, hat SAFE. keine Auswirkungen auf die Ausgabe. Wenn die Funktion nie einen Fehler zurückgibt, wie bei RAND, hat SAFE. keinerlei Wirkung.

Von Operatoren wie + und = wird das Präfix SAFE. nicht unterstützt. Sie vermeiden Fehler bei einer Division, indem Sie SAFE_DIVIDE verwenden. Manche Operatoren wie IN, ARRAY und UNNEST sind Funktionen ähnlich, unterstützen aber das Präfix SAFE. nicht. Die Funktionen CAST und EXTRACT unterstützen das Präfix SAFE. ebenfalls nicht. Verwenden Sie SAFE_CAST, um Fehler bei der Umwandlung zu vermeiden.

Beispiel

Im folgenden Beispiel würde bei der ersten Verwendung der SUBSTR-Funktion normalerweise ein Fehler zurückgegeben werden, weil die Funktion keine Längenargumente mit negativen Werten unterstützt. Das Präfix SAFE. sorgt aber dafür, dass die Funktion stattdessen NULL zurückgibt. Die zweite Verwendung der SUBSTR-Funktion führt zur erwarteten Ausgabe: Das Präfix SAFE. hat keine Wirkung.

SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;

+-------------+
| safe_output |
+-------------+
| NULL        |
| ba          |
+-------------+

Unterstützte Funktionen

BigQuery unterstützt die Verwendung des Präfixes SAFE. bei den meisten Skalarfunktionen, die Fehler verursachen können, einschließlich STRING-Funktionen, mathematischen Funktionen, DATE-Funktionen, DATETIME-Funktionen und TIMESTAMP-Funktionen. BigQuery unterstützt die Verwendung des Präfixes SAFE. nicht bei aggregierten Funktionen, Analysefunktionen und benutzerdefinierten Funktionen.

Konversionsregeln

"Konversion" umfasst unter anderem die Prozesse Umwandeln und Erzwingen.

  • Das Umwandeln ist eine explizite Konversion und verwendet die Funktion CAST().
  • Erzwingen ist eine von BigQuery unter nachfolgend beschriebenen Bedingungen automatisch durchgeführte implizite Konversion.
  • Es gibt eine dritte Gruppe von Konversionsfunktionen, die einen eigenen Funktionsnamen haben, zum Beispiel UNIX_DATE().

Nachfolgende Tabelle fasst alle möglichen Umwandlungs- (CAST-) und Erzwingungsmöglichkeiten für BigQuery-Datentypen zusammen. "Erzwingen in" gilt für alle Ausdrücke eines gegebenen Datentyps (z. B. eine Spalte), aber Literale und Parameter können auch erzwungen werden. Weitere Informationen finden Sie unter Literale erzwingen und Parameter erzwingen.

Von Typ Umwandeln in Erzwingen in
INT64 BOOL
INT64
NUMERIC
FLOAT64
STRING
FLOAT64
NUMERIC
NUMERIC INT64
NUMERIC
FLOAT64
STRING
FLOAT64
FLOAT64 INT64
NUMERIC
FLOAT64
STRING
 
BOOL BOOL
INT64
STRING
 
STRING BOOL
INT64
NUMERIC
FLOAT64
STRING
BYTES
DATE
DATETIME
TIME
TIMESTAMP
 
BYTES BYTES
STRING
 
DATE DATE
DATETIME
STRING
TIMESTAMP
 
DATETIME DATE
DATETIME
STRING
TIME
TIMESTAMP
 
TIME STRING
TIME
 
TIMESTAMP DATE
DATETIME
STRING
TIME
TIMESTAMP
 
ARRAY ARRAY  
STRUCT STRUCT  

Umwandeln

Syntax:

CAST(expr AS typename)

Die Umwandlungssyntax wird in einer Abfrage verwendet, um anzuzeigen, dass der Ergebnistyp eines Ausdrucks in einen anderen Typ umgewandelt werden soll.

Beispiel:

CAST(x=1 AS STRING)

Daraus ergibt sich "true", wenn x gleich 1 ist, "false" für jeden anderen Nicht-NULL-Wert und NULL, wenn x gleich NULL ist.

Umwandlungen von unterstützten Typen, die vom Originalwert nicht erfolgreich der Zieldomain zugeordnet werden können, erzeugen Laufzeitfehler. Zum Beispiel führt das Umwandeln von BYTES in STRING bei nicht gültiger UTF-8-Codierung der Bytesequenz zu einem Laufzeitfehler.

Beim Umwandeln eines Ausdrucks x nachfolgender Typen gelten diese Regeln:

Von Zu Regeln beim Umwandeln von x
INT64 FLOAT64 Gibt einen annähernden, aber möglicherweise keinen exakten FLOAT64-Wert zurück.
INT64 BOOL Gibt FALSE zurück, wenn x gleich 0 ist, ansonsten TRUE.
NUMERIC Gleitkomma NUMERIC wird in die nächste Gleitkommazahl konvertiert, was mit einem Genauigkeitsverlust verbunden sein kann.
FLOAT64 INT64 Gibt den am nächsten liegenden INT64-Wert zurück.
Halbe Zahlen wie 1,5 oder -0,5 runden von 0 weg.
FLOAT64 STRING Gibt eine ungefähre Stringdarstellung zurück.
FLOAT64 NUMERIC Wenn die Gleitkommazahl mehr als neun Nachkommastellen hat, werden halbe Zahlen von 0 weggerundet. Beim Umwandeln von NaN, +inf und -inf wird ein Fehler zurückgegeben. Beim Umwandeln eines Werts außerhalb des Bereichs von NUMERIC wird ein Überlauffehler zurückgegeben.
BOOL INT64 Gibt 1 zurück, wenn x gleich TRUE ist, ansonsten 0.
BOOL STRING Gibt "true" zurück, wenn x gleich TRUE ist, ansonsten "false".
STRING FLOAT64 Gibt x als einen FLOAT64-Wert zurück und interpretiert diesen als ein gültiges FLOAT64-Literal.
Unterstützt auch Umwandlungen von "inf", "+inf", "-inf", und "nan".
Bei der Konversion wird die Groß-/Kleinschreibung nicht beachtet.
STRING NUMERIC Das in STRING enthaltene numerische Literal darf die maximale Genauigkeit oder den maximalen Bereich des Typs NUMERIC nicht überschreiten. Andernfalls tritt ein Fehler auf. Wenn die Anzahl der Nachkommastellen größer als 9 ist, wird der resultierende NUMERIC-Wert bei halben Zahlen so von 0 weggerundet, dass neun Nachkommastellen übrig bleiben.
STRING BOOL Gibt TRUE zurück, wenn x gleich "true" ist, und FALSE, wenn x gleich "false" ist.
Andere Werte von x sind ungültig und erzeugen einen Fehler, statt in BOOL umgewandelt zu werden.
Bei STRINGs wird die Groß-/Kleinschreibung bei der Umwandlung in BOOL nicht beachtet.
STRING BYTES STRINGs werden mithilfe der UTF-8-Codierung in BYTES umgewandelt. Der STRING "©" wird beispielsweise bei der Umwandlung in BYTES eine 2-Byte-Sequenz mit den Hex-Werten C2 und A9.
BYTES STRING Gibt x als UTF-8-STRING interpretiert zurück.
Das BYTES-Literal b'\xc2\xa9' wird beispielsweise bei der Umwandlung in STRING als UTF-8 interpretiert und wird zum Unicode-Zeichen "©".
Falls x keine gültige UTF-8-Codierung ist, tritt ein Fehler auf.
ARRAY ARRAY Muss exakt derselbe ARRAY-Typ sein.
STRUCT STRUCT Zulässig, wenn folgende Bedingungen erfüllt sind:
  1. Die beiden STRUCTs haben dieselbe Feldanzahl.
  2. Die ursprünglichen STRUCT-Feldtypen können explizit in die entsprechenden Ziel-STRUCT-Feldtypen (wie nach Feldreihenfolge, nicht nach Feldname definiert) umgewandelt werden.

Sicheres Umwandeln

Bei der Umwandlung (CAST) kann eine Abfrage fehlschlagen, wenn BigQuery die Umwandlung nicht ausführen kann. Die folgende Abfrage erzeugt beispielsweise einen Fehler:

SELECT CAST("apple" AS INT64) AS not_a_number;

Wenn Sie Ihre Abfragen vor diesen Fehlern schützen möchten, können Sie SAFE_CAST verwenden. SAFE_CAST ist identisch zu CAST, aber anstatt einen Fehler zu melden, wird NULL zurückgegeben.

SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;

+--------------+
| not_a_number |
+--------------+
| NULL         |
+--------------+

Zur Umwandlung von Byte in Strings können Sie auch die Funktion SAFE_CONVERT_BYTES_TO_STRING verwenden. Alle ungültigen UTF-8-Zeichen werden durch das Unicode-Ersatzzeichen U+FFFD ersetzt. Weitere Informationen finden Sie unter SAFE_CONVERT_BYTES_TO_STRING.

Hex-Strings in ganze Zahlen umwandeln

Wenn Sie mit Hex-Strings (0x123) arbeiten, können Sie diese Strings in ganze Zahlen umwandeln:

SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | 291        |
+-----------+------------+

SELECT '0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | -291       |
+-----------+------------+

Datumstypen umwandeln

BigQuery unterstützt die Umwandlung von Datumstypen in/von Strings wie folgt:

CAST(date_expression AS STRING)
CAST(string_expression AS DATE)

Die Umwandlung von einem Datumstyp in einen String ist unabhängig von der Zeitzone und erfolgt in der Form YYYY-MM-DD. Bei der Umwandlung eines Strings in ein Datum muss der String dem unterstützten Datumsliteralformat entsprechen und ist von der Zeitzone unabhängig. Wenn der Stringausdruck ungültig ist oder ein Datum darstellt, das außerhalb des unterstützten Mindest-/Maximalbereichs liegt, wird ein Fehler erzeugt.

Zeitstempeltypen umwandeln

BigQuery unterstützt das Umwandeln von Zeitstempeltypen in/von Strings wie folgt:

CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)

Beim Umwandeln von Zeitstempeltypen in Strings wird der Zeitstempel mit der Standardzeitzone UTC interpretiert. Die Anzahl der erzeugten Stellen im Sekundenbruchteilbereich hängt von der Anzahl der nachlaufenden Nullen im Sekundenbruchteilbereich ab: Die CAST-Funktion trennt nach null, drei oder sechs Stellen.

Beim Umwandeln eines Strings in einen Zeitstempel muss string_expression den unterstützten Zeitstempel-Literalformaten entsprechen; andernfalls tritt ein Laufzeitfehler auf. string_expression kann selbst eine time_zone enthalten, siehe Zeitzonen. Falls im string_expression eine Zeitzone vorhanden ist, wird diese Zeitzone zur Conversion verwendet, ansonsten wird die Standardzeitzone UTC verwendet. Wenn der String weniger als sechs Ziffern hat, dann wird er implizit erweitert.

Wenn der string_expression ungültig ist, mehr als sechs Stellen im Sekundenbruchteilbereich hat (d. h eine höhere Genauigkeit hat, die Mikrosekunden übersteigt) oder eine Zeit außerhalb des unterstützten Zeitstempelbereichs darstellt, wird ein Fehler erzeugt.

Zwischen Datums- und Zeitstempeltypen umwandeln

BigQuery unterstützt das Umwandeln zwischen Datums- und Zeitstempeltypen wie folgt:

CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)

Die Umwandlung eines Datums in einen Zeitstempel interpretiert date_expression als Mitternacht (Tagesbeginn) in der Standardzeitzone UTC. Die Umwandlung eines Zeitstempels in ein Datum trennt den Zeitstempel effektiv ab der Standardzeitzone.

Erzwingen

BigQuery erzwingt den Ergebnistyp eines Ausdrucks als einen anderen Typ, wenn er mit Funktionssignaturen übereinstimmen muss. Wenn beispielsweise die Funktion func() so definiert ist, dass sie ein einziges Argument von Typ INT64 übernimmt, und ein Ausdruck als ein Argument mit einem Ergebnistyp von FLOAT64 verwendet wird, wird vor der Berechnung von func() der Typ INT64 für das Ergebnis des Ausdrucks erzwungen.

Literale erzwingen

BigQuery unterstützt folgende Literalerzwingungen:

Datentyp der Eingabe Datentyp des Ergebnisses Hinweise
STRING-Literal DATE
TIMESTAMP

Die Literalerzwingung ist notwendig, wenn sich der tatsächliche Literaltyp von dem von der betreffenden Funktion erwarteten Typ unterscheidet. Wenn die Funktion func() beispielsweise ein DATE-Argument übernimmt, ist der Ausdruck func("2014-09-27") gültig, weil die Umwandlung des STRING-Literals "2014-09-27" in DATE erzwungen wird.

Die Literalumwandlung wird zur Analysezeit ausgewertet und gibt einen Fehler aus, wenn das eingegebene Literal nicht erfolgreich in den Zieltyp konvertiert werden kann.

Hinweis: Es erfolgt keine erzwungene Umwandlung von String-Literalen in numerische Typen.

Parameter erzwingen

BigQuery unterstützt folgende Parametererzwingungen:

Datentyp der Eingabe Datentyp des Ergebnisses
STRING-Parameter

Kann der Parameterwert nicht erfolgreich als Zieltyp erzwungen werden, wird ein Fehler ausgegeben.

Zusätzliche Konversionsfunktionen

BigQuery bietet folgende zusätzliche Konversionsfunktionen:

Aggregatfunktionen

Eine Aggregatfunktion ist eine Funktion, die eine Berechnung auf Basis von Werten durchführt. COUNT, MIN und MAX sind Beispiele für Aggregatfunktionen.

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM UNNEST([NULL, "apple", "pear", "orange"]) as fruit;
+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

Folgende Abschnitte beschreiben die von BigQuery unterstützten Aggregatfunktionen:

ANY_VALUE

ANY_VALUE(expression)  [OVER (...)]

Beschreibung

Gibt einen beliebigen Wert aus der Eingabe NULL zurück, wenn Null-Zeilen vorhanden sind. Der zurückgegebene Wert ist nicht deterministisch, was bedeutet, dass Sie jedes Mal, wenn Sie diese Funktion nutzen, ein anderes Ergebnis erhalten.

Unterstützte Argumenttypen

Beliebig

Optionale Klausel

OVER: Gibt ein Fenster an. Siehe Analysefunktionen.

Zurückgegebene Datentypen

Entspricht dem Eingabedatentyp.

Beispiele

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+

SELECT
  fruit,
  ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+--------+-----------+
| fruit  | any_value |
+--------+-----------+
| pear   | pear      |
| apple  | pear      |
| banana | apple     |
+--------+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
          [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

Beschreibung

Gibt einen ARRAY von expression-Werten zurück.

Unterstützte Argumenttypen

Alle Datentypen außer ARRAY.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. OVER: Gibt ein Fenster an. Siehe Analysefunktionen. Diese Klausel ist derzeit inkompatibel zu allen anderen Klauseln in ARRAY_AGG().

  2. DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

  3. IGNORE NULLS oder RESPECT NULLS: Wenn IGNORE NULLS angegeben ist, werden die NULL-Werte vom Ergebnis ausgeschlossen. Wenn RESPECT NULLS angegeben oder nichts angegeben ist, werden die NULL-Werte in das Ergebnis übernommen (es wird jedoch ein Fehler ausgegeben, wenn ein Array im letzten Abfrageergebnis ein NULL-Element enthält).

  4. ORDER BY: Gibt die Reihenfolge der Werte an.

    • Für jeden Sortierschlüssel ist die Standard-Sortierrichtung ASC.
    • NULL-Werte: Im Rahmen der ORDER BY-Klausel sind NULL-Werte der mögliche Mindestwert; das heißt, NULL-Werte werden zuerst in der ASC-Sortierung und abschließend in der DESC-Sortierung angezeigt.
    • Gleitkommadatentypen: Weitere Informationen zur Sortierung und Gruppierung finden Sie unter Gleitkommasemantik.
    • Wenn auch DISTINCT angegeben ist, muss der Sortierschlüssel derselbe sein wie expression.
    • Ist ORDER BY nicht angegeben, ist die Reihenfolge der Elemente im Ausgabe-Array nicht deterministisch, was bedeutet, dass Sie jedes Mal, wenn Sie diese Funktion nutzen, ein anderes Ergebnis erhalten.
  5. LIMIT: Gibt die maximale Anzahl der expression-Eingaben im Ergebnis an. Das Limit n muss eine konstante INT64 sein.

Zurückgegebene Datentypen

ARRAY

Wenn keine Eingabezeilen vorhanden sind, gibt diese Funktion NULL zurück.

Beispiele

SELECT FORMAT("%T", ARRAY_AGG(x)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-------------------------------+
| array_agg                     |
+-------------------------------+
| [NULL, 1, -2, 3, -2, 1, NULL] |
+-------------------------------+

SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+------------------+
| array_agg        |
+------------------+
| [1, 3, -2, NULL] |
+------------------+

SELECT FORMAT("%T", ARRAY_AGG(x IGNORE NULLS)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

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

SELECT FORMAT("%T", ARRAY_AGG(x ORDER BY ABS(x))) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-------------------------------+
| array_agg                     |
+-------------------------------+
| [NULL, NULL, 1, 1, -2, -2, 3] |
+-------------------------------+

SELECT FORMAT("%T", ARRAY_AGG(x LIMIT 5)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+----------------------+
| array_agg            |
+----------------------+
| [NULL, 1, -2, 3, -2] |
+----------------------+

SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 2)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-----------+
| array_agg |
+-----------+
| [-2, 1]   |
+-----------+

SELECT
  x,
  FORMAT("%T", ARRAY_AGG(x) OVER (ORDER BY ABS(x))) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+------+-------------------------------+
| x    | array_agg                     |
+------+-------------------------------+
| NULL | [NULL, NULL]                  |
| NULL | [NULL, NULL]                  |
| 1    | [NULL, NULL, 1, 1]            |
| 1    | [NULL, NULL, 1, 1]            |
| -2   | [NULL, NULL, 1, 1, -2, -2]    |
| -2   | [NULL, NULL, 1, 1, -2, -2]    |
| 3    | [NULL, NULL, 1, 1, -2, -2, 3] |
+------+-------------------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])

Beschreibung

Verkettet Elemente aus expression des Typs ARRAY und gibt damit einen einzigen ARRAY als Ergebnis zurück. Diese Funktion ignoriert NULL-Eingabe-Arrays, berücksichtigt aber die NULL-Elemente in Nicht-NULL-Eingabe-Arrays (ein Fehler wird jedoch erhoben, wenn ein Array im letzten Abfrageergebnis ein NULL-Element enthält).

Unterstützte Argumenttypen

ARRAY

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. ORDER BY: Gibt die Reihenfolge der Werte an.

    • Für jeden Sortierschlüssel ist die Standard-Sortierrichtung ASC.
    • Array-Sortierung wird nicht unterstützt und somit kann der Sortierungsschlüssel nicht derselbe sein wie expression.
    • NULL-Werte: Im Rahmen der ORDER BY-Klausel sind NULL-Werte der mögliche Mindestwert; das heißt, NULL-Werte werden zuerst in der ASC-Sortierung und abschließend in der DESC-Sortierung angezeigt.
    • Gleitkommadatentypen: Weitere Informationen zur Sortierung und Gruppierung finden Sie unter Gleitkommasemantik.
    • Ist ORDER BY nicht angegeben, ist die Reihenfolge der Elemente im Ausgabe-Array nicht deterministisch, was bedeutet, dass Sie jedes Mal, wenn Sie diese Funktion nutzen, ein anderes Ergebnis erhalten.
  2. LIMIT: Gibt die maximale Anzahl der expression-Eingaben im Ergebnis an. Das Limit gilt für die Anzahl der Eingabe-Arrays, nicht für die Anzahl der Elemente in den Arrays. Ein leeres Array zählt als 1. Ein NULL-Array wird nicht gezählt. Das Limit n muss eine konstante INT64 sein.

Zurückgegebene Datentypen

ARRAY

Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression alle Zeilen als NULL auswertet.

Beispiele

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [5, 6, 7, 8, 9, NULL, 1, 2, 3, 4] |
+-----------------------------------+

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

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

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9]  |
+------------------+

AVG

AVG([DISTINCT] expression)  [OVER (...)]

Beschreibung

Gibt den Durchschnitt der Nicht-NULL-Eingabewerte zurück oder NaN, wenn die Eingabe eine NaN enthält.

Unterstützte Argumenttypen

Jeder numerische Eingabetyp, z. B. INT64. Bitte beachten Sie, dass das zurückgegebene Ergebnis für Gleitkomma-Eingabetypen nicht deterministisch ist, was bedeutet, dass Sie jedes Mal, wenn Sie diese Funktion nutzen, ein anderes Ergebnis erhalten.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. OVER: Gibt ein Fenster an. Siehe Analysefunktionen. Diese Klausel ist derzeit inkompatibel zu allen anderen Klauseln in AVG().

  2. DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

Zurückgegebene Datentypen

  • NUMERIC, wenn der Eingabetyp NUMERIC ist
  • FLOAT64

Beispiele

SELECT AVG(x) as avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+

SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+

SELECT
  x,
  AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

+------+------+
| x    | avg  |
+------+------+
| NULL | NULL |
| 0    | 0    |
| 2    | 1    |
| 4    | 3    |
| 4    | 4    |
| 5    | 4.5  |
+------+------+

BIT_AND

BIT_AND(expression)

Beschreibung

Führt eine bitweise AND-Operation für expression durch und gibt das Ergebnis zurück.

Unterstützte Argumenttypen

INT64

Zurückgegebene Datentypen

INT64

Beispiele

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR(expression)

Beschreibung

Führt eine bitweise OR-Operation für expression durch und gibt das Ergebnis zurück.

Unterstützte Argumenttypen

INT64

Zurückgegebene Datentypen

INT64

Beispiele

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR([DISTINCT] expression)

Beschreibung

Führt eine bitweise XOR-Operation für expression durch und gibt das Ergebnis zurück.

Unterstützte Argumenttypen

INT64

Optionale Klausel

DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

Zurückgegebene Datentypen

INT64

Beispiele

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 5678    |
+---------+

SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

COUNT

1.

COUNT(*)  [OVER (...)]

2.

COUNT([DISTINCT] expression)  [OVER (...)]

Beschreibung

  1. Gibt die Anzahl der Zeilen in der Eingabe zurück.
  2. Gibt die Anzahl der Zeilen mit expression zurück, ausgewertet als beliebiger Wert ungleich NULL.

Unterstützte Argumenttypen

expression kann ein beliebiger Datentyp sein.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. OVER: Gibt ein Fenster an. Siehe Analysefunktionen.

  2. DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

Rückgabedatentypen

INT64

Beispiele

SELECT
  COUNT(*) AS count_star,
  COUNT(x) AS count_x,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+--------------+
| count_star | count_x | count_dist_x |
+------------+---------+--------------+
| 5          | 4       | 3            |
+------------+---------+--------------+

SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x,
  COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------+------------+---------+--------------+
| x    | count_star | count_x | count_dist_x |
+------+------------+---------+--------------+
| NULL | 1          | 0       | 0            |
| 1    | 3          | 3       | 2            |
| 4    | 3          | 3       | 2            |
| 4    | 3          | 3       | 2            |
| 5    | 1          | 1       | 1            |
+------+------------+---------+--------------+

COUNTIF

COUNTIF(expression)  [OVER (...)]

Beschreibung

Gibt die Anzahl der TRUE-Werte für expression zurück. Gibt 0 zurück, wenn keine Eingabezeilen vorhanden sind oder expression alle Zeilen als FALSE auswertet.

Unterstützte Argumenttypen

BOOL

Optionale Klausel

OVER: Gibt ein Fenster an. Siehe Analysefunktionen.

Rückgabedatentypen

INT64

Beispiele

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+

SELECT
  x,
  COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

+------+--------------+
| x    | num_negative |
+------+--------------+
| NULL | 0            |
| 0    | 1            |
| -2   | 1            |
| 3    | 1            |
| 4    | 0            |
| 5    | 0            |
| 6    | 1            |
| -7   | 2            |
| -10  | 2            |
+------+--------------+

LOGICAL_AND

LOGICAL_AND(expression)

Beschreibung

Gibt das logische AND aller Nicht-NULL-Ausdrücke zurück. Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression alle Zeilen als NULL auswertet.

Unterstützte Argumenttypen

BOOL

Rückgabedatentypen

BOOL

Beispiele

SELECT LOGICAL_AND(x) as logical_and FROM UNNEST([true, false, true]) as x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(expression)

Beschreibung

Gibt das logische OR aller Nicht-NULL-Ausdrücke zurück. Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression alle Zeilen als NULL auswertet.

Unterstützte Argumenttypen

BOOL

Rückgabedatentypen

BOOL

Beispiele

SELECT LOGICAL_OR(x) as logical_or FROM UNNEST([true, false, true]) as x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(expression)  [OVER (...)]

Beschreibung

Gibt den höchsten Wert der Nicht-NULL-Ausdrücke zurück. Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression alle Zeilen als NULL auswertet. Gibt NaN zurück, wenn die Eingabe ein NaN enthält.

Unterstützte Argumenttypen

Alle Datentypen außer: STRUCT ARRAY

Optionale Klausel

OVER: Gibt ein Fenster an. Siehe Analysefunktionen.

Rückgabedatentypen

Derselbe Datentyp, der als Eingabewert verwendet wird.

Beispiele

SELECT MAX(x) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+

SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | max  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 8    |
| 4    | 8    |
| 37   | 55   |
| 55   | 55   |
+------+------+

MIN

MIN(expression)  [OVER (...)]

Beschreibung

Gibt den Mindestwert der Nicht-NULL-Ausdrücke zurück. Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression alle Zeilen als NULL auswertet. Gibt NaN zurück, wenn die Eingabe ein NaN enthält.

Unterstützte Argumenttypen

Alle Datentypen außer: STRUCT ARRAY

Optionale Klausel

OVER: Gibt ein Fenster an. Siehe Analysefunktionen.

Rückgabedatentypen

Derselbe Datentyp, der als Eingabewert verwendet wird.

Beispiele

SELECT MIN(x) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+

SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | min  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 4    |
| 4    | 4    |
| 37   | 37   |
| 55   | 37   |
+------+------+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter]  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

Beschreibung

Gibt einen Wert (entweder STRING oder BYTES) zurück, der durch die Verkettung von Nicht-Null-Werten erlangt wird.

Wenn ein delimiter angegeben wird, werden verkettete Werte durch dieses Trennzeichen voneinander getrennt; andernfalls wird ein Komma als Trennzeichen verwendet.

Unterstützte Argumenttypen

STRING BYTES

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. OVER: Gibt ein Fenster an. Siehe Analysefunktionen. Diese Klausel ist derzeit inkompatibel zu allen anderen Klauseln in STRING_AGG().

  2. DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

  3. ORDER BY: Gibt die Reihenfolge der Werte an.

    • Für jeden Sortierschlüssel ist die Standard-Sortierrichtung ASC.
    • NULL-Werte: Im Rahmen der ORDER BY-Klausel sind NULL-Werte der mögliche Mindestwert; das heißt, NULL-Werte werden zuerst in der ASC-Sortierung und abschließend in der DESC-Sortierung angezeigt.
    • Gleitkommadatentypen: Weitere Informationen zur Sortierung und Gruppierung finden Sie unter Gleitkommasemantik.
    • Wenn auch DISTINCT angegeben ist, muss der Sortierschlüssel derselbe sein wie expression.
    • Ist ORDER BY nicht angegeben, ist die Reihenfolge der Elemente im Ausgabe-Array nicht deterministisch, was bedeutet, dass Sie jedes Mal, wenn Sie diese Funktion nutzen, ein anderes Ergebnis erhalten.
  4. LIMIT: Gibt die maximale Anzahl der expression-Eingaben im Ergebnis an. Die Begrenzung gilt für die Anzahl von Eingabe-Strings, nicht für die Anzahl von Zeichen oder Byte in den Eingaben. Ein leerer String zählt als 1. Ein NULL-String wird nicht gezählt. Das Limit n muss eine konstante INT64 sein.

Rückgabedatentypen

STRING BYTES

Beispiele

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+

SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+

SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & banana & pear |
+-----------------------+

SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| pear & pear & apple & banana |
+------------------------------+

SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+--------------+
| string_agg   |
+--------------+
| apple & pear |
+--------------+

SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+---------------+
| string_agg    |
+---------------+
| pear & banana |
+---------------+

SELECT
  fruit,
  STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+--------+------------------------------+
| fruit  | string_agg                   |
+--------+------------------------------+
| NULL   | NULL                         |
| pear   | pear & pear                  |
| pear   | pear & pear                  |
| apple  | pear & pear & apple          |
| banana | pear & pear & apple & banana |
+--------+------------------------------+

SUM

SUM([DISTINCT] expression)  [OVER (...)]

Beschreibung

Gibt die Summe der Nicht-Null-Werte zurück.

Ist der Ausdruck ein Gleitkommawert, ist die Summe nicht-deterministisch, was bedeutet, dass Sie jedes Mal, wenn Sie diese Funktion nutzen, ein anderes Ergebnis erhalten.

Unterstützte Argumenttypen

Alle unterstützten numerischen Datentypen.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. OVER: Gibt ein Fenster an. Siehe Analysefunktionen.

  2. DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

Rückgabedatentypen

  • Gibt INT64 zurück, wenn die Eingabe eine ganze Zahl ist.

  • Gibt NUMERIC zurück, wenn der Eingabetyp NUMERIC ist.

  • Gibt FLOAT64 zurück, wenn die Eingabe ein Gleitkommawert ist.

Gibt NULL zurück, wenn die Eingabe nur NULL-Werte enthält.

Gibt Inf zurück, wenn die Eingabe Inf enthält.

Gibt -Inf zurück, wenn die Eingabe -Inf enthält.

Gibt NaN zurück, wenn die Eingabe ein NaN enthält.

Gibt NaN zurück, wenn die Eingabe eine Kombination aus Inf und -Inf enthält.

Beispiele

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+

SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+

SELECT
  x,
  SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 6   |
| 3 | 6   |
| 1 | 10  |
| 4 | 10  |
| 4 | 10  |
| 1 | 10  |
| 2 | 9   |
| 5 | 9   |
| 2 | 9   |
+---+-----+

SELECT
  x,
  SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 3   |
| 3 | 3   |
| 1 | 5   |
| 4 | 5   |
| 4 | 5   |
| 1 | 5   |
| 2 | 7   |
| 5 | 7   |
| 2 | 7   |
+---+-----+

Statistische Aggregatfunktionen

BigQuery unterstützt die folgenden statistischen Aggregatfunktionen:

CORR

CORR(X1, X2)  [OVER (...)]

Beschreibung

Gibt den Pearson-Korrelationskoeffizienten einer Reihe von Zahlenpaaren zurück. Bei jedem Zahlenpaar ist die erste Zahl die abhängige Variable und die zweite Zahl die unabhängige Variable. Das zurückgegebene Ergebnis liegt zwischen -1 und 1. Bei einem Ergebnis von 0 besteht keine Korrelation.

Diese Funktion ignoriert alle Eingabepaare, die einen oder mehrere NULL-Werte enthalten. Wenn weniger als zwei Eingangspaare ohne NULL-Werte vorhanden sind, gibt diese Funktion NULL zurück.

Unterstützte Eingabetypen

FLOAT64

Optionale Klausel

OVER: Gibt ein Fenster an. Siehe Analysefunktionen.

Rückgabedatentyp

FLOAT64

COVAR_POP

COVAR_POP(X1, X2)  [OVER (...)]

Beschreibung

Gibt die Populationskovarianz einer Reihe von Zahlenpaaren zurück. Die erste Zahl ist die abhängige Variable; die zweite Zahl ist die unabhängige Variable. Der zurückgegebene Wert liegt zwischen -Inf und +Inf.

Diese Funktion ignoriert alle Eingabepaare, die einen oder mehrere NULL-Werte enthalten. Wenn kein Eingabepaar ohne NULL-Wert vorhanden ist, gibt diese Funktion NULL zurück. Wenn genau ein Eingabepaar ohne NULL-Wert vorhanden ist, gibt diese Funktion 0 zurück.

Unterstützte Eingabetypen

FLOAT64

Optionale Klausel

OVER: Gibt ein Fenster an. Siehe Analysefunktionen.

Rückgabedatentyp

FLOAT64

COVAR_SAMP

COVAR_SAMP(X1, X2)  [OVER (...)]

Beschreibung

Gibt die Stichprobenkovarianz einer Reihe von Zahlenpaaren zurück. Die erste Zahl ist die abhängige Variable; die zweite Zahl ist die unabhängige Variable. Der zurückgegebene Wert liegt zwischen -Inf und +Inf.

Diese Funktion ignoriert alle Eingabepaare, die einen oder mehrere NULL-Werte enthalten. Wenn weniger als zwei Eingangspaare ohne NULL-Werte vorhanden sind, gibt diese Funktion NULL zurück.

Unterstützte Eingabetypen

FLOAT64

Optionale Klausel

OVER: Gibt ein Fenster an. Siehe Analysefunktionen.

Rückgabedatentyp

FLOAT64

STDDEV_POP

STDDEV_POP([DISTINCT] expression)  [OVER (...)]

Beschreibung

Gibt die (verzerrte) Populationsstandardabweichung der Werte zurück. Das zurückgegebene Ergebnis liegt zwischen 0 und +Inf.

Diese Funktion ignoriert alle NULL-Eingaben. Wenn alle Eingaben ignoriert werden, gibt diese Funktion NULL zurück.

Empfängt diese Funktion eine einzelne Nicht-NULL-Eingabe, gibt sie 0 zurück.

Unterstützte Eingabetypen

FLOAT64

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. OVER: Gibt ein Fenster an. Siehe Analysefunktionen. Diese Klausel ist derzeit inkompatibel zu allen anderen Klauseln in STDDEV_POP().

  2. DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

Rückgabedatentyp

FLOAT64

STDDEV_SAMP

STDDEV_SAMP([DISTINCT] expression)  [OVER (...)]

Beschreibung

Gibt die (unverzerrte) Standardabweichung der Werte zurück. Das zurückgegebene Ergebnis liegt zwischen 0 und +Inf.

Diese Funktion ignoriert alle NULL-Eingaben. Wenn weniger als zwei Nicht-NULL-Eingaben vorhanden sind, gibt diese Funktion NULL zurück.

Unterstützte Eingabetypen

FLOAT64

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. OVER: Gibt ein Fenster an. Siehe Analysefunktionen. Diese Klausel ist derzeit inkompatibel zu allen anderen Klauseln in STDDEV_SAMP().

  2. DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

Rückgabedatentyp

FLOAT64

STDDEV

STDDEV([DISTINCT] expression)  [OVER (...)]

Beschreibung

Ein Alias von STDDEV_SAMP.

VAR_POP

VAR_POP([DISTINCT] expression)  [OVER (...)]

Beschreibung

Gibt die (verzerrte) Populationsvarianz der Werte zurück. Der zurückgegebene Wert liegt zwischen 0 und +Inf.

Diese Funktion ignoriert alle NULL-Eingaben. Wenn alle Eingaben ignoriert werden, gibt diese Funktion NULL zurück.

Empfängt diese Funktion eine einzelne Nicht-NULL-Eingabe, gibt sie 0 zurück.

Unterstützte Eingabetypen

FLOAT64

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. OVER: Gibt ein Fenster an. Siehe Analysefunktionen. Diese Klausel ist derzeit inkompatibel zu allen anderen Klauseln in VAR_POP().

  2. DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

Rückgabedatentyp

FLOAT64

VAR_SAMP

VAR_SAMP([DISTINCT] expression)  [OVER (...)]

Beschreibung

Gibt die (nicht verzerrte) Stichprobenvarianz der Werte zurück. Der zurückgegebene Wert liegt zwischen 0 und +Inf.

Diese Funktion ignoriert alle NULL-Eingaben. Wenn weniger als zwei Nicht-NULL-Eingaben vorhanden sind, gibt diese Funktion NULL zurück.

Unterstützte Eingabetypen

FLOAT64

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. OVER: Gibt ein Fenster an. Siehe Analysefunktionen. Diese Klausel ist derzeit inkompatibel zu allen anderen Klauseln in VAR_SAMP().

  2. DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

Rückgabedatentyp

FLOAT64

VARIANCE

VARIANCE([DISTINCT] expression)  [OVER (...)]

Beschreibung

Ein Alias von VAR_SAMP.

Ungefähre Aggregatfunktionen

Ungefähre Aggregatfunktionen sind in Bezug auf Speichernutzung und Zeit skalierbar, erzeugen aber ungefähre Ergebnisse statt exakter Ergebnisse. Weitere Hintergrundinformationen finden Sie unter Ungefähre Aggregation.

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT(expression)

Beschreibung

Gibt das ungefähre Ergebnis für COUNT(DISTINCT expression) zurück. Der zurückgegebene Wert ist eine statistische Schätzung, nicht notwendigerweise der tatsächliche Wert.

Diese Funktion ist weniger genau als COUNT(DISTINCT expression), funktioniert aber bei großen Eingaben besser.

Unterstützte Argumenttypen

Alle Datentypen außer:

  • ARRAY
  • STRUCT

Zurückgegebene Datentypen

INT64

Beispiele

SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;

+-----------------+
| approx_distinct |
+-----------------+
| 5               |
+-----------------+

APPROX_QUANTILES

APPROX_QUANTILES([DISTINCT] expression, number [{IGNORE|RESPECT} NULLS])

Beschreibung

Gibt die ungefähren Grenzen für eine Gruppe von expression zurück, wobei number die Anzahl der zu erstellenden Quantile repräsentiert. Diese Funktion gibt einen Array von number + 1 Elementen zurück, wobei das erste Element das ungefähre Minimum und das letzte Element das ungefähre Maximum ist.

Unterstützte Argumenttypen

expression kann jeder unterstützte Datentyp sein, mit Ausnahme von:

  • ARRAY
  • STRUCT

number muss INT64 sein.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder einzelne Wert von expression fließt nur einmal in das Ergebnis ein.

  2. IGNORE NULLS oder RESPECT NULLS: Wird IGNORE NULLS oder keiner der beiden Werte angegeben, werden die NULL-Werte vom Ergebnis ausgeschlossen. Wird RESPECT NULLS angegeben, werden die NULL-Werte in das Ergebnis übernommen. Es wird jedoch ein Fehler zurückgegeben, wenn ein Array im letzten Abfrageergebnis ein NULL-Element enthält.

Zurückgegebene Datentypen

Ein ARRAY des durch den expression-Parameter spezifizierten Typs.

Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression alle Zeilen als NULL auswertet.

Beispiele

SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10]       |
+------------------+

SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10]       |
+------------------+

SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10]    |
+------------------+

SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10]    |
+------------------+

APPROX_TOP_COUNT

APPROX_TOP_COUNT(expression, number)

Beschreibung

Gibt die ungefähren oberen Elemente von expression zurück. Der number-Parameter gibt die Anzahl der zurückgegebenen Elemente an.

Unterstützte Argumenttypen

expression kann von jedem beliebigen Datentyp sein, der die GROUP BY-Klausel unterstützt.

number muss INT64 sein.

Zurückgegebene Datentypen

Ein Array vom Typ STRUCT. Der STRUCT enthält zwei Felder. Das erste Feld enthält einen input-Wert. Das zweite Feld enthält eine INT64, die die Häufigkeit angibt, in der input zurückgegeben wurde.

Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind.

Beispiele

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;

+-------------------------+
| approx_top_count        |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+

Umgang mit NULL

APPROX_TOP_COUNT ignoriert NULL-Werte in der Eingabe nicht. Beispiel:

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;

+------------------------+
| approx_top_count       |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+

APPROX_TOP_SUM

APPROX_TOP_SUM(expression, weight, number)

Beschreibung

Gibt die ungefähren oberen Elemente von expression zurück, bezogen auf die Summe eines zugewiesenen weight. Der number-Parameter gibt die Anzahl der zurückgegeben Elemente an.

Wenn weight negativ oder NaN ist, meldet die Funktion einen Fehler.

Unterstützte Argumenttypen

expression kann von jedem beliebigen Datentyp sein, der die GROUP BY-Klausel unterstützt.

weight muss einer der folgenden Datentypen sein:

  • INT64
  • FLOAT64

number muss INT64 sein.

Zurückgegebene Datentypen

Ein Array vom Typ STRUCT. Der STRUCT enthält zwei Felder: value und sum. Das value-Feld enthält den Wert des eingegebenen Ausdrucks. Das sum-Feld ist vom gleichen Typ wie weight und ist die ungefähre Summe des mit dem value-Feld verknüpften Eingabegewichts.

Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind.

Beispiele

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
  STRUCT("apple" AS x, 3 AS weight),
  ("pear", 2),
  ("apple", 0),
  ("banana", 5),
  ("pear", 4)
]);

+--------------------------+
| approx_top_sum           |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+

Umgang mit NULL

APPROX_TOP_SUM ignoriert NULL-Werte für die expression- und weight-Parameter nicht.

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);

+-------------------------+
| approx_top_sum          |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+

HyperLogLog++-Funktionen

BigQuery unterstützt die folgenden ungefähren Aggregatfunktionen mithilfe des Algorithmus HyperLogLog++. Eine Erläuterung der Arbeitsweise ungefährer Aggregatfunktionen finden Sie unter Ungefähre Aggregation.

HLL_COUNT.INIT

HLL_COUNT.INIT(input [, precision])

Beschreibung

Eine Skalarfunktion, die einen oder mehrere input-Werte in einer HyperLogLog++-Skizze zusammenfasst. Jede Skizze wird mithilfe von BYTES-Datentypen dargestellt. Anschließend können Sie Skizzen mithilfe von HLL_COUNT.MERGE oder HLL_COUNT.MERGE_PARTIAL zusammenführen. Falls keine Zusammenführung erforderlich ist, können Sie mithilfe von HLL_COUNT.EXTRACT die endgültige Anzahl unterschiedlicher Werte extrahieren.

Ein input kann einer der folgenden Datentypen sein:

  • INT64
  • STRING
  • BYTES

Diese Funktion unterstützt einen optionalen Parameter, precision. Dieser Parameter definiert die Genauigkeit der Schätzung auf Kosten eines für die Verarbeitung der Skizzen oder für Sicherung dieser auf einem Laufwerk zusätzlich erforderlichen Speichers. Die folgende Tabelle zeigt die zulässigen Genauigkeitswerte, die maximale Skizzengröße pro Gruppe und das Konfidenzintervall (CI) typischer Genauigkeiten:

Präzision Max. Skizzengröße (KiB) CI CI CI
10 1 ±1,63 % ±3,25 % ±6,50 %
11 2 ±1,15 % ±2,30 % ±4,60 %
12 4 ±0,81 % ±1,63 % ±3,25 %
13 8 ±0,57 % ±1,15 % ±1,72 %
14 16 ±0,41 % ±0,81 % ±1,22 %
15 (Standard) 32 ±0,29 % ±0,57 % ±0,86 %
16 64 ±0,20 % ±0,41 % ±0,61 %
17 128 ±0,14 % ±0,29 % ±0,43 %
18 256 ±0,10 % ±0,20 % ±0,41 %
19 512 ±0,07 % ±0,14 % ±0,29 %
20 1.024 ±0,05 % ±0,10 % ±0,20 %
21 2.048 ±0,04 % ±0,07 % ±0,14 %
22 4.096 ±0,03 % ±0,05 % ±0,10 %
23 8.192 ±0,02 % ±0,04 % ±0,07 %
24 16.384 ±0,01 % ±0,03 % ±0,05 %

Wenn die Eingabe NULL ist, gibt diese Funktion NULL zurück.

Weitere Informationen finden Sie unter HyperLogLog in der Praxis: Algorithmische Entwicklung eines modernen Algorithmus zur Abschätzung der Kardinalität.

Unterstützter Eingabetyp

BYTES

Rückgabetyp

BYTES

Beispiel

SELECT
  HLL_COUNT.INIT(respondent) AS respondents_hll,
  flavor,
  country
FROM UNNEST([
  STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
  (1, "Chocolate", "CH"),
  (2, "Chocolate", "US"),
  (2, "Strawberry", "US")])
GROUP BY flavor, country;

HLL_COUNT.MERGE

HLL_COUNT.MERGE(sketch)

Beschreibung

Eine Aggregatfunktion, die die Kardinalität mehrerer HyperLogLog++-Satz-Skizzen durch Berechnen ihrer Vereinigung zurückgibt.

Jede sketch (Skizze) benötigt dieselbe Präzision und muss aus demselben Typ initialisiert werden. Versuche, Skizzen mit unterschiedlicher Präzisionen oder für verschiedene Typen zusammenzuführen, führen zu einem Fehler. Beispielsweise können Sie eine aus INT64-Daten initialisierte Skizze nicht mit einer aus STRING-Daten initialisierten Skizze zusammenführen.

Diese Funktion ignoriert beim Zusammenführen von Skizzen die NULL-Werte. Wenn Nullzeilen oder nur NULL-Werte zusammengeführt werden, gibt die Funktion 0 zurück.

Unterstützte Eingabetypen

BYTES

Rückgabetyp

INT64

Beispiel

SELECT HLL_COUNT.MERGE(respondents_hll) AS num_respondents, flavor
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country)
GROUP BY flavor;

HLL_COUNT.MERGE_PARTIAL

HLL_COUNT.MERGE_PARTIAL(sketch)

Beschreibung

Eine Aggregatfunktion, die eine oder mehrere HyperLogLog++ sketch-Eingaben in einer neuen Skizze zusammenführt.

Diese Funktion gibt NULL zurück, wenn keine Eingabe vorhanden ist oder alle Eingaben NULL sind.

Unterstützte Eingabetypen

BYTES

Rückgabetyp

BYTES

Beispiel

SELECT HLL_COUNT.MERGE_PARTIAL(respondents_hll) AS num_respondents, flavor
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country)
GROUP BY flavor;

HLL_COUNT.EXTRACT

HLL_COUNT.EXTRACT(sketch)

Beschreibung

Eine Skalarfunktion, die eine Kardinalitätsschätzung einer einzelnen HyperLogLog ++-Skizze extrahiert.

Wenn sketch NULL ist, gibt diese Funktion eine Kardinalitätsschätzung von 0 zurück.

Unterstützte Eingabetypen

BYTES

Rückgabetyp

INT64

Beispiel

SELECT
  flavor,
  country,
  HLL_COUNT.EXTRACT(respondents_hll) AS num_respondents
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country);

+------------+---------+-----------------+
| flavor     | country | num_respondents |
+------------+---------+-----------------+
| Vanilla    | CH      | 1               |
| Chocolate  | CH      | 1               |
| Chocolate  | US      | 1               |
| Strawberry | US      | 1               |
+------------+---------+-----------------+

Nummerierungsfunktionen

In den folgenden Abschnitten werden die von BigQuery unterstützten Nummerierungsfunktionen beschrieben. Nummerierungsfunktionen sind eine Teilmenge der Analysefunktionen. Wie Analysefunktionen funktionieren, wird im Artikel zu den Konzepten für Analysefunktionen erläutert. Eine Beschreibung der Funktionsweise von Nummerierungsfunktionen finden Sie im Abschnitt zu den Konzepten für Nummerierungsfunktionen.

OVER-Klauselanforderungen:

  • PARTITION BY: Optional.
  • ORDER BY: Erforderlich, außer für ROW_NUMBER().
  • window_frame_clause: Nicht zulässig.

RANK

Beschreibung

Gibt den ordinalen (1-basierten) Rang jeder Zeile innerhalb der geordneten Partition zurück. Alle Peer-Zeilen erhalten den gleichen Rangwert. Die nächste Zeile oder der nächste Satz von Peer-Zeilen erhält einen Rangwert, der um die Anzahl der Peers mit dem vorherigen Rangwert ansteigt, anstelle von DENSE_RANK, der immer um 1 erhöht wird.

Unterstützte Argumenttypen

INT64

DENSE_RANK

Beschreibung

Gibt den ordinalen (1-basierten) Rang jeder Zeile innerhalb der Fensterpartition zurück. Alle Peer-Zeilen erhalten den gleichen Rangwert und der nachfolgende Rangwert wird um eins erhöht.

Unterstützte Argumenttypen

INT64

PERCENT_RANK

Beschreibung

Gibt den Perzentilrang einer Zeile zurück, der als (RK-1)/(NR-1) definiert ist, wobei RK der RANK der Zeile und NR die Anzahl der Zeilen in der Partition ist. Gibt 0 zurück, wenn NR = 1 ist.

Unterstützte Argumenttypen

FLOAT64

CUME_DIST

Beschreibung

Gibt den relativen Rang einer Zeile zurück, der als NP/NR definiert ist. NP ist definiert als die Anzahl der Zeilen, die entweder der aktuellen Zeile vorangehen oder die Peer-Zeilen der aktuellen Zeile sind. NR ist die Anzahl der Zeilen in der Partition.

Unterstützte Argumenttypen

FLOAT64

NTILE

NTILE(constant_integer_expression)

Beschreibung

Diese Funktion teilt die Zeilen in constant_integer_expression-Buckets auf Grundlage der Zeilenreihenfolge auf und gibt die auf 1-basierte Bucket-Nummer zurück, die jeder Zeile zugewiesen ist. Die Anzahl der Zeilen in den Buckets kann sich höchstens um 1 unterscheiden. Die Restwerte (der Rest der Anzahl der Reihen, die durch Buckets geteilt werden) werden auf jeden Bucket, beginnend mit Bucket 1, verteilt. Wenn constant_integer_expression NULL, 0 oder negativ auswertet, wird ein Fehler angezeigt.

Unterstützte Argumenttypen

INT64

ROW_NUMBER

Beschreibung

Die ORDER BY-Klausel ist nicht erforderlich. Die sequenzielle Zeilenordinale (1-basiert) jeder Zeile wird für jede geordnete Partition zurückgegeben. Ist die ORDER BY-Klausel nicht spezifiziert, so ist das Ergebnis nicht deterministisch.

Unterstützte Argumenttypen

INT64

Bit-Funktionen

BigQuery unterstützt die folgenden Bit-Funktionen.

BIT_COUNT

BIT_COUNT(expression)

Beschreibung

Die Eingabe expression muss eine Ganzzahl oder BYTES sein.

Gibt die Anzahl der Bits zurück, die im Eingabe-expression eingestellt sind. Bei Ganzzahlen ist dies die Anzahl der Bits in der Zweierkomplementform.

Rückgabedatentyp

INT64

Beispiel

SELECT a, BIT_COUNT(a) AS a_bits, FORMAT("%T", b) as b, BIT_COUNT(b) AS b_bits
FROM UNNEST([
  STRUCT(0 AS a, b'' AS b), (0, b'\x00'), (5, b'\x05'), (8, b'\x00\x08'),
  (0xFFFF, b'\xFF\xFF'), (-2, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFE'),
  (-1, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF'),
  (NULL, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF')
]) AS x;

+-------+--------+---------------------------------------------+--------+
| a     | a_bits | b                                           | b_bits |
+-------+--------+---------------------------------------------+--------+
| 0     | 0      | b""                                         | 0      |
| 0     | 0      | b"\x00"                                     | 0      |
| 5     | 2      | b"\x05"                                     | 2      |
| 8     | 1      | b"\x00\x08"                                 | 1      |
| 65535 | 16     | b"\xff\xff"                                 | 16     |
| -2    | 63     | b"\xff\xff\xff\xff\xff\xff\xff\xfe"         | 63     |
| -1    | 64     | b"\xff\xff\xff\xff\xff\xff\xff\xff"         | 64     |
| NULL  | NULL   | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | 80     |
+-------+--------+---------------------------------------------+--------+

Mathematische Funktionen

Alle mathematischen Funktionen haben die folgenden Verhaltensweisen:

  • Sie geben NULL zurück, wenn einer der Eingabeparameter NULL ist.
  • Sie geben NaN zurück, wenn eines der Argumente NaN ist.

ABS

ABS(X)

Beschreibung

Berechnet den absoluten Wert. Gibt einen Fehler zurück, wenn das Argument eine Ganzzahl ist und der Ausgabewert nicht als der gleiche Typ dargestellt werden kann. Dies geschieht nur für den größten negativen Eingabewert, der nicht positiv dargestellt wird. Gibt +inf für das Argument +/-inf zurück.

SIGN

SIGN(X)

Beschreibung

Gibt -1, 0 oder +1 für negative, Null- bzw. positive Argumente zurück. Bei Gleitkommaargumenten unterscheidet diese Funktion nicht zwischen positiv und negativ null. Gibt NaN für das Argument NaN zurück.

IS_INF

IS_INF(X)

Beschreibung

Gibt TRUE zurück, wenn der Wert positiv oder negativ unendlich ist. Gibt NULL für NULL-Eingaben zurück.

IS_NAN

IS_NAN(X)

Beschreibung

Gibt TRUE zurück, wenn der Wert ein NaN-Wert ist. Gibt NULL für NULL-Eingaben zurück.

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

Beschreibung

Dividiert X durch Y. Diese Funktion schlägt nie fehl. Gibt FLOAT64 zurück. Erzeugt im Gegensatz zum Divisionsoperator (/) keinen Fehler für die Division durch null oder Überlauf.

Sonderfälle:

  • Läuft das Ergebnis über, wird +/-inf zurückgegeben.
  • Wenn Y=0 und X=0, wird NaN zurückgegeben.
  • Wenn Y=0 und X!=0, wird +/-inf zurückgegeben.
  • Wenn X = +/-inf und Y = +/-inf, wird NaN zurückgegeben.

Das Verhalten von IEEE_DIVIDE ist in der nachfolgenden Tabelle weiter veranschaulicht.

Sonderfälle für IEEE_DIVIDE

In der folgenden Tabelle sind Sonderfälle für IEEE_DIVIDE aufgelistet:

Zähler-Datentyp (X) Nenner-Datentyp (Y) Ergebniswert
Alles außer 0 0 +/-inf
0 0 NaN
0 NaN NaN
NaN 0 NaN
+/-inf +/-inf NaN

RAND

RAND()

Beschreibung

Erzeugt einen Pseudozufallswert vom Typ FLOAT64 im Bereich von [0, 1], einschließlich 0 und exklusiv 1.

SQRT

SQRT(X)

Beschreibung

Berechnet die Quadratwurzel von X. Erzeugt einen Fehler, wenn X kleiner als 0 ist. Gibt +inf zurück, wenn X +inf entspricht.

POW

POW(X, Y)

Beschreibung

Gibt den Wert X zurück, der mit Y potenziert wird. Wenn das Ergebnis unterläuft und nicht darstellbar ist, gibt die Funktion den Wert Null zurück. Gibt einen Fehler zurück, wenn eine der folgenden Bedingungen zutrifft:

  • X ist ein endlicher Wert, der kleiner als 0 ist, und Y ist keine ganze Zahl.
  • X ist 0 und Y ist ein endlicher Wert, der kleiner als 0 ist.

Das Verhalten von POW() ist in der nachfolgenden Tabelle weiter veranschaulicht.

POWER

POWER(X, Y)

Beschreibung

Synonym für POW().

Sonderfälle für POW(X, Y) und POWER(X, Y)

Folgende Fälle sind Sonderfälle für POW(X, Y) und POWER(X, Y).

X Y POW(X, Y) oder POWER(X, Y)
1,0 Jeder Wert, einschließlich NaN 1,0
Beliebig, einschließlich NaN 0 1,0
-1,0 +/-inf 1,0
ABS (X) < 1 -inf +inf
ABS (X) > 1 -inf 0
ABS (X) < 1 +inf 0
ABS (X) > 1 +inf +inf
-inf Y < 0 0
-inf Y > 0 -inf wenn Y eine ungerade Ganzzahl ist, sonst +inf
+inf Y < 0 0
+inf Y > 0 +inf

EXP

EXP(X)

Beschreibung

Berechnet e zur Potenz von X, auch natürliche exponentielle Funktion genannt. Bei einem Unterlauf des Ergebnisses gibt diese Funktion eine Null zurück. Erzeugt einen Fehler, wenn ein Überlauf des Ergebnisses auftritt. Wenn X +/-inf ist, gibt diese Funktion +inf oder 0 zurück.

LN

LN(X)

Beschreibung

Berechnet den natürlichen Logarithmus von X. Erzeugt einen Fehler, wenn X kleiner oder gleich null ist. Wenn X +inf ist, gibt diese Funktion +inf zurück.

LOG

LOG(X [, Y])

Beschreibung

Wenn nur X vorhanden ist, ist LOG ein Synonym für LN. Falls Y ebenfalls vorhanden ist, berechnet LOG den Logarithmus von X zur Basis Y. Erzeugt in folgenden Fällen einen Fehler:

  • X ist kleiner oder gleich null.
  • Y ist 1,0.
  • Y ist kleiner oder gleich null.

Das Verhalten von LOG(X, Y) ist in der nachfolgenden Tabelle weiter veranschaulicht:

Sonderfälle für LOG(X, Y)

X Y LOG(X, Y)
-inf Beliebiger Wert NaN
Beliebiger Wert +inf NaN
+inf 0,0 Y < 1,0 -inf
+inf Y > 1,0 +inf

LOG10

LOG10(X)

Beschreibung

Ähnlich wie bei LOG, berechnet den Logarithmus aber auf Basis 10.

GREATEST

GREATEST(X1,...,XN)

Beschreibung

Gibt NULL zurück, wenn eine der Eingaben NULL ist. Andernfalls wird NaN zurückgegeben, wenn eine der Eingaben NaN ist. Andernfalls wird der größte Wert zwischen X1,...,XN nach < Vergleich zurückgegeben.

LEAST

LEAST(X1,...,XN)

Beschreibung

Gibt NULL zurück, wenn eine der Eingaben NULL ist. NaN wird zurückgegeben, wenn eine der Eingaben NaN ist. Andernfalls wird der kleinste Wert zwischen X1,...,XN nach < Vergleich zurückgegeben.

DIV

DIV(X, Y)

Beschreibung

Gibt das Ergebnis der Ganzzahldivision von X durch Y zurück. Die Division durch null gibt einen Fehler zurück. Division durch -1 kann überlaufen. Siehe nachfolgende Tabelle für mögliche Ergebnistypen.

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

Beschreibung

Entspricht dem Divisionsoperator (/), gibt aber NULL zurück, wenn ein Fehler auftritt, z. B. im Fall einer Division durch null.

MOD

MOD(X, Y)

Beschreibung

Modulo-Funktion: gibt den Rest der Division von X durch Y zurück. Der zurückgegebene Wert hat das gleiche Vorzeichen wie X. Ein Fehler wird erzeugt, wenn Y=0 ist. Siehe nachfolgende Tabelle für mögliche Ergebnistypen.

ROUND

ROUND(X [, N])

Beschreibung

Wenn nur X vorhanden ist, rundet ROUND X auf die nächste Ganzzahl. Wenn N vorhanden ist, rundet ROUND X auf N Dezimalstellen nach dem Dezimalzeichen. Wenn N negativ ist, rundet ROUND die Ziffern links vom Dezimalzeichen ab. Rundet halbe Zahlen von null weg. Erzeugt einen Fehler, wenn das Ergebnis überläuft.

TRUNC

TRUNC(X [, N])

Beschreibung

Wenn nur X vorhanden ist, rundet TRUNC X auf die nächste Ganzzahl, deren absoluter Wert nicht größer als der absolute Wert von X ist. Wenn N ebenfalls vorhanden ist, verhält sich TRUNC wie ROUND(X, N), rundet aber immer gegen null und läuft nie über.

CEIL

CEIL(X)

Beschreibung

Gibt den kleinsten Integralwert (mit FLOAT64-Typ) zurück, der nicht kleiner als X ist.

CEILING

CEILING(X)

Beschreibung

Synonym von CEIL(X)

FLOOR

FLOOR(X)

Beschreibung

Gibt den größten Integralwert (mit FLOAT64-Typ) zurück, der nicht größer als X ist.

Beispiel für Verhalten der Rundungsfunktion

Beispielverhalten von BigQuery-Rundungsfunktionen:

Eingabe "X" ROUND(X) TRUNC(X) CEIL(X) FLOOR(X)
2,0 2,0 2,0 2,0 2,0
2,3 2,0 2,0 3,0 2,0
2,8 3,0 2,0 3,0 2,0
2,5 3,0 2,0 3,0 2,0
-2,3 -2,0 -2,0 -2,0 -3,0
-2,8 -3,0 -2,0 -2,0 -3,0
-2,5 -3,0 -2,0 -2,0 -3,0
0 0 0 0 0
+/-inf +/-inf +/-inf +/-inf +/-inf
NaN NaN NaN NaN NaN

COS

COS(X)

Beschreibung

Berechnet den Kosinus von X. Schlägt nie fehl.

COSH

COSH(X)

Beschreibung

Berechnet den hyperbolischen Kosinus von X. Erzeugt einen Fehler, wenn ein Überlauf auftritt.

ACOS

ACOS(X)

Beschreibung

Berechnet den Hauptwert des Arkuskosinus von X. Der zurückgegebene Wert liegt im Bereich [0,]. Erzeugt einen Fehler, wenn X ein endlicher Wert außerhalb des Bereichs [-1, 1] ist.

ACOSH

ACOSH(X)

Beschreibung

Berechnet den inversen hyperbolischen Kosinus von X. Erzeugt einen Fehler, wenn X ein endlicher Wert kleiner als 1 ist.

SIN

SIN(X)

Beschreibung

Berechnet den Sinus von X. Schlägt nie fehl.

SINH

SINH(X)

Beschreibung

Berechnet den hyperbolischen Sinus von X. Erzeugt einen Fehler, wenn ein Überlauf auftritt.

ASIN

ASIN(X)

Beschreibung

Berechnet den Hauptwert des Arkussinus von X. Der zurückgegebene Wert liegt im Bereich [-π/2, π/2]. Erzeugt einen Fehler, wenn X ein endlicher Wert außerhalb des Bereichs [-1, 1] ist.

ASINH

ASINH(X)

Beschreibung

Berechnet den inversen hyperbolischen Sinus von X. Schlägt nie fehl.

TAN

TAN(X)

Beschreibung

Berechnet den Tangens von X. Erzeugt einen Fehler, wenn ein Überlauf auftritt.

TANH

TANH(X)

Beschreibung

Berechnet den hyperbolischen Tangens von X. Schlägt nie fehl.

ATAN

ATAN(X)

Beschreibung

Berechnet den Hauptwert des Arkustangens von X. Der zurückgegebene Wert liegt im Bereich [-π/2, π/2]. Schlägt nie fehl.

ATANH

ATANH(X)

Beschreibung

Berechnet die inverse hyperbolische Tangente von X. Erzeugt einen Fehler, wenn der Absolutwert von X größer oder gleich 1 ist.

ATAN2

ATAN2(Y, X)

Beschreibung

Berechnet den Hauptwert des Arkustangens von Y/X unter Verwendung der Vorzeichen der beiden Argumente, um den Quadranten zu bestimmen. Der zurückgegebene Wert liegt im Bereich [-π, π]. Das Verhalten dieser Funktion wird in nachfolgender Tabelle weiter veranschaulicht.

Sonderfälle für ATAN2()

Y X ATAN2(Y, X)
NaN Beliebiger Wert NaN
Beliebiger Wert NaN NaN
0 0 0, π oder -π, je nach Vorzeichen von X und Y
Endlicher Wert -inf π oder -π, je nach Vorzeichen von Y
Endlicher Wert +inf 0
+/-inf Endlicher Wert π/2 oder -π/2, je nach Vorzeichen von Y
+/-inf -inf ¾π oder -¾π, je nach Vorzeichen von Y
+/-inf +inf π/4 oder -π/4, je nach Vorzeichen von Y

Sonderfälle für trigonometrische und hyperbolische Rundungsfunktionen

X COS(X) COSH(X) ACOS(X) ACOSH(X) SIN(X) SINH(X) ASIN(X) ASINH(X) TAN(X) TANH(X) ATAN(X) ATANH(X)
+/-inf NaN =+inf NaN =+inf NaN =+inf NaN =+inf NaN =+1,0 π/2 NaN
-inf NaN =+inf NaN NaN NaN -inf NaN -inf NaN -1,0 -π/2 NaN
NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

In den folgenden Abschnitten werden die von BigQuery unterstützten Navigationsfunktionen beschrieben. Navigationsfunktionen sind eine Teilmenge der Analysefunktionen. Wie Analysefunktionen funktionieren, wird im Artikel zu den Konzepten für Analysefunktionen erläutert. Eine Beschreibung der Funktionsweise von Navigationsfunktionen finden Sie im Abschnitt zu den Konzepten für Navigationsfunktionen.

FIRST_VALUE

FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

Beschreibung

Gibt den Wert des value_expression für die erste Zeile im aktuellen Window Frame zurück.

Diese Funktion enthält in der Berechnung NULL-Werte, wenn IGNORE NULLS nicht vorhanden ist. Wenn IGNORE NULLS vorhanden ist, schließt die Funktion NULL-Werte aus der Berechnung aus.

Unterstützte Argumenttypen

value_expression kann jeder Datentyp sein, den ein Ausdruck zurückgeben kann.

Rückgabedatentyp

ANY

Beispiele

Das folgende Beispiel berechnet die schnellste Zeit für jede Division.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
  TIMESTAMP_DIFF(finish_time, fastest_time, SECOND) AS delta_in_seconds
FROM (
  SELECT name,
  finish_time,
  division,
  FIRST_VALUE(finish_time)
    OVER (PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fastest_time
  FROM finishers);

+-----------------+-------------+----------+--------------+------------------+
| name            | finish_time | division | fastest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                |
| Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 0                |
| Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 436              |
| Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 891              |
| Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 956              |
| Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 1109             |
| Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 0                |
| Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 426              |
| Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 691              |
| Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 733              |
+-----------------+-------------+----------+--------------+------------------+

LAST_VALUE

LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

Beschreibung

Gibt den Wert des value_expression für die letzte Zeile im aktuellen Window Frame zurück.

Diese Funktion enthält in der Berechnung NULL-Werte, wenn IGNORE NULLS nicht vorhanden ist. Wenn IGNORE NULLS vorhanden ist, schließt die Funktion NULL-Werte aus der Berechnung aus.

Unterstützte Argumenttypen

value_expression kann jeder Datentyp sein, den ein Ausdruck zurückgeben kann.

Rückgabedatentyp

ANY

Beispiele

Das folgende Beispiel berechnet die langsamste Zeit für jede Division.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', slowest_time) AS slowest_time,
  TIMESTAMP_DIFF(slowest_time, finish_time, SECOND) AS delta_in_seconds
FROM (
  SELECT name,
  finish_time,
  division,
  LAST_VALUE(finish_time)
    OVER (PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS slowest_time
  FROM finishers);

+-----------------+-------------+----------+--------------+------------------+
| name            | finish_time | division | slowest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                |
| Sophia Liu      | 02:51:45    | F30-34   | 03:10:14     | 1109             |
| Nikki Leith     | 02:59:01    | F30-34   | 03:10:14     | 673              |
| Jen Edwards     | 03:06:36    | F30-34   | 03:10:14     | 218              |
| Meghan Lederer  | 03:07:41    | F30-34   | 03:10:14     | 153              |
| Lauren Reasoner | 03:10:14    | F30-34   | 03:10:14     | 0                |
| Lisa Stelzner   | 02:54:11    | F35-39   | 03:06:24     | 733              |
| Lauren Matthews | 03:01:17    | F35-39   | 03:06:24     | 307              |
| Desiree Berry   | 03:05:42    | F35-39   | 03:06:24     | 42               |
| Suzy Slane      | 03:06:24    | F35-39   | 03:06:24     | 0                |
+-----------------+-------------+----------+--------------+------------------+

NTH_VALUE

NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])

Beschreibung

Gibt den Wert von value_expression in der N-ten Zeile des aktuellen Fensters zurück, wobei "N" durch constant_integer_expression definiert wird. Gibt NULL zurück, wenn es keine solche Zeile gibt.

Diese Funktion enthält in der Berechnung NULL-Werte, wenn IGNORE NULLS nicht vorhanden ist. Wenn IGNORE NULLS vorhanden ist, schließt die Funktion NULL-Werte aus der Berechnung aus.

Unterstützte Argumenttypen

  • value_expression kann jeder Datentyp sein, der von einem Ausdruck zurückgegeben werden kann.
  • constant_integer_expression kann jeder konstante Ausdruck sein, der eine Ganzzahl zurückgibt.

Rückgabedatentyp

ANY

Beispiele

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
  FORMAT_TIMESTAMP('%X', second_fastest) AS second_fastest
FROM (
  SELECT name,
  finish_time,
  division,finishers,
  FIRST_VALUE(finish_time)
    OVER w1 AS fastest_time,
  NTH_VALUE(finish_time, 2)
    OVER w1 as second_fastest
  FROM finishers
  WINDOW w1 AS (
    PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING));

+-----------------+-------------+----------+--------------+----------------+
| name            | finish_time | division | fastest_time | second_fastest |
+-----------------+-------------+----------+--------------+----------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | NULL           |
| Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 02:59:01       |
| Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 02:59:01       |
| Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 02:59:01       |
| Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 02:59:01       |
| Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 02:59:01       |
| Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 03:01:17       |
| Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 03:01:17       |
| Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 03:01:17       |
| Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 03:01:17       |
+-----------------+-------------+----------+--------------+----------------+

LEAD

LEAD (value_expression[, offset [, default_expression]])

Beschreibung

Gibt den Wert von value_expression in einer nachfolgenden Zeile zurück. Eine Änderung des offset-Werts ändert auch, welche nachfolgende Zeile zurückgegeben wird. Der Standardwert ist 1, der dabei die nächste Zeile im Window Frame angibt. Ein Fehler tritt auf, wenn der offset NULL oder ein negativer Wert ist.

Der optionale default_expression wird verwendet, wenn beim angegebenen Versatz keine Zeile im Window Frame vorhanden ist. Dieser Ausdruck muss ein konstanter Ausdruck sein und der Ausdruckstyp muss implizit in den Typ value_expression umgewandelt werden können. Wenn nicht angegeben, wird default_expression standardmäßig auf NULL gesetzt.

Unterstützte Argumenttypen

  • value_expression kann jeder Datentyp sein, der von einem Ausdruck zurückgegeben werden kann.
  • offset muss ein nicht-negatives Ganzzahlliteral oder ein Parameter sein.
  • default_expression muss mit dem Wertausdrucktyp kompatibel sein.

Rückgabedatentyp

ANY

Beispiele

Das folgende Beispiel veranschaulicht eine grundlegende Verwendung der LEAD-Funktion.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS followed_by
FROM finishers;

+-----------------+-------------+----------+-----------------+
| name            | finish_time | division | followed_by     |
+-----------------+-------------+----------+-----------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL            |
| Sophia Liu      | 02:51:45    | F30-34   | Nikki Leith     |
| Nikki Leith     | 02:59:01    | F30-34   | Jen Edwards     |
| Jen Edwards     | 03:06:36    | F30-34   | Meghan Lederer  |
| Meghan Lederer  | 03:07:41    | F30-34   | Lauren Reasoner |
| Lauren Reasoner | 03:10:14    | F30-34   | NULL            |
| Lisa Stelzner   | 02:54:11    | F35-39   | Lauren Matthews |
| Lauren Matthews | 03:01:17    | F35-39   | Desiree Berry   |
| Desiree Berry   | 03:05:42    | F35-39   | Suzy Slane      |
| Suzy Slane      | 03:06:24    | F35-39   | NULL            |
+-----------------+-------------+----------+-----------------+

Im nächsten Beispiel wird der optionale offset-Parameter verwendet.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name, 2)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      |
| Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   |
| Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  |
| Meghan Lederer  | 03:07:41    | F30-34   | NULL             |
| Lauren Reasoner | 03:10:14    | F30-34   | NULL             |
| Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    |
| Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       |
| Desiree Berry   | 03:05:42    | F35-39   | NULL             |
| Suzy Slane      | 03:06:24    | F35-39   | NULL             |
+-----------------+-------------+----------+------------------+

Das folgende Beispiel ersetzt NULL-Werte mit einem Standardwert.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name, 2, 'Nobody')
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | Nobody           |
| Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      |
| Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   |
| Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  |
| Meghan Lederer  | 03:07:41    | F30-34   | Nobody           |
| Lauren Reasoner | 03:10:14    | F30-34   | Nobody           |
| Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    |
| Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       |
| Desiree Berry   | 03:05:42    | F35-39   | Nobody           |
| Suzy Slane      | 03:06:24    | F35-39   | Nobody           |
+-----------------+-------------+----------+------------------+

LAG

LAG (value_expression[, offset [, default_expression]])

Beschreibung

Gibt den Wert von value_expression in einer vorangehenden Zeile zurück. Eine Änderung des offset-Werts ändert auch, welche vorangehende Zeile zurückgegeben wird. Der Standardwert ist 1, der dabei die vorhergehende Zeile im Window Frame angibt. Ein Fehler tritt auf, wenn offset NULL oder ein negativer Wert ist.

Der optionale default_expression wird verwendet, wenn beim angegebenen Versatz keine Zeile im Window Frame vorhanden ist. Dieser Ausdruck muss ein konstanter Ausdruck sein und der Ausdruckstyp muss implizit in den Typ value_expression umgewandelt werden können. Wenn nicht angegeben, wird default_expression standardmäßig auf NULL gesetzt.

Unterstützte Argumenttypen

  • value_expression kann jeder Datentyp sein, der von einem Ausdruck zurückgegeben werden kann.
  • offset muss ein nicht-negatives Ganzzahlliteral oder ein Parameter sein.
  • default_expression muss mit dem Wertausdrucktyp kompatibel sein.

Rückgabedatentyp

ANY

Beispiele

Das folgende Beispiel veranschaulicht eine grundlegende Verwendung der LAG-Funktion.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | preceding_runner |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | NULL             |
| Nikki Leith     | 02:59:01    | F30-34   | Sophia Liu       |
| Jen Edwards     | 03:06:36    | F30-34   | Nikki Leith      |
| Meghan Lederer  | 03:07:41    | F30-34   | Jen Edwards      |
| Lauren Reasoner | 03:10:14    | F30-34   | Meghan Lederer   |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL             |
| Lauren Matthews | 03:01:17    | F35-39   | Lisa Stelzner    |
| Desiree Berry   | 03:05:42    | F35-39   | Lauren Matthews  |
| Suzy Slane      | 03:06:24    | F35-39   | Desiree Berry    |
+-----------------+-------------+----------+------------------+

Im nächsten Beispiel wird der optionale offset-Parameter verwendet.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name, 2)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;

+-----------------+-------------+----------+-------------------+
| name            | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL              |
| Sophia Liu      | 02:51:45    | F30-34   | NULL              |
| Nikki Leith     | 02:59:01    | F30-34   | NULL              |
| Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        |
| Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       |
| Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL              |
| Lauren Matthews | 03:01:17    | F35-39   | NULL              |
| Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     |
| Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   |
+-----------------+-------------+----------+-------------------+

Das folgende Beispiel ersetzt NULL-Werte mit einem Standardwert.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name, 2, 'Nobody')
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;

+-----------------+-------------+----------+-------------------+
| name            | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte     | 03:08:58    | F25-29   | Nobody            |
| Sophia Liu      | 02:51:45    | F30-34   | Nobody            |
| Nikki Leith     | 02:59:01    | F30-34   | Nobody            |
| Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        |
| Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       |
| Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       |
| Lisa Stelzner   | 02:54:11    | F35-39   | Nobody            |
| Lauren Matthews | 03:01:17    | F35-39   | Nobody            |
| Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     |
| Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   |
+-----------------+-------------+----------+-------------------+

PERCENTILE_CONT

PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS])

Beschreibung

Berechnet den angegebenen Perzentilwert für value_expression mit linearer Interpolation.

Diese Funktion ignoriert NULL-Werte, wenn RESPECT NULLS nicht angegeben wurde. Wenn RESPECT NULLS angegeben wurde:

  • gibt die Interpolation zwischen zwei NULL-Werten NULL zurück.
  • gibt die Interpolation zwischen einem NULL-Wert und einem Nicht-NULL-Wert den Nicht-NULL-Wert zurück.

Unterstützte Argumenttypen

  • value_expression ist ein numerischer Ausdruck.
  • percentile ist ein DOUBLE-Literal im Bereich [0, 1].

Rückgabedatentyp

DOUBLE

Beispiele

Im folgenden Beispiel wird der Wert für einige Perzentilen aus einer Spalte von Werten berechnet, wobei Nullen ignoriert werden.

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+-----+-------------+--------+--------------+-----+
| min | percentile1 | median | percentile90 | max |
+-----+-------------+--------+--------------+-----+
| 0   | 0.03        | 1.5    | 2.7          | 3   |
+-----+-------------+--------+--------------+-----+

Im folgenden Beispiel wird der Wert für einige Perzentilen aus einer Spalte von Werten berechnet, wobei nullen berücksichtigt werden.

SELECT
  PERCENTILE_CONT(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_CONT(x, 0.01 RESPECT NULLS) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_CONT(x, 0.9 RESPECT NULLS) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+------+-------------+--------+--------------+-----+
| min  | percentile1 | median | percentile90 | max |
+------+-------------+--------+--------------+-----+
| NULL | 0           | 1      | 2.6          | 3   |
+------+-------------+--------+--------------+-----+

PERCENTILE_DISC

PERCENTILE_DISC (value_expression, percentile [{RESPECT | IGNORE} NULLS])

Beschreibung

Berechnet den angegebenen Perzentilwert für einen eigenständigen value_expression. Der zurückgegebene Wert ist der erste sortierte Wert von value_expression mit einer kumulativen Verteilung, die größer oder gleich dem angegebenen percentile-Wert ist.

Diese Funktion ignoriert NULL-Werte, es sei denn RESPECT NULLS wurde angegeben.

Unterstützte Argumenttypen

  • value_expression kann von jedem sortierbaren Typ sein.
  • percentile ist ein DOUBLE-Literal im Bereich [0, 1].

Rückgabedatentyp

ANY

Beispiele

Im folgenden Beispiel wird der Wert für einige Perzentilen aus einer Spalte von Werten berechnet, wobei Nullen ignoriert werden.

SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

+------+-----+--------+-----+
| x    | min | median | max |
+------+-----+--------+-----+
| c    | a   | b      | c   |
| NULL | a   | b      | c   |
| b    | a   | b      | c   |
| a    | a   | b      | c   |
+------+-----+--------+-----+

Im folgenden Beispiel wird der Wert für einige Perzentilen aus einer Spalte von Werten berechnet, wobei Nullen berücksichtigt werden.

SELECT
  x,
  PERCENTILE_DISC(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_DISC(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_DISC(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

+------+------+--------+-----+
| x    | min  | median | max |
+------+------+--------+-----+
| c    | NULL | a      | c   |
| NULL | NULL | a      | c   |
| b    | NULL | a      | c   |
| a    | NULL | a      | c   |
+------+------+--------+-----+

Aggregierte Analysefunktionen

In den folgenden Abschnitten werden die von BigQuery unterstützten aggregierten Analysefunktionen beschrieben. Wie Analysefunktionen funktionieren, wird im Artikel zu den Konzepten für Analysefunktionen erläutert. Eine Beschreibung der Funktionsweise aggregierter Analysefunktionen finden Sie im Abschnitt zu den Konzepten für aggregierte Analysefunktionen.

BigQuery unterstützt die folgenden Aggregatfunktionen als Analysefunktionen:

OVER-Klauselanforderungen:

  • PARTITION BY: Optional.
  • ORDER BY: Optional. Nicht zulässig, wenn DISTINCT vorhanden ist.
  • window_frame_clause: Optional. Nicht zulässig, wenn DISTINCT vorhanden ist.

Beispiel:

COUNT(*) OVER (ROWS UNBOUNDED PRECEDING)
SUM(DISTINCT x) OVER ()

Hash-Funktionen

FARM_FINGERPRINT

FARM_FINGERPRINT(value)

Beschreibung

Berechnet den Fingerabdruck der STRING- oder BYTES-Eingabe mithilfe der Funktion Fingerprint64 aus der Open-Source-FarmHash-Bibliothek. Die Ausgabe dieser Funktion für eine bestimmte Eingabe ändert sich nie.

Rückgabetyp

INT64

Beispiele

WITH example AS (
  SELECT 1 AS x, "foo" AS y, true AS z UNION ALL
  SELECT 2 AS x, "apple" AS y, false AS z UNION ALL
  SELECT 3 AS x, "" AS y, true AS z
)
SELECT
  *,
  FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
    AS row_fingerprint
FROM example;
+---+-------+-------+----------------------+
| x | y     | z     | row_fingerprint      |
+---+-------+-------+----------------------+
| 1 | foo   | true  | -1541654101129638711 |
| 2 | apple | false | 2794438866806483259  |
| 3 |       | true  | -4880158226897771312 |
+---+-------+-------+----------------------+

MD5

MD5(input)

Beschreibung

Berechnet den Hash der Eingabe mit dem MD5-Algorithmus. Die Eingabe kann entweder STRING oder BYTES sein. Die String-Version behandelt die Eingabe als ein Array von Byte.

Diese Funktion gibt 16 Byte zurück.

Rückgabetyp

BYTES

Beispiel

SELECT MD5("Hello World") as md5;

+-------------------------------------------------+
| md5                                             |
+-------------------------------------------------+
| \xb1\n\x8d\xb1d\xe0uA\x05\xb7\xa9\x9b\xe7.?\xe5 |
+-------------------------------------------------+

SHA1

SHA1(input)

Beschreibung

Berechnet den Hash der Eingabe mit dem SHA-1-Algorithmus. Die Eingabe kann entweder STRING oder BYTES sein. Die String-Version behandelt die Eingabe als ein Array von Byte.

Diese Funktion gibt 20 Byte zurück.

Rückgabetyp

BYTES

Beispiel

SELECT SHA1("Hello World") as sha1;

+-----------------------------------------------------------+
| sha1                                                      |
+-----------------------------------------------------------+
| \nMU\xa8\xd7x\xe5\x02/\xabp\x19w\xc5\xd8@\xbb\xc4\x86\xd0 |
+-----------------------------------------------------------+

SHA256

SHA256(input)

Beschreibung

Berechnet den Hash der Eingabe mit dem SHA-256-Algorithmus. Die Eingabe kann entweder STRING oder BYTES sein. Die String-Version behandelt die Eingabe als ein Array von Byte.

Diese Funktion gibt 32 Byte zurück.

Rückgabetyp

BYTES

Beispiel

SELECT SHA256("Hello World") as sha256;

SHA512

SHA512(input)

Beschreibung

Berechnet den Hash der Eingabe mit dem SHA-512 Algorithmus. Die Eingabe kann entweder STRING oder BYTES sein. Die String-Version behandelt die Eingabe als ein Array von Byte.

Diese Funktion gibt 64 Byte zurück.

Rückgabetyp

BYTES

Beispiel

SELECT SHA512("Hello World") as sha512;

String-Funktionen

Diese Stringfunktionen funktionieren mit zwei verschiedenen Werten: STRING- und BYTES-Datentypen. STRING-Werte müssen korrekt in UTF-8 formatiert sein.

Funktionen, die Positionswerte wie STRPOS zurückgeben, codieren diese Positionen als INT64. Der Wert 1 bezieht sich auf das erste Zeichen (oder Byte), 2 bezieht sich auf das zweite und so weiter. Der Wert 0 gibt einen ungültigen Index an. Bei der Bearbeitung von STRING-Typen beziehen sich die zurückgegebenen Positionen auf Zeichenpositionen.

Alle Stringvergleiche werden von Byte zu Byte durchgeführt, ohne Rücksicht auf kanonische Äquivalenz von Unicode.

BYTE_LENGTH

BYTE_LENGTH(value)

Beschreibung

Gibt die Länge des Wertes in Byte zurück, unabhängig davon, ob der Typ des Wertes STRING oder BYTES ist.

Rückgabetyp

INT64

Beispiele

WITH example AS
  (SELECT "абвгд" AS characters, b"абвгд" AS bytes)

SELECT
  characters,
  BYTE_LENGTH(characters) AS string_example,
  bytes,
  BYTE_LENGTH(bytes) AS bytes_example
FROM example;

+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд      | 10             | абвгд | 10            |
+------------+----------------+-------+---------------+

CHAR_LENGTH

CHAR_LENGTH(value)

Beschreibung

Gibt die Länge des STRINGs in Zeichen zurück.

Rückgabetyp

INT64

Beispiele

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHAR_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHARACTER_LENGTH

CHARACTER_LENGTH(value)

Beschreibung

Synonym für CHAR_LENGTH.

Rückgabetyp

INT64

Beispiele

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHARACTER_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_values)

Beschreibung

Nimmt ein Array von erweiterten ASCII-Codepunkten (ARRAY von INT64) und gibt BYTES zurück.

Wie BYTES in ein Array von Codepunkten umgewandelt werden, finden Sie unter TO_CODE_POINTS.

Rückgabetyp

BYTES

Beispiele

Nachfolgend ein einfaches Beispiel mit CODE_POINTS_TO_BYTES.

SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;

+-------+
| bytes |
+-------+
| AbCd  |
+-------+

Im folgendem Beispiel wird ein String mit dem ROT13-Algorithmus (Rotiere um 13 Stellen) codiert.

SELECT CODE_POINTS_TO_BYTES(ARRAY_AGG(
  (SELECT
      CASE
        WHEN chr BETWEEN b'a' and b'z'
          THEN TO_CODE_POINTS(b'a')[offset(0)] +
            MOD(code+13-TO_CODE_POINTS(b'a')[offset(0)],26)
        WHEN chr BETWEEN b'A' and b'Z'
          THEN TO_CODE_POINTS(b'A')[offset(0)] +
            MOD(code+13-TO_CODE_POINTS(b'A')[offset(0)],26)
        ELSE code
      END
   FROM
     (SELECT code, CODE_POINTS_TO_BYTES([code]) chr)
  ) ORDER BY OFFSET)) AS encoded_string
FROM UNNEST(TO_CODE_POINTS(b'Test String!')) code WITH OFFSET;

+----------------+
| encoded_string |
+----------------+
| Grfg Fgevat!   |
+----------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

Beschreibung

Nimmt ein Array von Unicode-Codepunkten (ARRAY von INT64) und gibt einen STRING zurück.

Wie ein String in ein Array von Codepunkten umgewandelt wird, finden Sie unter TO_CODE_POINTS.

Rückgabetyp

STRING

Beispiel

Nachfolgend ein einfaches Beispiel mit CODE_POINTS_TO_STRING.

SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+

Das folgende Beispiel berechnet die Häufigkeit der Buchstaben in einem Satz von Wörtern.

WITH Words AS (
  SELECT word
  FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
  CODE_POINTS_TO_STRING([code_point]) AS letter,
  COUNT(*) AS letter_count
FROM Words,
  UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;

+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a      | 5            |
| f      | 3            |
| r      | 2            |
| b      | 2            |
| l      | 2            |
| o      | 2            |
| g      | 1            |
| z      | 1            |
| e      | 1            |
| m      | 1            |
| i      | 1            |
+--------+--------------+

CONCAT

CONCAT(value1[, ...])

Beschreibung

Verkettet einen oder mehrere Werte in ein einzelnes Ergebnis.

Rückgabetyp

STRING oder BYTES

Beispiele

With Employees AS
  (SELECT
    "John" AS first_name,
    "Doe" AS last_name
  UNION ALL
  SELECT
    "Jane" AS first_name,
    "Smith" AS last_name
  UNION ALL
  SELECT
    "Joe" AS first_name,
    "Jackson" AS last_name)

SELECT
  CONCAT(first_name, " ", last_name)
  AS full_name
FROM Employees;

+---------------------+
| full_name           |
+---------------------+
| John Doe            |
| Jane Smith          |
| Joe Jackson         |
+---------------------+

ENDS_WITH

ENDS_WITH(value1, value2)

Beschreibung

Übernimmt zwei Werte. Gibt TRUE zurück, wenn der zweite Wert ein Suffix des ersten Werts ist.

Rückgabetyp

BOOL

Beispiele

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  ENDS_WITH(item, "e") as example
FROM items;

+---------+
| example |
+---------+
|    True |
|   False |
|    True |
+---------+

FORMAT

BigQuery unterstützt zur Formatierung von Strings die Funktion FORMAT(). Diese Funktion ähnelt der C-Funktion printf. Sie erzeugt einen STRING aus einem Formatstring, der null oder mehrere Formatspezifizierer enthält, zusammen mit einer Variablenlängenliste mit zusätzlichen Argumenten, die mit den Formatspezifizierern übereinstimmen. Hier einige Beispiele:

Beschreibung Anweisung Ergebnis
Einfache Ganzzahl format("%d", 10) 10
Ganzzahl mit Leerzeichenausgleich links format("|%10d|", 11) |           11|
Ganzzahl mit aufgefüllten Nullen links format("+%010d+", 12) +0000000012+
STRING format("-%s-", 'abcd efg') -abcd efg-
FLOAT64 format("%f %E", 1.1, 2.2) 1.100000 2.200000E+00
DATE format("%t", date "2015-09-01") 2015-09-01
TIMESTAMP format("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles") 2015‑09‑01 19:34:56+00

Die Funktion FORMAT() bietet weder eine vollständig anpassbare Formatierung für alle Typen und Werte noch eine sprachsensitive Formatierung.

Wenn eine benutzerdefinierte Formatierung für einen Typ erforderlich ist, müssen Sie sie zuerst mit typspezifischen Formatfunktionen wie FORMAT_DATE() oder FORMAT_TIMESTAMP() formatieren. Beispiel:

FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));

gibt zurück

date: January 02, 2015!

Syntax

Die Syntax FORMAT() nimmt einen Formatstring und eine Variablenlängenliste von Argumenten und erzeugt ein STRING-Ergebnis:

FORMAT(<format_string>, ...)

Der Ausdruck <format_string> kann null oder mehrere Formatspezifizierer enthalten. Jeder Formatspezifizierer wird durch das %-Zeichen eingeführt und muss einem oder mehreren der übrigen Argumente zugeordnet werden. Zum größten Teil ist dies eine Eins-zu-Eins-Zuordnung, außer wenn der *-Spezifizierer vorhanden ist. Zum Beispiel ist %.*i zwei Argumenten zugeordnet – einem Längenargument und einem signierten Ganzzahlenargument. Wenn die Anzahl der Argumente, die sich auf die Formatspezifizierer beziehen, nicht mit der Anzahl der Argumente übereinstimmt, tritt ein Fehler auf.

Unterstützte Formatspezifizierer

Der Formatspezifizierer der Funktion FORMAT() folgt diesem Prototyp:

%[flags][width][.precision]specifier

Die unterstützten Formatspezifizierer sind in der folgenden Tabelle aufgeführt. Erweiterungen von printf() sind kursiv hervorgehoben.

Spezifizierer Beschreibung Beispiele Typen
d oder i Ganze Dezimalzahl 392 INT64
o Oktalzahl 610
INT64*
x Hexadezimale Ganzzahl 7fa
INT64*
x Hexadezimale Ganzzahl (Großbuchstaben) 7FA
INT64*
f Dezimales Gleitkomma, Kleinbuchstaben 392,65
inf
NaN
NUMERIC
FLOAT64
f Dezimales Gleitkomma, Großbuchstaben 392,65
inf
NAN
NUMERIC
FLOAT64
e Exponentialschreibweise (Mantisse/Exponent), Kleinbuchstaben 3,9265e+2
inf
NaN
NUMERIC
FLOAT64
E Exponentialschreibweise (Mantisse/Exponent), Großbuchstaben 3,9265E+2
inf
NAN
NUMERIC
FLOAT64
g Kürzeste Schreibweise verwenden, %e oder %f 392,65
FLOAT64
G Kürzeste Schreibweise verwenden, %E oder %F 392,65
FLOAT64
s Zeichenfolge Beispiel STRING
t Gibt einen druckbaren String zurück, der den Wert darstellt. Sieht oft ähnlich wie die Umwandlung des Arguments in einen STRING aus. Siehe nachfolgender Abschnitt "%t". Beispiel
01.01. 2014
<beliebig>
T Erzeugt einen String, der eine gültige BigQuery-Konstante mit einem ähnlichen Typ zum Werttyp ist (vielleicht größer oder vielleicht ein String). Siehe den nachfolgenden Abschnitt %T. 'Beispiel'
b'bytes-Beispiel'
1234
2,3
Datum '01.01. 2014'
<beliebig>
% '%%' erzeugt ein einziges '%' % n. v.

*Die Spezifizierer o, x und X geben einen Fehler aus, wenn negative Werte verwendet werden.

Der Formatspezifizierer kann optional die oben identifizierten Unterspezifizierer im Spezifiziererprototyp enthalten.

Diese Unterspezifizierer müssen folgenden Spezifikationen entsprechen.

Kennzeichnungen
Kennzeichnungen Beschreibung
Linksbündig innerhalb der vorgegebenen Feldbreite; rechtsbündig ist die Voreinstellung (siehe Unterspezifizierer für die Breite).
+ Erzwingt, dass dem Ergebnis auch bei positiven Zahlen ein Plus- oder Minuszeichen (+ oder -) vorangestellt wird. Standardmäßig werden nur negative Zahlen mit einem Vorzeichen versehen.
<Leerzeichen> Wird kein Vorzeichen geschrieben, wird vor dem Wert ein Leerzeichen eingefügt.
Anz. Verwendet bei o-, x- oder X-Spezifizierern. Wird dem Wert mit 0, 0x oder 0X bzw. Werten, die sich von null unterscheiden, vorangestellt.
0 Füllt die Zahl links mit Nullen (0) statt mit Leerzeichen (0) auf, wenn das Auffüllen spezifiziert ist (siehe Unterspezifizierer für die Breite).
'

Formatiert Ganzzahlen mit dem passenden Gruppierungszeichen. Beispiel:

  • FORMAT("%'d", 12345678) gibt 12,345,678 zurück
  • FORMAT("%'x", 12345678) gibt bc:614e zurück
  • FORMAT("%'o", 55555) gibt 15,4403 zurück
  • Dieses Flag ist nur für Dezimal-, Hexadezimal- und Oktalwerte relevant.

Flags können in beliebiger Reihenfolge angegeben werden. Doppelte Flags sind kein Fehler. Wenn Flags für einen Elementtyp nicht relevant sind, werden sie ignoriert.

Breite
Breite Beschreibung
<Anzahl> Mindestanzahl der zu druckenden Zeichen. Wenn der zu druckende Wert kürzer als diese Zahl ist, wird das Ergebnis mit Leerzeichen aufgefüllt. Der Wert wird nicht abgeschnitten, auch wenn das Ergebnis größer ist.
* Die Breite wird im Formatstring nicht angegeben, sondern als zusätzliches Ganzzahlwert-Argument vor dem Argument, das formatiert werden muss.
Präzision
Präzision Beschreibung
.<Anzahl> Für Ganzzahlspezifizierer (d, i, o, u, x, X): Präzision gibt die Mindestanzahl der zu schreibenden Ziffern an. Wenn der zu schreibende Wert kürzer als diese Zahl ist, wird das Ergebnis mit nachlaufenden Nullen aufgefüllt. Der Wert wird nicht abgeschnitten, auch wenn das Ergebnis länger ist. Eine Präzision von 0 bedeutet, dass kein Zeichen für den Wert 0 geschrieben wird. Für a-, A-, e-, E-, f- und F-Spezifizierer: Dies ist die Anzahl der zu druckenden Ziffern nach dem Dezimalzeichen (standardmäßig 6 Ziffern).
,* Die Präzision wird im Formatstring nicht angegeben, sondern als zusätzliches Ganzzahlwertargument dem Argument, das formatiert werden muss, vorangestellt.

%t- und %T-Verhalten

Für alle Typen sind die Prozentsätze %t und %T formatiert. Breite, Präzision und Kennzeichnungen funktionieren wie bei %s: Die width ist die Mindestbreite und der STRING wird auf diese Größe aufgefüllt. Die precision ist die maximale Breite des zu zeigenden Inhalts und der STRING wird vor dem Auffüllen auf die Breite auf diese Größe gekürzt.

%t ist immer eine lesbare Form des Wertes.

%T ist immer ein gültiges SQL-Literal eines ähnlichen Typs, z. B. ein größerer numerischer Typ. Das Literal enthält keine Umwandlungen und keinen Typnamen, mit Ausnahme des Sonderfalls von nicht endlichen Gleitkommazahlenwerten.

Der STRING wird so formatiert:

Typ %t %T
NULL eines beliebigen Typs NULL NULL
INT64
123 123
NUMERIC 123,0 (immer mit ,0) NUMERIC "123,0"
FLOAT64 123,0 (immer mit ,0)
123e+10
inf
-inf
NaN
123,0 (immer mit ,0)
123e+10
CAST("inf" AS <Typ>)
CAST("-inf" AS <Typ>)
CAST("nan" AS <Typ>)
STRING Stringwert ohne Anführungszeichen String-Literal mit Anführungszeichen
BYTES Escape-Byte ohne Anführungszeichen
z. B. abc\x01\x02
Byte-Literal mit Anführungszeichen
z. B. "abc\x01\x02"
DATE 03.02.2011 DATE "03.02.2011"
TIMESTAMP 03.02.2011 04:05:06+00 TIMESTAMP "03.02.2011 04:05:06+00"
ARRAY [Wert, Wert, ...]
wobei Werte mit% t formatiert sind
[Wert, Wert, ...]
wobei Werte mit %T formatiert sind
STRUCT (Wert, Wert ...)
wobei Felder mit %t formatiert sind
(Wert, Wert ...)
wobei Felder mit %T formatiert sind

Sonderfälle:
Nullfelder: STRUCT()
Ein Feld: STRUCT(Wert)

Fehlerbedingungen

Wenn ein Formatspezifizierer ungültig ist oder nicht mit dem zugehörigen Argumenttyp kompatibel ist oder die falsche Nummer oder die falschen Argumente bereitgestellt werden, wird ein Fehler erzeugt. Zum Beispiel sind die folgenden <format_string>-Ausdrücke ungültig:

FORMAT('%s', 1)
FORMAT('%')

NULL-Argument-Behandlung

Ein NULL-Formatstring führt zu einem ausgegebenen NULL-STRING. Alle anderen Argumente werden in diesem Fall ignoriert.

Die Funktion erzeugt generell einen NULL-Wert, wenn ein NULL-Argument vorhanden ist. FORMAT('%i', <NULL expression>) beispielsweise erzeugt einen NULL-STRING als Ausgabe.

Allerdings gibt es einige Ausnahmen: Wenn der Format-Spezifizierer %t oder %T ist (beide erzeugen STRINGs, die effektiv mit CAST und Literalwertsemantik übereinstimmen), erzeugt ein NULL-Wert 'NULL' (ohne die Anführungszeichen) im Ergebnis STRING. Ein Beispiel: Die Funktion

FORMAT('00-%t-00', <NULL expression>);

gibt zurück

00-NULL-00

Zusätzliche semantische Regeln

FLOAT64-Werte können +/-inf oder NaN sein. Wenn ein Argument einen dieser Werte aufweist, ist das Ergebnis der Formatspezifizierer %f, %F, %e, %E, %g, %G, und %t gleich inf, -inf oder nan (oder dasselbe in Großbuchstaben), wenn zutreffend. Dies steht im Einklang damit, wie BigQuery diese Werte in STRING abgibt. Für %T gibt BigQuery Strings in Anführungszeichen für FLOAT64-Werte zurück, die keine Nicht-String-Literal-Darstellungen haben.

FROM_BASE32

FROM_BASE32(string_expr)

Beschreibung

Wandelt die base32-codierte Eingabe string_expr in das BYTES-Format um. Verwenden Sie TO_BASE32, um BYTES in einen base32-codierten STRING umzuwandeln.

Rückgabetyp

BYTES

Beispiel

SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;

+-----------+
| byte_data |
+-----------+
| abcde\xff |
+-----------+

FROM_BASE64

FROM_BASE64(string_expr)

Beschreibung

Wandelt die base64-codierte Eingabe string_expr in das BYTES-Format um. Verwenden Sie TO_BASE64, um BYTES in einen base64-codierten STRING umzuwandeln.

Rückgabetyp

BYTES

Beispiel

SELECT FROM_BASE64('3q2+7w==') AS byte_data;

+------------------+
| byte_data        |
+------------------+
| \xde\xad\xbe\xef |
+------------------+

FROM_HEX

FROM_HEX(string)

Beschreibung

Wandelt einen hexadezimal-codierten STRING in das BYTES-Format um. Gibt einen Fehler zurück, wenn der Eingabe-STRING Zeichen außerhalb des Bereichs (0..9, A..F, a..f) enthält. Die Groß-/Kleinschreibung der Zeichen spielt keine Rolle. Verwenden Sie TO_HEX, um BYTES in einen hexadezimal-codierten STRING umzuwandeln.

Rückgabetyp

BYTES

Beispiel

WITH Input AS (
  SELECT '00010203aaeeefff' AS hex_str UNION ALL
  SELECT '0AF' UNION ALL
  SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;
+------------------+----------------------------------+
| hex_str          | bytes_str                        |
+------------------+----------------------------------+
| 0AF              | \x00\xaf                         |
| 00010203aaeeefff | \x00\x01\x02\x03\xaa\xee\xef\xff |
| 666f6f626172     | foobar                           |
+------------------+----------------------------------+

LENGTH

LENGTH(value)

Beschreibung

Gibt die Länge des Wertes zurück. Der Wert wird für STRING-Argumente in Zeichen und für das BYTES-Argument in Byte zurückgegeben.

Rückgabetyp

INT64

Beispiele

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  LENGTH(characters) AS string_example,
  LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;

+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд      |              5 |            10 |
+------------+----------------+---------------+

LPAD

LPAD(original_value, return_length[, pattern])

Beschreibung

Gibt einen Wert zurück, der aus original_value mit vorangestelltem pattern besteht. Die return_length ist ein INT64-Wert, der die Länge des zurückgegebenen Wertes angibt. Ist original_value BYTES, ist return_length die Anzahl der Byte. Ist original_value STRING, ist return_length die Anzahl der Zeichen.

Der Standardwert des pattern ist ein Leerzeichen.

Sowohl original_value als auch pattern müssen der gleiche Datentyp sein.

Wenn return_length kleiner oder gleich der original_value-Länge ist, gibt diese Funktion den original_value-Wert zurück, der auf den return_length-Wert zugeschnitten wird. LPAD("hello world", 7); gibt beispielsweise "hello w" zurück.

Wenn original_value, return_length oder pattern NULL sind, gibt diese Funktion NULL zurück.

Diese Funktion gibt einen Fehler zurück, wenn:

  • return_length negativ ist
  • pattern leer ist

Rückgabetyp

STRING oder BYTES

Beispiele

SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);
t len LPAD
abc 5 "  abc"
abc 2 "ab"
例子 4 "  例子"
SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);
t len Muster LPAD
abc 8 def "defdeabc"
abc 5 "--abc"
例子 5 中文 "中文中例子"
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);
t len LPAD
b"abc" 5 b"  abc"
b"abc" 2 b"ab"
b"\xab\xcd\xef" 4 b" \xab\xcd\xef"
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);
t len Muster LPAD
b"abc" 8 b"def" b"defdeabc"
b"abc" 5 b"-" b"--abc"
b"\xab\xcd\xef" 5 b"\x00" b"\x00\x00\xab\xcd\xef"

LOWER

LOWER(value)

Beschreibung

Bei STRING-Argumenten wird der Originalstring mit allen alphabetischen Zeichen in Kleinbuchstaben zurückgegeben. Die Zuordnung zwischen Klein- und Großbuchstaben erfolgt nach der Unicode-Zeichendatenbank, ohne dabei sprachspezifische Zuordnungen zu berücksichtigen.

Bei BYTES-Argumenten wird das Argument als ASCII-Text behandelt, wobei alle Byte größer als 127 intakt bleiben.

Rückgabetyp

STRING oder BYTES

Beispiele

WITH items AS
  (SELECT
    "FOO" as item
  UNION ALL
  SELECT
    "BAR" as item
  UNION ALL
  SELECT
    "BAZ" as item)

SELECT
  LOWER(item) AS example
FROM items;

+---------+
| example |
+---------+
| foo     |
| bar     |
| baz     |
+---------+

LTRIM

LTRIM(value1[, value2])

Beschreibung

Identisch mit TRIM, es werden aber nur vorangestellte Zeichen entfernt.

Rückgabetyp

STRING oder BYTES

Beispiele

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", LTRIM(item), "#") as example
FROM items;

+-------------+
| example     |
+-------------+
| #apple   #  |
| #banana   # |
| #orange   # |
+-------------+

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  LTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| apple***  |
| banana*** |
| orange*** |
+-----------+

WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  LTRIM(item, "xyz") as example
FROM items;

+-----------+
| example   |
+-----------+
| applexxx  |
| bananayyy |
| orangezzz |
| pearxyz   |
+-----------+

NORMALIZE

NORMALIZE(value[, normalization_mode])

Beschreibung

Nimmt einen STRING (value) und gibt ihn als normalisierten String zurück.

Normalisierung wird verwendet, um sicherzustellen, dass zwei Strings einander entsprechen. Normalisierung wird oft in Situationen verwendet, in denen zwei Strings dasselbe auf dem Bildschirm darstellen, aber unterschiedliche Unicode-Codepunkte haben.

NORMALIZE unterstützt vier optionale Normalisierungsmodi:

Wert Name Beschreibung
NFC Normalisierungsform Kanonische Komposition Zerlegt Zeichen und setzt sie nach kanonischer Äquivalenz wieder zusammen.
NFKC Normalisierungsform Kompatibilitätskomposition Zerlegt Zeichen nach Kompatibilität und setzt sie nach kanonischer Äquivalenz wieder zusammen.
NFD Normalisierungsform Kanonische Zerlegung Zerlegt Zeichen nach kanonischer Äquivalenz und mehrere Kombinationszeichen werden in einer bestimmten Reihenfolge angeordnet.
NFKD Normalisierungsform Kompatibilitätszerlegung Zerlegt Zeichen nach Kompatibilität und mehrere Kombinationszeichen werden in einer bestimmten Reihenfolge angeordnet.

Der Standard-Normalisierungsmodus ist NFC.

Rückgabetyp

STRING

Beispiele

SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b)
AS normalize_example;

+---+---+------------+
| a | b | normalized |
+---+---+------------+
| ê | ê | true       |
+---+---+------------+

Das folgende Beispiel normalisiert verschiedene Leerzeichen.

WITH EquivalentNames AS (
  SELECT name
  FROM UNNEST([
      'Jane\u2004Doe',
      'John\u2004Smith',
      'Jane\u2005Doe',
      'Jane\u2006Doe',
      'John Smith']) AS name
)
SELECT
  NORMALIZE(name, NFKC) AS normalized_name,
  COUNT(*) AS name_count
FROM EquivalentNames
GROUP BY 1;

+-----------------+------------+
| normalized_name | name_count |
+-----------------+------------+
| John Smith      | 2          |
| Jane Doe        | 3          |
+-----------------+------------+

NORMALIZE_AND_CASEFOLD

NORMALIZE_AND_CASEFOLD(value[, normalization_mode])

Beschreibung

Übernimmt einen STRING (value) und führt dieselben Aktionen wie NORMALIZE sowie eine Angleichung der Groß- und Kleinschreibung für Vorgänge aus, bei denen diese nicht berücksichtigt wird.

NORMALIZE_AND_CASEFOLD unterstützt vier optionale Normalisierungsmodi:

Wert Name Beschreibung
NFC Normalisierungsform Kanonische Komposition Zerlegt Zeichen und setzt sie nach kanonischer Äquivalenz wieder zusammen.
NFKC Normalisierungsform Kompatibilitätskomposition Zerlegt Zeichen nach Kompatibilität und setzt sie nach kanonischer Äquivalenz wieder zusammen.
NFD Normalisierungsform Kanonische Zerlegung Zerlegt Zeichen nach kanonischer Äquivalenz und mehrere Kombinationszeichen werden in einer bestimmten Reihenfolge angeordnet.
NFKD Normalisierungsform Kompatibilitätszerlegung Zerlegt Zeichen nach Kompatibilität und mehrere Kombinationszeichen werden in einer bestimmten Reihenfolge angeordnet.

Der Standard-Normalisierungsmodus ist NFC.

Rückgabetyp

STRING

Beispiel

WITH Strings AS (
  SELECT '\u2168' AS a, 'IX' AS b UNION ALL
  SELECT '\u0041\u030A', '\u00C5'
)
SELECT a, b,
  NORMALIZE_AND_CASEFOLD(a, NFD)=NORMALIZE_AND_CASEFOLD(b, NFD) AS nfd,
  NORMALIZE_AND_CASEFOLD(a, NFC)=NORMALIZE_AND_CASEFOLD(b, NFC) AS nfc,
  NORMALIZE_AND_CASEFOLD(a, NFKD)=NORMALIZE_AND_CASEFOLD(b, NFKD) AS nkfd,
  NORMALIZE_AND_CASEFOLD(a, NFKC)=NORMALIZE_AND_CASEFOLD(b, NFKC) AS nkfc
FROM Strings;

+---+----+-------+-------+------+------+
| a | b  | nfd   | nfc   | nkfd | nkfc |
+---+----+-------+-------+------+------+
| Ⅸ | IX | false | false | true | true |
| Å | Å  | true  | true  | true | true |
+---+----+-------+-------+------+------+

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regex)

Beschreibung

Gibt TRUE zurück, wenn value eine partielle Übereinstimmung für den regulären Ausdruck regex ist. Sie können nach einer vollständigen Übereinstimmung suchen, indem Sie ^ (Anfang des Textes) und $ (Ende des Textes) verwenden.

Wenn das regex-Argument ungültig ist, gibt die Funktion einen Fehler zurück.

Hinweis: BigQuery unterstützt reguläre Ausdrücke mithilfe der re2-Bibliothek; in der jeweiligen Dokumentation finden Sie weitere Informationen zur Syntax der regulären Ausdrücke.

Rückgabetyp

BOOL

Beispiele

SELECT
  email,
  REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
  (SELECT
    ["foo@example.com", "bar@example.org", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| foo@example.com | true     |
| bar@example.org | true     |
| www.example.net | false    |
+-----------------+----------+

# Performs a full match, using ^ and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$")
    AS valid_email_address
FROM
  (SELECT
    ["foo@example.com", "bar@example.org", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+---------------------+
| email           | valid_email_address |
+-----------------+---------------------+
| foo@example.com | true                |
| bar@example.org | true                |
| www.example.net | false               |
+-----------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regex)

Beschreibung

Gibt den ersten Teilstring in value zurück, der dem regulären Ausdruck regex entspricht. Gibt NULL zurück, wenn es keine Übereinstimmung gibt.

Wenn der reguläre Ausdruck eine Erfassungsgruppe enthält, gibt die Funktion den Teilstring zurück, der mit dieser Erfassungsgruppe übereinstimmt. Wenn der Ausdruck keine Erfassungsgruppe enthält, gibt die Funktion den gesamten übereinstimmenden Teilstring zurück.

Gibt einen Fehler zurück, wenn:

  • Der reguläre Ausdruck ungültig ist
  • Der reguläre Ausdruck mehr als eine Erfassungsgruppe aufweist

BigQuery unterstützt reguläre Ausdrücke mithilfe der re2-Bibliothek; in der jeweiligen Dokumentation finden Sie weitere Informationen zur Syntax der regulären Ausdrücke.

Rückgabetyp

STRING oder BYTES

Beispiele

WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
  AS user_name
FROM email_addresses;

+-----------+
| user_name |
+-----------+
| foo       |
| bar       |
| baz       |
+-----------+

WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
  AS top_level_domain
FROM email_addresses;

+------------------+
| top_level_domain |
+------------------+
| com              |
| org              |
| net              |
+------------------+

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value, regex)

Beschreibung

Gibt ein Array aller Teilstrings von value zurück, die dem regulären Ausdruck regex entsprechen.

Die Funktion REGEXP_EXTRACT_ALL gibt ausschließlich nicht überlappende Übereinstimmungen zurück. Wenn man beispielsweise mit dieser Funktion ana aus banana extrahiert, wird nur ein Teilstring zurückgegeben, nicht zwei.

BigQuery unterstützt reguläre Ausdrücke mithilfe der re2-Bibliothek; in der jeweiligen Dokumentation finden Sie weitere Informationen zur Syntax der regulären Ausdrücke.

Rückgabetyp

Ein ARRAY von entweder STRINGs oder BYTES

Beispiele

WITH code_markdown AS
  (SELECT "Try `function(x)` or `function(y)`" as code)

SELECT
  REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;

+----------------------------+
| example                    |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+

REGEXP_REPLACE

REGEXP_REPLACE(value, regex, replacement)

Beschreibung

Gibt einen STRING zurück, in dem alle Teilstrings von value, die dem regulären Ausdruck regex entsprechen, durch replacement ersetzt werden.

Sie können innerhalb des replacement-Arguments Escape-Ziffern mit Backslash (\1 bis \9) verwenden, um Text einzufügen, der mit der entsprechenden Klammergruppe im regex-Muster übereinstimmt. Verwenden Sie \0, um auf den gesamten übereinstimmenden Text zu verweisen.

Hinweis: Wenn Sie dem regulären Ausdruck einen Backslash hinzufügen möchten, müssen Sie eine Maskierung (Escape) voranstellen. SELECT REGEXP_REPLACE("abc", "b(.)", "X\\1"); gibt beispielsweise aXc zurück.

Durch die Funktion REGEXP_REPLACE werden ausschließlich nicht überlappende Übereinstimmungen ersetzt. Wenn man beispielsweise ana in banana ersetzt, wird nur eine Ersetzung vorgenommen, nicht zwei.

Wenn das regex-Argument kein gültiger regulärer Ausdruck ist, gibt diese Funktion einen Fehler zurück.

Hinweis: BigQuery unterstützt reguläre Ausdrücke mithilfe der re2-Bibliothek. In der zugehörigen Dokumentation finden Sie weitere Informationen zur Syntax regulärer Ausdrücke.

Rückgabetyp

STRING oder BYTES

Beispiele

WITH markdown AS
  (SELECT "# Heading" as heading
  UNION ALL
  SELECT "# Another heading" as heading)

SELECT
  REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
  AS html
FROM markdown;

+--------------------------+
| html                     |
+--------------------------+
| <h1>Heading</h1>         |
| <h1>Another heading</h1> |
+--------------------------+

REPLACE

REPLACE(original_value, from_value, to_value)

Beschreibung

Ersetzt in original_value alle Vorkommen von from_value durch to_value. Wenn from_value leer ist, wird keine Ersetzung vorgenommen.

Rückgabetyp

STRING oder BYTES

Beispiele

WITH desserts AS
  (SELECT "apple pie" as dessert
  UNION ALL
  SELECT "blackberry pie" as dessert
  UNION ALL
  SELECT "cherry pie" as dessert)

SELECT
  REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;

+--------------------+
| example            |
+--------------------+
| apple cobbler      |
| blackberry cobbler |
| cherry cobbler     |
+--------------------+

REPEAT

REPEAT(original_value, repetitions)

Beschreibung

Gibt einen Wert zurück, der aus Wiederholungen von original_value besteht. Der Parameter repetitions gibt an, wie oft original_value wiederholt wird. Gibt NULL zurück, wenn entweder original_value oder repetitions NULL ist.

Diese Funktion gibt einen Fehler zurück, wenn der Wert von repetitions negativ ist.

Rückgabetyp

STRING oder BYTES

Beispiele

SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
  STRUCT('abc' AS t, 3 AS n),
  ('例子', 2),
  ('abc', null),
  (null, 3)
]);
t n REPEAT
abc 3 abcabcabc
例子 2 例子例子
abc NULL NULL
NULL 3 NULL

REVERSE

REVERSE(value)

Beschreibung

Gibt die Umkehrung der Eingabe als STRING oder BYTES zurück.

Rückgabetyp

STRING oder BYTES

Beispiele

WITH example AS (
  SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
  SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
  sample_string,
  REVERSE(sample_string) AS reverse_string,
  sample_bytes,
  REVERSE(sample_bytes) AS reverse_bytes
FROM example;

+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo           | oof            | bar          | rab           |
| абвгд         | дгвба          | 123          | 321           |
+---------------+----------------+--------------+---------------+

RPAD

RPAD(original_value, return_length[, pattern])

Beschreibung

Gibt einen Wert zurück, der aus original_value mit angehängtem pattern besteht. Die return_length ist ein INT64-Wert, der die Länge des zurückgegebenen Wertes angibt. Ist original_value BYTES, ist return_length die Anzahl der Byte. Ist original_value STRING, ist return_length die Anzahl der Zeichen.

Der Standardwert des pattern ist ein Leerzeichen.

Sowohl original_value als auch pattern müssen der gleiche Datentyp sein.

Wenn return_length kleiner oder gleich der return_length-Länge ist, gibt diese Funktion den original_value-Wert zurück, der auf den Wert der original_value zugeschnitten wird. RPAD("hello world", 7); gibt beispielsweise "hello w" zurück.

Wenn original_value, return_length oder pattern NULL ist, gibt diese Funktion NULL zurück.

Diese Funktion gibt einen Fehler zurück, wenn:

  • return_length negativ ist
  • pattern leer ist

Rückgabetyp

STRING oder BYTES

Beispiele

SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);
t len RPAD
abc 5 "abc  "
abc 2 "ab"
例子 4 "例子  "
SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);
t len Muster RPAD
abc 8 def "abcdefde"
abc 5 "abc--"
例子 5 中文 "例子中文中"
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);
t len RPAD
b"abc" 5 b"abc  "
b"abc" 2 b"ab"
b"\xab\xcd\xef" 4 b"\xab\xcd\xef "
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);
t len Muster RPAD
b"abc" 8 b"def" b"abcdefde"
b"abc" 5 b"-" b"abc--"
b"\xab\xcd\xef" 5 b"\x00" b"\xab\xcd\xef\x00\x00"

RTRIM

RTRIM(value1[, value2])

Beschreibung

Identisch mit TRIM, es werden aber nur nachgestellte Zeichen entfernt.

Rückgabetyp

STRING oder BYTES

Beispiele

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  RTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| ***apple  |
| ***banana |
| ***orange |
+-----------+

WITH items AS
  (SELECT "applexxx" as item
  UNION ALL
  SELECT "bananayyy" as item
  UNION ALL
  SELECT "orangezzz" as item
  UNION ALL
  SELECT "pearxyz" as item)

SELECT
  RTRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

Beschreibung

Wandelt eine Sequenz von Byte in einen String um. Alle ungültigen UTF-8-Zeichen werden durch das Unicode-Ersatzzeichen U+FFFD ersetzt.

Rückgabetyp

STRING

Beispiele

Die folgende Anweisung gibt das Unicode-Ersetzungszeichen � zurück.

SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;

SPLIT

SPLIT(value[, delimiter])

Beschreibung

Teilt value mithilfe des delimiter-Arguments.

Für STRING ist das Standardtrennzeichen das Komma (,).

Für BYTES müssen Sie ein Trennzeichen angeben.

Das Aufteilen mit einem leeren Trennzeichen erzeugt ein Array von UTF-8-Zeichen für STRING-Werte und ein Array von BYTE für BYTES-Werte.

Das Aufteilen eines leeren STRINGs gibt einen ARRAY mit einem einzigen leeren STRING zurück.

Rückgabetyp

ARRAY vom Typ STRING oder ARRAY vom Typ BYTES

Beispiele

WITH letters AS
  (SELECT "a b c d" as letter_group
  UNION ALL
  SELECT "e f g h" as letter_group
  UNION ALL
  SELECT "i j k l" as letter_group)

SELECT SPLIT(letter_group, " ") as example
FROM letters;

+----------------------+
| example              |
+----------------------+
| [a, b, c, d]         |
| [e, f, g, h]         |
| [i, j, k, l]         |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

Beschreibung

Übernimmt zwei Werte. Gibt TRUE zurück, wenn der zweite Wert ein Präfix des ersten Wertes ist.

Rückgabetyp

BOOL

Beispiele

WITH items AS
  (SELECT "foo" as item
  UNION ALL
  SELECT "bar" as item
  UNION ALL
  SELECT "baz" as item)

SELECT
  STARTS_WITH(item, "b") as example
FROM items;

+---------+
| example |
+---------+
|   False |
|    True |
|    True |
+---------+

STRPOS

STRPOS(string, substring)

Beschreibung

Gibt den 1-basierten Index des ersten Vorkommens von substring innerhalb von string zurück. Gibt 0 zurück, wenn substring nicht gefunden wird.

Rückgabetyp

INT64

Beispiele

WITH email_addresses AS
  (SELECT
    "foo@example.com" AS email_address
  UNION ALL
  SELECT
    "foobar@example.com" AS email_address
  UNION ALL
  SELECT
    "foobarbaz@example.com" AS email_address
  UNION ALL
  SELECT
    "quxexample.com" AS email_address)

SELECT
  STRPOS(email_address, "@") AS example
FROM email_addresses;

+---------+
| example |
+---------+
|       4 |
|       7 |
|      10 |
|       0 |
+---------+

SUBSTR

SUBSTR(value, position[, length])

Beschreibung

Gibt einen Teilstring des gelieferten Wertes zurück. Das position-Argument ist eine Ganzzahl, die die Ausgangsposition des Teilstrings angibt, wobei Position = 1 das erste Zeichen oder Byte angibt. Das length-Argument ist die maximale Anzahl von Zeichen für STRING-Argumente oder Byte für BYTES-Argumente.

Wenn die position negativ ist, zählt die Funktion ab value-Ende, wobei -1 das letzte Zeichen angibt.

Ist position eine Position am linken Ende des STRING (position = 0 oder position < -LENGTH(value)), so beginnt die Funktion ab Position = 1. Wenn die length die Länge des value überschreitet, wird weniger als die length-Zeichen zurückgegeben.

Wenn die length kleiner als 0 ist, gibt die Funktion einen Fehler zurück.

Rückgabetyp

STRING oder BYTES

Beispiele

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2) as example
FROM items;

+---------+
| example |
+---------+
| pple    |
| anana   |
| range   |
+---------+

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2, 2) as example
FROM items;

+---------+
| example |
+---------+
| pp      |
| an      |
| ra      |
+---------+

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, -2) as example
FROM items;

+---------+
| example |
+---------+
| le      |
| na      |
| ge      |
+---------+

TO_BASE32

TO_BASE32(bytes_expr)

Beschreibung

Wandelt eine Sequenz von BYTES in einen base32-codierten STRING um. Verwenden Sie FROM_BASE32, um einen base32-codierten STRING in BYTES umzuwandeln.

Rückgabetyp

STRING

Beispiel

SELECT TO_BASE32(b'abcde\xFF') AS base32_string;

+------------------+
| base32_string    |
+------------------+
| MFRGGZDF74====== |
+------------------+

TO_BASE64

TO_BASE64(bytes_expr)

Beschreibung

Wandelt eine Sequenz von BYTES in einen base64-codierten STRING um. FROM_BASE64 muss verwendet werden, um einen base64-codierten STRING in BYTES zu umzuwandeln.

Rückgabetyp

STRING

Beispiel

SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;

+---------------+
| base64_string |
+---------------+
| 3q2+7w==      |
+---------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

Beschreibung

Nimmt einen Wert und gibt ein Array von INT64 zurück.

  • Wenn value ein STRING ist, stellt jedes Element im zurückgegebenen Array einen Codepunkt dar. Jeder Codepunkt fällt in den Bereich von [0, 0xD7FF] und [0xE000, 0x10FFFF].
  • Ist der value BYTES, ist jedes Element im Array ein erweiterter ASCII-Zeichenwert im Bereich von [0, 255].

Informationen zur Umwandlung eines Arrays von Codepunkten in einen STRING oder BYTES finden Sie unter CODE_POINTS_TO_STRING oder CODE_POINTS_TO_BYTES.

Rückgabetyp

ARRAY von INT64

Beispiele

Das folgende Beispiel erhält die Codepunkte für jedes Element in einem Array von Wörtern.

SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;

+---------+------------------------------------+
| word    | code_points                        |
+---------+------------------------------------+
| foo     | [102, 111, 111]                    |
| bar     | [98, 97, 114]                      |
| baz     | [98, 97, 122]                      |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama   | [108, 108, 97, 109, 97]            |
+---------+------------------------------------+

Das folgende Beispiel wandelt ganzzahlige Darstellungen von BYTES in ihre entsprechenden ASCII-Zeichenwerte um.

SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;

+------------------+------------------------+
| word             | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255]        |
| foo              | [102, 111, 111]        |
+------------------+------------------------+

Das folgende Beispiel zeigt den Unterschied zwischen einem BYTES-Ergebnis und einem STRING-Ergebnis.

SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;

+------------+----------+
| b_result   | s_result |
+------------+----------+
| [196, 128] | [256]    |
+------------+----------+

Beachten Sie, dass das Zeichen Ā als 2-Byte-Unicode-Sequenz dargestellt wird. Deshalb gibt die BYTES-Version von TO_CODE_POINTS ein Array mit zwei Elementen zurück, während die STRING-Version ein Array mit einem einzigen Element zurückgibt.

TO_HEX

TO_HEX(bytes)

Beschreibung

Wandelt eine Folge von BYTES in einen hexadezimalen STRING um. Wandelt jedes Byte im STRING als zwei hexadezimale Zeichen im Bereich (0..9, a..f) um. Verwenden Sie FROM_HEX, um einen hexadezimal-codierten STRING in BYTES umzuwandeln.

Rückgabetyp

STRING

Beispiel

WITH Input AS (
  SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
  SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;
+----------------------------------+------------------+
| byte_string                      | hex_string       |
+----------------------------------+------------------+
| foobar                           | 666f6f626172     |
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
+----------------------------------+------------------+

TRIM

TRIM(value1[, value2])

Beschreibung

Entfernt alle voran- und nachgestellten Zeichen, die mit value2 übereinstimmen. Wenn value2 nicht angegeben ist, werden alle voran- und nachgestellten Leerzeichen (wie nach Unicode-Standard definiert) entfernt. Ist das erste Argument vom Typ BYTES, ist das zweite Argument erforderlich.

Wenn value2 mehr als ein Zeichen oder Byte enthält, entfernt die Funktion alle voran- oder nachgestellten Zeichen oder Byte, die in value2 enthalten sind.

Rückgabetyp

STRING oder BYTES

Beispiele

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", TRIM(item), "#") as example
FROM items;

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  TRIM(item, "*") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
+---------+

WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  TRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

UPPER

UPPER(value)

Beschreibung

Bei STRING-Argumenten wird die ursprüngliche Zeichenfolge mit allen alphabetischen Zeichen in Großbuchstaben zurückgegeben. Die Zuordnung zwischen Groß- und Kleinbuchstaben erfolgt ohne Berücksichtigung sprachspezifischer Zuordnungen nach der Unicode-Zeichendatenbank.

Bei BYTES-Argumenten wird das Argument als ASCII-Text behandelt, wobei alle Byte größer als 127 intakt bleiben.

Rückgabetyp

STRING oder BYTES

Beispiele

WITH items AS
  (SELECT
    "foo" as item
  UNION ALL
  SELECT
    "bar" as item
  UNION ALL
  SELECT
    "baz" as item)

SELECT
  UPPER(item) AS example
FROM items;

+---------+
| example |
+---------+
| FOO     |
| BAR     |
| BAZ     |
+---------+

JSON-Funktionen

BigQuery unterstützt Funktionen, die Ihnen beim Abrufen von Daten und Funktionen helfen, die in JSON-formatierten Strings gespeichert sind und mit denen Sie Daten in JSON-formatierte Strings umwandeln können.

JSON_EXTRACT oder JSON_EXTRACT_SCALAR

JSON_EXTRACT(json_string_expr, json_path_string_literal), wodurch JSON-Werte als STRINGs zurückgegeben werden.

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal), wodurch skalare JSON-Werte als STRINGs zurückgegeben werden.

Beschreibung

Der Parameter json_string_expr muss ein JSON-formatierter String sein. Beispiel:

{"class" : {"students" : [{"name" : "Jane"}]}}

Der Parameter json_path_string_literal identifiziert den Wert oder die Werte, die Sie aus der JSON-formatierten Zeichenfolge erhalten möchten. Sie konstruieren diesen Parameter mithilfe des Formats JSONPath. Als Teil dieses Formats muss dieser Parameter mit einem $-Zeichen beginnen, das sich auf die äußerste Ebene des JSON-formatierten Strings bezieht. Sie können unter Verwendung von Punkt- oder Klammer-Notation untergeordnete Werte identifizieren. Wenn das JSON-Objekt ein Array ist, können Sie Klammern verwenden, um den Array-Index anzugeben.

JSONPath Beschreibung
Root-Objekt oder Element
. oder [] Untergeordneter Operator
[] Tiefgestellt-Operator

Beide Funktionen geben NULL zurück, wenn der Parameter json_path_string_literal nicht mit einem Wert in json_string_expr übereinstimmt. Wenn der ausgewählte Wert für JSON_EXTRACT_SCALAR nicht skalar ist, wie etwa ein Objekt oder ein Array, gibt die Funktion NULL zurück.

Wenn der JSONPath ungültig ist, erheben diese Funktionen einen Fehler.

In Fällen, in denen ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit einfachen Anführungszeichen und den Klammern [' '] maskieren. Beispiel:

SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;

+-------+
| hello |
+-------+
| world |
+-------+

Beispiele

SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

Die vorstehende Abfrage generiert das folgende Ergebnis:

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

Die vorstehende Abfrage generiert das folgende Ergebnis:

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

Die vorstehende Abfrage generiert das folgende Ergebnis:

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| {"first":"Jamie"} |
+-------------------+
SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

Die vorstehende Abfrage generiert das folgende Ergebnis:

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

Die vorstehende Abfrage generiert das folgende Ergebnis:

+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob"   | Jakob       | "6"      | 6      |
+-----------+-------------+----------+--------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Beschreibung

Gibt einen JSON-formatierten String zurück, der value darstellt. Diese Funktion unterstützt einen optionalen pretty_print-Parameter. Wenn pretty_print angegeben wurde, wird der zurückgegebene Wert zur besseren Lesbarkeit formatiert.

Datentyp der Eingabe Rückgabewert
NULL eines beliebigen Typs null
BOOL true oder false
INT64

Entspricht CAST(value AS STRING), wenn value im Bereich [-253, 253] liegt, dem Bereich der Ganzzahlen, die als Gleitkommazahlen nach IEEE 754 mit doppelter Genauigkeit verlustfrei dargestellt werden können. Werte außerhalb dieses Bereichs werden als Strings in Anführungszeichen dargestellt. Beispiel:

-1
0
12345678901
9007199254740992
-9007199254740992
"9007199254740993"

9007199254740993 ist größer als 253 und wird daher als String in Anführungszeichen dargestellt.

NUMERIC

Entspricht CAST(value AS STRING), wenn value im Bereich [-253, 253] liegt und keinen Bruchteil hat. Werte außerhalb dieses Bereichs werden als Strings in Anführungszeichen dargestellt. Beispiel:

-1
0
"9007199254740993"
"123.56"
FLOAT64 +/-inf und NaN werden jeweils als Infinity, -Infinity und NaN dargestellt.

Entspricht ansonsten CAST(value AS STRING).

STRING Stringwert in Anführungszeichen, gemäß JSON-Standard maskiert. ", \ und die Steuerzeichen von U+0000 bis U+001F werden maskiert.
BYTES

Base64-maskierter RFC 4648-Wert in Anführungszeichen. Beispiel:

"R29vZ2xl" ist die Base64-Darstellung der Bytes b"Google".

DATE

Datum in Anführungszeichen. Beispiel:

"2017-03-06"
TIMESTAMP

Datum/Uhrzeit gemäß ISO 8601 in Anführungszeichen, wobei T das Datum von der Uhrzeit trennt und Zulu/UTC die Zeitzone darstellt. Beispiel:

"2017-03-06T12:34:56.789012Z"
DATETIME

Datum/Uhrzeit gemäß ISO 8601 in Anführungszeichen, wobei T das Datum von der Uhrzeit trennt. Beispiel:

"2017-03-06T12:34:56.789012"
TIME

Uhrzeit gemäß ISO 8601 in Anführungszeichen. Beispiel:

"12:34:56.789012"
ARRAY

[elem1,elem2,...] , wobei jedes elem gemäß dem Elementtyp formatiert ist.

Beispiel mit Formatierung:

[
  elem1,
  elem2,
  ...
]

Dabei ist jedes Element gemäß dem Elementtyp formatiert. Ein leeres Array wird als [] dargestellt.

STRUCT {"field_name1":field_value1,"field_name2":field_value2,...}

Dabei ist jeder field_value gemäß seinem Typ formatiert.

Beispiel mit Formatierung:

{
  "field_name1": field_value1,
  "field_name2": field_value2,
  ...
}

Dabei ist jeder field_value gemäß seinem Typ formatiert. Wenn ein field_value ein nicht leeres ARRAY oder ein nicht leeres STRUCT ist, werden die Elemente auf die entsprechende Ebene eingerückt. Ein leeres STRUCT wird als {} dargestellt.

Felder mit identischen Namen können dazu führen, dass das JSON-Format nicht geparst werden kann. Anonyme Felder werden mit "" dargestellt.

Ungültige UTF-8-Feldnamen können dazu führen, dass das JSON-Format nicht geparst werden kann. Stringwerte werden nach dem JSON-Standard maskiert. ", \ und die Steuerzeichen von U+0000 bis U+001F werden maskiert.

Rückgabetyp

JSON-String-Darstellung des Wertes.

Beispiele

Umwandeln von Zeilen einer Tabelle in das JSON-Format.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  t,
  TO_JSON_STRING(t) AS json_row
FROM Input AS t;

Die vorstehende Abfrage generiert das folgende Ergebnis:

+-----------------------------------+-------------------------------------------------------+
| t                                 | json_row                                              |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
| {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
+-----------------------------------+-------------------------------------------------------+

Umwandeln von Zeilen einer Tabelle in JSON mit Formatierung.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;

Die vorstehende Abfrage generiert das folgende Ergebnis:

+-----------------------+
| json_row              |
+-----------------------+
| {                     |
|  "x": [               |
|    1,                 |
|    2                  |
|  ],                   |
|  "y": "foo",          |
|  "s": {               |
|    "a": true,         |
|    "b": "2017-04-05"  |
|  }                    |
|}                      |
| {                     |
|  "x": null,           |
|  "y": "",             |
|  "s": {               |
|    "a": false,        |
|    "b": "0001-01-01"  |
|  }                    |
|}                      |
| {                     |
|  "x": [               |
|    3                  |
|  ],                   |
|  "y": "bar",          |
|  "s": {               |
|    "a": null,         |
|    "b": "2016-12-05"  |
|  }                    |
|}                      |
+-----------------------+

Arrayfunktionen

ARRAY

ARRAY(subquery)

Beschreibung

Die Funktion ARRAY gibt ein ARRAY zurück, das für jede Zeile in einer Unterabfrage genau ein Element enthält.

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

Wenn durch die subquery eine Wertetabelle erzeugt wird, ist jedes Element im Ausgabe-ARRAY die gesamte entsprechende 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, wird von der ARRAY-Funktion ein ARRAY zurückgegeben, das diese Klausel beachtet.
  • Falls 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 ARRAY zurückgegeben werden, gibt die ARRAY-Funktion einen Fehler zurück: BigQuery unterstützt keine ARRAYs mit Elementen vom Typ ARRAY.
  • Wenn bei der Unterabfrage keine Zeilen zurückgegeben werden, gibt die ARRAY-Funktion ein leeres ARRAY zurück. Es wird nie ein NULL-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 erzeugen möchten, ändern Sie die Unterabfrage so, dass SELECT AS STRUCT verwendet wird. Daraufhin wird von der ARRAY-Funktion ein ARRAY mit STRUCTs zurückgegeben. 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}] |
+------------------------+

Ändern Sie analog dazu die Unterabfrage so, dass SELECT AS STRUCT verwendet wird, um ein ARRAY aus einer Unterabfrage mit einem oder mehreren ARRAYs zu erzeugen.

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 ["apples", "bananas", NULL, "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "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 |
+---------------------------------+------+
| [apples, bananas, NULL, grapes] | 4    |
| [coffee, tea, 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 sein.

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

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

Beispiele

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

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

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  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                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| 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 inklusiven Start und das inklusive Ende des Arrays.

Die Funktion GENERATE_ARRAY akzeptiert folgende Datentypen als Eingaben:

  • INT64
  • NUMERIC
  • FLOAT64

Der Parameter step_expression bestimmt die Schrittweite, die 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 gesetzt ist oder wenn eine Eingabe NaN ist.

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

Rückgabedatentyp

ARRAY

Beispiele

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

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

In folgendem Beispiel wird ein Array mit einem negativen Wert, -3 als Schrittgröße, zurückgeben.

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

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

In folgendem Beispiel wird ein Array mit demselben Wert für den start_expression und end_expression zurückgegeben.

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

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

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

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

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

Das folgende Beispiel gibt ein NULL-Array zurück, da end_expression NULL ist.

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

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

In folgendem 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 inklusiven Start und das inklusive Ende des Arrays.

Die Funktion GENERATE_DATE_ARRAY akzeptiert folgende Datentypen als Eingaben:

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

Der Parameter INT64_expr bestimmt die Schrittweite, die zum Generieren von Daten verwendet wird. Der Standardwert für diesen Parameter ist 1 Tag.

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

Rückgabedatentyp

Ein ARRAY mit mindestens 0 DATE-Werten.

Beispiele

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

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

In folgendem Beispiel wird ein Array mit einem negativen Wert, -3 als Schrittgröße, zurückgeben.

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

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

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

In folgendem Beispiel wird ein leeres Array zurückgegeben, da der start_date größer als der end_date ist und der step-Wert positiv ist.

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

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

In folgendem Beispiel wird ein NULL-Array zurückgegeben, da einer seiner Eingaben NULL ist.

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

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

In folgendem Beispiel wird ein Array von Daten zurückgegeben, wobei MONTH als date_part verwendet wird:

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

In folgendem Beispiel werden nicht konstante Daten zur Generierung eines Arrays verwendet.

WITH StartsAndEnds AS (
  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"
)
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM StartsAndEnds;

+--------------------------------------------------------------+
| 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, die durch ein bestimmtes Intervall getrennt sind. Die Parameter start_timestamp und end_timestamp bestimmen die inklusive untere und obere Grenze des ARRAYs.

Die Funktion GENERATE_TIMESTAMP_ARRAY akzeptiert folgende Datentypen als Eingaben:

  • start_timestamp: TIMESTAMP
  • end_timestamp: TIMESTAMP
  • step_expression: INT64
  • Erlaubte date_part-Werte sind MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR oder DAY.

Der Parameter step_expression bestimmt die Schrittweite, die 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 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 bzw. 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           |
+----------------------------------+---------------+----------------+

DATE-Funktionen

BigQuery unterstützt die folgenden DATE-Funktionen.

CURRENT_DATE

CURRENT_DATE([time_zone])

Beschreibung

Gibt das aktuelle Datum in der angegebenen oder der Standardzeitzone zurück.

Diese Funktion unterstützt einen optionalen Parameter time_zone. Dieser Parameter ist ein String, der die zu verwendende Zeitzone darstellt. Wenn keine Zeitzone angegeben ist, wird die Standardzeitzone UTC verwendet. Siehe Zeitzonendefinitionen für Anweisungen, wie eine Zeitzone festgelegt wird.

Wenn der time_zone-Parameter NULL auswertet, gibt diese Funktion NULL zurück.

Rückgabedatentyp

DATE

Beispiel

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

Beschreibung

Gibt den Wert zurück, der dem angegebenen Datumsteil entspricht. part muss einen der folgenden Werte aufweisen:

  • DAYOFWEEK: Gibt Werte im Bereich [1,7] mit Sonntag als dem ersten Tag der Woche zurück.
  • DAY
  • DAYOFYEAR
  • WEEK: gibt die Kalenderwoche des Datums im Bereich [0, 53] zurück. Wochen beginnen mit Sonntag und Datumsangaben vor dem ersten Sonntag des Jahres liegen in Woche 0.

  • WEEK(<WEEKDAY>): gibt die Kalenderwoche des Datums im Bereich [0, 53] zurück. Wochen beginnen mit WEEKDAY. Datumsangaben vor dem ersten WEEKDAY des Jahres liegen in Woche 0. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY und SATURDAY.

  • ISOWEEK: gibt die Wochennummer nach ISO 8601 für date_expression zurück. ISOWEEKs beginnen mit Montag. Rückgabewerte liegen im Bereich [1, 53]. Die erste ISOWEEK eines ISO-Jahres beginnt mit dem Montag vor dem ersten Donnerstag des gregorianischen Kalenderjahres.
  • MONTH
  • QUARTER: gibt Werte im Bereich [1,4] zurück.
  • YEAR
  • ISOYEAR: gibt das Jahr gemäß der Wochennummerierung nach ISO 8601 zurück, das dem gregorianischen Kalenderjahr mit dem Donnerstag der Woche entspricht, zu der date_expression gehört.

Rückgabedatentyp

INT64

Beispiele

Im folgenden Beispiel gibt EXTRACT einen Wert zurück, der DAY entspricht.

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

Im folgenden Beispiel gibt EXTRACT Werte zurück, die verschiedenen Teilen der Terminangabe aus einer Spalte von Terminen rund um das Jahresende entsprechen.

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

Im folgenden Beispiel fällt date_expression auf einen Sonntag. EXTRACT berechnet die erste Spalte mit Wochen, die am Sonntag beginnen, und die zweite Spalte mit Wochen, die am Montag beginnen.

WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
  date,
  EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;

+------------+-------------+-------------+
| date       | week_sunday | week_monday |
+------------+-------------+-------------+
| 2017-11-05 | 45          | 44          |
+------------+-------------+-------------+

DATE

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])

Beschreibung

  1. Konstruiert ein DATE aus INT64-Werten, die das Jahr, den Monat und den Tag repräsentieren.
  2. Wandelt einen timestamp_expression in einen DATE-Datentyp um. Ein optionaler Parameter zur Angabe einer Zeitzone wird unterstützt. Wenn keine Zeitzone angegeben ist, wird die Standardzeitzone UTC verwendet.

Rückgabedatentyp

DATE

Beispiel

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+
| date_ymd   | date_tstz  |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL INT64_expr date_part)

Beschreibung

Fügt einem DATE ein bestimmtes Zeitintervall hinzu.

DATE_ADD unterstützt die folgenden date_part-Werte:

  • DAY
  • WEEK. Entspricht 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

MONTH, QUARTER und YEAR müssen speziell behandelt werden, wenn das Datum am (oder in der Nähe des) letzten Tags des Monats liegt. Wenn der daraus resultierende Monat weniger Tage hat als der Monat des ursprünglichen Datums, dann ist der Ergebnistag der letzte Tag des neuen Monats.

Rückgabedatentyp

DATE

Beispiel

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL INT64_expr date_part)

Beschreibung

Subtrahiert ein bestimmtes Zeitintervall von einem DATE.

DATE_SUB unterstützt die folgenden date_part-Werte:

  • DAY
  • WEEK. Entspricht 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

MONTH, QUARTER und YEAR müssen speziell behandelt werden, wenn das Datum am (oder in der Nähe des) letzten Tags des Monats liegt. Wenn der daraus resultierende Monat weniger Tage hat als der Monat des ursprünglichen Datums, dann ist der Ergebnistag der letzte Tag des neuen Monats.

Rückgabedatentyp

DATE

Beispiel

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression, date_expression, date_part)

Beschreibung

Gibt die Anzahl der date_part-Grenzwerte zwischen den beiden Datumsausdrücken (date_expressions) zurück. Wenn das erste Datum vor dem zweiten Datum liegt, ist das Ergebnis nicht positiv.

DATE_DIFF unterstützt die folgenden date_part-Werte:

  • DAY
  • WEEK: Dieser Datumsteil beginnt mit Sonntag.
  • WEEK(<WEEKDAY>): Dieser Datumsteil beginnt mit WEEKDAY. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY und SATURDAY.
  • ISOWEEK: Verwendet Wochengrenzen nach ISO 8601. ISO-Wochen beginnen mit Montag.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: Verwendet die Jahresgrenze gemäß der Wochennummerierung nach ISO 8601. Die ISO-Jahresgrenze ist der Montag der ersten Woche, in der der Donnerstag in das entsprechende gregorianische Kalenderjahr fällt.

Rückgabedatentyp

INT64

Beispiel

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+
SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

Im obigen Beispiel wird das Ergebnis von DATE_DIFF für zwei aufeinanderfolgende Tage dargestellt. DATE_DIFF mit dem Datumsteil WEEK gibt 1 zurück, da von DATE_DIFF die Anzahl der Datumsteilgrenzen in diesem Datumsbereich gezählt wird. Jede WEEK beginnt mit Sonntag. Deshalb gibt es eine Datumsteilgrenze zwischen Samstag, dem 14.10.2017, und Sonntag, dem 15.10.2017.

Im folgenden Beispiel wird das Ergebnis von DATE_DIFF für zwei Datumsangaben in verschiedenen Jahren dargestellt. DATE_DIFF mit dem Datumsteil YEAR gibt 3 zurück, da die Anzahl der Grenzen zwischen gregorianischen Kalenderjahren zwischen den beiden Datumsangaben gezählt wird. DATE_DIFF mit dem Datumsteil ISOYEAR gibt 2 zurück, da das zweite Datum zum ISO-Jahr 2015 zählt. Der erste Donnerstag des Kalenderjahres 2015 fiel auf den 01.01.2015. Deshalb beginnt das ISO-Jahr 2015 mit dem Montag davor am 29.12.2014.

SELECT
  DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
  DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

Im folgenden Beispiel wird das Ergebnis von DATE_DIFF für zwei aufeinanderfolgende Tage dargestellt. Das erste Datum fällt auf einen Montag, das zweite auf einen Sonntag. DATE_DIFF mit dem Datumsteil WEEK gibt 0 zurück, da von diesem Datumsteil Wochen verwendet werden, die mit Sonntag beginnen. DATE_DIFF mit dem Datumsteil WEEK(MONDAY) gibt 1 zurück. DATE_DIFF mit dem Datumsteil ISOWEEK gibt ebenfalls 1 zurück, da ISO-Wochen mit Montag beginnen.

SELECT
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

Beschreibung

Kürzt das Datum auf die angegebene Granularität.

DATE_TRUNC unterstützt die folgenden Werte für date_part:

  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): kürzt date_expression auf die vorhergehende Wochengrenze, wobei Wochen mit WEEKDAY beginnen. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY und SATURDAY.
  • ISOWEEK: kürzt date_expression auf die vorhergehende Wochengrenze nach ISO 8601. ISOWEEKs beginnen mit Montag. Die erste ISOWEEK eines ISO-Jahres enthält den ersten Donnerstag des entsprechenden gregorianischen Kalenderjahres. Jede date_expression vor diesem Datum wird auf den vorhergehenden Montag gekürzt.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: kürzt date_expression auf die vorhergehende Jahresgrenze gemäß der Wochennummerierung nach ISO 8601. Die ISO-Jahresgrenze ist der Montag der ersten Woche, in der der Donnerstag in das entsprechende gregorianische Kalenderjahr fällt.

Rückgabedatentyp

DATE

Beispiele

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

Im folgenden Beispiel fällt das ursprüngliche Datum auf einen Sonntag. Da es sich bei date_part um WEEK(MONDAY) handelt, gibt DATE_TRUNC das DATE für den vorhergehenden Montag zurück.

SELECT date AS original, DATE_TRUNC(date, WEEK(MONDAY)) AS truncated
FROM (SELECT DATE('2017-11-05') AS date);

+------------+------------+
| original   | truncated  |
+------------+------------+
| 2017-11-05 | 2017-10-30 |
+------------+------------+

Im folgenden Beispiel liegt der ursprüngliche date_expression im gregorianischen Kalenderjahr 2015. DATE_TRUNC mit dem Datumsteil ISOYEAR kürzt date_expression jedoch bis zum Anfang des ISO-Jahres und nicht des gregorianischen Kalenderjahres. Der erste Donnerstag des Kalenderjahres 2015 fiel auf den 01.01.2015. Deshalb beginnt das ISO-Jahr 2015 mit dem Montag davor am 29.12.2014. Entsprechend fällt die ISO-Jahresgrenze vor date_expression 2015-06-15 auf den 29.12.2014.

SELECT
  DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;

+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29       | 2015           |
+------------------+----------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(INT64_expression)

Beschreibung

Interpretiert INT64_expression als Anzahl der Tage seit 01.01.1970.

Rückgabedatentyp

DATE

Beispiel

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

Beschreibung

Formatiert date_expr entsprechend dem angegebenen format_string.

Unter Unterstützte Formatelemente für DATE ist eine Liste von Formatelementen aufgeführt, die von dieser Funktion unterstützt werden.

Rückgabedatentyp

STRING

Beispiel

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Beschreibung

Verwendet für die Rückgabe eines DATE-Objekts einen format_string und eine Stringdarstellung eines Datums.

Bei der Verwendung von PARSE_DATE bitte Folgendes beachten:

  • Nicht spezifizierte Felder. Jedes unspezifizierte Feld wird ab 1970-01-01 initialisiert.
  • Groß-/Kleinschreibung kann ignoriert werden. Bei Namen, wie Monday, February und so weiter, kann die Groß-/Kleinschreibung ignoriert werden.
  • Leerraum. Ein oder mehrere aufeinanderfolgende Leerräume im Formatstring stimmen mit null oder mehreren aufeinanderfolgenden Leerräumen im Datumsstring überein. Darüber hinaus sind voran- und nachgestellte Leerräume im Datumsstring immer erlaubt­­ – auch wenn sie nicht im Formatstring vorhanden sind.
  • Formatvorrang. Wenn zwei (oder mehrere) Formatelemente überlappende Informationen aufweisen (z. B. %F und %Y beeinflussen beide das Jahr), dann überschreibt im Allgemeinen das letzte Format alle vorherigen Formate.

Unter Unterstützte Formatelemente für DATE ist eine Liste von Formatelementen aufgeführt, die von dieser Funktion unterstützt werden.

Rückgabedatentyp

DATE

Beispiel

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

Beschreibung

Gibt die Anzahl der Tage seit 01.01.1970 zurück.

Rückgabedatentyp

INT64

Beispiel

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

Unterstützte Formatelemente für DATE

Wenn nicht anders angegeben, unterstützen DATE-Funktionen, die Formatstrings verwenden, folgende Elemente:

Formatelement Beschreibung
%A Der vollständige Wochentagname.
%a Der abgekürzte Wochentagname.
%B Der vollständige Monatsname.
%b oder %h Der abgekürzte Monatsname.
%C Das Jahrhundert (ein Jahr geteilt durch 100 und gekürzt auf eine Ganzzahl) als Dezimalzahl (00–99).
%D Das Datum im Format %m/%d/%y.
%d Der Tag des Monats als Dezimalzahl (01–31).
%e Der Tag des Monats als Dezimalzahl (1–31); den einzelnen Ziffern ist ein Leerzeichen vorangestellt.
%F Das Datum im Format %Y-%m-%d.
%G Das Jahr nach ISO 8601 mit Jahrhundert als Dezimalzahl.
%g Das Jahr nach ISO 8601 ohne Jahrhundert als Dezimalzahl (00–99).
%j Der Tag des Jahres als Dezimalzahl (001–366).
%m Der Monat als Dezimalzahl (01–12).
%n Ein Zeilenumbruch.
%t Ein Tab-Zeichen.
%U Die Wochennummer des Jahres (Sonntag als erster Tag der Woche) als Dezimalzahl (00–53).
%u Der Wochentag (Montag als erster Tag der Woche) als Dezimalzahl (1–7).
%V Die Wochennummer des Jahres (Montag als erster Tag der Woche) als Dezimalzahl (01–53). Wenn die Woche einschließlich 1. Januar mindestens vier Tage im neuen Jahr aufweist, dann ist es Woche 1; andernfalls ist es Woche 53 des Vorjahres und die nächste Woche ist Woche 1.
%W Die Wochennummer des Jahres (Montag als erster Tag der Woche) als Dezimalzahl (00–53).
%w Der Wochentag (Sonntag als erster Tag der Woche) als Dezimalzahl (0–6).
%x Die Datumsdarstellung im MM/TT/JJ-Format.
%Y Das Jahr mit Jahrhundert als Dezimalzahl.
%y Das Jahr ohne Jahrhundert als Dezimalzahl (00–99) mit einer optionalen vorangestellten Null. Kann mit %C gemischt werden. Wenn %C nicht angegeben ist, sind die Jahre 00–68 2000er, während die Jahre 69–99 1900er sind.
%E4Y Jahre mit vier Ziffern (0001... 9999). Beachten Sie, dass %Y so viele Zeichen produziert, wie nötig sind, um das Jahr komplett darzustellen.

DATETIME-Funktionen

BigQuery unterstützt die folgenden DATETIME-Funktionen.

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

Beschreibung

Gibt die aktuelle Uhrzeit als DATETIME-Objekt zurück.

Diese Funktion unterstützt einen optionalen timezone-Parameter. Unter Zeitzonendefinitionen finden Sie Anweisungen, wie eine Zeitzone festgelegt wird.

Rückgabedatentyp

DATETIME

Beispiel

SELECT CURRENT_DATETIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 2016-05-19 10:38:47.046465 |
+----------------------------+

DATETIME

1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression, time_expression)
3. DATETIME(timestamp_expression [, timezone])

Beschreibung

  1. Konstruiert ein DATETIME-Objekt mit INT64-Werten, die das Jahr, den Monat, den Tag, die Stunde, die Minute und die Sekunde darstellen.
  2. Konstruiert ein DATETIME-Objekt mit einem DATE-Objekt und einem TIME-Objekt.
  3. Konstruiert ein DATETIME-Objekt mit einem TIMESTAMP-Objekt. Ein optionaler Parameter zur Angabe einer Zeitzone wird unterstützt. Wenn keine Zeitzone angegeben ist, wird die Standardzeitzone UTC verwendet.

Rückgabedatentyp

DATETIME

Beispiel

SELECT
  DATETIME(2008, 12, 25, 05, 30, 00) as datetime_ymdhms,
  DATETIME(TIMESTAMP "2008-12-25 05:30:00+00", "America/Los_Angeles") as datetime_tstz;

+---------------------+---------------------+
| datetime_ymdhms     | datetime_tstz       |
+---------------------+---------------------+
| 2008-12-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL INT64_expr part)

Beschreibung

Fügt INT64_expr-Einheiten des part dem DATETIME-Objekt hinzu.

DATETIME_ADD unterstützt die folgenden Werte für part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK. Entspricht 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

MONTH, QUARTER und YEAR müssen speziell behandelt werden, wenn das Datum der letzte Tag des Monats ist oder in dessen Nähe liegt. Wenn der daraus resultierende Monat weniger Tage als der Monat des ursprünglichen Datums hat, ist der Ergebnistag der letzte Tag des neuen Monats.

Rückgabedatentyp

DATETIME

Beispiel

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_ADD(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as later;

+-----------------------------+------------------------+
| original_date               | later                  |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:40:00    |
+-----------------------------+------------------------+

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL INT64_expr part)

Beschreibung

Subtrahiert INT64_expr-Einheiten des part aus dem DATETIME.

DATETIME_SUB unterstützt die folgenden Werte für part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK. Entspricht 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

MONTH, QUARTER und YEAR müssen speziell behandelt werden, wenn das Datum der letzte Tag des Monats ist oder in dessen Nähe liegt. Wenn der daraus resultierende Monat weniger Tage als der Monat des ursprünglichen Datums hat, ist der Ergebnistag der letzte Tag des neuen Monats.

Rückgabedatentyp

DATETIME

Beispiel

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date               | earlier                |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:20:00    |
+-----------------------------+------------------------+

DATETIME_DIFF

DATETIME_DIFF(datetime_expression, datetime_expression, part)

Beschreibung

Gibt die Anzahl der part-Grenzen zwischen den zwei datetime_expressions zurück. Wenn das erste DATETIME-Objekt vor dem zweiten DATETIME-Objekt liegt, ist das Ergebnis nicht positiv. Ein Fehler wird ausgegeben, wenn die Berechnung den Ergebnistyp überläuft, z. B. wenn der Unterschied in Mikrosekunden zwischen den beiden DATETIME-Objekten einen INT64-Wert überlaufen würde.

DATETIME_DIFF unterstützt die folgenden Werte für part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK: Dieser Datumsteil beginnt mit Sonntag.
  • WEEK(<WEEKDAY>): Dieser Datumsteil beginnt mit WEEKDAY. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY und SATURDAY.
  • ISOWEEK: verwendet Wochengrenzen nach ISO 8601. ISO-Wochen beginnen mit Montag.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: Verwendet die Jahresgrenze gemäß der Wochennummerierung nach ISO 8601. Die ISO-Jahresgrenze ist der Montag der ersten Woche, in der der Donnerstag in das entsprechende gregorianische Kalenderjahr fällt.

Rückgabedatentyp

INT64

Beispiel

SELECT
  DATETIME "2010-07-07 10:20:00" as first_datetime,
  DATETIME "2008-12-25 15:30:00" as second_datetime,
  DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",
    DATETIME "2008-12-25 15:30:00", DAY) as difference;

+----------------------------+------------------------+------------------------+
| first_datetime             | second_datetime        | difference             |
+----------------------------+------------------------+------------------------+
| 2010-07-07 10:20:00        | 2008-12-25 15:30:00    | 559                    |
+----------------------------+------------------------+------------------------+
SELECT
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', DAY) as days_diff,
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

Im obigen Beispiel wird das Ergebnis von DATETIME_DIFF für zwei DATETIMEs dargestellt, die 24 Stunden auseinanderliegen. DATETIME_DIFF mit dem Teil WEEK gibt 1 zurück, da von DATETIME_DIFF die Anzahl der Teilgrenzen in diesem DATETIME-Bereich gezählt wird. Jede WEEK beginnt mit dem Sonntag. Deshalb gibt es eine Teilgrenze zwischen Samstag, 2017-10-14 00:00:00, und Sonntag, 2017-10-15 00:00:00.

Im folgenden Beispiel wird das Ergebnis von DATETIME_DIFF für zwei Datumsangaben in verschiedenen Jahren dargestellt. DATETIME_DIFF mit dem Datumsteil YEAR gibt 3 zurück, da die Anzahl der Grenzen zwischen gregorianischen Kalenderjahren zwischen den beiden DATETIME-Angaben gezählt wird. DATETIME_DIFF mit dem Datumsteil ISOYEAR gibt 2 zurück, da die zweite DATETIME-Angabe zum ISO-Jahr 2015 zählt. Der erste Donnerstag des Kalenderjahres 2015 fiel auf den 01.01.2015. Deshalb beginnt das ISO-Jahr 2015 mit dem Montag davor am 29.12.2014.

SELECT
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', YEAR) AS year_diff,
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

Im folgenden Beispiel wird das Ergebnis von DATETIME_DIFF für zwei aufeinanderfolgende Tage dargestellt. Das erste Datum fällt auf einen Montag, das zweite auf einen Sonntag. DATETIME_DIFF mit dem Datumsteil WEEK gibt 0 zurück, da von diesem Datumsteil Wochen verwendet werden, die mit Sonntag beginnen. DATETIME_DIFF mit dem Datumsteil WEEK(MONDAY) gibt 1 zurück. DATETIME_DIFF mit dem Datumsteil ISOWEEK gibt ebenfalls 1 zurück, da ISO-Wochen mit Montag beginnen.

SELECT
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

Beschreibung

Kürzt ein DATETIME-Objekt auf die Granularität von part

DATETIME_TRUNC unterstützt die folgenden Werte für part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): kürzt datetime_expression auf die vorhergehende Wochengrenze, wobei Wochen mit WEEKDAY beginnen. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY und SATURDAY.
  • ISOWEEK: kürzt datetime_expression auf die vorhergehende Wochengrenze nach ISO 8601. ISOWEEKs beginnen mit Montag. Die erste ISOWEEK eines ISO-Jahres enthält den ersten Donnerstag des entsprechenden gregorianischen Kalenderjahres. Jede date_expression vor diesem Datum wird auf den vorhergehenden Montag gekürzt.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: kürzt datetime_expression auf die vorhergehende Jahresgrenze gemäß der Wochennummerierung nach ISO 8601. Die ISO-Jahresgrenze ist der Montag der ersten Woche, in der der Donnerstag in das entsprechende gregorianische Kalenderjahr fällt.

Rückgabedatentyp

DATETIME

Beispiele

SELECT
  DATETIME "2008-12-25 15:30:00" as original,
  DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) as truncated;

+----------------------------+------------------------+
| original                   | truncated              |
+----------------------------+------------------------+
| 2008-12-25 15:30:00        | 2008-12-25 00:00:00    |
+----------------------------+------------------------+

Im folgenden Beispiel fällt die ursprüngliche DATETIME-Angabe auf einen Sonntag. Da es sich bei part um WEEK(MONDAY) handelt, gibt DATE_TRUNC die DATETIME-Angabe für den vorhergehenden Montag zurück.

SELECT
 datetime AS original,
 DATETIME_TRUNC(datetime, WEEK(MONDAY)) AS truncated
FROM (SELECT DATETIME(TIMESTAMP '2017-11-05 00:00:00') AS datetime);

+---------------------+---------------------+
| original            | truncated           |
+---------------------+---------------------+
| 2017-11-05 00:00:00 | 2017-10-30 00:00:00 |
+---------------------+---------------------+

Im folgenden Beispiel liegt die ursprüngliche datetime_expression im gregorianischen Kalenderjahr 2015. DATETIME_TRUNC mit dem Datumsteil ISOYEAR kürzt datetime_expression jedoch bis zum Anfang des ISO-Jahres und nicht des gregorianischen Kalenderjahres. Der erste Donnerstag des Kalenderjahres 2015 fiel auf den 01.01.2015. Deshalb beginnt das ISO-Jahr 2015 mit dem Montag davor am 29.12.2014. Entsprechend fällt die ISO-Jahresgrenze vor datetime_expression 2015-06-15 00:00:00 auf den 29.12.2014.

SELECT
  DATETIME_TRUNC('2015-06-15 00:00:00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATETIME '2015-06-15 00:00:00') AS isoyear_number;

+---------------------+----------------+
| isoyear_boundary    | isoyear_number |
+---------------------+----------------+
| 2014-12-29 00:00:00 | 2015           |
+---------------------+----------------+

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Beschreibung

Formatiert ein DATETIME-Objekt entsprechend dem angegebenen format_string. Unter Unterstützte Formatelemente für DATETIME ist eine Liste von Formatelementen aufgeführt, die von dieser Funktion unterstützt werden.

Rückgabedatentyp

STRING

Beispiel

SELECT
  FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
  AS formatted;

PARSE_DATETIME

PARSE_DATETIME(format_string, string)

Beschreibung

Verwendet einen format_string und eine Stringdarstellung eines Zeitstempels, um ein TIMESTAMP-Objekt zurückzugeben. Unter Unterstützte Formatelemente für DATETIME ist eine Liste von Formatelementen aufgeführt, die von dieser Funktion unterstützt werden.

Bei der Verwendung von PARSE_DATETIME bitte Folgendes beachten:

  • Nicht spezifizierte Felder. Jedes nicht spezifizierte Feld wird ab 1970-01-01 00:00:00.0 initialisiert. Wenn beispielsweise das Jahr nicht spezifiziert ist, wird standardmäßig 1970 festgelegt usw.
  • Groß-/Kleinschreibung kann ignoriert werden. Bei Namen wie Monday, February usw. kann die Groß-/Kleinschreibung ignoriert werden.
  • Leerraum. Ein oder mehrere aufeinanderfolgende Leerräume im Formatstring stimmen mit null oder mehreren aufeinanderfolgenden Leerräumen im DATETIME-String überein. Darüber hinaus sind voran- und nachgestellte Leerräume im DATETIME-String immer erlaubt – auch wenn sie nicht im Formatstring vorhanden sind.
  • Formatvorrang. Wenn zwei oder mehr Formatelemente überlappende Informationen haben (%F und %Y beeinflussen z. B. beide das Jahr), überschreibt im Allgemeinen das letzte Format alle vorherigen Formate, mit einigen Ausnahmen (siehe Beschreibung von %s, %C und %y).

Rückgabedatentyp

DATETIME

Unterstützte Formatelemente für DATETIME

Wenn nicht anders angegeben, unterstützen DATETIME-Funktionen, die Formatstrings verwenden, folgende Elemente:

Formatelement Beschreibung
%A Der vollständige Wochentagname.
%a Der abgekürzte Wochentagname.
%B Der vollständige Monatsname.
%b oder %h Der abgekürzte Monatsname.
%C Das Jahrhundert (ein Jahr geteilt durch 100 und gekürzt auf eine Ganzzahl) als Dezimalzahl (00–99).
%c Die Datums- und Uhrzeitdarstellung.
%D Das Datum im Format %m/%d/%y.
%d Der Tag des Monats als Dezimalzahl (01–31).
%e Der Tag des Monats als Dezimalzahl (1–31); den einzelnen Ziffern ist ein Leerzeichen vorangestellt.
%F Das Datum im Format %Y-%m-%d.
%G Das Jahr nach ISO 8601 mit Jahrhundert als Dezimalzahl.
%g Das Jahr nach ISO 8601 ohne Jahrhundert als Dezimalzahl (00–99).
%H Die Stunde (24-Stunden-Anzeige) als Dezimalzahl (00–23).
%I Die Stunde (12-Stunden-Anzeige) als Dezimalzahl (01–12).
%j Der Tag des Jahres als Dezimalzahl (001–366).
%k Die Stunde (24-Stunden-Anzeige) als Dezimalzahl (0–23); den einzelnen Ziffern ist ein Leerzeichen vorangestellt.
%l Die Stunde (12-Stunden-Anzeige) als Dezimalzahl (1–12); den einzelnen Ziffern ist ein Leerzeichen vorangestellt.
%M Die Minute als Dezimalzahl (00–59).
%m Der Monat als Dezimalzahl (01–12).
%n Ein Zeilenumbruch.
%P Entweder "am" oder "pm".
%p Entweder "AM" oder "PM".
%R Die Zeit im Format %H:%M.
%r Die 12-Stunden-Anzeige mit AM/PM-Notation.
%S Die Sekunde als Dezimalzahl (00–60).
%s Die Anzahl der Sekunden seit 01.01.1970 00:00:00. Überschreibt stets alle anderen Formatelemente, unabhängig davon, wo %s im String angezeigt wird. Wenn mehrere %s Elemente angezeigt werden, dann hat das letzte Element Vorrang.
%T Die Zeit im Format %H:%M:%S.
%t Ein Tab-Zeichen.
%U Die Wochennummer des Jahres (Sonntag als erster Tag der Woche) als Dezimalzahl (00–53).
%u Der Wochentag (Montag als erster Tag der Woche) als Dezimalzahl (1–7).
%V Die Wochennummer des Jahres (Montag als erster Tag der Woche) als Dezimalzahl (01–53). Wenn die Woche einschließlich 1. Januar mindestens vier Tage im neuen Jahr aufweist, dann ist es Woche 1; andernfalls ist es Woche 53 des Vorjahres und die nächste Woche ist Woche 1.
%W Die Wochennummer des Jahres (Montag als erster Tag der Woche) als Dezimalzahl (00–53).
%w Der Wochentag (Sonntag als erster Tag der Woche) als Dezimalzahl (0–6).
%X Die Zeitdarstellung im HH:MM:SS-Format.
%x Die Datumsdarstellung im MM/TT/JJ-Format.
%Y Das Jahr mit Jahrhundert als Dezimalzahl.
%y Das Jahr ohne Jahrhundert als Dezimalzahl (00–99) mit einer optionalen vorangestellten Null. Kann mit %C gemischt werden. Wenn %C nicht angegeben ist, sind die Jahre 00–68 2000er, während die Jahre 69–99 1900er sind.
%% Ein einzelnes %-Zeichen.
%E#S Sekunden mit #-Ziffern in Bruchgenauigkeit.
%E*S Sekunden mit vollständiger Bruchgenauigkeit (ein literales "*").
%E4Y Jahre mit vier Ziffern (0001... 9999). Beachten Sie, dass %Y so viele Zeichen produziert, wie nötig sind, um das Jahr komplett darzustellen.

TIME-Funktionen

BigQuery unterstützt die folgenden TIME-Funktionen.

CURRENT_TIME

CURRENT_TIME()

Beschreibung

Gibt die aktuelle Uhrzeit als TIME-Objekt zurück.

Rückgabedatentyp

TIME

Beispiel

SELECT CURRENT_TIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 15:31:38.776361            |
+----------------------------+

TIME

1. TIME(hour, minute, second)
2. TIME(timestamp, [timezone])
3. TIME(datetime)

Beschreibung

  1. Erzeugt ein TIME-Objekt mit INT64-Werten, die die Stunde, die Minute und die Sekunde repräsentieren.
  2. Erzeugt ein TIME-Objekt mit einem TIMESTAMP-Objekt. Es wird ein optionaler Parameter zur Angabe einer Zeitzone unterstützt. Wenn keine Zeitzone angegeben ist, wird die Standardzeitzone UTC verwendet.
  3. Erzeugt ein TIME-Objekt mit einem DATETIME-Objekt.

Rückgabedatentyp

TIME

Beispiel

SELECT
  TIME(15, 30, 00) as time_hms,
  TIME(TIMESTAMP "2008-12-25 15:30:00+08", "America/Los_Angeles") as time_tstz;
+----------+-----------+
| time_hms | time_tstz |
+----------+-----------+
| 15:30:00 | 23:30:00  |
+----------+-----------+
SELECT
  TIME(DATETIME "2008-12-25 15:30:00.000000") AS time_dt;
+----------+
| time_dt  |
+----------+
| 15:30:00 |
+----------+

TIME_ADD

TIME_ADD(time_expression, INTERVAL INT64_expr part)

Beschreibung

Fügt INT64_expr-Einheiten von part dem TIME-Objekt hinzu.

TIME_ADD unterstützt die folgenden Werte für part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

Diese Funktion passt sich automatisch an, wenn Werte außerhalb der Begrenzung 00:00:00 bis 24:00:00 liegen. Wenn Sie beispielsweise zu 23:30:00 eine Stunde hinzufügen, ist der zurückgegebene Wert 00:30:00.

Rückgabedatentypen

TIME

Beispiel

SELECT
  TIME "15:30:00" as original_time,
  TIME_ADD(TIME "15:30:00", INTERVAL 10 MINUTE) as later;

+-----------------------------+------------------------+
| original_time               | later                  |
+-----------------------------+------------------------+
| 15:30:00                    | 15:40:00               |
+-----------------------------+------------------------+

TIME_SUB

TIME_SUB(time_expression, INTERVAL INT_expr part)

Beschreibung

Subtrahiert INT64_expr-Einheiten von part aus dem TIME-Objekt.

TIME_SUB unterstützt die folgenden Werte für part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

Diese Funktion passt sich automatisch an, wenn Werte außerhalb der Begrenzung 00:00:00 bis 24:00:00 liegen. Wenn Sie beispielsweise von 00:30:00 eine Stunde abziehen, ist der zurückgegebene Wert 23:30:00.

Rückgabedatentyp

TIME

Beispiel

SELECT
  TIME "15:30:00" as original_date,
  TIME_SUB(TIME "15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date                | earlier                |
+-----------------------------+------------------------+
| 15:30:00                    | 15:20:00               |
+-----------------------------+------------------------+

TIME_DIFF

TIME_DIFF(time_expression, time_expression, part)

Beschreibung

Gibt die Anzahl der gesamten angegebenen part-Intervalle zwischen zwei TIME-Objekten zurück. Ein Fehler wird ausgegeben, wenn die Berechnung den Ergebnistyp überläuft, z. B. wenn der Unterschied in Mikrosekunden zwischen den beiden Zeitobjekten einen INT64-Wert überlaufen würde.

TIME_DIFF unterstützt die folgenden Werte für part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

Rückgabedatentyp

INT64

Beispiel

SELECT
  TIME "15:30:00" as first_time,
  TIME "14:35:00" as second_time,
  TIME_DIFF(TIME "15:30:00", TIME "14:35:00", MINUTE) as difference;

+----------------------------+------------------------+------------------------+
| first_time                 | second_time            | difference             |
+----------------------------+------------------------+------------------------+
| 15:30:00                   | 14:35:00               | 55                     |
+----------------------------+------------------------+------------------------+

TIME_TRUNC

TIME_TRUNC(time_expression, part)

Beschreibung

Kürzt ein TIME-Objekt auf die Granularität von part.

TIME_TRUNC unterstützt die folgenden Werte für part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

Rückgabedatentyp

TIME

Beispiel

SELECT
  TIME "15:30:00" as original,
  TIME_TRUNC(TIME "15:30:00", HOUR) as truncated;

+----------------------------+------------------------+
| original                   | truncated              |
+----------------------------+------------------------+
| 15:30:00                   | 15:00:00               |
+----------------------------+------------------------+

FORMAT_TIME

FORMAT_TIME(format_string, time_object)

Beschreibung: Formatiert ein TIME-Objekt entsprechend dem angegebenen format_string. Unter Unterstützte Formatelemente für TIME ist eine Liste von Formatelementen aufgeführt, die von dieser Funktion unterstützt werden.

Rückgabedatentyp

STRING

Beispiel

SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time;

+----------------+
| formatted_time |
+----------------+
| 15:30          |
+----------------+

PARSE_TIME

PARSE_TIME(format_string, string)

Beschreibung

Verwendet einen format_string und einen String zur Rückgabe eines TIME-Objekts. Unter Unterstützte Formatelemente für TIME ist eine Liste von Formatelementen aufgeführt, die von dieser Funktion unterstützt werden.

Bei der Verwendung von PARSE_TIME bitte Folgendes beachten:

  • Nicht spezifizierte Felder. Jedes nicht spezifizierte Feld wird ab 00:00:00.0 initialisiert. Wenn seconds beispielsweise nicht spezifiziert ist, dann ist der Standardwert 00 usw.
  • Leerraum. Ein oder mehrere aufeinanderfolgende Leerräume im Formatstring stimmen mit null oder mehreren aufeinanderfolgenden Leeräumen im TIME-String überein. Darüber hinaus sind voran- und nachgestellte Leeräume im TIME-String immer erlaubt – auch wenn sie nicht im Formatstring vorhanden sind.
  • Formatvorrang. Wenn zwei (oder mehrere) Formatelemente überlappende Informationen aufweisen, dann überschreibt im Allgemeinen das letzte Format alle vorherigen Formate.

Rückgabedatentyp

TIME

Beispiel

SELECT PARSE_TIME("%H", "15") as parsed_time;

+-------------+
| parsed_time |
+-------------+
| 15:00:00    |
+-------------+

Unterstützte Formatelemente für TIME

Wenn nicht anders angegeben, unterstützen TIME-Funktionen, die Formatstrings verwenden, folgende Elemente:

Formatelement Beschreibung
%H Die Stunde (24-Stunden-Anzeige) als Dezimalzahl (00–23).
%I Die Stunde (12-Stunden-Anzeige) als Dezimalzahl (01–12).
%j Der Tag des Jahres als Dezimalzahl (001–366).
%k Die Stunde (24-Stunden-Anzeige) als Dezimalzahl (0–23); den einzelnen Ziffern ist ein Leerzeichen vorangestellt.
%l Die Stunde (12-Stunden-Anzeige) als Dezimalzahl (1–12); den einzelnen Ziffern ist ein Leerzeichen vorangestellt.
%M Die Minute als Dezimalzahl (00–59).
%n Ein Zeilenumbruch.
%P Entweder "am" oder "pm".
%p Entweder "AM" oder "PM".
%R Die Zeit im Format %H:%M.
%r Die 12-Stunden-Anzeige mit AM/PM-Notation.
%S Die Sekunde als Dezimalzahl (00–60).
%T Die Zeit im Format %H:%M:%S.
%t Ein Tabulatorzeichen.
%X Die Zeitdarstellung im Format HH:MM:SS.
%% Ein einzelnes %-Zeichen.
%E#S Sekunden mit #-Ziffern in Bruchgenauigkeit.
%E*S Sekunden mit vollständiger Bruchgenauigkeit (ein literales "*").

Zeitstempelfunktionen

BigQuery unterstützt die folgenden TIMESTAMP-Funktionen.

HINWEIS: Diese Funktionen geben bei Überlauf einen Laufzeitfehler zurück. Ergebniswerte werden durch die definierten Mindest-/Maximalwerte von Datum und Zeitstempel begrenzt.

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

Beschreibung

Klammern sind optional. Diese Funktion verarbeitet Schaltsekunden, indem sie über ein Fenster von 20 Stunden um die eingefügten Schaltsekunden herum verteilt werden. CURRENT_TIMESTAMP() erzeugt einen TIMESTAMP-Wert, der kontinuierlich und eindeutig ist, genau 60 Sekunden pro Minute hat und keine Werte über die Schaltsekunde wiederholt.

Unterstützte Eingabetypen

Nicht zutreffend

Datentyp des Ergebnisses

TIMESTAMP

Beispiel

SELECT CURRENT_TIMESTAMP() as now;

+-------------------------------+
| now                           |
+-------------------------------+
| 2016-05-16 18:12:47.145482+00 |
+-------------------------------+

EXTRACT

EXTRACT(part FROM timestamp_expression [AT TIME ZONE tz_spec])

Beschreibung

Gibt einen INT64-Wert zurück, der dem bestimmten part aus einer angegebenen timestamp_expression entspricht.

Zulässige Werte für part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK: gibt die Kalenderwoche des Datums im Bereich [0, 53] zurück. Wochen beginnen mit Sonntag und Datumsangaben vor dem ersten Sonntag des Jahres liegen in Woche 0.

  • WEEK(<WEEKDAY>): gibt die Kalenderwoche von timestamp_expression im Bereich [0, 53] zurück. Wochen beginnen mit WEEKDAY. datetimes vor dem ersten WEEKDAY des Jahres liegen in Woche 0. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY und SATURDAY.

  • ISOWEEK: gibt die Wochennummer nach ISO 8601 für datetime_expression zurück. ISOWEEKs beginnen mit Montag. Rückgabewerte liegen im Bereich [1, 53]. Die erste ISOWEEK eines ISO-Jahres beginnt mit dem Montag vor dem ersten Donnerstag des gregorianischen Kalenderjahres.

  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: gibt das Jahr gemäß der Wochennummerierung nach ISO 8601 zurück, das dem gregorianischen Kalenderjahr mit dem Donnerstag der Woche entspricht, zu der date_expression gehört.
  • DATE
  • DATETIME
  • TIME

Bei den zurückgegebenen Werten werden jeweils die kleineren Zeiträume weggekürzt. Wenn Sie zum Beispiel Sekunden extrahieren, werden durch EXTRACT die Millisekunden- und Mikrosekundenwerte entfernt.

Informationen zur Vorgehensweise beim Angeben einer Zeitzone finden Sie in Zeitzonendefinitionen.

Rückgabedatentyp

Im Allgemeinen INT64. Gibt DATE zurück, wenn part DATE ist.

Beispiele

Im folgenden Beispiel gibt EXTRACT einen Wert zurück, der dem DAY entspricht.

SELECT EXTRACT(DAY
  FROM TIMESTAMP "2008-12-25 15:30:00" AT TIME ZONE "America/Los_Angeles")
  AS the_day;

+------------+
| the_day    |
+------------+
| 25         |
+------------+

Im folgenden Beispiel gibt EXTRACT Werte zurück, die verschiedenen Teilen der Terminangabe aus einer Spalte mit Zeitstempeln entsprechen.

WITH Timestamps AS (
  SELECT TIMESTAMP '2005-01-03 12:34:56' AS timestamp UNION ALL
  SELECT TIMESTAMP '2007-12-31' UNION ALL
  SELECT TIMESTAMP '2009-01-01' UNION ALL
  SELECT TIMESTAMP '2009-12-31' UNION ALL
  SELECT TIMESTAMP '2017-01-02' UNION ALL
  SELECT TIMESTAMP '2017-05-26'
)
SELECT
  timestamp,
  EXTRACT(ISOYEAR FROM timestamp) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp) AS isoweek,
  EXTRACT(YEAR FROM timestamp) AS year,
  EXTRACT(WEEK FROM timestamp) AS week
FROM Timestamps
ORDER BY timestamp;
+------------------------+---------+---------+------+------+
| timestamp              | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03 12:34:56+00 | 2005    | 1       | 2005 | 1    |
| 2007-12-31 00:00:00+00 | 2008    | 1       | 2007 | 52   |
| 2009-01-01 00:00:00+00 | 2009    | 1       | 2009 | 0    |
| 2009-12-31 00:00:00+00 | 2009    | 53      | 2009 | 52   |
| 2017-01-02 00:00:00+00 | 2017    | 1       | 2017 | 1    |
| 2017-05-26 00:00:00+00 | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

Im folgenden Beispiel fällt timestamp_expression auf einen Sonntag. EXTRACT berechnet die erste Spalte mit Wochen, die am Sonntag beginnen, und die zweite Spalte mit Wochen, die am Montag beginnen.

WITH table AS (SELECT TIMESTAMP('2017-11-05 00:00:00') AS timestamp)
SELECT
  timestamp,
  EXTRACT(WEEK(SUNDAY) FROM timestamp) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM timestamp) AS week_monday
FROM table;

+------------------------+-------------+---------------+
| timestamp              | week_sunday | week_monday |
+------------------------+-------------+---------------+
| 2017-11-05 00:00:00+00 | 45          | 44            |
+------------------------+-------------+---------------+

STRING

STRING(timestamp_expression[, timezone])

Beschreibung

Wandelt einen timestamp_expression in einen STRING-Datentyp um. Unterstützt einen optionalen Parameter zur Angabe einer Zeitzone. Informationen zur Vorgehensweise beim Angeben einer Zeitzone finden Sie in Zeitzonendefinitionen.

Rückgabedatentyp

STRING

Beispiel

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles") as string;

+-------------------------------+
| string                        |
+-------------------------------+
| 2008-12-25 15:30:00-08        |
+-------------------------------+

TIMESTAMP

1. TIMESTAMP(string_expression[, timezone])
2. TIMESTAMP(date_expression[, timezone])
3. TIMESTAMP(datetime_expression[, timezone])

Beschreibung

  1. Wandelt einen STRING-Ausdruck in einen TIMESTAMP-Datentyp um.

  2. Wandelt ein DATE-Objekt in einen TIMESTAMP-Datentyp um.

  3. Wandelt ein DATETIME-Objekt in einen TIMESTAMP-Datentyp um.

Diese Funktion unterstützt einen optionalen Parameter zur Angabe einer Zeitzone. Wenn keine Zeitzone angegeben ist, wird die Standardzeitzone UTC verwendet.

Rückgabedatentyp

TIMESTAMP

Beispiel

SELECT
  CAST(TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_str,
  CAST(TIMESTAMP(DATE "2008-12-25", "America/Los_Angeles") AS STRING) AS timestamp_date,
  CAST(TIMESTAMP(DATETIME "2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_datetime;

+------------------------+------------------------+------------------------+
| timestamp_str          | timestamp_date         | timestamp_datetime     |
+------------------------+------------------------+------------------------+
| 2008-12-25 23:30:00+00 | 2008-12-25 08:00:00+00 | 2008-12-25 23:30:00+00 |
+------------------------+------------------------+------------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

Beschreibung

Fügt unabhängig von jeder Zeitzone int64_expression-Einheiten des Typs date_part zum Zeitstempel hinzu.

TIMESTAMP_ADD unterstützt die folgenden Werte für date_part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR. Entspricht 60 MINUTEn.

Rückgabedatentypen

TIMESTAMP

Beispiel

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS later;

+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:40:00+00 |
+------------------------+------------------------+

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

Beschreibung

Subtrahiert unabhängig von jeder Zeitzone int64_expression-Einheiten für date_part vom Zeitstempel.

TIMESTAMP_SUB unterstützt die folgenden Werte für date_part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR. Entspricht 60 MINUTEn.

Rückgabedatentyp

TIMESTAMP

Beispiel

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS earlier;

+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:20:00+00 |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression, timestamp_expression, date_part)

Beschreibung

Gibt die Anzahl der gesamten angegebenen date_part-Intervalle zwischen zwei Zeitstempeln zurück. Die erste timestamp_expression repräsentiert das spätere Datum. Wenn die erste timestamp_expression vor der zweiten timestamp_expression liegt, ist die Ausgabe negativ. Ein Fehler wird ausgegeben, wenn die Berechnung den Ergebnistyp überläuft, z. B. wenn der Unterschied in Mikrosekunden zwischen den beiden Zeitstempeln einen INT64-Wert überlaufen würde.

TIMESTAMP_DIFF unterstützt die folgenden Werte für date_part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR. Entspricht 60 MINUTEn.

Rückgabedatentyp

INT64

Beispiel

SELECT
  TIMESTAMP "2010-07-07 10:20:00 UTC" as later_timestamp,
  TIMESTAMP "2008-12-25 15:30:00 UTC" as earlier_timestamp,
  TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00 UTC",
    TIMESTAMP "2008-12-25 15:30:00 UTC", HOUR) AS hours;

+------------------------+------------------------+-------+
| later_timestamp        | earlier_timestamp      | hours |
+------------------------+------------------------+-------+
| 2010-07-07 10:20:00+00 | 2008-12-25 15:30:00+00 | 13410 |
+------------------------+------------------------+-------+

Im folgenden Beispiel liegt der erste Zeitstempel vor dem zweiten, was zu einer negativen Ausgabe führt.

SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY);

+---------------+
| negative_diff |
+---------------+
| -61           |
+---------------+

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

Beschreibung

Kürzt einen Zeitstempel auf den Detaillierungsgrad von date_part.

TIMESTAMP_TRUNC unterstützt die folgenden Werte für date_part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): kürzt timestamp_expression auf die vorhergehende Wochengrenze, wobei die Wochen mit WEEKDAY beginnen. Gültige Werte für WEEKDAY sind SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY und SATURDAY.
  • ISOWEEK: kürzt timestamp_expression auf die vorhergehende Wochengrenze nach ISO 8601. ISOWEEKs beginnen mit Montag. Die erste ISOWEEK eines ISO-Jahres enthält den ersten Donnerstag des entsprechenden gregorianischen Kalenderjahres. Jede date_expression vor diesem Datum wird auf den vorhergehenden Montag gekürzt.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: kürzt timestamp_expression auf die vorhergehende Jahresgrenze gemäß der Wochennummerierung nach ISO 8601. Die ISO-Jahresgrenze ist der Montag der ersten Woche, in der der Donnerstag in das entsprechende gregorianische Kalenderjahr fällt.

Die Funktion TIMESTAMP_TRUNC unterstützt einen optionalen Parameter time_zone. Dieser Parameter gilt für die folgenden date_parts:

  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>)
  • MONTH
  • QUARTER
  • YEAR

Verwenden Sie diesen Parameter, wenn eine andere Zeitzone als die Standardzeitzone UTC als Teil der Kürzung verwendet werden soll.

Rückgabedatentyp

TIMESTAMP

Beispiele

SELECT
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'UTC') as utc,
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'America/Los_Angeles') as la;

+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25 00:00:00+00 | 2008-12-25 08:00:00+00 |
+------------------------+------------------------+

Im folgenden Beispiel hat timestamp_expression einen Zeitzonenversatz von +12. Die erste Spalte enthält timestamp_expression in UTC-Zeit. Die zweite Spalte enthält die Ausgabe von TIMESTAMP_TRUNC mit Wochen, die am Montag beginnen. Da timestamp_expression in UTC auf einen Sonntag fällt, wird es von TIMESTAMP_TRUNC bis zum vorherigen Montag gekürzt. In der dritten Spalte wird dieselbe Funktion mit dem optionalen Zeitzonen-Definitionsargument "Pacific/Auckland" angezeigt. Hier wird timestamp_expression von der Funktion unter Verwendung der neuseeländischen Sommerzeit gekürzt. Darin fällt die Angabe auf einen Montag.

SELECT
  timestamp,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY)) AS utc_truncated,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY), 'Pacific/Auckland') AS nzdt_truncated
FROM (SELECT TIMESTAMP('2017-11-06 00:00:00+12') AS timestamp);

+------------------------+------------------------+------------------------+
| timestamp              | utc_truncated          | nzdt_truncated         |
+------------------------+------------------------+------------------------+
| 2017-11-05 12:00:00+00 | 2017-10-30 07:00:00+00 | 2017-11-05 11:00:00+00 |
+------------------------+------------------------+------------------------+

Im folgenden Beispiel liegt die ursprüngliche timestamp_expression im gregorianischen Kalenderjahr 2015. TIMESTAMP_TRUNC mit dem Datumsteil ISOYEAR kürzt timestamp_expression jedoch bis zum Anfang des ISO-Jahres und nicht des gregorianischen Kalenderjahres. Der erste Donnerstag des Kalenderjahres 2015 fiel auf den 01.01.2015. Deshalb beginnt das ISO-Jahr 2015 mit dem Montag davor am 29.12.2014. Entsprechend fällt die ISO-Jahresgrenze vor timestamp_expression 2015-06-15 00:00:00+00 auf den 29.12.2014.

SELECT
  TIMESTAMP_TRUNC('2015-06-15 00:00:00+00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM TIMESTAMP '2015-06-15 00:00:00+00') AS isoyear_number;

+------------------------+----------------+
| isoyear_boundary       | isoyear_number |
+------------------------+----------------+
| 2014-12-29 00:00:00+00 | 2015           |
+------------------------+----------------+

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])

Beschreibung

Formatiert einen Zeitstempel entsprechend dem angegebenen format_string.

Unter Unterstützte Formatelemente für TIMESTAMP ist eine Liste mit Formatelementen aufgeführt, die von dieser Funktion unterstützt werden.

Rückgabedatentyp

STRING

Beispiel

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles")
  AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 07:30:00 2008 |
+--------------------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, string[, time_zone])

Beschreibung

Verwendet einen format_string und eine Stringdarstellung eines Zeitstempels, um ein TIMESTAMP-Objekt zurückzugeben.

Bei der Verwendung von PARSE_TIMESTAMP bitte Folgendes beachten:

  • Nicht spezifizierte Felder. Jedes unspezifizierte Feld wird ab 1970-01-01 00:00:00.0 initialisiert. Dieser Initialisierungswert verwendet die durch das Zeitzonenargument der Funktion angegebene Zeitzone, falls vorhanden. Wenn nicht, verwendet der Initialisierungswert die Standardzeitzone UTC. Wenn beispielsweise das Jahr nicht angegeben ist, wird standardmäßig 1970 verwendet usw.
  • Groß-/Kleinschreibung kann ignoriert werden. Bei Namen, wie Monday, February und so weiter, kann die Groß-/Kleinschreibung ignoriert werden.
  • Leerraum. Ein oder mehrere aufeinanderfolgende Leerräume im Formatstring stimmen mit null oder mehreren aufeinanderfolgenden Leeräumen im Zeitstempelstring überein. Darüber hinaus sind voran- und nachgestellte Leerräume im Zeitstempelstring immer erlaubt – auch wenn sie nicht im Formatstring vorhanden sind.
  • Formatvorrang. Wenn zwei oder mehr Formatelemente überlappende Informationen haben (%F und %Y beeinflussen z. B. beide das Jahr), überschreibt im Allgemeinen das letzte Format alle vorherigen Formate, mit einigen Ausnahmen (siehe Beschreibung von %s, %C und %y).

Unter Unterstützte Formatelemente für TIMESTAMP ist eine Liste mit Formatelementen aufgeführt, die von dieser Funktion unterstützt werden.

Rückgabedatentyp

TIMESTAMP

Beispiel

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008", "America/Los_Angeles") as parsed;

+-------------------------+
| parsed                  |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

Beschreibung

Interpretiert int64_expression als Anzahl der Sekunden seit 01.01.1970 00:00:00 UTC.

Rückgabedatentyp

TIMESTAMP

Beispiel

SELECT TIMESTAMP_SECONDS(1230219000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

Beschreibung

Interpretiert int64_expression als Anzahl der Millisekunden seit 01.01.1970 00:00:00 UTC.

Rückgabedatentyp

TIMESTAMP

Beispiel

SELECT TIMESTAMP_MILLIS(1230219000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

Beschreibung

Interpretiert int64_expression als Anzahl der Mikrosekunden seit 01.01.1970 00:00:00 UTC.

Rückgabedatentyp

TIMESTAMP

Beispiel

SELECT TIMESTAMP_MICROS(1230219000000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

Beschreibung

Gibt die Anzahl der Sekunden seit 01.01.1970 00:00:00 UTC zurück. Einheiten höherer Genauigkeit werden weggekürzt.

Rückgabedatentyp

INT64

Beispiel

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00") as seconds;

+------------+
| seconds    |
+------------+
| 1230219000 |
+------------+

UNIX_MILLIS

UNIX_MILLIS(timestamp_expression)

Beschreibung

Gibt die Anzahl der Millisekunden seit 01.01.1970 00:00:00 UTC zurück. Einheiten höherer Genauigkeit werden weggekürzt.

Rückgabedatentyp

INT64

Beispiel

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00 UTC") as millis;

+---------------+
| millis        |
+---------------+
| 1230219000000 |
+---------------+

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

Beschreibung

Gibt die Anzahl der Mikrosekunden seit 01.01.1970 00:00:00 UTC zurück. Einheiten höherer Genauigkeit werden weggekürzt.

Rückgabedatentyp

INT64

Beispiel

SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00") as micros;

+------------------+
| micros           |
+------------------+
| 1230219000000000 |
+------------------+

Unterstützte Formatelemente für TIMESTAMP

Wenn nicht anders angegeben, unterstützen TIMESTAMP-Funktionen, die Formatstrings verwenden, folgende Elemente:

Formatelement Beschreibung
%A Der vollständige Wochentagname.
%a Der abgekürzte Wochentagname.
%B Der vollständige Monatsname.
%b oder %h Der abgekürzte Monatsname.
%C Das Jahrhundert (ein Jahr geteilt durch 100 und gekürzt auf eine Ganzzahl) als Dezimalzahl (00–99).
%c Die Datums- und Uhrzeitdarstellung.
%D Das Datum im Format %m/%d/%y.
%d Der Tag des Monats als Dezimalzahl (01–31).
%e Der Tag des Monats als Dezimalzahl (1–31); den einzelnen Ziffern ist ein Leerzeichen vorangestellt.
%F Das Datum im Format %Y-%m-%d.
%G Das Jahr nach ISO 8601 mit Jahrhundert als Dezimalzahl.
%g Das Jahr nach ISO 8601 ohne Jahrhundert als Dezimalzahl (00–99).
%H Die Stunde (24-Stunden-Anzeige) als Dezimalzahl (00–23).
%I Die Stunde (12-Stunden-Anzeige) als Dezimalzahl (01–12).
%j Der Tag des Jahres als Dezimalzahl (001–366).
%k Die Stunde (24-Stunden-Anzeige) als Dezimalzahl (0–23); den einzelnen Ziffern ist ein Leerzeichen vorangestellt.
%l Die Stunde (12-Stunden-Anzeige) als Dezimalzahl (1–12); den einzelnen Ziffern ist ein Leerzeichen vorangestellt.
%M Die Minute als Dezimalzahl (00–59).
%m Der Monat als Dezimalzahl (01–12).
%n Ein Zeilenumbruch.
%P Entweder "am" oder "pm".
%p Entweder "AM" oder "PM".
%R Die Zeit im Format %H:%M.
%r Die 12-Stunden-Anzeige mit AM/PM-Notation.
%S Die Sekunde als Dezimalzahl (00–60).
%s Die Anzahl der Sekunden seit 01.01.1970 00:00:00 UTC. Überschreibt immer alle anderen Formatelemente, unabhängig davon, wo %s im String angezeigt wird. Wenn mehrere %s Elemente angezeigt werden, dann hat das letzte Element Vorrang.
%T Die Zeit im Format %H:%M:%S.
%t Ein Tab-Zeichen.
%U Die Wochennummer des Jahres (Sonntag als erster Tag der Woche) als Dezimalzahl (00–53).
%u Der Wochentag (Montag als erster Tag der Woche) als Dezimalzahl (1–7).
%V Die Wochennummer des Jahres (Montag als erster Tag der Woche) als Dezimalzahl (01–53). Wenn die Woche einschließlich 1. Januar mindestens vier Tage im neuen Jahr aufweist, dann ist es Woche 1; andernfalls ist es Woche 53 des Vorjahres und die nächste Woche ist Woche 1.
%W Die Wochennummer des Jahres (Montag als erster Tag der Woche) als Dezimalzahl (00–53).
%w Der Wochentag (Sonntag als erster Tag der Woche) als Dezimalzahl (0–6).
%X Die Zeitdarstellung im HH:MM:SS-Format.
%x Die Datumsdarstellung im MM/TT/JJ-Format.
%Y Das Jahr mit Jahrhundert als Dezimalzahl.
%y Das Jahr ohne Jahrhundert als Dezimalzahl (00–99) mit einer optionalen vorangestellten Null. Kann mit %C gemischt werden. Wenn %C nicht angegeben ist, sind die Jahre 00–68 2000er, während die Jahre 69–99 1900er sind.
%Z Der Zeitzonenname.
%z Der Versatz von der Nullmeridian-Zeitzone je nach Bedarf im Format +HHMM oder -HHMM, wobei die positiven Werte Orte östlich von Greenwich darstellen.
%% Ein einzelnes %-Zeichen.
%Ez RFC 3339-kompatible numerische Zeitzone (+HH:MM oder -HH:MM).
%E#S Sekunden mit #-Ziffern in Bruchgenauigkeit.
%E*S Sekunden mit vollständiger Bruchgenauigkeit (ein literales "*").
%E4Y Jahre mit vier Ziffern (0001... 9999). Beachten Sie, dass %Y so viele Zeichen produziert, wie nötig sind, um das Jahr komplett darzustellen.

Zeitzonendefinitionen

Durch bestimmte Datums- und Zeitstempel-Funktionen kann die Standardzeitzone durch eine andere Zeitzone ersetzt werden. Sie können eine Zeitzone bestimmen, indem Sie den UTC-Versatz der Zeitzone in folgendem Format angeben:

(+|-)H[H][:M[M]]

Beispiel:

-08:00

Sicherheitsfunktionen

BigQuery unterstützt die folgenden Sicherheitsfunktionen.

SESSION_USER

SESSION_USER()

Beschreibung

Gibt die E-Mail-Adresse des Nutzers zurück, der die Abfrage ausführt.

Rückgabedatentyp

STRING

Beispiel

SELECT SESSION_USER() as user;

+----------------------+
| user                 |
+----------------------+
| jdoe@example.com     |
+----------------------+

NET-Funktionen

NET.IP_FROM_STRING

NET.IP_FROM_STRING(addr_str)

Beschreibung

Wandelt eine IPv4- oder IPv6-Adresse vom Textformat (STRING) in das Binärformat (BYTES) in Netzwerk-Byte-Reihenfolge um.

Diese Funktion unterstützt die folgenden Formate für addr_str:

  • IPv4: Dotted-Quad-Format (vier durch Punkte voneinander getrennte Zahlen). Beispielsweise 10.1.2.3.
  • IPv6: durch Doppelpunkte getrenntes Format. Beispielsweise 1234:5678:90ab:cdef:1234:5678:90ab:cdef. Weitere Beispiele finden Sie in der Beschreibung der Adressierungsarchitektur von IP-Version 6.

Diese Funktion unterstützt keine CIDR-Notation wie etwa 10.1.2.3/32.

Wenn diese Funktion eine NULL-Eingabe empfängt, gibt sie NULL zurück. Wenn die Eingabe als ungültig erachtet wird, tritt ein OUT_OF_RANGE-Fehler auf.

Rückgabedatentyp

BYTES

Beispiel

SELECT
  addr_str, FORMAT("%T", NET.IP_FROM_STRING(addr_str)) AS ip_from_string
FROM UNNEST([
  '48.49.50.51',
  '::1',
  '3031:3233:3435:3637:3839:4041:4243:4445',
  '::ffff:192.0.2.128'
]) AS addr_str;
addr_str ip_from_string
48.49.50.51 b"0123"
::1 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01"
3031:3233:3435:3637:3839:4041:4243:4445 b"0123456789@ABCDE"
::ffff:192.0.2.128 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"

NET.SAFE_IP_FROM_STRING

NET.SAFE_IP_FROM_STRING(addr_str)

Beschreibung

Ähnlich wie NET.IP_FROM_STRING, gibt aber NULL zurück, statt bei ungültiger Eingabe einen Fehler auszugeben.

Rückgabedatentyp

BYTES

Beispiel

SELECT
  addr_str,
  FORMAT("%T", NET.SAFE_IP_FROM_STRING(addr_str)) AS safe_ip_from_string
FROM UNNEST([
  '48.49.50.51',
  '::1',
  '3031:3233:3435:3637:3839:4041:4243:4445',
  '::ffff:192.0.2.128',
  '48.49.50.51/32',
  '48.49.50',
  '::wxyz'
]) AS addr_str;
addr_str safe_ip_from_string
48.49.50.51 b"0123"
::1 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01"
3031:3233:3435:3637:3839:4041:4243:4445 b"0123456789@ABCDE"
::ffff:192.0.2.128 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"
48.49.50.51/32 NULL
48.49.50 NULL
::wxyz NULL

NET.IP_TO_STRING

NET.IP_TO_STRING(addr_bin)

Beschreibung Wandelt eine IPv4- oder IPv6-Adresse vom Binärformat (BYTES) in Netzwerk-Byte-Reihenfolge in das Text-Format (STRING) um.

Wenn die Eingabe 4 Byte ist, gibt die Funktion eine IPv4-Adresse als STRING zurück. Wenn die Eingabe 16 Byte ist, gibt sie eine IPv6-Adresse als STRING zurück.

Wenn diese Funktion eine NULL-Eingabe empfängt, gibt sie NULL zurück. Wenn die Eingabe eine andere Länge als 4 oder 16 Byte hat, tritt der Fehler OUT_OF_RANGE auf.

Rückgabedatentyp

STRING

Beispiel

SELECT FORMAT("%T", x) AS addr_bin, NET.IP_TO_STRING(x) AS ip_to_string
FROM UNNEST([
  b"0123",
  b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01",
  b"0123456789@ABCDE",
  b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"
]) AS x;
addr_bin ip_to_string
b"0123" 48.49.50.51
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01" ::1
b"0123456789@ABCDE" 3031:3233:3435:3637:3839:4041:4243:4445
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80" ::ffff:192.0.2.128

NET.IP_NET_MASK

NET.IP_NET_MASK(num_output_bytes, prefix_length)

Beschreibung

Gibt eine Netzwerkmaske zurück: eine Byte-Sequenz mit Länge gleich num_output_bytes, wobei die ersten num_output_bytes-Bits auf 1 eingestellt sind und die anderen Bits auf 0. prefix_length und prefix_length sind INT64. Diese Funktion gibt einen Fehler aus, wenn num_output_bytes nicht 4 Byte (für IPv4) oder 16 Byte (für IPv6) sind. Außerdem wird ein Fehler ausgegeben, wenn prefix_length negativ oder größer als 8 * num_output_bytes ist.

Rückgabedatentyp

BYTES

Beispiel

SELECT x, y, FORMAT("%T", NET.IP_NET_MASK(x, y)) AS ip_net_mask
FROM UNNEST([
  STRUCT(4 as x, 0 as y),
  (4, 20),
  (4, 32),
  (16, 0),
  (16, 1),
  (16, 128)
]);
x y ip_net_mask
4 0 b"\x00\x00\x00\x00"
4 20 b"\xff\xff\xf0\x00"
4 32 b"\xff\xff\xff\xff"
16 0 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00"
16 1 b"\x80\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00"
16 128 b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff"

NET.IP_TRUNC

NET.IP_TRUNC(addr_bin, prefix_length)

Beschreibung: Übernimmt addr_bin, eine IPv4- oder IPv6-Adresse im Binärformat (BYTES) in Netzwerk-Byte-Reihenfolge, und gibt eine Subnetzadresse im selben Format zurück. Das Ergebnis hat die gleiche Länge wie addr_bin, wobei die ersten prefix_length-Bits gleich denen in addr_bin und die verbleibenden Bits 0 sind.

Diese Funktion gibt einen Fehler aus, wenn LENGTH(addr_bin) nicht 4 oder 16 Byte sind oder wenn prefix_len negativ oder größer LENGTH(addr_bin) * 8 ist.

Rückgabedatentyp

BYTES

Beispiel

SELECT
  FORMAT("%T", x) as addr_bin, prefix_length,
  FORMAT("%T", NET.IP_TRUNC(x, prefix_length)) AS ip_trunc
FROM UNNEST([
  STRUCT(b"\xAA\xBB\xCC\xDD" as x, 0 as prefix_length),
  (b"\xAA\xBB\xCC\xDD", 11), (b"\xAA\xBB\xCC\xDD", 12),
  (b"\xAA\xBB\xCC\xDD", 24), (b"\xAA\xBB\xCC\xDD", 32),
  (b'0123456789@ABCDE', 80)
]);
addr_bin prefix_length ip_trunc
b"\xaa\xbb\xcc\xdd" 0 b"\x00\x00\x00\x00"
b"\xaa\xbb\xcc\xdd" 11 b"\xaa\xa0\x00\x00"
b"\xaa\xbb\xcc\xdd" 12 b"\xaa\xb0\x00\x00"
b"\xaa\xbb\xcc\xdd" 24 b"\xaa\xbb\xcc\x00"
b"\xaa\xbb\xcc\xdd" 32 b"\xaa\xbb\xcc\xdd"
b"0123456789@ABCDE" 80 b"0123456789\x00\x00\x00\x00\x00\x00"

NET.IPV4_FROM_INT64

NET.IPV4_FROM_INT64(integer_value)

Beschreibung

Wandelt eine IPv4-Adresse vom Ganzzahlformat in das Binärformat (BYTES) in Netzwerk-Byte-Reihenfolge um. Bei der Ganzzahleingabe wird ungeachtet der Host- oder Clientarchitektur das niedrigstwertige Bit der IP-Adresse im niedrigstwertigen Bit der Ganzzahl gespeichert. 1 bedeutet zum Beispiel 0.0.0.1 und 0x1FF bedeutet 0.0.1.255.

Diese Funktion prüft, ob entweder alle höchstwertigen 32 Bits 0 sind oder alle höchstwertigen 33 Bits 1 sind (zeichenerweitert von einer 32-Bit-Ganzzahl). Mit anderen Worten, die Eingabe sollte im Bereich [-0x80000000, 0xFFFFFFFF] liegen. Andernfalls gibt diese Funktion einen Fehler aus.

Diese Funktion unterstützt kein IPv6.

Rückgabedatentyp

BYTES

Beispiel

SELECT x, x_hex, FORMAT("%T", NET.IPV4_FROM_INT64(x)) AS ipv4_from_int64
FROM (
  SELECT CAST(x_hex AS INT64) x, x_hex
  FROM UNNEST(["0x0", "0xABCDEF", "0xFFFFFFFF", "-0x1", "-0x2"]) AS x_hex
);
x x_hex ipv4_from_int64
0 0x0 b"\x00\x00\x00\x00"
11259375 0xABCDEF b"\x00\xab\xcd\xef"
4294967295 0xFFFFFFFF b"\xff\xff\xff\xff"
-1 -0x1 b"\xff\xff\xff\xff"
-2 -0x2 b"\xff\xff\xff\xfe"

NET.IPV4_TO_INT64

NET.IPV4_TO_INT64(addr_bin)

Beschreibung

Wandelt eine IPv4-Adresse vom Binärformat (BYTES) in Netzwerk-Byte-Reihenfolge in das Ganzzahlformat um. Bei der Ganzzahlausgabe wird ungeachtet der Host- oder Client-Architektur das niedrigstwertige Bit der IP-Adresse in das niedrigstwertige Bit der Ganzzahl gespeichert. 1, zum Beispiel, bedeutet 0.0.0.1 und 0x1FF bedeutet 0.0.1.255. Die Ausgabe liegt im Bereich [0, 0xFFFFFFFF].

Wenn die Eingabelänge nicht 4 Byte ist, gibt diese Funktion einen Fehler aus.

Diese Funktion unterstützt kein IPv6.

Rückgabedatentyp

INT64

Beispiel

SELECT
  FORMAT("%T", x) AS addr_bin,
  FORMAT("0x%X", NET.IPV4_TO_INT64(x)) AS ipv4_to_int64
FROM
UNNEST([b"\x00\x00\x00\x00", b"\x00\xab\xcd\xef", b"\xff\xff\xff\xff"]) AS x;
addr_bin ipv4_to_int64
b"\x00\x00\x00\x00" 0x0
b"\x00\xab\xcd\xef" 0xABCDEF
b"\xff\xff\xff\xff" 0xFFFFFFFF

NET.HOST

NET.HOST(url)

Beschreibung

Übernimmt eine URL als STRING und gibt den Host als STRING zurück. Für optimale Ergebnisse sollten URL-Werte dem durch RFC 3986 definierten Format entsprechen. Wenn der URL-Wert nicht mit der Formatierung von RFC 3986 übereinstimmt, versucht diese Funktion trotzdem, die Eingabe zu parsen und ein relevantes Ergebnis zurückzugeben. Wenn die Funktion die Eingabe nicht parsen kann, gibt sie NULL zurück.

Hinweis: Die Funktion führt keine Normalisierung durch.

Rückgabedatentyp

STRING

Beispiel

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Google.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
Eingabe Beschreibung Host Suffix Domain
"" Ungültige Eingabe NULL NULL NULL
"http://abc.xyz" Standard-URL "abc.xyz" "xyz" "abc.xyz"
"//user:password@a.b:80/path?query" Standard-URL mit relativem Schema, Port, Pfad und relativer Abfrage, aber kein öffentliches Suffix "a.b" NULL NULL
"https://[::1]:80" Standard-URL mit IPv6-Host "[::1]" NULL NULL
"http://例子.卷筒纸.中国" Standard-URL mit internationalisiertem Domainnamen "例子.卷筒纸.中国" "中国" "卷筒纸.中国"
"    www.Google.Co.UK    " Nicht-Standard-URL mit Leerzeichen, Großbuchstaben und ohne Schema "www.Google.Co.UK" "Co.UK" "Google.Co.UK"
"mailto:?to=&subject=&body=" URI statt URL – nicht unterstützt "mailto" NULL NULL

NET.PUBLIC_SUFFIX

NET.PUBLIC_SUFFIX(url)

Beschreibung

Übernimmt eine URL als STRING und gibt das öffentliche Suffix (z. B. com, org oder net) als STRING zurück. Ein öffentliches Suffix ist eine ICANN-Domain, die bei publicsuffix.org registriert ist. Für optimale Ergebnisse sollten URL-Werte dem durch RFC 3986 definierten Format entsprechen. Wenn der URL-Wert nicht mit der Formatierung von RFC 3986 übereinstimmt, versucht diese Funktion trotzdem, die Eingabe zu parsen und ein relevantes Ergebnis zurückzugeben.

Diese Funktion gibt NULL zurück, wenn eine der folgenden Aussagen zutrifft:

  • Der Host kann nicht aus der Eingabe geparst werden.
  • Der geparste Host enthält in der Mitte benachbarte Punkte (nicht voran- oder nachgestellt).
  • Der geparste Host enthält kein öffentliches Suffix.

Bevor das öffentliche Suffix abgerufen wird, normalisiert diese Funktion den Host vorübergehend. Hierzu werden englische Großbuchstaben in Kleinbuchstaben umgewandelt und alle Nicht-ASCII-Zeichen mit Punycode codiert. Die Funktion gibt dann das öffentliche Suffix als Teil des ursprünglichen Hosts anstelle des normalisierten Hosts zurück.

Hinweis: Die Funktion führt keine Unicode-Normalisierung durch.

Hinweis: Die öffentlichen Suffixdaten bei publicsuffix.org enthalten auch private Domains. Diese Funktion ignoriert die privaten Domains.

Hinweis: Die öffentlichen Suffix-Daten können sich im Laufe der Zeit ändern. Folglich kann die Eingabe, die derzeit ein NULL-Ergebnis erzeugt, in Zukunft einen Nicht-NULL-Wert erzeugen.

Rückgabedatentyp

STRING

Beispiel

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Google.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
Eingabe Beschreibung Host Suffix Domain
"" Ungültige Eingabe NULL NULL NULL
"http://abc.xyz" Standard-URL "abc.xyz" "xyz" "abc.xyz"
"//user:password@a.b:80/path?query" Standard-URL mit relativem Schema, Port, Pfad und relativer Abfrage, aber kein öffentliches Suffix "a.b" NULL NULL
"https://[::1]:80" Standard-URL mit IPv6-Host "[::1]" NULL NULL
"http://例子.卷筒纸.中国" Standard-URL mit internationalisiertem Domainnamen "例子.卷筒纸.中国" "中国" "卷筒纸.中国"
"    www.Google.Co.UK    " Nicht-Standard-URL mit Leerzeichen, Großbuchstaben und ohne Schema "www.Google.Co.UK" "Co.UK" "Google.Co.UK"
"mailto:?to=&subject=&body=" URI statt URL – nicht unterstützt "mailto" NULL NULL

NET.REG_DOMAIN

NET.REG_DOMAIN(url)

Beschreibung

Übernimmt eine URL als STRING und gibt die registrierte oder registrierbare Domain (das öffentliche Suffix plus ein vorangestelltes Label) als STRING zurück. Für optimale Ergebnisse sollten URL-Werte dem durch RFC 3986 definierten Format entsprechen. Wenn der URL-Wert nicht mit der Formatierung von RFC 3986 übereinstimmt, versucht diese Funktion trotzdem, die Eingabe zu parsen und ein relevantes Ergebnis zurückzugeben.

Diese Funktion gibt NULL zurück, wenn eine der folgenden Aussagen zutrifft:

  • Der Host kann nicht aus der Eingabe geparst werden.
  • Der geparste Host enthält benachbarte Punkte in der Mitte (nicht voran- oder nachgestellt);
  • Der geparste Host enthält kein öffentliches Suffix;
  • Der geparste Host enthält nur ein öffentliches Suffix ohne vorangestelltes Label.

Bevor das öffentliche Suffix abgerufen wird, normalisiert diese Funktion den Host vorübergehend, indem englische Großbuchstaben in Kleinbuchstaben umwandelt und alle Nicht-ASCII-Zeichen mit Punycode codiert werden. Die Funktion gibt dann die registrierte oder registrierbare Domain als Teil des ursprünglichen Hosts anstelle des normalisierten Hosts zurück.

Hinweis: Die Funktion führt keine Unicode-Normalisierung durch.

Hinweis: Die öffentlichen Suffixdaten bei publicsuffix.org enthalten auch private Domains. Diese Funktion behandelt eine private Domain nicht als öffentliches Suffix. Wenn zum Beispiel "us.com" eine private Domain in den öffentlichen Suffixdaten ist, gibt NET.REG_DOMAIN ("foo.us.com") "us.com" (das öffentliche Suffix "com" plus das vorangestellte Label "us") statt "foo.us.com" zurück (die private Domain "us.com" plus das vorangestellte Label "foo").

Hinweis: Die öffentlichen Suffixdaten können sich im Laufe der Zeit ändern. Folglich kann die Eingabe, die derzeit ein NULL-Ergebnis erzeugt, in Zukunft einen Nicht-NULL-Wert erzeugen.

Rückgabedatentyp

STRING

Beispiel

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Google.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
Eingabe Beschreibung Host Suffix Domain
"" Ungültige Eingabe NULL NULL NULL
"http://abc.xyz" Standard-URL "abc.xyz" "xyz" "abc.xyz"
"//user:password@a.b:80/path?query" Standard-URL mit relativem Schema, Port, Pfad und relativer Abfrage, aber kein öffentliches Suffix "a.b" NULL NULL
"https://[::1]:80" Standard-URL mit IPv6-Host "[::1]" NULL NULL
"http://例子.卷筒纸.中国" Standard-URL mit internationalisiertem Domainnamen "例子.卷筒纸.中国" "中国" "卷筒纸.中国"
"    www.Google.Co.UK    " Nicht-Standard-URL mit Leerzeichen, Großbuchstaben und ohne Schema "www.Google.Co.UK" "Co.UK" "Google.Co.UK"
"mailto:?to=&subject=&body=" URI statt URL – nicht unterstützt "mailto" NULL NULL

Operatoren

Operatoren werden durch Sonderzeichen oder Schlüsselwörter dargestellt; sie verwenden keine Funktionsaufrufsyntax. Ein Operator manipuliert eine beliebige Anzahl von Dateneingaben, auch Operanden genannt, und gibt ein Ergebnis zurück.

Allgemeine Konventionen:

  • Wenn nicht anders angegeben, geben alle Operatoren NULL zurück, wenn einer der Operanden NULL ist.
  • Alle Operatoren geben einen Fehler aus, wenn das Berechnungsergebnis überläuft.
  • Bei allen Gleitkommaoperationen dürfen +/-inf und NaN nur zurückgegeben werden, wenn einer der Operanden +/-inf oder NaN ist. In anderen Fällen wird ein Fehler zurückgegeben.

In der folgenden Tabelle sind alle BigQuery-Operatoren von der höchsten bis zur niedrigsten Priorität aufgeführt, d. h. in der Reihenfolge, in der sie innerhalb einer Anweisung ausgewertet werden.

Rangfolge Operator Datentypen der Eingabe Name Operator-Arität
1 . STRUCT
Mitgliedsfeldzugriffsoperator Binär
  [ ] ARRAY Arrayposition. Muss mit OFFSET oder ORDINAL verwendet werden ­–siehe ARRAY-Funktionen. Binär
2 - Alle numerischen Typen Unäres Minus Unär
  ~ Ganzzahl oder BYTES Bitweises NOT Unär
3 * Alle numerischen Typen Multiplikation Binär
  / Alle numerischen Typen Division Binär
4 + Alle numerischen Typen Addition Binär
  - Alle numerischen Typen Subtraktion Binär
5 << Ganzzahl oder BYTES Bitweise Linksverschiebung Binär
  >> Ganzzahl oder BYTES Bitweise Rechtsverschiebung Binär
6 und Ganzzahl oder BYTES Bitweises AND Binär
7 ^ Ganzzahl oder BYTES Bitweises XOR Binär
8 | Ganzzahl oder BYTES Bitweises OR Binär
9 (Vergleichsoperatoren) = Jeder vergleichbare Typ. Eine vollständige Liste erhalten Sie unter Datentypen Gleich Binär
  < Jeder vergleichbare Typ. Eine vollständige Liste erhalten Sie unter Datentypen Kleiner als Binär
  > Jeder vergleichbare Typ. Eine vollständige Liste erhalten Sie unter Datentypen Größer als Binär
  <= Jeder vergleichbare Typ. Eine vollständige Liste erhalten Sie unter Datentypen Kleiner als oder gleich Binär
  >= Jeder vergleichbare Typ. Eine vollständige Liste erhalten Sie unter Datentypen Größer als oder gleich Binär
  !=, <> Jeder vergleichbare Typ. Eine vollständige Liste erhalten Sie unter Datentypen Ungleich Binär
  [NOT] LIKE STRING und Byte Wert entspricht [nicht] dem angegebenen Muster Binär
  [NOT] BETWEEN Alle vergleichbaren Typen. Unter Datentypen ist eine Liste aufgeführt. Wert ist [nicht] innerhalb des angegebenen Bereichs Binär
  [NOT] IN Alle vergleichbaren Typen. Unter Datentypen ist eine Liste aufgeführt. Wert ist [nicht] in der Liste der angegebenen Werte Binär
  IS [NOT] NULL Alle Wert ist [nicht] NULL. Unär
  IS [NOT] TRUE BOOL Wert ist [nicht] TRUE. Unär
  IS [NOT] FALSE BOOL Wert ist [nicht] FALSE. Unär
10 NOT BOOL Logisches NOT Unär
11 AND BOOL Logisches AND Binär
12 OR BOOL Logisches OR Binär

Operatoren mit der gleichen Präzedenz sind links-assoziativ. Das heißt, dass diese Operatoren von links nach rechts gruppiert werden. Zum Beispiel wird der Ausdruck:

x AND y AND z

interpretiert als:

( ( x AND y ) AND z )

Der Ausdruck:

x * y / z

wird interpretiert als:

( ( x * y ) / z )

Alle Vergleichsoperatoren haben die gleiche Priorität und werden links-assoziativ gruppiert. Vergleichsoperatoren sind jedoch nicht assoziativ. Infolgedessen ist zur Verbesserung der Lesbarkeit die Verwendung von Klammern empfehlenswert. Außerdem ist so sichergestellt, dass die Ausdrücke wie gewünscht aufgelöst werden. Beispiel:

(x < y) IS FALSE

ist gegenüber Folgendem vorzuziehen:

x < y IS FALSE

Operatoren für den Elementzugriff

Operator Syntax Datentypen der Eingabe Datentyp des Ergebnisses Beschreibung
. expression.fieldname1... STRUCT
Typ T in fieldname1 gespeichert Dot-Operator. Kann für den Zugriff auf verschachtelte Felder verwendet werden, z. B. expression.fieldname1.fieldname2...
[ ] array_expression [position_keyword (int_expression ) ] Siehe ARRAY-Funktionen. Typ T in ARRAY gespeichert Position_keyword ist entweder OFFSET oder ORDINAL. Siehe ARRAY-Funktionen für die beiden Funktionen, die diesen Operator verwenden.

Arithmetischer Operator

Alle arithmetischen Operatoren akzeptieren die Eingabe des numerischen Typs T und der Ergebnistyp weist den Typ T auf, sofern in der nachfolgenden Beschreibung nichts anderes angegeben ist:

Name Syntax
Addition X + Y
Subtraktion X - Y
Multiplikation X * Y
Division X / Y
Unäres Minus - X

HINWEIS: Division durch Nulloperationen gibt einen Fehler zurück. Ziehen Sie die Funktionen IEEE_DIVIDE oder SAFE_DIVIDE in Betracht, um ein anderes Ergebnis zurückzugeben.

Ergebnistypen für Addition und Multiplikation:

 INT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

Ergebnistypen für Subtraktion:

 INT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

Ergebnistypen für Division:

 INT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

Ergebnistypen für unäres Minus:

Datentyp der Eingabe Datentyp des Ergebnisses
INT64 INT64
NUMERIC NUMERIC
FLOAT64 FLOAT64

Bitweiser Operator

Alle bitweisen Operatoren geben den gleichen Typ und die gleiche Länge wie der erste Operand zurück.

Name Syntax Datentyp der Eingabe Beschreibung
Bitweises NOT ~ X Ganzzahl oder BYTES Führt eine logische Negation auf jedem Bit durch und bildet das Einerkomplement des gegebenen Binärwerts.
Bitweises OR X | Y X: Ganzzahl oder BYTES
Y: Gleicher Typ wie X
Nimmt zwei Bitmuster gleicher Länge und führt die logische inklusive OR-Operation bei jedem Paar der entsprechenden Bits durch. Dieser Operator gibt einen Fehler aus, wenn X- und Y-BYTES unterschiedlicher Länge sind.
Bitweises XOR X ^ Y X: Ganzzahl oder BYTES
Y: Gleicher Typ wie X
Nimmt zwei Bitmuster gleicher Länge und führt die logische exklusive OR-Operation bei jedem Paar der entsprechenden Bits durch. Dieser Operator gibt einen Fehler aus, wenn X- und Y-BYTES unterschiedlicher Länge sind.
Bitweises AND X & Y X: Ganzzahl oder BYTES
Y: Gleicher Typ wie X
Nimmt zwei Bitmuster gleicher Länge und führt die logische AND-Operation bei jedem Paar der entsprechenden Bits durch. Dieser Operator gibt einen Fehler aus, wenn X- und Y-BYTES unterschiedlicher Länge sind.
Linksverschiebung X << Y X: Ganzzahl oder BYTES
Y: INT64
Verschiebt den ersten Operanden X nach links. Dieser Operator gibt 0 oder eine Bytesequenz von b'\x00' zurück, wenn der zweite Operand Y größer oder gleich der Bitlänge des ersten Operanden X ist (z. B. 64, wenn X den Typ INT64 aufweist). Dieser Operator gibt einen Fehler aus, wenn Y negativ ist.
Rechtsverschiebung X >> Y X: Ganzzahl oder BYTES
Y: INT64
Verschiebt den ersten Operanden X nach rechts. Dieser Operator macht keine Vorzeichenbit-Erweiterung bei einem vorzeichenbehafteten Typ (d. h. er befüllt freie Bits auf der linken Seite mit 0). Dieser Operator gibt 0 oder eine Bytesequenz von b'\x00' zurück, wenn der zweite Operand Y größer oder gleich der Bitlänge des ersten Operanden X ist (z. B. 64, wenn X den Typ INT64 aufweist). Dieser Operator gibt einen Fehler aus, wenn Y negativ ist.

Logische Operatoren

Alle logischen Operatoren erlauben nur die BOOL-Eingabe.

Name Syntax Beschreibung
Logisches NOT NOT X Gibt FALSE zurück, wenn die Eingabe TRUE ist. Gibt TRUE zurück, wenn die Eingabe FALSE ist. Gibt ansonsten NULL zurück.
Logisches AND X AND Y Gibt FALSE zurück, wenn mindestens eine Eingabe FALSE ist. Gibt TRUE zurück, wenn sowohl X als auch Y TRUE ist. Gibt ansonsten NULL zurück.
Logisches OR X OR Y Gibt FALSE zurück, wenn X und Y beide FALSE sind. Gibt TRUE zurück, wenn mindestens eine Eingabe TRUE ist. Andernfalls wird NULL zurückgegeben.

Vergleichsoperatoren

Vergleiche geben immer BOOL zurück. Sie erfordern im Allgemeinen, dass beide Operanden vom selben Typ sind. Wenn die Operanden einen unterschiedlichen Typ haben und BigQuery die Werte dieser Typen ohne Genauigkeitsverlust in einen allgemeinen Typ umwandeln kann, erzwingt BigQuery in der Regel für den Vergleich eine Umwandlung in diesen allgemeinen Typ. Im Allgemeinen erzwingt BigQuery die Umwandlung von Literalen in Nicht-Literale, sofern vorhanden. Vergleichbare Datentypen sind in Datentypen definiert.

STRUCTs unterstützen nur 4 Vergleichsoperatoren: gleich (=), nicht gleich (!= und <>) sowie IN.

Beim Vergleich dieser Datentypen gelten folgende Regeln:

  • FLOAT64: Alle Vergleiche mit NaN geben FALSE zurück, außer != und <>, die TRUE zurückgeben.
  • BOOL: FALSE ist kleiner als TRUE.
  • STRING: Strings werden von Codepunkt zu Codepunkt miteinander verglichen, was bedeutet, dass der Vergleich kanonisch äquivalenter Strings nur dann garantiert gleich ist, wenn sie zuerst normalisiert wurden.
  • NULL: Hier gilt folgende Konvention: Jede Operation mit einer NULL-Eingabe gibt NULL zurück.
Name Syntax Beschreibung
Kleiner als X < Y Gibt TRUE zurück, wenn X kleiner als Y ist.
Kleiner als oder gleich X <= Y Gibt TRUE zurück, wenn X kleiner oder gleich Y ist.
Größer als X > Y Gibt TRUE zurück, wenn X größer als Y ist.
Größer als oder gleich X >= Y Gibt TRUE zurück, wenn X größer oder gleich Y ist.
Gleich X = Y Gibt TRUE zurück, wenn X gleich Y ist.
Ungleich X != Y
X <> Y
Gibt TRUE zurück, wenn X ungleich Y ist.
BETWEEN X [NOT] BETWEEN Y AND Z Gibt TRUE zurück, wenn X in dem angegebenen Bereich [nicht] vorhanden ist. Das Ergebnis von "X BETWEEN Y AND Z" ist äquivalent mit "Y <= X AND X <= Z", aber X wird nur einmal in ersterem ausgewertet.
LIKE X [NOT] LIKE Y Überprüft, ob der STRING im ersten Operanden X mit einem vom zweiten Operanden Y angegebenen Muster übereinstimmt. Ausdrücke können diese Zeichen enthalten:
  • Ein Prozentzeichen "%" entspricht einer beliebigen Anzahl von Zeichen oder Byte
  • Ein Unterstrich "_" entspricht einem einzelnen Zeichen oder Byte
  • Sie können "\", "_" oder "%" mit zwei Backslashs maskieren. Beispielsweise "\\%". Wenn Sie Rohstrings verwenden, ist nur ein einziger Backslash erforderlich. Beispielsweise r"\%".
IN Mehrere – siehe unten Gibt FALSE zurück, wenn der rechte Operand leer ist. Gibt NULL zurück, wenn der linke Operand NULL ist. Gibt TRUE oder NULL, niemals FALSE zurück, wenn der rechte Operand NULL enthält. Argumente auf beiden Seiten von IN sind allgemeine Ausdrücke. Keiner der Operanden muss ein Literal zu sein, obwohl die Verwendung eines Literals auf der rechten Seite am häufigsten ist. X wird nur einmal ausgewertet.

Beim Testen von Werten, die einen STRUCT-Datentyp für Gleichheit haben, ist es möglich, dass ein oder mehrere Felder NULL sind. In solchen Fällen:

  • Wenn alle Nicht-NULL-Feldwerte gleich sind, gibt der Vergleich NULL zurück.
  • Wenn alle Nicht-NULL-Feldwerte ungleich sind, gibt der Vergleich FALSE zurück.

Die folgende Tabelle zeigt, wie STRUCT-Datentypen verglichen werden, wenn sie Felder aufweisen, die mit NULL bewertet werden.

Struct1 Struct2 Struct1 = Struct2
STRUCT(1, NULL) STRUCT(1, NULL) NULL
STRUCT(1, NULL) STRUCT(2, NULL) FALSE
STRUCT(1,2) STRUCT(1, NULL) NULL

IN-Operator

Der IN-Operator unterstützt die folgenden Syntaxen:

x [NOT] IN (y, z, ... ) # Requires at least one element
x [NOT] IN (<subquery>)
x [NOT] IN UNNEST(<array expression>) # analysis error if the expression
                                      # does not return an ARRAY type.

Argumente auf beiden Seiten des IN-Operators sind allgemeine Ausdrücke. Es ist üblich, Literale auf der rechten Seite des Ausdrucks zu verwenden. Dies ist jedoch nicht erforderlich.

Die Semantik von:

x IN (y, z, ...)

ist gleichbedeutend mit:

(x = y) OR (x = z) OR ...

und die Unterabfrage und Arrayformen sind in ähnlicher Weise definiert.

x NOT IN ...

entspricht:

NOT(x IN ...)

Die UNNEST-Form behandelt einen Arrayscan wie UNNEST in der Klausel FROM:

x [NOT] IN UNNEST(<array expression>)

Diese Form wird häufig mit ARRAY-Parametern verwendet. Beispiel:

x IN UNNEST(@array_parameter)

Hinweis: Ein NULL-ARRAY wird genauso behandelt wie ein leeres ARRAY.

Weitere Informationen zur Verwendung dieser Syntax finden Sie im Thema Arrays.

Bei Verwendung des IN-Operators gilt folgende Semantik:

  • IN mit einem leeren Ausdruck auf der rechten Seiten ist immer FALSE.
  • IN mit einem NULL-Ausdruck auf der linken Seite und einem nicht-leeren Ausdruck auf der rechten Seite ist immer NULL.
  • IN mit einer NULL in der IN-Liste kann nur TRUE oder NULL, niemals FALSE zurückgeben.
  • NULL IN (NULL) gibt NULL zurück.
  • IN UNNEST(<NULL array>) gibt FALSE zurück (nicht NULL).

IN kann unter Verwendung der STRUCT-Konstruktorsyntax mit mehrteiligen Schlüsseln verwendet werden. Beispiel:

(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )

Weitere Informationen zu dieser Syntax enthält der Abschnitt "Struct-Typ" unter "Datentypen".

IS-Operatoren

IS-Operatoren geben TRUE oder FALSE für die Bedingung zurück, die sie testen. Sie geben nie NULL zurück, auch nicht bei NULL-Eingaben, im Unterschied zu den unter Mathematische Funktionen definierten Funktionen IS_INF und IS_NAN. Wenn NOT vorhanden ist, wird der Ausgabewert BOOL invertiert.

Funktions-Syntax Datentyp der Eingabe Datentyp des Ergebnisses Beschreibung

X IS [NOT] NULL
Jeder Werttyp BOOL Gibt TRUE zurück, wenn der Operand X als NULL ausgewertet wird. Ansonsten wird FALSE zurückgegeben.

X IS [NOT] TRUE
BOOL BOOL Gibt TRUE zurück, wenn der Operand BOOL als TRUE ausgewertet wird. Ansonsten wird FALSE zurückgegeben.

X IS [NOT] FALSE
BOOL BOOL Gibt TRUE zurück, wenn der Operand BOOL als FALSE ausgewertet wird. Ansonsten wird FALSE zurückgegeben.

Bedingte Ausdrücke

Bedingte Ausdrücke erzwingen Einschränkungen für die Auswertungsreihenfolge ihrer Eingaben. Im Wesentlichen werden sie von links nach rechts nach dem Kurzschlussprinzip ausgewertet und zwar nur der gewählte Ausgabewert. Im Gegensatz dazu werden alle Eingaben in reguläre Funktionen vor Aufruf der Funktion ausgewertet. Die Kurzschlussauswertung in bedingten Ausdrücken kann für Fehlerbehandlung oder Leistungsabstimmung genutzt werden.

Syntax Datentypen der Eingabe Datentyp des Ergebnisses Beschreibung

CASE expr
  WHEN value THEN result
  [WHEN ...]
  [ELSE else_result]
  END
expr und value: Beliebiger Typ result und else_result: Supertyp der Eingabetypen. Vergleicht expr mit dem Wert jeder aufeinanderfolgenden WHEN-Klausel und gibt das erste Ergebnis zurück, wenn dieser Vergleich TRUE zurückgibt. Die verbleibenden WHEN-Klauseln und else_result werden nicht ausgewertet. Wenn der Vergleich expr = value für alle WHEN-Klauseln FALSE oder NULL zurückgibt, wird else_result zurückgegeben, sofern vorhanden. Falls nicht vorhanden, wird NULL zurückgegeben. expr- und value-Ausdrücke müssen implizit zu einem gemeinsamen Supertypen gezwungen werden können. Gleichheitsvergleiche werden auf erzwungene Werte angewendet. result- und else_result-Ausdrücke müssen zu einem gemeinsamen Supertypen gezwungen werden können.

CASE
  WHEN cond1 THEN result
  [WHEN cond2...]
  [ELSE else_result]
  END
cond: BOOL result und else_result: Supertyp der Eingabetypen. Wertet die Bedingung cond jeder aufeinanderfolgenden WHEN-Klausel aus und gibt das erste Ergebnis zurück, wenn die Bedingung wahr ist. Alle verbleibenden WHEN-Klauseln und else_result werden nicht ausgewertet. Wenn alle Bedingungen FALSE oder NULL sind, wird else_result zurückgegeben, sofern vorhanden. Falls nicht vorhanden, wird NULL zurückgegeben. result- und else_result-Ausdrücke müssen implizit zu einem gemeinsamen Supertypen gezwungen werden können.
COALESCE(expr1, ..., exprN) Alle Typen Supertyp der Eingabetypen Gibt den Wert des ersten Nicht-Null-Ausdrucks zurück. Die übrigen Ausdrücke werden nicht ausgewertet. Alle Eingabeausdrücke müssen implizit zu einem gemeinsamen Supertypen gezwungen werden können.
IF(cond, true_result, else_result) cond: BOOL true_result und else_result: Beliebiger Typ Wenn cond wahr ist, wird true_result zurückgegeben, ansonsten wird else_result zurückgegeben. else_result wird nicht ausgewertet, wenn cond wahr ist. true_result wird nicht ausgewertet, wenn cond falsch oder NULL ist. true_result und else_result müssen zu einem gemeinsamen Supertypen gezwungen werden können.
IFNULL(expr, null_result) Alle Typen Beliebiger Typ oder Supertyp von Eingabetypen. Wenn expr gleich NULL ist, wird null_result zurückgegeben. Ansonsten wird expr zurückgegeben. Wenn expr nicht NULL ist, wird null_result nicht ausgewertet. expr und null_result müssen implizit zu einem gemeinsamen Supertypen gezwungen werden können. Synonym für COALESCE(expr, null_result).
NULLIF (Ausdruck, expression_to_match) Beliebiger Typ T oder Subtyp von T Beliebiger Typ T oder Subtyp von T Gibt NULL zurück, wenn expression = expression_to_match wahr ist, ansonsten wird expression zurückgegeben. expression und expression_to_match müssen implizit zu einem gemeinsamen Typ gezwungen werden können; ein Gleichheitsvergleich wird an erzwungenen Werten durchgeführt.

Ausdruck-Unterabfragen

Es gibt vier Typen von Ausdrucks-Unterabfragen, d. h. Unterabfragen, die als Ausdrücke verwendet werden. Ausdruck-Unterabfragen geben im Gegensatz zu einer Spalte oder Tabelle NULL oder einen einzelnen Wert zurück und müssen von Klammern umgeben sein. Eine ausführlichere Beschreibung von Unterabfragen ist unter Unterabfragen aufgeführt.

Unterabfragentyp Datentyp des Ergebnisses Beschreibung
Skalar Beliebiger Typ T Eine Unterabfrage in Klammern innerhalb eines Ausdrucks (z. B. in der SELECT-Liste oder WHERE-Klausel) wird als skalare Unterabfrage interpretiert. Die SELECT-Liste in einer skalaren Unterabfrage muss genau ein Feld haben. Wenn die Unterabfrage genau eine Zeile zurückgibt, ist dieser einzelne Wert das skalare Unterabfrageergebnis. Wenn die Unterabfrage Nullzeilen zurückgibt, ist der skalare Unterabfragewert NULL. Wenn die Unterabfrage mehr als eine Zeile zurückgibt, schlägt die Abfrage aufgrund eines Laufzeitfehlers fehl. Wenn die Unterabfrage mit SELECT AS STRUCT geschrieben wird, kann sie mehrere Spalten enthalten, und der zurückgegebene Wert ist der konstruierte STRUCT-Wert. Die Auswahl mehrerer Spalten ohne SELECT AS ist ein Fehler.
ARRAY ARRAY Kann SELECT AS STRUCT verwenden, um Arrays von Structs zu erstellen, und umgekehrt. Die Auswahl mehrerer Spalten ohne SELECT AS ist ein Fehler. Gibt ein leeres ARRAY zurück, wenn die Unterabfrage Nullzeilen zurückgibt. Gibt nie ein NULL-ARRAY zurück.
IN BOOL Tritt in einem Ausdruck nach dem IN-Operator auf. Die Unterabfrage muss eine einzelne Spalte erzeugen, deren Typ mit dem Ausdruck auf der linken Seite des IN-Operators gleich kompatibel ist. Gibt FALSE zurück, wenn die Unterabfrage Nullzeilen zurückgibt. x IN () entspricht x IN (value, value, ...) Die vollständige Semantik finden Sie unter IN beim Operator IN.
EXISTS BOOL Gibt TRUE zurück, wenn die Unterabfrage eine oder mehrere Zeilen erzeugt hat. Gibt FALSE zurück, wenn die Unterabfrage Nullzeilen erzeugt. Gibt niemals NULL zurück. Im Gegensatz zu allen anderen Ausdruck-Unterabfragen gibt es keine Regeln über die Spaltenliste. Es können beliebig viele Spalten ausgewählt werden. Das Abfrageergebnis wird dabei nicht beeinflusst.

Beispiele

Die folgenden Beispiele für Ausdruck-Unterabfragen gehen davon aus, dass t.int_array den Typ ARRAY<INT64> aufweist.

Typ Unterabfrage Datentyp des Ergebnisses Hinweise
Skalar (SELECT COUNT(*) FROM t.int_array) INT64  
(SELECT DISTINCT i FROM t.int_array i) INT64, möglicher Laufzeitfehler  
(SELECT i FROM t.int_array i WHERE i=5) INT64, möglicher Laufzeitfehler  
(SELECT ARRAY_AGG(i) FROM t.int_array i) ARRAY Verwendet die ARRAY_AGG-Aggregationsfunktion, um ein ARRAY zurückzugeben.
(SELECT 'xxx' a) STRING  
(SELECT 'xxx' a, 123 b) Fehler Gibt einen Fehler zurück, da es mehr als eine Spalte gibt.
(SELECT AS STRUCT 'xxx' a, 123 b) STRUCT  
(SELECT AS STRUCT 'xxx' a) STRUCT  
ARRAY ARRAY(SELECT COUNT(*) FROM t.int_array) ARRAY der Größe 1  
ARRAY(SELECT x FROM t) ARRAY  
ARRAY(SELECT 5 a, COUNT(*) b FROM t.int_array) Fehler Gibt einen Fehler zurück, da es mehr als eine Spalte gibt.
ARRAY(SELECT AS STRUCT 5 a, COUNT(*) b FROM t.int_array) ARRAY  
ARRAY(SELECT AS STRUCT i FROM t.int_array i) ARRAY Erstellt ein ARRAY von STRUCTs mit einem Feld.
ARRAY(SELECT AS STRUCT 1 x, 2, 3 x) ARRAY Gibt einen ARRAY von STRUCTs mit anonymen oder doppelten Feldern zurück.
ARRAY(SELECT AS TypeName SUM(x) a, SUM(y) b, SUM(z) c from t) array<TypeName> Auswahl eines benannten Typs. Nimmt an, dass TypeName ein STRUCT-Typ mit den Feldern a, b, c ist.
STRUCT (SELECT AS STRUCT 1 x, 2, 3 x) STRUCT Konstruiert einen STRUCT mit anonymen oder doppelten Feldern.
EXISTS EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
NOT EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
IN x IN (SELECT y FROM table WHERE z) BOOL  
x NOT IN (SELECT y FROM table WHERE z) BOOL  

Fehlerbehebungsfunktionen

BigQuery unterstützt die folgenden Fehlerbehebungsfunktionen.

ERROR

ERROR(error_message)

Beschreibung

Gibt einen Fehler zurück. Das Argument error_message ist ein STRING.

BigQuery behandelt ERROR auf dieselbe Weise wie alle Ausdrücke, die zu einem Fehler führen können: Es gibt keine spezielle Garantie für die Auswertungsreihenfolge.

Rückgabedatentyp

BigQuery leitet den Rückgabetyp im Kontext ab.

Beispiele

Im folgenden Beispiel wird durch die Abfrage eine Fehlermeldung zurückgegeben, wenn der Wert der Zeile keinem von zwei definierten Werten entspricht.

SELECT
  CASE
    WHEN value = 'foo' THEN 'Value is foo.'
    WHEN value = 'bar' THEN 'Value is bar.'
    ELSE ERROR(concat('Found unexpected value: ', value))
  END AS new_value
FROM (
  SELECT 'foo' AS value UNION ALL
  SELECT 'bar' AS value UNION ALL
  SELECT 'baz' AS value);

Found unexpected value: baz

Im folgenden Beispiel kann BigQuery die ERROR-Funktion vor oder nach der Bedingung x > 0 auswerten, weil BigQuery im Allgemeinen keine Reihenfolge zwischen WHERE-Klauselbedingungen garantiert und es keine speziellen Garantien für die ERROR-Funktion gibt.

SELECT *
FROM (SELECT -1 AS x)
WHERE x > 0 AND ERROR('Example error');

Im nächsten Beispiel wertet die WHERE-Klausel eine IF-Bedingung aus, die dafür sorgt, dass BigQuery die ERROR-Funktion nur dann auswertet, wenn die Bedingung nicht erfüllt ist.

SELECT *
FROM (SELECT -1 AS x)
WHERE IF(x > 0, true, ERROR(FORMAT('Error: x must be positive but is %t', x)));'

Error: x must be positive but is -1
Hat Ihnen diese Seite weitergeholfen? Teilen Sie uns Ihr Feedback mit:

Feedback geben zu...