Ausdrücke, Funktionen und Operatoren in Google Standard-SQL

Auf dieser Seite werden Ausdrücke wie Funktionen und Operatoren von Google-Standard-SQL erläutert.

Funktionsaufrufregeln in Google Standard SQL

Folgende Regeln gelten für alle Funktionen, es sei denn, die Funktionsbeschreibung lautet explizit anders:

  • 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.
  • Für Funktionen, bei denen die Zeitzone beachtet werden muss (wie in der Funktionsbeschreibung angegeben), wird die Standardzeitzone "America/Los_Angeles" verwendet, wenn keine Zeitzone festgelegt ist.

SAFE.-Präfix

Syntax:

SAFE.function_name()

Beschreibung

Wenn Sie eine Skalarfunktion mit dem Präfix SAFE. beginnen, wird anstelle eines Fehlers NULL zurückgegeben. Das Präfix SAFE. verhindert nur Fehler bei der mit dem Präfix versehenen Funktion selbst. Fehler während der Auswertung von Argumentausdrücken werden damit nicht vermieden. Das Präfix SAFE. verhindert nur Fehler im Zusammenhang mit dem Wert, der für die Funktion eingegeben wird, z. B. Fehler vom Typ "Wert außerhalb des Bereichs". Andere Fehler wie interne Fehler oder Systemfehler können trotzdem auftreten. Wenn die Funktion keinen Fehler zurückgibt, hat SAFE. keine Auswirkungen auf die Ausgabe.

Operatoren wie + und = unterstützen das Präfix SAFE. nicht. Verwenden Sie SAFE_DIVIDE, um Fehler bei einer Division zu vermeiden. 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 Funktion SUBSTR normalerweise ein Fehler zurückgegeben, weil die Funktion keine Längenargumente mit negativen Werten unterstützt. Das Präfix SAFE. bewirkt jedoch, dass die Funktion stattdessen NULL zurückgibt. Die zweite Verwendung der Funktion SUBSTR führt zur erwarteten Ausgabe: Das Präfix SAFE. hat keine Auswirkung.

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

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

Konversionsregeln in Google Standard-SQL

Die Umwandlung umfasst unter anderem das Umwandeln, Erzwingen und Supertyping.

  • Das Umwandeln ist eine explizite Konversion, für die die Funktion CAST() verwendet wird.
  • Erzwingen ist eine von Google-Standard-SQL unter nachfolgend beschriebenen Bedingungen automatisch durchgeführte implizite Konversion.
  • Ein Supertyp ist ein gemeinsamer Typ, in den zwei oder mehr Ausdrücke erzwungen werden können.

Außerdem gibt es Konversionen mit eigenen Funktionsnamen, z. B. PARSE_DATE(). Weitere Informationen zu diesen Funktionen finden Sie unter Konversionsfunktionen.

Umwandeln und Erzwingen im Vergleich

In der folgenden Tabelle sind alle möglichen Umwandlungs- und Erzwingungsmöglichkeiten für Google-Standard-SQL-Datentypen zusammengefasst. Die Spalte Erzwingen in gilt für alle Ausdrücke eines bestimmten Datentyps (beispielsweise einer Spalte).

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

Umwandeln

Die meisten Datentypen können mit der Funktion CAST von einem Typ in einen anderen übertragen werden. Bei der Umwandlung (CAST) kann eine Abfrage fehlschlagen, wenn Google-Standard-SQL die Umwandlung nicht ausführen kann. Wenn Sie Ihre Abfragen vor diesen Fehlern schützen möchten, können Sie SAFE_CAST verwenden. Weitere Informationen zu den Regeln für CAST, SAFE_CAST und andere Umwandlungsfunktionen finden Sie unter Konvertierungsfunktionen.

Erzwingen

Google-Standard-SQL erzwingt den Ergebnistyp eines Argumentausdrucks als einen anderen Typ, wenn er mit Funktionssignaturen übereinstimmen muss. Wenn beispielsweise die Funktion func() so definiert ist, dass sie ein einzelnes Argument vom Typ FLOAT64 annimmt, und ein Ausdruck als Argument mit einem Ergebnistyp von INT64 verwendet wird, dann wird das Ergebnis des Ausdrucks in den Typ FLOAT64 umgewandelt, bevor func() berechnet wird.

Supertypen

Ein Supertyp ist ein gemeinsamer Typ, in den zwei oder mehr Ausdrücke erzwungen werden können. Supertypen werden mit Satzvorgängen wie UNION ALL und Ausdrücken wie CASE verwendet, die mehrere Argumente mit Übereinstimmungstypen erwarten. Jeder Typ hat einen oder mehrere Supertypen, einschließlich seiner, die seine Gruppe von Supertypen definieren.

Eingabetyp Supertypen
BOOL BOOL
INT64 INT64
FLOAT64
NUMERIC
FLOAT64 FLOAT64
NUMERIC NUMERIC
FLOAT64
STRING STRING
DATE DATE
TIMESTAMP TIMESTAMP
BYTES BYTES
STRUCT STRUCT mit denselben Feldpositionstypen.
ARRAY ARRAY mit denselben Elementtypen.

Wenn Sie den Supertyp für eine Reihe von Eingabetypen ermitteln möchten, legen Sie zuerst die Schnittmenge der Supertypen für jeden Eingabetyp fest. Wenn dieser Satz leer ist, haben die Eingabetypen keinen gemeinsamen Supertyp. Wenn dieser Satz nicht leer ist, ist der allgemeine Supertyp in der Regel der besondere Typ in diesem Satz. Im Allgemeinen ist der spezifischste Typ der Typ mit der restriktivsten Domain.

Beispiele

Eingabetypen Gemeinsamer Supertyp Rückgabe: Hinweise
INT64
FLOAT64
FLOAT64 FLOAT64 Wenn Sie INT64 und BOOL supertypisieren, ist dies erfolgreich, da sie einen gemeinsamen Supertyp, FLOAT64, haben.
INT64
BOOL
Fehler Wenn Sie INT64 und BOOL supertypisieren, schlägt dies fehl, da sie keinen gemeinsamen Supertyp haben.

Exakte und ungenaue Typen

Numerische Typen können exakt oder ungenau sein. Für die Supertypisierung gilt: Wenn alle Eingabetypen exakte Typen sind, kann der resultierende Supertyp nur ein exakter Typ sein.

Die folgende Tabelle enthält die genauen, exakten numerischen Datentypen.

Exakte Typen Ungenaue Typen
INT64
NUMERIC
FLOAT64

Beispiele

Eingabetypen Gemeinsamer Supertyp Rückgabe: Hinweise
INT64
FLOAT64
FLOAT64 FLOAT64 Wenn die Supertyping-Funktion auf INT64 und DOUBLE angewendet wird, ist das Supertypisieren erfolgreich, da exakte und ungenaue numerische Typen supertypisiert werden.

Typenspezifität

Jeder Typ hat eine Domain mit unterstützten Werten. Ein Typ mit einer niedrigeren Domain ist spezifischer als ein Typ mit einer größeren Domain. Exakte Typen sind spezifischer als ungenaue Typen, da diese einen größeren Bereich von unterstützten Domainwerten enthalten als exakte Typen. Beispielsweise ist INT64 spezifischer als FLOAT64.

Supertypen und Literale

Supertypregeln für Literale sind großzügiger als für normale Ausdrücke und stimmen mit impliziten Erzwingungsregeln überein. Der folgende Algorithmus wird verwendet, wenn der Eingabesatz Typen enthält, die sich auf Literale beziehen:

  • Wenn der Satz Nicht-Literale enthält, suchen Sie den Satz allgemeiner Supertypen der Nicht-Literale.
  • Wenn es mindestens einen möglichen Supertyp gibt, suchen Sie nach dem spezifischsten Typ, für den die verbleibenden Literaltypen implizit erzwungen werden können, und geben Sie diesen Supertyp zurück. Andernfalls gibt es keinen Supertyp.
  • Wenn der Satz nur Typen enthält, die sich auf Literale beziehen, berechnen Sie den Supertyp der Literaltypen.
  • Wenn alle Eingabetypen mit NULL-Literalen verknüpft sind, lautet der resultierende Supertyp INT64.
  • Wenn kein gemeinsamer Supertyp gefunden wird, wird ein Fehler erzeugt.

Beispiele

Eingabetypen Gemeinsamer Supertyp Rückgabe:
INT64-Literal
UINT64-Ausdruck
UINT64 UINT64
TIMESTAMP-Ausdruck
STRING-Literal
TIMESTAMP TIMESTAMP
NULL-Literal
NULL-Literal
INT64 INT64
BOOL-Literal
TIMESTAMP-Literal
Fehler

Aggregatfunktionen in Google Standard-SQL

Eine Aggregatfunktion ist eine Funktion, die die Zeilen einer Gruppe in einem einzelnen Wert zusammenfasst. Beispiele für Aggregatfunktionen sind COUNT, MIN und MAX.

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
      SELECT "apple" as fruit UNION ALL
      SELECT "pear" as fruit UNION ALL
      SELECT "orange" as fruit)

+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

Bei Verwendung in Verbindung mit einer GROUP BY-Klausel haben die zusammengefassten Gruppen normalerweise mindestens eine Zeile. Wenn der zugehörige Wert SELECT keine GROUP BY-Klausel hat oder wenn bestimmte Modifikatoren für Aggregatfunktionen Zeilen aus der zusammenzufassenden Gruppe filtern, ist es möglich, dass die Aggregatfunktion eine leere Gruppe zusammenfassen muss. In diesem Fall geben die Funktionen COUNT und COUNTIF den Wert 0 zurück, während alle anderen Aggregatfunktionen NULL zurückgeben.

In den folgenden Abschnitten werden die von Google Standard-SQL unterstützten Aggregatfunktionen erläutert.

ANY_VALUE

ANY_VALUE(
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

Gibt für eine aus der Gruppe ausgewählte Zeile expression zurück. Welche Zeile ausgewählt wird, ist unbestimmt und nicht zufällig. Gibt NULL zurück, wenn die Eingabe keine Zeilen erzeugt. Gibt NULL zurück, wenn expression für alle Zeilen in der Gruppe NULL ist.

ANY_VALUE verhält sich so, als wäre IGNORE NULLS angegeben. Zeilen, für die expression den Wert NULL hat, werden nicht berücksichtigt und nicht ausgewählt.

Unterstützte Argumenttypen

Beliebig

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Zurückgegebene Datentypen

Entspricht dem Eingabedatentyp.

Beispiele

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

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

ARRAY_AGG

ARRAY_AGG(
  [DISTINCT]
  expression
  [{IGNORE|RESPECT} NULLS]
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

Gibt ein ARRAY von expression-Werten zurück.

Unterstützte Argumenttypen

Alle Datentypen außer ARRAY.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. IGNORE NULLS oder RESPECT NULLS: Wenn IGNORE NULLS angegeben ist, werden die NULL-Werte aus dem Ergebnis ausgeschlossen. Wenn RESPECT NULLS angegeben ist, werden die NULL-Werte in das Ergebnis eingeschlossen. Wenn keiner der beiden Werte angegeben ist, werden die NULL-Werte in das Ergebnis eingeschlossen.
  3. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Reihenfolge der Elemente in der Ausgabe

Die Reihenfolge der Elemente in der Ausgabe ist nicht deterministisch. Dies bedeutet, dass Sie bei jeder Nutzung dieser Funktion möglicherweise ein anderes Ergebnis erhalten.

Zurückgegebene Datentypen

ARRAY

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

Beispiele

SELECT ARRAY_AGG(x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT 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] |
+-------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

Verkettet Elemente aus expression vom Typ ARRAY und gibt ein einziges ARRAY als Ergebnis zurück. Diese Funktion ignoriert NULL-Eingabe-Arrays, berücksichtigt aber die NULL-Elemente in Nicht-NULL-Eingabe-Arrays.

Unterstützte Argumenttypen

ARRAY

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Reihenfolge der Elemente in der Ausgabe

Die Reihenfolge der Elemente in der Ausgabe ist nicht deterministisch. Dies bedeutet, dass Sie bei jeder Nutzung dieser Funktion möglicherweise ein anderes Ergebnis erhalten.

Zurückgegebene Datentypen

ARRAY

Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression für alle Zeilen als NULL ausgewertet wird.

Beispiele

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

AVG

AVG(
  [DISTINCT]
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

Gibt den Durchschnitt der Nicht-NULL-Eingabewerte zurück oder NaN, wenn die Eingabe einen NaN-Wert 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. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Zurückgegebene Datentypen

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

Beispiele

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

+-----+
| avg |
+-----+
| 3   |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

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

BIT_AND

BIT_AND(
  [DISTINCT]
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

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

Unterstützte Argumenttypen

  • INT64

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

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(
  [DISTINCT]
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

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

Unterstützte Argumenttypen

  • INT64

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

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
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

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

Unterstützte Argumenttypen

  • INT64

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

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(*)

2.

COUNT(
  [DISTINCT]
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

  1. Gibt die Anzahl der Zeilen in der Eingabe zurück.
  2. Gibt die Anzahl der Zeilen zurück, in denen expression als beliebiger Wert außer NULL ausgewertet wurde.

Unterstützte Argumenttypen

expression kann ein beliebiger Datentyp sein. Wenn DISTINCT vorhanden ist, kann expression nur einen Datentyp haben, der gruppierbar ist.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Rückgabedatentypen

INT64

Beispiele

Mit der COUNT-Funktion können Sie die Anzahl der Zeilen in einer Tabelle oder die Anzahl verschiedener Werte eines Ausdrucks zurückgeben. Beispiel:

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

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

+------------+---------+
| count_star | count_x |
+------------+---------+
| 5          | 4       |
+------------+---------+

Wenn Sie die Anzahl der eindeutigen Werte eines Ausdrucks zählen möchten, für die eine bestimmte Bedingung erfüllt ist, können Sie dieses Schema verwenden:

COUNT(DISTINCT IF(condition, expression, NULL))

Hier gibt IF den Wert von expression zurück, wenn condition gleich TRUE ist. Andernfalls wird NULL zurückgegeben. Das umgebende COUNT(DISTINCT ...) ignoriert die NULL-Werte, sodass nur die verschiedenen Werte von expression gezählt werden, für die condition gleich TRUE ist.

So zählen Sie beispielsweise die Anzahl der verschiedenen positiven Werte von x:

SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;

+-------------------+
| distinct_positive |
+-------------------+
| 3                 |
+-------------------+

Oder wenn Sie die Anzahl der verschiedenen Daten zählen, an denen eine bestimmte Art von Ereignis aufgetreten ist:

WITH Events AS (
  SELECT DATE '2021-01-01' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-03' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
)
SELECT
  COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
    AS distinct_dates_with_failures
FROM Events;

+------------------------------+
| distinct_dates_with_failures |
+------------------------------+
| 2                            |
+------------------------------+

COUNTIF

COUNTIF(
  [DISTINCT]
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

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

Da expression ein BOOL sein muss, ist die Form COUNTIF(DISTINCT ...) im Allgemeinen nicht nützlich: Es gibt nur einen eindeutigen Wert von TRUE. COUNTIF(DISTINCT ...) gibt also 1 zurück, wenn expression für eine oder mehrere Eingabezeilen als TRUE ausgewertet wird. Andernfalls wird 0 zurückgegeben. Wenn jemand COUNTIF und DISTINCT kombinieren möchte, möchte er in der Regel die Anzahl der verschiedenen Werte eines Ausdrucks zählen, für die eine bestimmte Bedingung erfüllt ist. Dies kann beispielsweise so aussehen:

COUNT(DISTINCT IF(condition, expression, NULL))

Dabei wird COUNT und nicht COUNTIF verwendet. Der Teil IF wurde nach innen verschoben. Weitere Informationen finden Sie in den Beispielen für COUNT.

Unterstützte Argumenttypen

BOOL

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Rückgabedatentypen

INT64

Beispiele

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

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

LOGICAL_AND

LOGICAL_AND(
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

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

Unterstützte Argumenttypen

BOOL

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

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
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

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

Unterstützte Argumenttypen

BOOL

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Rückgabedatentypen

BOOL

Beispiele

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

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

MAX

MAX(
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

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

Unterstützte Argumenttypen

Jeder sortierbare Datentyp.

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Rückgabedatentypen

Derselbe Datentyp, der als Eingabewert verwendet wird.

Beispiele

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

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

MIN

MIN(
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

Gibt den Mindestwert der Nicht-NULL-Ausdrücke zurück. Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression für alle Zeilen als NULL ausgewertet wird. Gibt NaN zurück, wenn die Eingabe einen NaN-Wert enthält.

Unterstützte Argumenttypen

Jeder sortierbare Datentyp.

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Rückgabedatentypen

Derselbe Datentyp, der als Eingabewert verwendet wird.

Beispiele

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

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

STRING_AGG

STRING_AGG(
  [DISTINCT]
  expression [, delimiter]
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

Gibt einen Wert (entweder STRING oder BYTES) zurück, der durch die Verkettung von Nicht-Null-Werten erlangt wird. Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression für alle Zeilen als NULL ausgewertet wird.

Wenn ein delimiter angegeben ist, 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. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Reihenfolge der Elemente in der Ausgabe

Die Reihenfolge der Elemente in der Ausgabe ist nicht deterministisch. Dies bedeutet, dass Sie bei jeder Nutzung dieser Funktion möglicherweise ein anderes Ergebnis erhalten.

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", "pear", "banana", "pear"]) AS fruit;

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

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

SUM

SUM(
  [DISTINCT]
  expression
  [HAVING {MAX | MIN} expression2]
)

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. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Rückgabedatentypen

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

Sonderfälle:

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

Gibt NULL zurück, wenn die Eingabe keine Zeilen 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 einen NaN-Wert 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 SUM(x) AS sum
FROM UNNEST([]) AS x;

+------+
| sum  |
+------+
| NULL |
+------+

Allgemeine Klauseln

HAVING MAX und HAVING MIN-Klausel

Die meisten Aggregatfunktionen unterstützen zwei optionale Klauseln mit dem Namen HAVING MAX und HAVING MIN, die den Satz von Zeilen, die von einer Funktion zusammengefasst werden, auf Zeilen mit einem Höchst- oder Mindestwert in einer bestimmten Spalte einschränken. Die Syntax sieht im Allgemeinen so aus:

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
  • HAVING MAX: Beschränkt den Satz von Zeilen, die von der Funktion zusammengefasst werden, auf einen Wert für expression2, der dem Höchstwert für expression2 innerhalb der Gruppe entspricht. Der Höchstwert entspricht dem Ergebnis von MAX(expression2).
  • HAVING MIN: Beschränkt den Satz von Zeilen, die von der Funktion zusammengefasst werden, auf einen Wert für expression2, der dem Mindestwert für expression2 innerhalb der Gruppe entspricht. Der Mindestwert entspricht dem Ergebnis von MIN(expression2).

Diese Klauseln ignorieren NULL-Werte, wenn der Höchst- oder Mindestwert berechnet wird, es sei denn, expression2 ergibt NULL für alle Zeilen.

Diese Klauseln unterstützen nur sortierbare Datentypen.

Beispiel

In diesem Beispiel wird der durchschnittliche Niederschlag für das letzte Jahr 2001 zurückgegeben.

WITH Precipitation AS
 (SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
  SELECT 2001, 'winter', 1 UNION ALL
  SELECT 2000, 'fall', 3 UNION ALL
  SELECT 2000, 'summer', 5 UNION ALL
  SELECT 2000, 'spring', 7 UNION ALL
  SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation

+---------+
| average |
+---------+
| 5       |
+---------+

Zuerst ruft die Abfrage die Zeilen mit dem Höchstwert in der Spalte year ab. Es gibt zwei:

+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9      |
| 2001 | winter | 1      |
+------+--------+--------+

Schließlich ermittelt die Abfrage die Werte in der Spalte inches (9 und 1) mit diesem Ergebnis:

+---------+
| average |
+---------+
| 5       |
+---------+

Statistische Aggregatfunktionen in Standard-SQL

Google-Standard-SQL unterstützt die folgenden statistischen Aggregatfunktionen:

STDDEV_SAMP

STDDEV_SAMP(
  [DISTINCT]
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

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

Alle numerischen Typen werden unterstützt. Wenn die Eingabe NUMERIC ist, kann die interne Aggregation mit der endgültigen Ausgabe in ein FLOAT64 umgewandelt werden. Andernfalls wird die Eingabe vor der Aggregation in ein FLOAT64 konvertiert, was zu einem potenziell instabilen Ergebnis führen kann.

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

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Rückgabedatentyp

FLOAT64

STDDEV

STDDEV(
  [DISTINCT]
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

Ein Alias von STDDEV_SAMP.

VAR_SAMP

VAR_SAMP(
  [DISTINCT]
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

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

Alle numerischen Typen werden unterstützt. Wenn die Eingabe NUMERIC ist, kann die interne Aggregation mit der endgültigen Ausgabe in ein FLOAT64 umgewandelt werden. Andernfalls wird die Eingabe vor der Aggregation in ein FLOAT64 konvertiert, was zu einem potenziell instabilen Ergebnis führen kann.

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

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Rückgabedatentyp

FLOAT64

VARIANCE

VARIANCE(
  [DISTINCT]
  expression
  [HAVING {MAX | MIN} expression2]
)

Beschreibung

Ein Alias von VAR_SAMP.

Konvertierungsfunktionen in Google-Standard-SQL

Google-Standard-SQL unterstützt die folgenden Konversionsfunktionen. Diese Datentyp-Konversionen sind explizit. Einige Konversionen können jedoch implizit erfolgen. Weitere Informationen zur impliziten und expliziten Konversionen finden Sie hier.

CAST-Übersicht

CAST(expression AS typename)

Beschreibung

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

Bei der Umwandlung (CAST) kann eine Abfrage fehlschlagen, wenn Google-Standard-SQL die Umwandlung nicht ausführen kann. Wenn Sie Ihre Abfragen vor diesen Fehlern schützen möchten, können Sie SAFE_CAST verwenden.

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.

Beispiele

Die folgende Abfrage führt zu "true", wenn x gleich 1 ist, "false" für jeden anderen Nicht-NULL-Wert und NULL, wenn x gleich NULL ist.

CAST(x=1 AS STRING)

ALS ARRAY UMWANDELN

CAST(expression AS ARRAY<element_type>)

Beschreibung

Google-Standard-SQL unterstützt die Umwandlung in ARRAY. Der Parameter expression kann einen Ausdruck für diese Datentypen darstellen:

  • ARRAY

Konversionsregeln

Von Zu Regel(n) beim Umwandeln von x
ARRAY ARRAY Muss exakt derselbe ARRAY-Typ sein.

ALS BOOL UMWANDELN

CAST(expression AS BOOL)

Beschreibung

Google-Standard-SQL unterstützt die Umwandlung in BOOL. Der Parameter expression kann einen Ausdruck für diese Datentypen darstellen:

  • INT64
  • BOOL
  • STRING

Konversionsregeln

Von Zu Regel(n) beim Umwandeln von x
INT64 BOOL Gibt FALSE zurück, wenn x gleich 0 ist, ansonsten TRUE.
STRING BOOL Gibt TRUE zurück, wenn x gleich "true" ist, und FALSE, wenn x gleich "false" ist.
Alle anderen Werte von x sind ungültig und erzeugen einen Fehler, statt in BOOL umgewandelt zu werden.
Bei STRING-Werten wird bei der Umwandlung in BOOL die Groß-/Kleinschreibung nicht berücksichtigt.

ALS BYTES UMWANDELN

CAST(expression AS BYTES)

Beschreibung

Google-Standard-SQL unterstützt die Umwandlung in BYTES. Der Parameter expression kann einen Ausdruck für diese Datentypen darstellen:

  • BYTES
  • STRING

Konversionsregeln

Von Zu Regel(n) beim Umwandeln von x
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.

ALS DATE UMWANDELN

CAST(expression AS DATE)

Beschreibung

Google-Standard-SQL unterstützt die Umwandlung in DATE. Der Parameter expression kann einen Ausdruck für diese Datentypen darstellen:

  • STRING
  • TIMESTAMP

Konversionsregeln

Von Zu Regel(n) beim Umwandeln von x
STRING DATE 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.
TIMESTAMP DATE Beim Umwandeln eines Zeitstempels in ein Datum wird der Zeitstempel gemäß der Standardzeitzone effektiv gekürzt.

ALS FLOAT64 UMWANDELN

CAST(expression AS FLOAT64)

Beschreibung

Google-Standard-SQL unterstützt die Umwandlung in Gleitkommatypen. Der Parameter expression kann einen Ausdruck für diese Datentypen darstellen:

  • INT64
  • FLOAT64
  • NUMERIC
  • STRING

Konversionsregeln

Von Zu Regel(n) beim Umwandeln von x
INT64 FLOAT64 Gibt einen annähernden, aber möglicherweise keinen exakten Gleitkommawert zurück.
NUMERIC FLOAT64 NUMERIC wird in die nächste Gleitkommazahl konvertiert, was mit einem Genauigkeitsverlust verbunden sein kann.
STRING FLOAT64 Gibt x als Gleitkommawert zurück und interpretiert ihn so, als hätte er die gleiche Form wie ein gültiges Gleitkommaliteral. Unterstützt auch Umwandlungen von "[+,-]inf" zu [,-]Infinity, "[+,-]infinity" zu [,-]Infinity und von "[+,-]nan" zu NaN. Bei der Konversion wird die Groß-/Kleinschreibung nicht beachtet.

ALS INT64 UMWANDELN

CAST(expression AS INT64)

Beschreibung

Google-Standard-SQL unterstützt die Umwandlung in Ganzzahltypen. Der Parameter expression kann einen Ausdruck für diese Datentypen darstellen:

  • INT64
  • FLOAT64
  • NUMERIC
  • BOOL
  • STRING

Konversionsregeln

Von Zu Regel(n) beim Umwandeln von x
FLOAT64 INT64 Gibt den nächstgelegenen ganzzahligen Wert zurück.
Halbe Zahlen wie 1,5 oder -0,5 runden von 0 weg.
BOOL INT64 Gibt 1 zurück, wenn x gleich TRUE ist, ansonsten 0.
STRING INT64 Ein Hexadezimalstring kann in eine Ganzzahl umgewandelt werden. Beispiel: 0x123 zu 291 oder -0x123 zu -291.

Beispiele

Wenn Sie mit Hex-Strings (0x123) arbeiten, können Sie diese in Ganzzahlen 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       |
+-----------+------------+

ALS NUMERIC UMWANDELN

CAST(expression AS NUMERIC)

Beschreibung

Google-Standard-SQL unterstützt die Umwandlung in NUMERIC. Der Parameter expression kann einen Ausdruck für diese Datentypen darstellen:

  • INT64
  • FLOAT64
  • NUMERIC
  • STRING

Konversionsregeln

Von Zu Regel(n) beim Umwandeln von x
FLOAT64 NUMERIC Die Gleitkommazahl wird kaufmännisch gerundet. Beim Umwandeln von NaN, +inf oder -inf wird ein Fehler zurückgegeben. Beim Umwandeln eines Werts außerhalb des Bereichs NUMERIC wird ein Überlauffehler zurückgegeben.
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 neun ist, wird der resultierende NUMERIC-Wert kaufmännisch gerundet, sodass neun Nachkommastellen übrig bleiben.

ALS STRING UMWANDELN

CAST(expression AS STRING)

Beschreibung

Google-Standard-SQL unterstützt die Umwandlung in STRING. Der Parameter expression kann einen Ausdruck für diese Datentypen darstellen:

  • INT64
  • FLOAT64
  • NUMERIC
  • BOOL
  • BYTES
  • DATE
  • TIMESTAMP
  • STRING

Konversionsregeln

Von Zu Regel(n) beim Umwandeln von x
FLOAT64 STRING Gibt eine ungefähre Stringdarstellung zurück.
BOOL STRING Gibt "true" zurück, wenn x gleich TRUE ist, ansonsten "false".
BYTES STRING Gibt x als UTF-8-STRING interpretiert zurück.
Beispiel: Das BYTES-Literal b'\xc2\xa9' wird beim Umwandeln in STRING als UTF-8 interpretiert und zum Unicode-Zeichen „©“ konvertiert.
Falls x keine gültige UTF-8-Codierung ist, tritt ein Fehler auf.
DATE STRING Die Umwandlung von einem Datumstyp in einen String ist unabhängig von der Zeitzone und erfolgt in der Form YYYY-MM-DD.
TIMESTAMP STRING Beim Umwandeln von Zeitstempeltypen in Strings wird der Zeitstempel mit der Standardzeitzone America/Los_Angeles 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.

Beispiele

SELECT CAST(CURRENT_DATE() AS STRING) AS current_date

+---------------+
| current_date  |
+---------------+
| 2021-03-09    |
+---------------+

ALS STRUCT UMWANDELN

CAST(expression AS STRUCT)

Beschreibung

Google-Standard-SQL unterstützt die Umwandlung in STRUCT. Der Parameter expression kann einen Ausdruck für diese Datentypen darstellen:

  • STRUCT

Konversionsregeln

Von Zu Regel(n) beim Umwandeln von x
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.

ALS TIMESTAMP UMWANDELN

CAST(expression AS TIMESTAMP)

Beschreibung

Google-Standard-SQL unterstützt die Umwandlung in TIMESTAMP. Der Parameter expression kann einen Ausdruck für diese Datentypen darstellen:

  • STRING
  • TIMESTAMP

Konversionsregeln

Von Zu Regel(n) beim Umwandeln von x
STRING TIMESTAMP Beim Umwandeln von Strings in Zeitstempel muss string_expression den unterstützten Zeitstempel-Literalformaten entsprechen. Ansonsten tritt ein Laufzeitfehler auf. string_expression kann selbst eine Zeitzone enthalten.
Wenn in string_expression eine Zeitzone vorhanden ist, wird diese Zeitzone für die Umwandlung verwendet, andernfalls gilt die Standardzeitzone „America/Los_Angeles“. Wenn der String weniger als sechs Ziffern hat, dann wird er implizit erweitert.
Wenn 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.
DATE TIMESTAMP Die Umwandlung von einem Datum in einen Zeitstempel interpretiert date_expression ab Mitternacht (Tagesanfang) in der Standardzeitzone „America/Los_Angeles“.

Beispiele

Das folgende Beispiel wandelt einen String-formatierten Zeitstempel in einen Zeitstempel um:

SELECT CAST("2020-06-02 17:00:53.110+00:00" AS TIMESTAMP) AS as_timestamp

-- Results depend upon where this query was executed.
+-------------------------+
| as_timestamp            |
+-------------------------+
| 2020-06-03T00:00:53.11Z |
+-------------------------+

SAFE_CAST

SAFE_CAST(expression AS typename)

Beschreibung

Bei der Umwandlung (CAST) kann eine Abfrage fehlschlagen, wenn Google-Standard-SQL 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 mit CAST identisch, allerdings wird NULL zurückgegeben und kein Fehler ausgelöst.

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

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

Zum Umwandeln 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.

Weitere Konversionsfunktionen

Weitere Informationen zu diesen Konversionsfunktionen finden Sie an anderer Stelle in der Dokumentation:

Konversionsfunktionen Von To
ARRAY_TO_STRING ARRAY STRING
DATE Verschiedene Datentypen DATE
FROM_BASE32 STRING BYTEs
FROM_BASE64 STRING BYTES
FROM_HEX STRING BYTES
PARSE_DATE STRING DATE
PARSE_JSON STRING JSON
PARSE_TIMESTAMP STRING TIMESTAMP
SAFE_CONVERT_BYTES_TO_STRING BYTES STRING
STRING TIMESTAMP STRING
TIMESTAMP Verschiedene Datentypen TIMESTAMP
TO_BASE32 BYTES STRING
TO_BASE64 BYTES STRING
TO_HEX BYTES STRING
TO_JSON Alle Datentypen JSON
TO_JSON_STRING JSON STRING

Mathematische Funktionen in Google-Standard-SQL

Alle mathematischen Funktionen verhalten sich in folgender Weise:

  • 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.

X ABS(X)
25 25
-25 25
+inf +inf
-inf +inf

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

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.

X SIGN(X)
25 +1
0 0
-25 -1
NaN NaN

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

IS_INF

IS_INF(X)

Beschreibung

Gibt TRUE zurück, wenn der Wert positiv oder negativ unendlich ist.

Gibt FALSE für NUMERIC-Eingaben zurück, da NUMERIC nicht INF sein kann.

X IS_INF(X)
+inf TRUE
-inf TRUE
25 FALSE

IS_NAN

IS_NAN(X)

Beschreibung

Gibt TRUE zurück, wenn der Wert ein NaN-Wert ist.

Gibt FALSE für NUMERIC-Eingaben zurück, da NUMERIC nicht NaN sein kann.

X IS_NAN(X)
NaN TRUE
25 FALSE

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.

X J IEEE_DIVIDE(X, Y)
20.0 4.0 5,0
0,0 25.0 0,0
25.0 0,0 +inf
-25.0 0,0 -inf
0,0 0,0 NaN
0,0 NaN NaN
NaN 0,0 NaN
+inf +inf NaN
-inf -inf NaN

SQRT

SQRT(X)

Beschreibung

Berechnet die Quadratwurzel aus X. Generiert einen Fehler, wenn X kleiner als 0 ist.

X SQRT(X)
25.0 5.0
+inf +inf
X < 0 Fehler

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

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.

X J POW(X, Y)
2.0 3,0 8.0
1,0 Beliebiger Wert, einschließlich NaN 1,0
Beliebiger Wert, einschließlich NaN 0 1,0
-1,0 +inf 1,0
-1,0 -inf 1,0
ABS (X) < 1 -inf +inf
ABS (X) > 1 -inf 0,0
ABS (X) < 1 +inf 0,0
ABS (X) > 1 +inf +inf
-inf Y < 0 0,0
-inf Y > 0 -inf, wenn Y eine ungerade Ganzzahl ist, sonst +inf
+inf Y < 0 0
+inf Y > 0 +inf
Endlicher Wert < 0 Keine Ganzzahl Fehler
0 Endlicher Wert < 0 Fehler

Rückgabedatentyp

Der Rückgabedatentyp wird durch die Argumenttypen mit der folgenden Tabelle bestimmt.

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

POWER

POWER(X, Y)

Beschreibung

Synonym für POW(X, Y).

EXP

EXP(X)

Beschreibung

Potenziert e mit X, was auch als natürliche Exponentialfunktion bezeichnet wird. Bei einem Unterlauf des Ergebnisses gibt diese Funktion eine Null zurück. Erzeugt einen Fehler, wenn ein Überlauf des Ergebnisses auftritt.

X EXP(X)
0,0 1,0
+inf +inf
-inf 0,0

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

LN

LN(X)

Beschreibung

Berechnet den natürlichen Logarithmus von X. Generiert einen Fehler, wenn X kleiner als oder gleich null ist.

X LN(X)
1,0 0,0
+inf +inf
X < 0 Fehler

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

LOG

LOG(X [, Y])

Beschreibung

Wenn nur X vorhanden ist, dann ist LOG ein Synonym für LN. Ist Y ebenfalls vorhanden, berechnet LOG den Logarithmus von X zur Basis Y.

X Y LOG(X, Y)
100,0 10,0 2.0
-inf Beliebiger Wert NaN
Beliebiger Wert +inf NaN
+inf 0.0 < Y < 1.0 -inf
+inf Y > 1,0 +inf
X <= 0 Beliebiger Wert Fehler
Beliebiger Wert Y <= 0 Fehler
Beliebiger Wert 1,0 Fehler

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

LOG10

LOG10(X)

Beschreibung

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

X LOG10(X)
100,0 2.0
-inf NaN
+inf NaN
X <= 0 Fehler

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

GREATEST

GREATEST(X1,...,XN)

Beschreibung

Der größte Wert wird unter X1,…,XN gemäß Kleiner-als-Vergleich (<) zurückgegeben. Wenn Teile von X1,...,XN NULL sind, ist der Rückgabewert NULL.

X1,...,XN GREATEST(X1,...,XN)
3,5,1 5

Rückgabedatentypen

Der Datentyp der Eingabewerte.

LEAST

LEAST(X1,...,XN)

Beschreibung

Der kleinste Wert wird unter X1,…,XN gemäß Größer-als-Vergleich (>) zurückgegeben. Wenn Teile von X1,...,XN NULL sind, ist der Rückgabewert NULL.

X1,...,XN LEAST(X1,...,XN)
3,5,1 1

Rückgabedatentypen

Der Datentyp der Eingabewerte.

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. Wenn beide Eingaben NUMERIC sind und das Ergebnis ist vom Typ Überlauf, gibt es einen numeric overflow-Fehler zurück.

X J DIV(X, Y)
20 4 5
0 20 0
20 0 Fehler

Rückgabedatentyp

Der Rückgabedatentyp wird durch die Argumenttypen mit der folgenden Tabelle bestimmt.

INPUTINT64NUMERIC
INT64INT64NUMERIC
NUMERICNUMERICNUMERIC

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

Beschreibung

Entspricht dem Divisionsoperator (X / Y), gibt jedoch NULL zurück, wenn ein Fehler auftritt, beispielsweise bei einer Division durch null.

XJSAFE_DIVIDE(X, Y)
2045
0200
200NULL

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

Beschreibung

Entspricht dem Multiplikationsoperator (*), gibt jedoch NULL zurück, wenn ein Überlauf auftritt.

XJSAFE_MULTIPLY(X, Y)
20480

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_NEGATE

SAFE_NEGATE(X)

Beschreibung

Entspricht dem Unäres-Minus-Operator (-), gibt jedoch NULL zurück, wenn ein Überlauf auftritt.

XSAFE_NEGATE(X)
+1-1
-1+1
00

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

SAFE_ADD

SAFE_ADD(X, Y)

Beschreibung

Entspricht dem Additionsoperator (+), gibt jedoch NULL zurück, wenn ein Überlauf auftritt.

XJSAFE_ADD(X, Y)
549

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

Beschreibung

Gibt das Ergebnis von X minus Y zurück. Entspricht dem Subtraktionsoperator (-), gibt jedoch NULL zurück, wenn ein Überlauf auftritt.

XJSAFE_SUBTRACT(X, Y)
541

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

MOD

MOD(X, Y)

Beschreibung

Modulo-Funktion: gibt den Rest der Division von X durch Y zurück. Der zurückgegebene Wert hat dasselbe Vorzeichen wie X. Wenn Y gleich 0 ist, wird ein Fehler ausgegeben.

X J MOD(X, Y)
25 12 1
25 0 Fehler

Rückgabedatentyp

Der Rückgabedatentyp wird durch die Argumenttypen mit der folgenden Tabelle bestimmt.

INPUTINT64NUMERIC
INT64INT64NUMERIC
NUMERICNUMERICNUMERIC

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 ein Überlauf auftritt.

X ROUND(X)
2.0 2,0
2,3 2.0
2,8 3,0
2,5 3,0
-2,3 -2,0
-2,8 -3,0
-2,5 -3,0
0 0
+inf +inf
-inf -inf
NaN NaN

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

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.

X TRUNC(X)
2.0 2,0
2,3 2.0
2,8 2.0
2,5 2.0
-2,3 -2,0
-2,8 -2,0
-2,5 -2,0
0 0
+inf +inf
-inf -inf
NaN NaN

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

CEIL

CEIL(X)

Beschreibung

Gibt den kleinsten Integralwert zurück, der nicht kleiner als X ist.

X CEIL(X)
2.0 2,0
2,3 3,0
2,8 3,0
2,5 3,0
-2,3 -2,0
-2,8 -2,0
-2,5 -2,0
0 0
+inf +inf
-inf -inf
NaN NaN

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

CEILING

CEILING(X)

Beschreibung

Synonym von CEIL(X)

FLOOR

FLOOR(X)

Beschreibung

Gibt den größten Integralwert zurück, der nicht größer als X ist.

X FLOOR(X)
2,0 2,0
2,3 2.0
2,8 2.0
2,5 2.0
-2,3 -3,0
-2,8 -3,0
-2,5 -3,0
0 0
+inf +inf
-inf -inf
NaN NaN

Rückgabedatentyp

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

COS

COS(X)

Beschreibung

Berechnet den Kosinus von X, wobei X als Radiant angegeben wird. Schlägt niemals fehl.

X COS(X)
+inf NaN
-inf NaN
NaN NaN

COSH

COSH(X)

Beschreibung

Berechnet den hyperbolischen Kosinus von X, wobei X als Radiant angegeben wird. Erzeugt einen Fehler, wenn ein Überlauf auftritt.

Wenn X NUMERIC ist, ist die Ausgabe FLOAT64.

X COSH(X)
+inf +inf
-inf +inf
NaN NaN

ACOS

ACOS(X)

Beschreibung

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

Wenn X NUMERIC ist, ist die Ausgabe FLOAT64.

X ACOS(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 Fehler
X > 1 Fehler

ACOSH

ACOSH(X)

Beschreibung

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

Wenn X NUMERIC ist, ist die Ausgabe FLOAT64.

X ACOSH(X)
+inf +inf
-inf NaN
NaN NaN
X < 1 Fehler

SIN

SIN(X)

Beschreibung

Berechnet den Sinus von X, wobei X als Radiant angegeben wird. Schlägt niemals fehl.

X SIN(X)
+inf NaN
-inf NaN
NaN NaN

SINH

SINH(X)

Beschreibung

Berechnet den hyperbolischen Sinus von X, wobei X als Radiant angegeben wird. Erzeugt einen Fehler, wenn ein Überlauf auftritt.

Wenn X NUMERIC ist, ist die Ausgabe FLOAT64.

X SINH(X)
+inf +inf
-inf -inf
NaN NaN

ASIN

ASIN(X)

Beschreibung

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

Wenn X NUMERIC ist, ist die Ausgabe FLOAT64.

X ASIN(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 Fehler
X > 1 Fehler

ASINH

ASINH(X)

Beschreibung

Berechnet den inversen hyperbolischen Sinus von X. Scheitert nie.

Wenn X NUMERIC ist, ist die Ausgabe FLOAT64.

X ASINH(X)
+inf +inf
-inf -inf
NaN NaN

TAN

TAN(X)

Beschreibung

Berechnet den Tangens von X, wobei X als Radiant angegeben wird. Erzeugt einen Fehler, wenn das Ergebnis überläuft.

X TAN(X)
+inf NaN
-inf NaN
NaN NaN

TANH

TANH(X)

Beschreibung

Berechnet den hyperbolischen Tangens von X, wobei X als Radiant angegeben wird. Schlägt nie fehl.

Wenn X NUMERIC ist, ist die Ausgabe FLOAT64.

X TANH(X)
+inf 1,0
-inf -1,0
NaN NaN

ATAN

ATAN(X)

Beschreibung

Berechnet den Hauptwert des inversen Tangens von X. Der zurückgegebene Wert liegt im Bereich [-π/2,π/2]. Scheitert nie.

Wenn X NUMERIC ist, ist die Ausgabe FLOAT64.

X ATAN(X)
+inf π/2
-inf -π/2
NaN NaN

ATANH

ATANH(X)

Beschreibung

Berechnet den inversen hyperbolischen Tangens von X. Erzeugt einen Fehler, wenn X außerhalb des Bereichs [-1, 1] liegt.

Wenn X NUMERIC ist, ist die Ausgabe FLOAT64.

X ATANH(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 Fehler
X > 1 Fehler

ATAN2

ATAN2(X, Y)

Beschreibung

Berechnet den Hauptwert des inversen Tangens von X/Y unter Verwendung der Vorzeichen der beiden Argumente, um den Quadranten zu bestimmen. Der zurückgegebene Wert liegt im Bereich [-π, π].

Wenn Y NUMERIC ist, ist die Ausgabe FLOAT64.

X J ATAN2(X, Y)
NaN Beliebiger Wert NaN
Beliebiger Wert NaN NaN
0,0 0,0 0,0
Positiver endlicher Wert -inf π
Negativer endlicher Wert -inf
Endlicher Wert +inf 0,0
+inf Endlicher Wert π/2
-inf Endlicher Wert -π/2
+inf -inf ¾π
-inf -inf -¾π
+inf +inf π/4
-inf +inf -π/4

Hash-Funktionen in Google Standard-SQL

FARM_FINGERPRINT

FARM_FINGERPRINT(value)

Beschreibung

Berechnet den Fingerabdruck der STRING- oder BYTES-Eingabe mit der Fingerprint64-Funktion 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 |
+---+-------+-------+----------------------+

SHA1

SHA1(input)

Beschreibung

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

Diese Funktion gibt 20 Bytes zurück.

Rückgabetyp

BYTES

Beispiel

SELECT SHA1("Hello World") as sha1;

-- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string.
+------------------------------+
| sha1                         |
+------------------------------+
| Ck1VqNd45QIvq3AZd8XYQLvEhtA= |
+------------------------------+

SHA256

SHA256(input)

Beschreibung

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

Diese Funktion gibt 32 Bytes 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 Stringversion behandelt die Eingabe als ein Array von Byte.

Diese Funktion gibt 64 Bytes zurück.

Rückgabetyp

BYTES

Beispiel

SELECT SHA512("Hello World") as sha512;

Stringfunktionen in Google-Standard-SQL

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

Funktionen, die Positionswerte wie STRPOS zurückgeben, codieren diese Positionen im Format INT64. Der Wert 1 bezieht sich auf das erste Zeichen (oder Byte), 2 bezieht sich auf das zweite Zeichen (oder Byte) und so weiter. Der Wert 0 gibt einen ungültigen Index an. Bei der Nutzung von Daten vom Typ STRING 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 STRING- oder BYTES-Werts in BYTES zurück, unabhängig davon, ob der Typ des Werts 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 von STRING 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

Gibt für ein Array von erweiterten ASCII-Codepunkten (ARRAY von INT64) BYTES zurück.

Informationen zum Umwandeln von BYTES in ein Array von Codepunkten 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;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'AbCd' is the result.
+----------+
| bytes    |
+----------+
| QWJDZA== |
+----------+

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

SELECT CODE_POINTS_TO_BYTES(ARRAY(
  (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(ARRAY[code]) AS chr
      FROM UNNEST(TO_CODE_POINTS(input)) AS code WITH OFFSET
      ORDER BY OFFSET)
   ))) AS encoded_string
FROM UNNEST(ARRAY['Test String!']) AS input;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'Grfg Fgevat!' is the result.
+------------------+
| encoded_string   |
+------------------+
| R3JmZyBGZ2V2YXQh |
+------------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

Beschreibung

Gibt für ein Array von Unicode-Codepunkten (ARRAY von INT64) einen STRING-Wert zurück. Wenn ein Codepunkt 0 ist, wird im STRING kein Zeichen dafür zurückgegeben.

Informationen zum Umwandeln eines Strings in ein Array von Codepunkten finden Sie unter TO_CODE_POINTS.

Rückgabetyp

STRING

Beispiele

Es folgen einfache Beispiele, in denen CODE_POINTS_TO_STRING verwendet wird.

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

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+
SELECT CODE_POINTS_TO_STRING([97, 0, 0xF9B5]) AS string;

+--------+
| string |
+--------+
| a例    |
+--------+
SELECT CODE_POINTS_TO_STRING([65, 255, NULL, 1024]) AS string;

+--------+
| string |
+--------+
| NULL   |
+--------+

Das folgende Beispiel berechnet die Häufigkeit der Buchstaben in einer Gruppe 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 STRING oder BYTE zu einem einzelnen Ergebnis.

Die Funktion gibt NULL zurück, wenn eines der Eingabeargumente NULL ist.

Rückgabetyp

STRING oder BYTES

Beispiele

SELECT CONCAT('T.P.', ' ', 'Bar') as author;

+---------------------+
| author              |
+---------------------+
| T.P. Bar            |
+---------------------+

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

Verwendet zwei STRING- oder BYTES-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

FORMAT(format_string_expression, data_type_expression[, ...])

Beschreibung

FORMAT formatiert einen Datentypausdruck als String.

  • format_string_expression: Kann null oder mehrere Formatspezifizierer enthalten. Jeder Formatspezifizierer wird durch das %-Zeichen eingeführt und muss einem oder mehreren der übrigen Argumente zugeordnet werden. Im Allgemeinen 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.
  • data_type_expression: Der Wert, der als String formatiert werden soll. Dies kann ein beliebiger Google-Standard-SQL-Datentyp sein.

Rückgabetyp

STRING

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+
Ganzzahl mit Kommas FORMAT("%'d", 123456789) 123,456,789
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 ihn zuerst mit typspezifischen Formatfunktionen wie FORMAT_DATE() oder FORMAT_TIMESTAMP() formatieren. Beispiel:

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

Rückgabe:

date: January 02, 2015!

Unterstützte Formatspezifizierer

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

Ein Formatspezifizierer fügt Formatierung hinzu, wenn ein Wert in einen String umgewandelt wird. Optional kann er diese Unterspezifizierer enthalten:

Weitere Informationen zu Formatspezifizierern:

Formatspezifizierer
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 Dezimalschreibweise: [-](Ganzzahl).(Bruchteil) für endliche Werte, in Kleinbuchstaben für nicht endliche Werte 392.650000
inf
nan
NUMERIC
FLOAT64
F Dezimalschreibweise: [-](Ganzzahl).(Bruchteil) für endliche Werte, in Großbuchstaben für nicht endliche Werte 392.650000
INF
NAN
NUMERIC
FLOAT64
e Exponentialschreibweise (Mantisse/Exponent), Kleinbuchstaben 3.926500e+02
inf
nan
NUMERIC
FLOAT64
E Exponentialschreibweise (Mantisse/Exponent), Großbuchstaben 3.926500E+02
INF
NAN
NUMERIC
FLOAT64
g Entweder Dezimalschreibweise oder wissenschaftliche Schreibweise, abhängig vom Exponenten des Eingabewerts und der angegebenen Genauigkeit. Kleinschreibung. Weitere Informationen finden Sie unter %g- und %G-Verhalten. 392.65
3.9265e+07
inf
nan
NUMERIC
FLOAT64
G Entweder Dezimalschreibweise oder wissenschaftliche Schreibweise, abhängig vom Exponenten des Eingabewerts und der angegebenen Genauigkeit. Großschreibung. Weitere Informationen finden Sie unter %g- und %G-Verhalten. 392.65
3.9265E+07
INF
NAN
NUMERIC
FLOAT64
p Erzeugt einen druckbaren String, der JSON darstellt. Informationen finden Sie unter Verhalten von %p und %P.

{"month":10,"year":2019}
JSON
P Erzeugt einen mehrzeiligen druckbaren String, der JSON darstellt. Informationen finden Sie unter Verhalten von %p und %P.

{
  "month": 10,
  "year": 2019
}
JSON
s String Beispiel STRING
t Gibt einen druckbaren String zurück, der den Wert darstellt. Ähnelt häufig der Umwandlung des Arguments in STRING. Informationen dazu finden Sie unter %t- und %T-Verhalten. Beispiel
01.01.2014
<any>
T Generiert einen String, der eine gültige Standard SQL-Konstante mit dem gleichen Typ wie der Typ des Werts ist (eventuell größer oder ein String). Informationen dazu finden Sie unter %t- und %T-Verhalten. 'sample'
b'bytes sample'
1234
2.3
Datum '2014‑01‑01'
<any>
% '%%' erzeugt ein einziges '%' %

*Die Spezifizierer %o, %x und %X geben bei negativen Werten einen Fehler aus.

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 dem Vorzeichen - versehen.
<Leerzeichen> Wird kein Vorzeichen geschrieben, wird vor dem Wert ein Leerzeichen eingefügt.
#
  • Bei "%o", "%x" und "%X" bedeutet dieses Flag, dass dem Wert 0, 0x bzw. 0X vorangestellt wird, wenn er nicht null ist.
  • Bei "%f", "%F", "%e" und "%E" bedeutet dieses Flag, dass das Dezimalzeichen auch dann hinzugefügt wird, wenn kein Bruchteil vorhanden ist, es sei denn, der Wert ist nicht endlich.
  • Bei "%g" und "%G" bedeutet dieses Flag, dass das Dezimalzeichen auch dann hinzugefügt wird, wenn kein Bruchteil vorhanden ist, es sei denn, der Wert ist nicht endlich. Die nachlaufenden Nullen nach dem Dezimalzeichen werden nie entfernt.
0 Füllt die Zahl von links mit Nullen (0) statt mit Leerzeichen 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 spielt nur für Dezimal-, Hexadezimal- und Oktalwerte eine Rolle.

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 Wert, der ausgegeben werden soll, 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 nicht im Formatstring angegeben, sondern als zusätzliches Ganzzahlargument vor dem Argument, das formatiert werden muss, vorangestellt.
Precision
Precision Beschreibung
.<Anzahl>
  • Für die Ganzzahlspezifizierer "%d", "%i", "%o", "%u", "%x" und "%X": Die Genauigkeit 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 Genauigkeit von 0 bedeutet, dass kein Zeichen für den Wert 0 geschrieben wird.
  • Für die Spezifizierer "%a", "%A", "%e", "%E", "%f" und "%F" ist dies die Anzahl an Ziffern, die nach dem Dezimalzeichen ausgegeben werden sollen. Der Standardwert ist 6.
  • Für die Spezifizierer "%g" und "%G" ist dies die Anzahl an signifikanten Ziffern, die ausgegeben werden sollen, bevor die Nullen nach dem Dezimalzeichen entfernt werden. Der Standardwert ist 6.
.* Die Präzision wird im Formatstring nicht angegeben, sondern als zusätzliches Ganzzahlwertargument dem Argument vorangestellt, das formatiert werden muss.
Verhalten von %g und %G

Die Formatspezifizierer %g und %G wenden entweder die Dezimalschreibweise (z. B. %f und %F) oder die wissenschaftliche Schreibweise (z. B. %e und %E) an. Dies ist abhängig vom Exponenten des Eingabewerts und der angegebenen Präzision.

Übernehmen Sie p für die angegebene Genauigkeit. Der Standardwert ist 6, und 1, wenn die angegebene Genauigkeit kleiner als 1 ist. Der Eingabewert wird zuerst in die wissenschaftliche Schreibweise mit der Präzision = (p - 1) umgewandelt. Wenn der resultierende Exponentenanteil x kleiner als -4 oder nicht kleiner als p ist, wird die wissenschaftliche Schreibweise mit der Präzision = (p - 1) verwendet. Andernfalls wird die Dezimalschreibweise mit der Präzision = (p - 1 - x) verwendet.

Sofern kein #-Flag vorhanden ist, werden die nachlaufenden Nullen nach dem Dezimalzeichen entfernt. Das Dezimalzeichen wird ebenfalls entfernt, wenn ihm keine Ziffer folgt.

Verhalten von %p und %P

Der Formatspezifizierer %p erzeugt einen einzeiligen druckbaren String. Der Formatspezifizierer %P erzeugt einen mehrzeiligen druckbaren String. Sie können diese Formatspezifizierer mit den folgenden Datentypen verwenden:

Typ %p %P
JSON

JSON-Eingabe:


JSON '
{
  "month": 10,
  "year": 2019
}
'

Erzeugt einen druckbaren String, der JSON darstellt:


{"month":10,"year":2019}

JSON-Eingabe:


JSON '
{
  "month": 10,
  "year": 2019
}
'

Erzeugt einen mehrzeiligen druckbaren String, der JSON darstellt:


{
  "month": 10,
  "year": 2019
}
%t- und %T-Verhalten

Die Formatspezifizierer %t und %T werden für alle Typen definiert. Die Breite, Präzision und Flags funktionieren wie bei %s: Die Breite ist die Mindestbreite und der STRING wird auf diese Größe aufgefüllt. Die Präzision 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.

Der Spezifizierer %t ist immer eine lesbare Form des Werts.

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

Der STRING ist in folgender Weise 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. 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
JSON Einzeiliger druckbarer String, der JSON darstellt.

{"name":"apple","stock":3}
Einzeiliger druckbarer String, der ein JSON-Literal darstellt.

JSON '{"name":"apple","stock":3}'
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. Die folgenden <format_string>-Ausdrücke sind beispielsweise ungültig:

FORMAT('%s', 1)
FORMAT('%')
NULL-Argument-Behandlung

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

Die Funktion erzeugt generell einen NULL-Wert, wenn ein NULL-Argument vorhanden ist. Beispielsweise generiert FORMAT('%i', NULL_expression) einen NULL STRING als Ausgabe.

Allerdings gibt es dafür Ausnahmen. Wenn der Formatspezifizierer %t oder %T ist (beide generieren STRINGs, die effektiv mit CAST und der Literalwertsemantik übereinstimmen), generiert ein STRING-Wert den String "NULL" (ohne Anführungszeichen) im Ergebnis-NULL. Ein Beispiel dafür ist die folgende Funktion:

FORMAT('00-%t-00', NULL_expression);

Rückgabe:

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 (in Klein- oder Großbuchstaben), soweit zutreffend. Dies steht im Einklang damit, wie Google-Standard-SQL diese Werte in STRING umwandelt. Für %T gibt Google-Standard-SQL Strings in Anführungszeichen für FLOAT64-Werte zurück, die keine Nicht-String-Literaldarstellungen haben.

FROM_BASE32

FROM_BASE32(string_expr)

Beschreibung

Wandelt den base32-codierten Eingabe-string_expr in das BYTES-Format um. Zum Umwandeln von BYTES in einen base32-codierten STRING verwenden Sie TO_BASE32.

Rückgabetyp

BYTES

Beispiel

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

-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/  |
+-----------+

FROM_BASE64

FROM_BASE64(string_expr)

Beschreibung

Wandelt den base64-codierten Eingabe-string_expr in das BYTES-Format um. Zum Umwandeln von BYTES in einen base64-codierten STRING verwenden Sie TO_BASE64.

Es gibt mehrere gängige base64-Codierungen, die sich darin unterscheiden, mit welchem Alphabet aus 65 ASCII-Zeichen die 64 Ziffern und das Auffüllen codiert werden. Weitere Informationen finden Sie unter RFC 4648. Diese Funktion erwartet das Alphabet [A-Za-z0-9+/=].

Rückgabetyp

BYTES

Beispiel

SELECT FROM_BASE64('/+A=') AS byte_data;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| /+A=      |
+-----------+

Wenn Sie mit einer Codierung mit einem anderen base64-Standard arbeiten möchten, müssen Sie unter Umständen FROM_BASE64 mit der Funktion REPLACE erstellen. Die URL-sichere und dateinamen-sichere base64url-Codierung, die häufig von Webprogrammierern verwendet wird, verwendet -_= statt +/= als letzte Zeichen. Um einen base64url-codierten String zu decodieren, ersetzen Sie - und _ durch + bzw. /.

SELECT FROM_BASE64(REPLACE(REPLACE('_-A=', '-', '+'), '_', '/')) AS binary;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+--------+
| binary |
+--------+
| /+A=   |
+--------+

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. Wenn der Eingabe-STRING eine ungerade Anzahl an Zeichen enthält, verhält sich die Funktion so, als würde die Eingabe eine zusätzliche führende 0 enthalten. Zum Umwandeln von BYTES in einen hexadezimal codierten STRING verwenden Sie TO_HEX.

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;

-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str          | bytes_str    |
+------------------+--------------+
| 0AF              | AAECA6ru7/8= |
| 00010203aaeeefff | AK8=         |
| 666f6f626172     | Zm9vYmFy     |
+------------------+--------------+

LENGTH

LENGTH(value)

Beschreibung

Gibt die Länge des STRING- oder BYTES-Werts 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 STRING- oder BYTES-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 Werts angibt. Wenn original_value vom Typ BYTES ist, dann stellt return_length die Anzahl der Byte dar. Wenn original_value vom Typ STRING ist, dann gibt return_length die Anzahl der Zeichen wieder.

Der Standardwert von pattern ist ein Leerzeichen.

original_value und pattern müssen denselben Datentyp haben.

Wenn return_length kleiner oder gleich der Länge von original_value ist, gibt diese Funktion den Wert von original_value zurück, gekürzt auf den Wert von return_length. Beispiel: LPAD('hello world', 7); gibt 'hello w' zurück.

Wenn original_value, return_length oder pattern den Wert NULL hat, 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 | pattern | 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 | pattern | 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.

BYTES-Argumente werden 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 jedoch 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

Gibt einen Stringwert als normalisierten String zurück. Wenn Sie keinen Normalisierungsmodus bereitstellen, wird NFC verwendet.

Normalisierung wird verwendet, damit zwei Strings einander entsprechen. Normalisierung wird oft in Situationen verwendet, in denen zwei Strings dasselbe auf dem Bildschirm rendern, 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.

Rückgabetyp

STRING

Beispiele

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

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

Verschiedene Leerzeichen normalisieren:

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

Nimmt einen Stringwert und gibt ihn als normalisierten String zurück.

Normalisierung wird verwendet, damit zwei Strings einander entsprechen. Normalisierung wird oft in Situationen verwendet, in denen zwei Strings dasselbe auf dem Bildschirm rendern, aber unterschiedliche Unicode-Codepunkte haben.

Umwandlung in einheitliche Groß- oder Kleinschreibung wird für den Vergleich von Strings ohne Berücksichtigung der Groß- und Kleinschreibung verwendet. Wenn Sie Strings vergleichen müssen und die Groß- und Kleinschreibung nicht berücksichtigt werden soll, verwenden Sie NORMALIZE_AND_CASEFOLD. Andernfalls verwenden Sie NORMALIZE.

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.

Rückgabetyp

STRING

Beispiele

SELECT
  a, b,
  NORMALIZE(a) = NORMALIZE(b) as normalized,
  NORMALIZE_AND_CASEFOLD(a) = NORMALIZE_AND_CASEFOLD(b) as normalized_with_case_folding
FROM (SELECT 'The red barn' AS a, 'The Red Barn' AS b);

+--------------+--------------+------------+------------------------------+
| a            | b            | normalized | normalized_with_case_folding |
+--------------+--------------+------------+------------------------------+
| The red barn | The Red Barn | false      | true                         |
+--------------+--------------+------------+------------------------------+
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, regexp)

Beschreibung

Gibt TRUE zurück, wenn value partiell mit dem regulären regexp-Ausdruck übereinstimmt.

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

Sie können auch nach einer vollständigen Übereinstimmung suchen. Dazu verwenden Sie ^ (Anfang des Textes) und $ (Ende des Textes). Aufgrund der Rangfolge der Operatoren für reguläre Ausdrücke empfehlen sich Klammern um alles zwischen ^ und $.

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 $. Due to regular expression operator
-- precedence, it is good practice to use parentheses around everything between ^
-- and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r'^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$')
    AS valid_email_address,
  REGEXP_CONTAINS(email, r'^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$')
    AS without_parentheses
FROM
  (SELECT
    ['a@foo.com', 'a@foo.computer', 'b@bar.org', '!b@bar.org', 'c@buz.net']
    AS addresses),
  UNNEST(addresses) AS email;

+----------------+---------------------+---------------------+
| email          | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com      | true                | true                |
| a@foo.computer | false               | true                |
| b@bar.org      | true                | true                |
| !b@bar.org     | false               | true                |
| c@buz.net      | false               | false               |
+----------------+---------------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regexp)

Beschreibung

Gibt den ersten Teilstring in value zurück, der mit dem regulären Ausdruck regexp übereinstimmt. 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

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, regexp)

Beschreibung

Gibt ein Array aller Teilstrings von value zurück, die mit dem regulären Ausdruck regexp übereinstimmen.

Die Funktion REGEXP_EXTRACT_ALL gibt ausschließlich nicht überlappende Übereinstimmungen zurück. Wenn Sie beispielsweise diese Funktion verwenden, um ana aus banana zu extrahieren, wird nur ein Teilstring zurückgegeben, nicht zwei.

Rückgabetyp

Ein ARRAY entweder aus 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, regexp, replacement)

Beschreibung

Gibt einen STRING zurück, in dem alle Teilstrings von value, die mit dem regulären regexp-Ausdruck übereinstimmen, durch replacement ersetzt werden.

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

Wenn Sie dem regulären Ausdruck einen umgekehrten Schrägstrich (Backslash) hinzufügen möchten, müssen Sie ein Escapezeichen voranstellen. Beispiel: SELECT REGEXP_REPLACE('abc', 'b(.)', 'X\\1'); gibt aXc zurück. Sie können auch Rohstrings verwenden, um eine Escape-Ebene zu entfernen, zum Beispiel SELECT REGEXP_REPLACE('abc', 'b(.)', r'X\1');.

Durch die Funktion REGEXP_REPLACE werden ausschließlich nicht überlappende Übereinstimmungen ersetzt. Beispielsweise führt das Ersetzen von ana in banana zu nur einer Ersetzung, nicht zwei.

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

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 alle Vorkommen von from_value durch to_value in original_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 STRING- oder BYTES-Wert zurück, der aus Wiederholungen von original_value besteht. Der Parameter repetitions gibt die Anzahl der Wiederholungen von original_value an. Gibt NULL zurück, wenn entweder original_value oder repetitions gleich 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 den umgekehrten Wert der STRING- oder BYTES-Eingabe 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 STRING- oder BYTES-Wert zurück, der aus dem original_value mit angehängtem pattern besteht. Der Parameter return_length ist ein INT64-Wert, der die Länge des zurückgegebenen Werts angibt. Wenn für original_value der Typ BYTES gilt, dann ist return_length die Anzahl der Byte. Wenn für original_value der Typ STRING gilt, dann ist return_length die Anzahl der Zeichen.

Der Standardwert von pattern ist ein Leerzeichen.

original_value und pattern müssen denselben Datentyp haben.

Wenn return_length kleiner oder gleich der Länge von original_value ist, gibt diese Funktion den Wert von original_value zurück, gekürzt auf den Wert von return_length. Beispiel: RPAD('hello world', 7); gibt 'hello w' zurück.

Wenn original_value, return_length oder pattern den Wert NULL hat, 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 | pattern | 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 | pattern | 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 jedoch 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 BYTES-Sequenz in STRING um. Alle ungültigen UTF-8-Zeichen werden durch das Unicode-Ersatzzeichen U+FFFD ersetzt.

Rückgabetyp

STRING

Beispiele

Diese 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 Arguments delimiter.

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

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

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

Das Aufteilen eines leeren STRING gibt ein ARRAY aus einem einzelnen leeren STRING zurück.

Rückgabetyp

ARRAY vom Typ STRING oder ARRAY vom Typ BYTES.

Beispiele

WITH letters AS
  (SELECT '' as letter_group
  UNION ALL
  SELECT 'a' as letter_group
  UNION ALL
  SELECT 'b c d' as letter_group)

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

+----------------------+
| example              |
+----------------------+
| []                   |
| [a]                  |
| [b, c, d]            |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

Beschreibung

Verwendet zwei STRING- oder BYTES-Werte. Gibt TRUE zurück, wenn der zweite Wert ein Präfix des ersten Werts 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(value1, value2)

Beschreibung

Verwendet zwei STRING- oder BYTES-Werte. Gibt den 1-basierten Index des ersten Vorkommens von value2 innerhalb von value1 zurück. Gibt 0 zurück, wenn value2 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 angegebenen STRING- oder BYTES-Werts zurück. Das Argument position ist eine Ganzzahl, die die Startposition des Teilstrings angibt, wobei Position = 1 das erste Zeichen oder Byte festlegt. Das Argument length ist die maximale Anzahl an Zeichen für STRING-Argumente oder an Byte für BYTES-Argumente.

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

Ist position eine Position vom linken Ende des STRING (position = 0 oder position < -LENGTH(value)), startet die Funktion ab Position = 1. Wenn length die Länge von value überschreitet, gibt die Funktion weniger als die Anzahl der length-Zeichen zurück.

Ist length kleiner als 0, 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 aus BYTES in einen base32-codierten STRING um. Zum Konvertieren eines base32-codierten STRING in BYTES verwenden Sie FROM_BASE32.

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 aus BYTES in einen base64-codierten STRING um. Zum Konvertieren eines base64-codierten STRING in BYTES verwenden Sie FROM_BASE64.

Es gibt mehrere gängige base64-Codierungen, die sich darin unterscheiden, mit welchem Alphabet aus 65 ASCII-Zeichen die 64 Ziffern und das Auffüllen codiert werden. Weitere Informationen finden Sie unter RFC 4648. Diese Funktion fügt einen Abstand hinzu und verwendet das Alphabet [A-Za-z0-9+/=].

Rückgabetyp

STRING

Beispiel

SELECT TO_BASE64(b'\377\340') AS base64_string;

+---------------+
| base64_string |
+---------------+
| /+A=          |
+---------------+

Wenn Sie mit einer Codierung mit einem anderen base64-Standard arbeiten möchten, müssen Sie unter Umständen TO_BASE64 mit der Funktion REPLACE erstellen. Die URL-sichere und dateinamen-sichere base64url-Codierung, die häufig von Webprogrammierern verwendet wird, verwendet -_= statt +/= als letzte Zeichen. Um einen base64url-codierten String zu codieren, ersetzen Sie + und / durch - bzw. _.

SELECT REPLACE(REPLACE(TO_BASE64(b'\377\340'), '+', '-'), '/', '_') as websafe_base64;

+----------------+
| websafe_base64 |
+----------------+
| _-A=           |
+----------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

Beschreibung

Gibt für einen Wert ein INT64-Array zurück.

  • Wenn value ein STRING ist, stellt jedes Element im zurückgegebenen Array einen Codepunkt dar. Jeder Codepunkt liegt im Bereich von [0, 0xD7FF] und [0xE000, 0x10FFFF].
  • Wenn value ein BYTES-Wert ist, stellt jedes Element im Array einen erweiterten ASCII-Zeichenwert im Bereich von [0, 255] dar.

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

Rückgabetyp

ARRAY von INT64

Beispiele

Das folgende Beispiel ruft die Codepunkte für jedes Element in einem Array von Wörtern ab.

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

Im folgenden Beispiel wird der Unterschied zwischen einem BYTES-Ergebnis und einem STRING-Ergebnis veranschaulicht.

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 von zwei Elementen zurück, während die STRING-Version ein Array aus einem einzelnen Element zurückgibt.

TO_HEX

TO_HEX(bytes)

Beschreibung

Wandelt eine Sequenz von BYTES in einen hexadezimalen STRING um. Wandelt jedes Byte im STRING in zwei hexadezimale Zeichen im Bereich (0..9, a..f) um. Zum Konvertieren eines hexadezimal codierten STRING in BYTES verwenden Sie FROM_HEX.

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       |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar                           | 666f6f626172     |
+----------------------------------+------------------+

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. Wenn das erste Argument vom Typ BYTES ist, 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 der ursprüngliche String mit allen alphabetischen Zeichen in Großbuchstaben zurückgegeben. Die Zuordnung zwischen Groß- und Kleinbuchstaben erfolgt nach der Unicode-Zeichendatenbank, ohne dabei sprachspezifische Zuordnungen zu berücksichtigen.

BYTES-Argumente werden 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 in Google-Standard-SQL

Google-Standard-SQL unterstützt die folgenden Funktionen, die JSON-Daten abrufen und transformieren können.

Funktionsübersicht

JSON-Extraktionsfunktionen

Die folgenden Funktionen verwenden doppelte Anführungszeichen, um ungültige JSONPath-Zeichen zu maskieren: "a.b".

Dieses Verhalten entspricht dem ANSI-Standard.

JSON-Funktion Beschreibung Rückgabetyp
JSON_QUERY Extrahiert einen JSON-Wert, z. B. ein Array oder Objekt oder einen skalaren JSON-formatierten Wert, wie einen String, eine Zahl oder einen booleschen Wert. JSON-formatiertes STRING oder JSON
JSON_VALUE Extrahiert einen skalaren Wert. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Entfernt die äußeren Anführungszeichen und maskiert die Werte. Gibt einen SQL-NULL zurück, wenn ein nicht-skalarer Wert ausgewählt ist. STRING
JSON_QUERY_ARRAY Extrahiert ein Array von JSON-Werten, wie Arrays oder Objekte, und skalare Werte im JSON-Format, wie Strings, Zahlen und boolesche Werte. ARRAY<JSON-formatted STRING> oder ARRAY<JSON>
JSON_VALUE_ARRAY Extrahiert ein Array von skalaren Werten. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Entfernt die äußeren Anführungszeichen und maskiert die Werte. Gibt einen SQL-NULL zurück, wenn der ausgewählte Wert kein Array oder kein Array mit skalaren Werten ist. ARRAY<STRING>

Weitere JSON-Funktionen

JSON-Funktion Beschreibung Rückgabetyp
PARSE_JSON Gibt für einen JSON-formatierten String einen JSON-Wert zurück. JSON
TO_JSON Gibt für einen SQL-Wert einen JSON-Wert zurück. JSON
TO_JSON_STRING Gibt für einen JSON-Wert eine JSON-formatierte Stringdarstellung des Werts zurück. JSON-formatierte STRING

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)
JSON_QUERY(json_expr, json_path)

Beschreibung

Extrahiert einen JSON-Wert, z. B. ein Array oder Objekt oder einen skalaren JSON-formatierten Wert, wie einen String, eine Zahl oder einen booleschen Wert. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

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

    Extrahiert einen SQL-NULL, wenn der JSON-formatierte String „null“ auftritt. Beispiel:

    SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
    
  • json_expr: JSON. Beispiel:

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

    Extrahiert einen JSON-null, wenn ein JSON-null gefunden wird.

    SELECT JSON_QUERY(JSON 'null', "$") -- Returns a JSON 'null'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., dass alle Daten analysiert werden.

    SELECT JSON_QUERY('{"a":null}', "$.a"); -- Returns a SQL NULL
    SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL
    
    SELECT JSON_QUERY(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
    SELECT JSON_QUERY(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL
    

Wenn Sie nicht skalare Werte wie Arrays in die Extraktion aufnehmen möchten, verwenden Sie JSON_QUERY. Wenn Sie nur skalare Werte wie Strings, Zahlen und boolesche Werte extrahieren möchten, verwenden Sie JSON_VALUE.

Rückgabetyp

  • json_string_expr: Ein JSON-formatierter STRING
  • json_expr: JSON

Beispiele

Im folgenden Beispiel werden JSON-Daten extrahiert und als JSON zurückgegeben.

SELECT
  JSON_QUERY(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

In den folgenden Beispielen werden JSON-Daten extrahiert und als JSON-formatierte Strings zurückgegeben.

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

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(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;

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

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

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+

JSON_VALUE

JSON_VALUE(json_string_expr[, json_path])
JSON_VALUE(json_expr[, json_path])

Beschreibung

Extrahiert einen skalaren Wert und gibt ihn dann als String zurück. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Entfernt die äußeren Anführungszeichen und maskiert die Rückgabewerte. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., dass alle Daten analysiert werden.

    Wenn json_path einen JSON-null oder einen nicht skalaren Wert zurückgibt (d. h., wenn json_path auf ein Objekt oder ein Array verweist), wird ein NULL-SQL zurückgegeben. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., der gesamte JSON-formatierte String wird analysiert.

Wenn Sie nur skalare Werte wie Strings, Zahlen und boolesche Werte extrahieren möchten, verwenden Sie JSON_VALUE. Wenn Sie nicht skalare Werte wie Arrays in die Extraktion aufnehmen möchten, verwenden Sie JSON_QUERY.

Rückgabetyp

STRING

Beispiele

Im folgenden Beispiel werden JSON-Daten extrahiert und als skalarer Wert zurückgegeben.

SELECT JSON_VALUE(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

Im folgenden Beispiel wird verglichen, wie Ergebnisse für die Funktionen JSON_QUERY und JSON_VALUE zurückgegeben werden.

SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') AS json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') AS scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') AS json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob"   | Jakob       | "6"      | 6          |
+-----------+-------------+----------+------------+
SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

+--------------------+------------+
| json_query         | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL       |
+--------------------+------------+

Falls ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren. Beispiel:

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') AS hello;

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

JSON_QUERY_ARRAY

JSON_QUERY_ARRAY(json_string_expr[, json_path])
JSON_QUERY_ARRAY(json_expr[, json_path])

Beschreibung

Extrahiert ein Array von JSON-Werten, wie Arrays oder Objekte, und skalare Werte im JSON-Format, wie Strings, Zahlen und boolesche Werte. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., dass alle Daten analysiert werden.

Rückgabetyp

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

Beispiele

Damit werden Elemente in JSON in ein Array von JSON-Werten extrahiert:

SELECT JSON_QUERY_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS json_array;

+---------------------------------+
| json_array                      |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

Damit werden die Elemente in einem JSON-formatierten String in ein String-Array extrahiert:

SELECT JSON_QUERY_ARRAY('[1,2,3]') AS string_array;

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

Damit wird ein String-Array extrahiert und in ein ganzzahliges Array umgewandelt:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_QUERY_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

Damit werden Stringwerte in einem JSON-formatierten String in ein Array extrahiert:

-- Doesn't strip the double quotes
SELECT JSON_QUERY_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_VALUE(string_element, '$')
  FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Dadurch werden nur die Elemente im Attribut fruit in ein Array extrahiert:

SELECT JSON_QUERY_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}',
  '$.fruit'
) AS string_array;

+-------------------------------------------------------+
| string_array                                          |
+-------------------------------------------------------+
| [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
+-------------------------------------------------------+

Diese beiden Anweisungen sind gleichwertig:

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

Falls ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren: " " Beispiel:

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

+-----------+
| hello     |
+-----------+
| ["world"] |
+-----------+

In den folgenden Beispielen wird gezeigt, wie ungültige Anfragen und leere Arrays gehandhabt werden:

-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_QUERY_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])
JSON_VALUE_ARRAY(json_expr[, json_path])

Beschreibung

Extrahiert ein Array von skalaren Werten und gibt ein Array mit stringcodierten Werten zurück. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., dass alle Daten analysiert werden.

Rückgabetyp

ARRAY<STRING>

Beispiele

Damit werden Elemente in JSON in ein String-Array extrahiert:

SELECT JSON_VALUE_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Im folgenden Beispiel wird verglichen, wie Ergebnisse für die Funktionen JSON_QUERY_ARRAY und JSON_VALUE_ARRAY zurückgegeben werden.

SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
       JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

Damit werden die Elemente in einem JSON-formatierten String in ein String-Array extrahiert:

-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

Damit wird ein String-Array extrahiert und in ein ganzzahliges Array umgewandelt:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_VALUE_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

Diese beiden Anweisungen sind gleichwertig:

SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Falls ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren: " " Beispiel:

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

+---------+
| hello   |
+---------+
| [world] |
+---------+

In den folgenden Beispielen wird gezeigt, wie ungültige Anfragen und leere Arrays gehandhabt werden:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar objects and a JSON null,
-- then the output is an array of the scalar objects and a SQL NULL.
SELECT JSON_VALUE_ARRAY('["world", null, 1]') AS result;

+------------------+
| result           |
+------------------+
| [world, NULL, 1] |
+------------------+

PARSE_JSON

PARSE_JSON(json_string_expr[, wide_number_mode=>{ 'exact' | 'round' } ])

Beschreibung

Gibt für einen STRING-SQL-Wert einen JSON-SQL-Wert zurück. Der Wert STRING stellt einen JSON-Wert im Stringformat dar.

Diese Funktion unterstützt ein optionales obligatorisch-benanntes Argument mit dem Namen wide_number_mode, das bestimmt, wie Zahlen verarbeitet werden, die nicht in einem JSON-Wert ohne Genauigkeitsverlust gespeichert werden können. Bei Verwendung muss wide_number_mode einen der folgenden Werte enthalten:

  • exact: Akzeptiert nur Zahlen, die ohne Genauigkeitsverlust gespeichert werden können. Wenn eine Zahl auftritt, die nicht ohne Genauigkeitsverlust gespeichert werden kann, gibt die Funktion einen Fehler aus.
  • round: Wenn eine Zahl, die nicht ohne Genauigkeitsverlust gespeichert werden kann, auftritt, versuchen Sie, diese auf eine Zahl zu runden, die ohne Genauigkeitsverlust gespeichert werden kann. Wenn die Zahl nicht gerundet werden kann, gibt die Funktion einen Fehler aus.

Wenn wide_number_mode nicht verwendet wird, enthält die Funktion implizit wide_number_mode=>'exact'. Wenn eine Zahl in einem JSON-Objekt oder Array angezeigt wird, wird das Argument wide_number_mode auf die Zahl im Objekt oder Array angewendet.

Zahlen aus den folgenden Domains können ohne Genauigkeitsverlust in JSON gespeichert werden:

  • 64-Bit-Ganzzahlen mit/ohne Vorzeichen, z. B. INT64
  • FLOAT64

Rückgabetyp

JSON

Beispiele

Im folgenden Beispiel wird ein JSON-formatierter String in JSON konvertiert.

SELECT PARSE_JSON('{"coordinates":[10,20],"id":1}') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+

Die folgenden Abfragen schlagen fehl:

  • Die übergebene Zahl kann nicht ohne Genauigkeitsverlust gespeichert werden.
  • wide_number_mode=>'exact' wird implizit in der ersten Abfrage und explizit in der zweiten Abfrage verwendet.
SELECT PARSE_JSON('{"id":922337203685477580701}') AS json_data; -- fails
SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'exact') AS json_data; -- fails

Die folgende Abfrage rundet die Zahl auf eine Zahl, die in JSON gespeichert werden kann.

SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'round') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"id":9.223372036854776e+20}   |
+--------------------------------+

TO_JSON

TO_JSON(sql_value[, stringify_wide_numbers=>{ TRUE | FALSE } ])

Beschreibung

Gibt für einen SQL-Wert einen JSON-Wert zurück. Der Wert muss ein unterstützter Google Standard-SQL-Datentyp sein. Die von dieser Funktion unterstützten Google SQL-Datentypen und ihre JSON-Codierungen finden Sie hier.

Diese Funktion unterstützt ein optionales obligatorisch-benanntes Argument mit dem Namen stringify_wide_numbers.

  • Wenn dieses Argument TRUE ist, werden numerische Werte außerhalb der Domain vom Typ FLOAT64 als Strings codiert.
  • Wenn dieses Argument nicht verwendet wird oder FALSE ist, werden numerische Werte außerhalb der Domain vom Typ FLOAT64 nicht als Strings codiert, aber als JSON-Nummern gespeichert. Wenn ein numerischer Wert nicht ohne Genauigkeitsverlust in JSON gespeichert werden kann, wird ein Fehler ausgegeben.

Folgende numerische Datentypen werden vom stringify_wide_numbers-Argument beeinflusst:

  • INT64
  • NUMERIC

Wenn einer dieser numerischen Datentypen in einem Containerdatentyp angezeigt wird, z. B. ARRAY oder STRUCT, wird das Argument stringify_wide_numbers auf die numerischen Datentypen im Containerdatentyp angewendet.

Rückgabetyp

Einen JSON-Wert

Beispiele

Im folgenden Beispiel wandelt die Abfrage Zeilen in einer Tabelle in JSON-Werte um.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates) UNION ALL
    (SELECT 3 AS id, [50,60] AS coordinates))
SELECT TO_JSON(t) AS json_objects
FROM CoordinatesTable AS t;

+--------------------------------+
| json_objects                   |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
| {"coordinates":[30,40],"id":2} |
| {"coordinates":[50,60],"id":3} |
+--------------------------------+

Im folgenden Beispiel gibt die Abfrage einen großen numerischen Wert als JSON-String zurück.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>TRUE) as stringify_on

+--------------------+
| stringify_on       |
+--------------------+
| "9007199254740993" |
+--------------------+

Im folgenden Beispiel geben beide Abfragen einen großen numerischen Wert als JSON-Nummer zurück.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>FALSE) as stringify_off
SELECT TO_JSON(9007199254740993) as stringify_off

+------------------+
| stringify_off    |
+------------------+
| 9007199254740993 |
+------------------+

Im folgenden Beispiel werden nur große numerische Werte in JSON-Strings konvertiert.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+---------------------------+
| json_objects              |
+---------------------------+
| {"id":"9007199254740993"} |
| {"id":2}                  |
+---------------------------+

In diesem Beispiel werden die Werte 9007199254740993 (INT64) und 2.1 (FLOAT64) in den gemeinsamen Supertyp FLOAT64 konvertiert, der nicht vom Argument stringify_wide_numbers betroffen ist.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2.1 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+------------------------------+
| json_objects                 |
+------------------------------+
| {"id":9.007199254740992e+15} |
| {"id":2.1}                   |
+------------------------------+

TO_JSON_STRING

TO_JSON_STRING(json_expr)

Beschreibung

Gibt für einen JSON-Ausdruck eine JSON-formatierte Stringdarstellung des Werts aus dem Ausdruck zurück.

Rückgabetyp

Ein JSON-formatierter STRING

Beispiel

JSON in einen JSON-formatierten String konvertieren

SELECT TO_JSON_STRING(JSON '{"id":1, "coordinates":[10,20]}') AS json_string

+--------------------------------+
| json_string                    |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+

JSON-Codierungen

Die folgende Tabelle enthält gängige Codierungen, die verwendet werden, wenn ein SQL-Wert als JSON-Wert mit der Funktion TO_JSON codiert wird.

Von SQL In JSON Beispiele
NULL

null

SQL-Eingabe: NULL
JSON-Ausgabe: null
BOOL Boolesch SQL-Eingabe: TRUE
JSON-Ausgabe: true

SQL-Eingabe: FALSE
JSON-Ausgabe: false
INT64

Zahl oder String

Wenn das Argument stringify_wide_numbers TRUE lautet und der Wert außerhalb der FLOAT64-Domain liegt, wird der Wert als String codiert. Wenn der Wert nicht ohne Genauigkeitsverlust in JSON gespeichert werden kann, schlägt die Funktion fehl. Andernfalls wird der Wert als Zahl codiert.

Wenn stringify_wide_numbers nicht verwendet wird oder FALSE ist, werden numerische Werte außerhalb der FLOAT64-Domain nicht als Strings codiert, sondern als JSON-Nummern gespeichert. Wenn ein numerischer Wert nicht ohne Genauigkeitsverlust in JSON gespeichert werden kann, wird ein Fehler ausgegeben.

SQL-Eingabe: 9007199254740992
JSON-Ausgabe: 9007199254740992

SQL-Eingabe: 9007199254740993
JSON Ausgabe: 9007199254740993

SQL-Eingabe mit stringify_wide_numbers=>TRUE: 9007199254740992
JSON-Ausgabe: 9007199254740992

SQL-Eingabe mit stringify_wide_numbers=>TRUE: 9007199254740993
JSON-Ausgabe: "9007199254740993"
NUMERIC

Zahl oder String

Wenn das Argument stringify_wide_numbers TRUE lautet und der Wert außerhalb der FLOAT64-Domain liegt, wird er als String codiert. Andernfalls wird er als Zahl codiert.

SQL-Eingabe: -1
JSON-Ausgabe: -1

SQL-Eingabe: 0
JSON Ausgabe: 0

SQL-Eingabe: 9007199254740993
JSON-Ausgabe: 9007199254740993

SQL-Eingabe: 123.56
JSON-Ausgabe: 123.56

SQL-Eingabe mit stringify_wide_numbers=>TRUE: 9007199254740993
JSON-Ausgabe: "9007199254740993"

SQL-Eingabe mit stringify_wide_numbers=>TRUE: 123.56
JSON Ausgabe: 123.56
FLOAT64

Zahl oder String

+/-inf und NaN sind als Infinity, -Infinity und NaN codiert. Andernfalls wird dieser Wert als Zahl codiert.

SQL-Eingabe: 1.0
JSON-Ausgabe: 1

SQL-Eingabe: 9007199254740993
JSON-Ausgabe: 9007199254740993

SQL-Eingabe: "+inf"
JSON-Ausgabe: "Infinity"

SQL-Eingabe: "-inf"
JSON-Ausgabe: "-Infinity"

SQL-Eingabe: "NaN"
JSON-Ausgabe: "NaN"
STRING

String

Codiert als String, codiert gemäß dem JSON-Standard. Insbesondere ", \ und die Steuerelemente von U+0000 bis U+001F werden maskiert.

SQL-Eingabe: "abc"
JSON-Ausgabe: "abc"

SQL-Eingabe: "\"abc\""
JSON-Ausgabe: "\"abc\""
BYTES

String

Verwendet die Base64-Datencodierung gemäß RFC 4648.

SQL-Eingabe: b"Google"
JSON-Ausgabe: "R29vZ2xl"
DATE String SQL-Eingabe: DATE '2017-03-06'
JSON-Ausgabe: "2017-03-06"
TIMESTAMP

String

Codiert als ISO 8601-Datum und -Uhrzeit, wobei T das Datum von der Uhrzeit trennt und Z (Zulu/UTC) die Zeitzone darstellt.

SQL-Eingabe: TIMESTAMP '2017-03-06 12:34:56.789012'
JSON-Ausgabe: "2017-03-06T12:34:56.789012Z"
ARRAY

array

Kann null oder mehr Elemente enthalten.

SQL-Eingabe: ["red", "blue", "green"]
JSON-Ausgabe: ["red", "blue", "green"]

SQL-Eingabe: [1, 2, 3]
JSON-Ausgabe: [1, 2, 3]
STRUCT

Objekt

Das Objekt kann null oder mehr Schlüssel/Wert-Paare enthalten. Jeder Wert wird entsprechend seinem Typ formatiert.

Bei TO_JSON ist ein Feld im Ausgabestring enthalten. Duplikate dieses Felds werden weggelassen.

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.

SQL-Eingabe: STRUCT(12 AS purchases, TRUE AS inStock)
JSON-Ausgabe: {"inStock": true,"purchases":12}

JSONPath

Die meisten JSON-Funktionen übergeben einen Parameter json_string_expr oder json_path. Der Parameter json_string_expr übergibt einen JSON-formatierten String und der Parameter json_path gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String erhalten möchten.

Der Parameter json_string_expr muss ein JSON-String im folgenden Format sein:

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

Sie erstellen den Parameter json_path im JSONPath-Format. Gemäß diesem Format muss der Parameter mit einem $-Zeichen beginnen, das sich auf die äußerste Ebene des JSON-formatierten Strings bezieht. Sie können untergeordnete Werte mithilfe von Punkten angeben. Wenn das JSON-Objekt ein Array ist, können Sie Klammern verwenden, um den Arrayindex anzugeben. Wenn die Schlüssel $, Punkte oder Klammern enthalten, beachten Sie die Informationen zu den einzelnen JSON-Funktionen zur Maskierung dieser Zeichen.

JSONPath Beschreibung Beispiel Ergebnis mit dem obigen json_string_expr
$ Root-Objekt oder Element "$" {"class":{"students":[{"name":"Jane"}]}}
. Untergeordneter Operator "$.class.students" [{"name":"Jane"}]
[] Tiefgestellt-Operator "$.class.students[0]" {"name":"Jane"}

Eine JSON-Funktion gibt NULL zurück, wenn der Parameter json_path mit keinem Wert in json_string_expr übereinstimmt. Wenn der ausgewählte Wert für eine skalare Funktion nicht skalar ist, z. B. ein Objekt oder ein Array, gibt die Funktion NULL zurück.

Wenn der JSONPath-Wert ungültig ist, löst die Funktion einen Fehler aus.

Arrayfunktionen in Google Standard-SQL

ARRAY

ARRAY(subquery)

Beschreibung

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

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

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

Einschränkungen

  • Unterabfragen sind nicht geordnet. Deshalb wird von den Elementen des Ausgabe-ARRAY die Reihenfolge in der Quelltabelle für die Unterabfrage nicht zwingend beibehalten. Wenn die Unterabfrage jedoch die Klausel ORDER BY enthält, gibt die Funktion ARRAY ein ARRAY zurück, das diese Klausel beachtet.
  • Wenn bei der Unterabfrage mehr als eine Spalte zurückgegeben wird, gibt die ARRAY-Funktion einen Fehler zurück.
  • Wenn bei der Unterabfrage eine Spalte des Typs ARRAY oder Zeilen des Typs ARRAY zurückgegeben werden, gibt die Funktion ARRAY einen Fehler zurück: Google-Standard-SQL unterstützt keine ARRAY-Objekte mit Elementen des Typs 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 erstellen möchten, ändern Sie die Unterabfrage so, dass SELECT AS STRUCT verwendet wird. Daraufhin gibt die ARRAY-Funktion ein ARRAY von STRUCTs zurück. Das ARRAY enthält ein STRUCT für jede Zeile in der Unterabfrage und jedes dieser STRUCTs enthält wiederum ein Feld für jede Spalte in dieser Zeile.

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

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

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

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

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

ARRAY_CONCAT

ARRAY_CONCAT(array_expression[, ...])

Beschreibung

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

Die Funktion gibt NULL zurück, wenn eines der Eingabeargumente NULL ist.

Rückgabetyp

ARRAY

Beispiele

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

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

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

Beschreibung

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

Rückgabetyp

INT64

Beispiele

WITH items AS
  (SELECT ["coffee", NULL, "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)
SELECT ARRAY_TO_STRING(list, ', ', 'NULL'), ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

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

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])

Beschreibung

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

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

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

Beispiele

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

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

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

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

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

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

Beschreibung

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

Die Funktion GENERATE_ARRAY akzeptiert folgende Datentypen als Eingaben:

  • INT64
  • NUMERIC
  • FLOAT64

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

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

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

Rückgabedatentyp

ARRAY

Beispiele

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

SELECT GENERATE_ARRAY(1, 5) AS example_array;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Im folgenden Beispiel werden mehrere Arrays zurückgegeben.

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

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

GENERATE_DATE_ARRAY

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

Beschreibung

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

Die Funktion GENERATE_DATE_ARRAY akzeptiert folgende Datentypen als Eingaben:

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

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

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

Rückgabedatentyp

Ein ARRAY mit mindestens 0 DATE-Werten.

Beispiele

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ARRAY_IS_DISTINCT

ARRAY_IS_DISTINCT(value)

Beschreibung

Gibt „true“ zurück, wenn das Array keine wiederkehrenden Elemente enthält und dieselbe Gleichheitsvergleichslogik wie SELECT DISTINCT verwendet.

Rückgabetyp

BOOL

Beispiele

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [1, 1, 1] AS arr UNION ALL
  SELECT [1, 2, NULL] AS arr UNION ALL
  SELECT [1, 1, NULL] AS arr UNION ALL
  SELECT [1, NULL, NULL] AS arr UNION ALL
  SELECT [] AS arr UNION ALL
  SELECT CAST(NULL AS ARRAY<INT64>) AS arr
)
SELECT
  arr,
  ARRAY_IS_DISTINCT(arr) as is_distinct
FROM example;

+-----------------+-------------+
| arr             | is_distinct |
+-----------------+-------------+
| [1, 2, 3]       | true        |
| [1, 1, 1]       | false       |
| [1, 2, NULL]    | true        |
| [1, 1, NULL]    | false       |
| [1, NULL, NULL] | false       |
| []              | true        |
| NULL            | NULL        |
+-----------------+-------------+

DATE-Funktionen in Google Standard-SQL

Google-Standard-SQL 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. Klammern sind optional, wenn sie ohne Argumente aufgerufen werden.

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

Wenn der Parameter time_zone als NULL ausgewertet wird, gibt diese Funktion NULL zurück.

Rückgabedatentyp

DATE

Beispiel

SELECT CURRENT_DATE() AS the_date;

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

Wenn eine Spalte mit dem Namen current_date vorhanden ist, sind der Spaltenname und der Funktionsaufruf ohne Klammern nicht eindeutig. Fügen Sie Klammern hinzu, um den Funktionsaufruf zu gewährleisten. Um den Spaltennamen sicherzustellen, geben Sie für ihn seine Bereichsvariable an. Die folgende Abfrage wählt beispielsweise die Funktion in der Spalte the_date und die Tabellenspalte in der Spalte current_date aus.

WITH t AS (SELECT 'column value' AS `current_date`)
SELECT current_date() AS the_date, t.current_date FROM t;

+------------+--------------+
| the_date   | current_date |
+------------+--------------+
| 2016-12-25 | column value |
+------------+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

Beschreibung

Gibt den Wert zurück, der dem angegebenen Datumsteil entspricht. part muss einer der folgenden Typen sein:

  • 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.
  • ISOWEEK: gibt die ISO 8601-Wochennummer von date_expression zurück. ISOWEEK-Wochen beginnen am Montag. Rückgabewerte liegen im Bereich [1, 53]. Die erste ISOWEEK-Woche eines ISO-Jahres beginnt am Montag vor dem ersten Donnerstag des gregorianischen Kalenderjahres.
  • MONTH
  • QUARTER: gibt Werte im Bereich [1,4] zurück.
  • YEAR
  • ISOYEAR: gibt das Kalenderjahr mit ISO 8601-Wochennummerierung zurück. Das entspricht dem gregorianischen Kalenderjahr mit dem Donnerstag der Woche, zu der date_expression gehört.

Rückgabedatentyp

INT64

Beispiele

Im folgenden Beispiel gibt EXTRACT einen Wert zurück, der dem Datumsteil 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 Daten 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    |
+------------+---------+---------+------+------+

DATE

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

Beschreibung

  1. Erstellt einen DATE-Wert aus INT64-Werten, die das Jahr, den Monat und den Tag darstellen.
  2. Extrahiert DATE aus einem TIMESTAMP-Ausdruck. Ein optionaler Parameter zur Angabe einer Zeitzone wird unterstützt. Wenn keine Zeitzone angegeben ist, wird die Standardzeitzone "America/Los_Angeles" 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_expression date_part)

Beschreibung

Fügt einem DATE ein bestimmtes Zeitintervall hinzu.

DATE_ADD unterstützt die folgenden date_part-Werte:

  • DAY
  • WEEK. Entspricht sieben 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 das Ergebnis-Datum das letzte Datum dieses 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_expression date_part)

Beschreibung

Subtrahiert ein bestimmtes Zeitintervall von einem DATE.

DATE_SUB unterstützt die folgenden date_part-Werte:

  • DAY
  • WEEK. Entspricht sieben 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 das Ergebnis-Datum das letzte Datum dieses 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_a, date_expression_b, date_part)

Beschreibung

Gibt die Anzahl der gesamten angegebenen date_part-Intervalle zwischen zwei DATE-Objekten (date_expression_adate_expression_b) zurück. Wenn der erste DATE vor dem zweiten liegt, ist die Ausgabe negativ.

DATE_DIFF unterstützt die folgenden date_part-Werte:

  • DAY
  • WEEK: Dieser Datumsteil beginnt mit Sonntag.
  • ISOWEEK: Verwendet Wochengrenzen nach ISO 8601. ISO-Wochen beginnen mit Montag.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: Verwendet die Jahresgrenze gemäß der ISO 8601-Wochennummerierung. 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 DATE_DIFF die Anzahl der Datumsteilgrenzen in diesem Datumsbereich zählt. 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 von 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 dieser Datumsteil Wochen verwendet, die mit Sonntag beginnen. DATE_DIFF mit dem Datumsteil ISOWEEK gibt 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', ISOWEEK) AS isoweek_diff;

+-----------+--------------+
| week_diff | isoweek_diff |
+-----------+--------------+
| 0         | 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
  • ISOWEEK: kürzt date_expression auf die vorhergehende Wochengrenze nach ISO 8601. ISOWEEKs beginnen mit dem 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 Montag davor gekürzt.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: kürzt date_expression auf die vorhergehende Jahresgrenze gemäß der ISO 8601-Wochennummerierung. 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 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 dem 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 dem 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 sind die Formatelemente aufgelistet, die von dieser Funktion unterstützt werden.

Rückgabedatentyp

STRING

Beispiele

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

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Beschreibung

Wandelt eine Stringdarstellung des Datums in ein DATE-Objekt um.

format_string enthält die Formatelemente, die definieren, wie date_string formatiert ist. Jedem Element in date_string muss ein Element in format_string entsprechen. Die Position jedes Elements in format_string muss mit der Position der einzelnen Elemente in date_string übereinstimmen.

-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")

-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")

-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")

Der Formatstring unterstützt die meisten Formatelemente vollständig, mit Ausnahme von %a, %A, %g, %G, %j, %u und %U, %V, %w und %W.

Beachten Sie bei Verwendung von PARSE_DATE Folgendes:

  • Nicht angegebene Felder: Jedes nicht angegebene Feld wird ab 1970-01-01 initialisiert.
  • Groß-/Kleinschreibung nicht berücksichtigend. Bei Namen wie Monday, February usw. wird die Groß-/Kleinschreibung nicht berücksichtigt.
  • 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 mehr) Formatelemente überlappende Informationen enthalten (beispielsweise beeinflussen %F und %Y beide das Jahr), überschreibt im Allgemeinen das letzte Format alle vorherigen Formate.

Rückgabedatentyp

DATE

Beispiele

In diesem Beispiel wird ein als MM/DD/YY formatierter String in ein DATE-Objekt konvertiert:

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

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

In diesem Beispiel wird ein als YYYYMMDD formatierter String in ein DATE-Objekt konvertiert:

SELECT PARSE_DATE("%Y%m%d", "20081225") AS parsed;

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

UNIX_DATE

UNIX_DATE(date_expression)

Beschreibung

Gibt die Anzahl der Tage seit dem 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 Beispiel
%A Der vollständige Wochentagname. Mittwoch
%a Der abgekürzte Wochentagname. Mi
%B Der vollständige Monatsname. Januar
%b oder %h Der abgekürzte Monatsname. Jan
%C Das Jahrhundert (ein Jahr g