Funktionen und Operatoren in Legacy-SQL

In diesem Dokument werden alte SQL-Funktionen und -Operatoren beschrieben. Die bevorzugte Abfragesyntax für BigQuery ist Standard-SQL. Informationen zu Standard-SQL finden Sie unter Standard-SQL-Funktionen und -Operatoren.

Unterstützte Funktionen und Operatoren

Die meisten SELECT-Anweisungsklauseln unterstützen Funktionen. Felder, die in einer Funktion referenziert sind, brauchen nicht in einer SELECT-Klausel aufgelistet zu werden. Daher ist die folgende Abfrage gültig, obwohl das Feld clicks nicht direkt angezeigt wird:

#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;
Aggregatfunktionen
AVG() Gibt den Durchschnittswert einer Gruppe von Zeilen zurück...
BIT_AND() Gibt das Ergebnis einer bitweisen AND-Operation zurück...
BIT_OR() Gibt das Ergebnis einer bitweisen OR-Operation zurück...
BIT_XOR() Gibt das Ergebnis einer bitweisen XOR-Operation zurück...
CORR() Gibt den Pearson-Korrelationskoeffizienten einer Reihe von Paaren zurück.
COUNT() Gibt die Gesamtanzahl an Werten zurück...
COUNT([DISTINCT]) Gibt die Gesamtanzahl an Nicht-NULL-Werten zurück...
COVAR_POP() Berechnet die Populationskovarianz der Werte...
COVAR_SAMP() Berechnet die Stichprobenkovarianz der Werte...
EXACT_COUNT_DISTINCT() Gibt die exakte Anzahl an unterschiedlichen Nicht-NULL-Werten für das angegebene Feld zurück.
FIRST() Gibt den ersten sequenziellen Wert im Bereich der Funktion zurück.
GROUP_CONCAT() Verkettet mehrere Strings zu einem String...
GROUP_CONCAT_UNQUOTED() Verkettet mehrere Strings zu einem String ... es werden keine Anführungszeichen hinzugefügt...
LAST() Gibt den letzten sequenziellen Wert zurück...
MAX() Gibt den Höchstwert zurück...
MIN() Gibt den Mindestwert zurück...
NEST() Führt alle Werte im aktuellen Aggregationsbereich zu einem wiederholten Feld zusammen.
NTH() Gibt den n-ten sequenziellen Wert zurück...
QUANTILES() Berechnet den ungefähren Mindestwert, Höchstwert und die Quantile...
STDDEV() Gibt die Standardabweichung zurück...
STDDEV_POP() Berechnet die Standardabweichung der Population...
STDDEV_SAMP() Berechnet die Standardabweichung der Stichprobe...
SUM() Gibt die Gesamtsumme der Werte zurück...
TOP() ... COUNT(*) Gibt die höchsten max_records-Datensätze nach Häufigkeit zurück.
UNIQUE() Gibt den Satz einzigartiger Nicht-NULL-Werte zurück...
VARIANCE() Berechnet die Varianz der Werte...
VAR_POP() Berechnet die Populationsvarianz der Werte...
VAR_SAMP() Berechnet die Stichprobenvarianz der Werte...
Arithmetische Operatoren
+ Addition
- Subtraktion
* Multiplikation
/ Division
% Modulo
Bitweise Funktionen
& Bitweises AND
| Bitweises OR
^ Bitweises XOR
<< Bitweise Linksverschiebung
>> Bitweise Rechtsverschiebung
~ Bitweises NOT
BIT_COUNT() Gibt die Anzahl der Bit zurück...
Umwandlungsfunktionen
BOOLEAN() In boolesche Werte umwandeln.
BYTES() In Byte umwandeln.
CAST(expr AS type) expr in eine Variable des Typs type umwandeln.
FLOAT() In Double umwandeln.
HEX_STRING() In Hexadezimalstring umwandeln.
INTEGER() In Ganzzahl umwandeln.
STRING() In String umwandeln.
Vergleichsfunktionen
expr1 = expr2 Gibt true zurück, wenn die Ausdrücke gleich sind.
expr1 != expr2
expr1 <> expr2
Gibt true zurück, wenn die Ausdrücke nicht gleich sind.
expr1 > expr2 Gibt true zurück, wenn expr1 größer als expr2 ist.
expr1 < expr2 Gibt true zurück, wenn expr1 kleiner als expr2 ist.
expr1 >= expr2 Gibt true zurück, wenn expr1 größer oder gleich expr2 ist.
expr1 <= expr2 Gibt true zurück, wenn expr1 kleiner oder gleich expr2 ist.
expr1 BETWEEN expr2 AND expr3 Gibt true zurück, wenn der Wert von expr1 im Bereich von expr2 bis expr3 liegt.
expr IS NULL Gibt true zurück, wenn expr NULL ist.
expr IN() Gibt true zurück, wenn expr dem Wert von expr1, expr2 oder einem anderen Wert in den Klammern entspricht.
COALESCE() Gibt das erste Argument zurück, das nicht NULL ist.
GREATEST() Gibt den größten Parameter numeric_expr zurück.
IFNULL() Gibt das Argument zurück, wenn es nicht null ist.
IS_INF() Gibt true zurück, wenn der Wert positiv oder negativ unendlich ist.
IS_NAN() Gibt true zurück, wenn das Argument NaN ist.
IS_EXPLICITLY_DEFINED() Verworfen: Verwenden Sie stattdessen expr IS NOT NULL.
LEAST() Gibt den kleinsten Argumentparameter numeric_expr zurück.
NVL() Wenn expr nicht null ist, wird expr zurückgegeben, ansonsten null_default.
Funktionen für Datum und Uhrzeit
CURRENT_DATE() Gibt das aktuelle Datum im Format %Y-%m-%d zurück.
CURRENT_TIME() Gibt die aktuelle Uhrzeit des Servers im Format %H:%M:%S zurück.
CURRENT_TIMESTAMP() Gibt die aktuelle Uhrzeit des Servers im Format %Y-%m-%d %H:%M:%S zurück.
DATE() Gibt das Datum im Format %Y-%m-%d zurück.
DATE_ADD() Fügt das angegebene Intervall zu einem TIMESTAMP-Datentyp hinzu.
DATEDIFF() Gibt die Anzahl von Tagen zwischen zwei TIMESTAMP-Datentypen zurück.
DAY() Gibt den Tag des Monats als Ganzzahl zwischen 1 und 31 zurück.
DAYOFWEEK() Gibt den Wochentag als Ganzzahl zwischen 1 (Sonntag) und 7 (Samstag) zurück.
DAYOFYEAR() Gibt den Tag des Jahres als Ganzzahl zwischen 1 und 366 an.
FORMAT_UTC_USEC() Gibt einen UNIX-Zeitstempel im Format YYYY-MM-DD HH:MM:SS.uuuuuu zurück.
HOUR() Gibt die Stunde eines TIMESTAMP als Ganzzahl zwischen 0 und 23 zurück.
MINUTE() Gibt die Minuten eines TIMESTAMP als Ganzzahl zwischen 0 und 59 zurück.
MONTH() Gibt den Monat eines TIMESTAMP als Ganzzahl zwischen 0 und 12 zurück.
MSEC_TO_TIMESTAMP() Verwandelt einen UNIX-Zeitstempel in einen TIMESTAMP.
NOW() Gibt den aktuellen UNIX-Zeitstempel in Mikrosekunden zurück.
PARSE_UTC_USEC() Verwandelt einen Datumsstring in einen UNIX-Zeitstempel in Mikrosekunden.
QUARTER() Gibt das Quartal eines TIMESTAMP als Ganzzahl zwischen 1 und 4 zurück.
SEC_TO_TIMESTAMP() Verwandelt einen UNIX-Zeitstempel in Sekunden in einen TIMESTAMP.
SECOND() Gibt die Sekunden eines TIMESTAMP als Ganzzahl zwischen 0 und 59 zurück.
STRFTIME_UTC_USEC() Gibt einen Datumsstring im Format date_format_str zurück.
TIME() Gibt einen TIMESTAMP im Format %H:%M:%S zurück.
TIMESTAMP() Verwandelt einen Datumsstring in einen TIMESTAMP.
TIMESTAMP_TO_MSEC() Verwandelt einen TIMESTAMP in einen UNIX-Zeitstempel in Millisekunden.
TIMESTAMP_TO_SEC() Verwandelt einen TIMESTAMP in einen UNIX-Zeitstempel in Sekunden.
TIMESTAMP_TO_USEC() Verwandelt einen TIMESTAMP in einen UNIX-Zeitstempel in Mikrosekunden.
USEC_TO_TIMESTAMP() Verwandelt einen UNIX-Zeitstempel in Mikrosekunden in einen TIMESTAMP.
UTC_USEC_TO_DAY() Verschiebt einen UNIX-Zeitstempel in Mikrosekunden an den Beginn des Tages, an dem er stattgefunden hat.
UTC_USEC_TO_HOUR() Verschiebt einen UNIX-Zeitstempel in Mikrosekunden an den Beginn der Stunde, in der er stattgefunden hat.
UTC_USEC_TO_MONTH() Verschiebt einen UNIX-Zeitstempel in Mikrosekunden an den Beginn des Monats, in dem er stattgefunden hat.
UTC_USEC_TO_WEEK() Gibt einen UNIX-Zeitstempel in Mikrosekunden zurück, der für einen Wochentag steht.
UTC_USEC_TO_YEAR() Gibt einen UNIX-Zeitstempel in Mikrosekunden zurück, der für das Jahr steht.
WEEK() Gibt die Woche eines TIMESTAMP als Ganzzahl zwischen 1 und 53 zurück.
YEAR() Gibt das Jahr eines TIMESTAMP zurück.
IP-Funktionen
FORMAT_IP() Wandelt die 32 niedrigstwertigsten Bit von integer_value in einen menschenlesbaren IPv4-Adressstring um.
PARSE_IP() Wandelt einen String, der für eine IPv4-Adresse steht, in einen vorzeichenlosen Ganzzahlwert um.
FORMAT_PACKED_IP() Gibt eine menschenlesbare IP-Adresse im Format 10.1.5.23 oder 2620:0:1009:1:216:36ff:feef:3f zurück.
PARSE_PACKED_IP() Gibt eine IP-Adresse als BYTES zurück.
JSON-Funktionen
JSON_EXTRACT() Wählt einen Wert anhand des JSONPath-Ausdrucks aus und gibt einen JSON-String zurück.
JSON_EXTRACT_SCALAR() Wählt einen Wert anhand des JSONPath-Ausdrucks aus und gibt einen JSON-Skalar zurück.
Logische Operatoren
expr AND expr Gibt true zurück, wenn beide Ausdrücke wahr sind.
expr OR expr Gibt true zurück, wenn einer oder beide Ausdrücke wahr sind.
NOT expr Gibt true zurück, wenn der Ausdruck falsch ist.
Mathematische Funktionen
ABS() Gibt den absoluten Wert des Arguments zurück.
ACOS() Gibt den Arkuskosinus des Arguments zurück.
ACOSH() Gibt den hyperbolischen Arkuskosinus des Arguments zurück.
ASIN() Gibt den Arkussinus des Arguments zurück.
ASINH() Gibt den hyperbolischen Arkussinus des Arguments zurück.
ATAN() Gibt den Arkustangens des Arguments zurück.
ATANH() Gibt den hyperbolischen Arkustangens des Arguments zurück.
ATAN2() Gibt den Arkustangens der beiden Argumente zurück.
CEIL() Rundet das Argument auf die nächste ganze Zahl auf und gibt den gerundeten Wert zurück.
COS() Gibt den Kosinus des Arguments zurück.
COSH() Gibt den hyperbolischen Kosinus des Arguments zurück.
DEGREES() Wandelt von Radianten in Grad um.
EXP() Gibt die e. Potenz des Arguments zurück.
FLOOR() Rundet das Argument auf die nächste ganze Zahl ab.
LN()
LOG()
Gibt den natürlichen Logarithmus des Arguments zurück.
LOG2() Gibt den Logarithmus zur Basis 2 des Arguments zurück.
LOG10() Gibt den Logarithmus zur Basis 10 des Arguments zurück.
PI() Gibt die Konstante π zurück.
POW() Gibt das erste Argument zur Potenz des zweiten Arguments zurück.
RADIANS() Wandelt von Grad in Radianten um.
RAND() Gibt einen zufälligen Float-Wert im Bereich 0,0 <= Wert < 1,0 zurück.
ROUND() Rundet das Argument auf die nächste ganze Zahle auf oder ab.
SIN() Gibt den Sinus des Arguments zurück.
SINH() Gibt den hyperbolischen Sinus des Arguments zurück.
SQRT() Gibt die Quadratwurzel des Ausdrucks zurück.
TAN() Gibt den Tangens des Arguments zurück.
TANH() Gibt den hyperbolischen Tangens des Arguments zurück.
Funktionen für reguläre Ausdrücke
REGEXP_MATCH() Gibt "true" zurück, wenn das Argument mit dem regulären Ausdruck übereinstimmt.
REGEXP_EXTRACT() Gibt den Teil des Arguments zurück, der mit der Erfassungsgruppe innerhalb des regulären Ausdrucks übereinstimmt.
REGEXP_REPLACE() Ersetzt einen Teilstring, der mit einem regulären Ausdruck übereinstimmt.
Stringfunktionen
CONCAT() Gibt die Verkettung von zwei oder mehr Strings oder NULL zurück, wenn einer der Werte NULL ist.
expr CONTAINS 'str' Gibt true zurück, wenn expr das angegebene Stringargument enthält.
INSTR() Gibt den auf Eins basierenden Index des ersten Vorkommens eines Strings zurück.
LEFT() Gibt die Zeichen zurück, die im String ganz links stehen.
LENGTH() Gibt die Länge des Strings zurück.
LOWER() Gibt den ursprünglichen String mit allen Zeichen in Kleinbuchstaben zurück.
LPAD() Fügt Zeichen links neben einem String ein.
LTRIM() Löscht Zeichen auf der linken Seite eines Strings.
REPLACE() Ersetzt alle Vorkommnisse eines Teilstrings.
RIGHT() Gibt die Zeichen zurück, die im String ganz rechts stehen.
RPAD() Fügt Zeichen rechts neben einem String ein.
RTRIM() Entfernt überflüssige Zeichen rechts neben einem String.
SPLIT() Teilt einen String in wiederholte Teilstrings auf.
SUBSTR() Gibt einen Teilstring zurück...
UPPER() Gibt den Originalstring mit allen Zeichen in Großbuchstaben zurück.
Tabellen-Platzhalterfunktionen
TABLE_DATE_RANGE() Führt Abfragen auf mehrere Tagestabellen durch, die einen bestimmten Datumsbereich abdecken.
TABLE_DATE_RANGE_STRICT() Führt Abfragen auf mehrere Tagestabellen durch, die einen bestimmten Datumsbereich abdecken; ohne fehlende Daten.
TABLE_QUERY() Fragt Tabellen ab, deren Namen mit einem bestimmten Prädikat übereinstimmen.
URL-Funktionen
HOST() Gibt nach der Angabe einer URL den Hostnamen als String zurück.
DOMAIN() Gibt nach der Angabe einer URL die Domain als String zurück.
TLD() Gibt nach der Angabe einer URL die Top-Level-Domain und jede beliebige Länderdomain in der URL zurück.
Fensterfunktionen
AVG()
COUNT(*)
COUNT([DISTINCT])
MAX()
MIN()
STDDEV()
SUM()
Die gleichen Operationen wie die zugehörigen Aggregatfunktionen; sie werden jedoch für ein von der OVER-Klausel definiertes Fenster berechnet.
CUME_DIST() Gibt einen Double-Wert zurück, der die kumulative Verteilung eines Werts in einer Gruppe von Werten angibt...
DENSE_RANK() Gibt den Ganzzahlrang eines Werts in einer Gruppe von Werten an.
FIRST_VALUE() Gibt den ersten Wert des angegebenen Felds im Fenster an.
LAG() Ermöglicht das Lesen der Daten aus einer vorherigen Zeile in einem Fenster.
LAST_VALUE() Gibt den letzten Wert des angegebenen Felds im Fenster zurück.
LEAD() Ermöglicht das Lesen der Daten aus einer folgenden Zeile innerhalb eines Fensters.
NTH_VALUE() Gibt den Wert von <expr> an der Position <n> im Fensterrahmen zurück...
NTILE() Teilt das Fenster in die angegebene Anzahl von Buckets auf.
PERCENT_RANK() Gibt den Rang der aktuellen Zeile relativ zu den anderen Zeilen in der Partition an.
PERCENTILE_CONT() Gibt einen interpolierten Wert zurück, der unter Berücksichtigung des Fensters auf das Perzentil-Argument abgebildet wird...
PERCENTILE_DISC() Gibt den Wert zurück, der dem Perzentil des Arguments für das Fenster am nächsten kommt.
RANK() Gibt den Ganzzahlrang eines Werts in einer Gruppe von Werten an.
RATIO_TO_REPORT() Gibt das Verhältnis jeden Werts zur Summe der Werte an.
ROW_NUMBER() Gibt die aktuelle Zeilennummer des Abfrageergebnisses für das Fenster zurück.
Andere Funktionen
CASE WHEN ... THEN Mit CASE kann aus zwei oder mehr alternativen Ausdrücken in einer Abfrage gewählt werden.
CURRENT_USER() Gibt die E-Mail-Adresse des Nutzers zurück, der die Abfrage ausführt.
EVERY() Gibt "true" zurück, wenn das Argument für alle Eingaben wahr ist.
FROM_BASE64() Wandelt den Eingabestring in Base-64-Verschlüsselung in das BYTES-Format um.
HASH() Berechnet und gibt einen signierten 64-Bit-Hash-Wert zurück...
FARM_FINGERPRINT() Berechnet und gibt einen signierten 64-Bit-Fingerabdruckwert zurück...
IF() Gibt das zweite Argument zurück, wenn das erste Argument wahr ist. Ansonsten wird das dritte Argument zurückgegeben.
POSITION() Gibt die auf Eins basierende sequenzielle Position des Arguments zurück.
SHA1() Gibt einen SHA1-Hash-Wert im BYTES-Format zurück.
SOME() Gibt "true" zurück, wenn das Argument für mindestens eine Ausgabe wahr ist.
TO_BASE64() Wandelt das BYTES-Argument in einen Base-64-codierten String um.

Abfragesyntax

Hinweis: Bei Suchbegriffen wird nicht zwischen Groß- und Kleinschreibung unterschieden. In diesem Dokument werden Keywords wie SELECT zur Verdeutlichung großgeschrieben.

SELECT-Klausel

Die SELECT-Klausel gibt eine Liste von Ausdrücken an, die berechnet werden sollen. Die Ausdrücke in der SELECT-Klausel können Feldnamen, Literale und Funktionsaufrufe (einschließlich Aggregatfunktionen und Fensterfunktionen) sowie Kombinationen der drei enthalten. Die Ausdrucksliste ist kommagetrennt.

Jeder Ausdruck kann einen Alias erhalten, indem hinter dem Ausdruck ein Leerzeichen, gefolgt von einer Kennung, hinzugefügt wird. Für eine bessere Lesbarkeit kann zwischen dem Ausdruck und dem Alias das optionale Keyword AS hinzugefügt werden. In einer SELECT-Klausel definierte Aliase können von den GROUP BY-, HAVING- und ORDER BY-Klauseln der Abfrage referenziert werden; nicht jedoch von den FROM-, WHERE- oder OMIT RECORD IF-Klauseln oder anderen Ausdrücken in derselben SELECT-Klausel.

Hinweise:

  • Bei Verwendung einer Aggregatfunktion in der SELECT-Klausel muss entweder in allen Ausdrücken eine Aggregatfunktion verwendet werden oder die Abfrage muss eine GROUP BY-Klausel enthalten, die alle nicht aggregierten Felder in der SELECT -Klausel als Gruppierungsschlüssel enthält. Beispiel:
    #legacySQL
    SELECT
      word,
      corpus,
      COUNT(word)
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th"
    GROUP BY
      word,
      corpus; /* Succeeds because all non-aggregated fields are group keys. */
    
    #legacySQL
    SELECT
      word,
      corpus,
      COUNT(word)
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th"
    GROUP BY
      word; /* Fails because corpus is not aggregated nor is it a group key. */
    
  • Mithilfe von eckigen Klammern können reservierte Wörter speziell markiert werden, um sie als Feldnamen und Aliase verwenden zu können. Beispiel: Sie haben eine Spalte namens "partition". Da dies in der BigQuery-Syntax ein reserviertes Wort ist, schlagen die Abfragen, die sich auf dieses Feld beziehen, fehl und erzeugen unklare Fehlermeldungen, wenn "partition" nicht in eckige Klammern gesetzt wird:
    SELECT [partition] FROM ...
Beispiel

Dieses Beispiel definiert Aliase in der SELECT-Klausel und referenziert dann einen davon in der ORDER BY-Klausel. Die Spalte word in der WHERE-Klausel kann nicht über word_alias referenziert werden. Sie muss über den Namen referenziert werden. Auch der Alias len ist in der WHERE-Klausel nicht sichtbar. Er würde jedoch einer HAVING-Klausel angezeigt werden.

#legacySQL
SELECT
  word AS word_alias,
  LENGTH(word) AS len
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS 'th'
ORDER BY
  len;

WITHIN-Modifikator für Aggregatfunktionen

aggregate_function WITHIN RECORD [ [ AS ] alias ]

Das WITHIN-Keyword sorgt dafür, dass die Aggregatfunktion innerhalb jedes Datensatzes über wiederholte Werte hinweg Aggregationen durchführt. Für jeden Eingabedatensatz wird genau eine aggregierte Ausgabe erstellt. Diese Art der Aggregation wird als Bereichsaggregation bezeichnet. Da die Bereichsaggregation für jeden Datensatz eine Ausgabe produziert, können nicht aggregierte Ausdrücke zusammen mit Bereichsaggregations-Ausdrücken ausgewählt werden, ohne dass eine GROUP BY-Klausel verwendet werden muss.

Sie werden im Rahmen der Bereichsaggregation am häufigsten den RECORD-Bereich verwenden. Handelt es sich um ein sehr komplex verschachteltes, wiederholtes Schema, kann es erforderlich sein, Aggregationen innerhalb von Teildatensatz-Bereichen durchzuführen. Hierzu wird das RECORD-Keyword in der oben aufgeführten Syntax durch den Namen des Knotens in dem Schema, in dem die Aggregation durchgeführt werden soll, ersetzt. Weitere Informationen zu dieser erweiterten Verhaltensweise finden Sie unter Datenhandhabung.

Beispiel

In diesem Beispiel wird eine COUNT-Bereichsaggregation durchgeführt, bevor die Datensätze anhand des aggregierten Werts gefiltert und sortiert werden.

#legacySQL
SELECT
  repository.url,
  COUNT(payload.pages.page_name) WITHIN RECORD AS page_count
FROM
  [bigquery-public-data:samples.github_nested]
HAVING
  page_count > 80
ORDER BY
  page_count DESC;

FROM-Klausel

FROM
  [project_name:]datasetId.tableId [ [ AS ] alias ] |
  (subquery) [ [ AS ] alias ] |
  JOIN clause |
  FLATTEN clause |
  table wildcard function

Die FROM-Klausel gibt die zu durchsuchenden Quelldaten an. BigQuery-Abfragen können direkt auf Tabellen, Unterabfragen, verknüpfte Tabellen oder Tabellen, die mithilfe besonderer Operatoren (siehe unten) geändert wurden, angewendet werden. Mit einem Komma (dem UNION ALL-Operator in BigQuery) können Kombinationen dieser Datenquellen durchsucht werden.

Tabellen referenzieren

Beim Referenzieren einer Tabelle müssen sowohl die datasetId als auch die tableId angegeben werden; project_name ist optional. Wenn project_name nicht angegeben wird, verwendet BigQuery automatisch das aktuelle Projekt. Wenn der Projektname einen Bindestrich enthält, muss die gesamte Tabellenreferenz in Klammern gesetzt werden.

Beispiel
[my-dashed-project:dataset1.tableName]

Tabellen können einen Alias erhalten, indem hinter dem Tabellennamen ein Leerzeichen, gefolgt von einer Kennung, hinzugefügt wird. Zur besseren Lesbarkeit kann zwischen AS und dem Alias das optionale Keyword AS eingefügt werden.

Beim Referenzieren von Spalten aus einer Tabelle kann der einfache Spaltenname verwendet werden oder dem Spaltennamen der Alias (sofern vorhanden) oder die datasetId und die tableId als Präfix vorangestellt werden. Dies gilt jedoch nur, wenn kein project_name angegeben wurde. Der project_name darf nicht im Spaltenpräfix enthalten sein, da das Doppelpunktzeichen nicht in Feldnamen erlaubt ist.

Beispiele

In diesem Beispiel wird eine Spalte ohne Tabellenpräfix referenziert.

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare];

In diesem Beispiel wird dem Spaltennamen datasetId und tableId vorangestellt. Beachten Sie, dass der project_name in diesem Beispiel nicht verwendet werden kann. Die Methode funktioniert nur, wenn sich das Dataset im derzeitigen Standardprojekt befindet.

#legacySQL
SELECT
  samples.shakespeare.word
FROM
  samples.shakespeare;

In diesem Beispiel wird dem Spaltennamen ein Tabellenalias vorangestellt.

#legacySQL
SELECT
  t.word
FROM
  [bigquery-public-data:samples.shakespeare] AS t;

Unterabfragen verwenden

Eine Unterabfrage ist eine verschachtelte SELECT-Anweisung, die in Klammern gesetzt ist. Der äußeren Abfrage stehen die in der SELECT-Klausel der Unterabfrage berechneten Ausdrücke wie die Spalten einer Tabelle zur Verfügung.

Unterabfragen können zum Berechnen von Aggregationen und anderen Ausdrücken verwendet werden. Für die Unterabfrage steht das gesamte Spektrum an SQL-Operatoren zur Verfügung. Dies bedeutet, dass Unterabfragen eigene Unterabfragen enthalten, Zusammenführungen durchführen, Aggregationen gruppieren usw. können.

Komma als UNION ALL

Anders als die Standard-SQL-Syntax verwendet BigQuery das Komma als UNION ALL-Operator anstelle des CROSS JOIN-Operators. Dies ist ein geerbtes Verhalten, das sich daraus entwickelt hat, dass BigQuery CROSS JOIN ursprünglich nicht unterstützt hat und BigQuery-Nutzer regelmäßig UNION ALL-Abfragen schreiben mussten. In der Standard-SQL-Syntax sind Abfragen, die Union-Operationen durchführen, besonders umfangreich. Mit dem Komma als Union-Operator können derartige Abfragen viel effizienter geschrieben werden. Die folgende Abfrage kann beispielsweise verwendet werden, um eine einzelne Abfrage für die Logs mehrerer Tage auszuführen.

#legacySQL
SELECT
  FORMAT_UTC_USEC(event.timestamp_in_usec) AS time,
  request_url
FROM
  [applogs.events_20120501],
  [applogs.events_20120502],
  [applogs.events_20120503]
WHERE
  event.username = 'root' AND
  NOT event.source_ip.is_internal;

Abfragen, die zahlreiche Tabellen zusammenfassen, brauchen normalerweise länger als Abfragen, die die gleiche Datenmenge in einer einzelnen Tabelle verarbeiten. Der Leistungsunterschied kann bis zu 50 ms pro zusätzlicher Tabelle betragen. In einer Abfrage können maximal 1.000 Tabellen zusammengefasst werden.

Tabellenplatzhalter-Funktionen

Der Begriff Tabellenplatzhalter-Funktion bezieht sich auf eine spezielle Art von Funktion, die es so nur in BigQuery gibt. Diese Funktionen werden in der FROM-Klausel verwendet, um mithilfe von einem oder mehreren Filtertypen eine Sammlung von Tabellennamen zu erstellen. Beispielsweise kann die Funktion TABLE_DATE_RANGE verwendet werden, um nur einen bestimmten Satz von täglichen Tabellen zu durchsuchen. Weitere Informationen über diese Funktionen finden Sie unter Tabellenplatzhalter-Funktionen.

FLATTEN-Operator

(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened))
(FLATTEN((subquery), field_to_be_flattened))

Anders als die gewöhnlichen SQL-Verarbeitungssysteme ist BigQuery darauf ausgerichtet, wiederholte Daten zu verarbeiten. Aus diesem Grund müssen BigQuery-Nutzer manchmal Abfragen schreiben, die die Struktur wiederholter Datensätze ändern. Der FLATTEN-Operator ist eine Möglichkeit, dies zu tun.

FLATTEN wandelt einen Knoten im Schema von "wiederholt" in "optional" um. Bei einem Datensatz mit einem oder mehreren Werten für ein wiederholtes Feld erzeugt FLATTEN mehrere Datensätze: einen für jeden Wert im wiederholten Feld. Alle anderen im Datensatz ausgewählten Felder werden in jedem neuen Ausgabedatensatz dupliziert. FLATTEN kann wiederholt angewendet werden, um mehrere Wiederholungslevel zu entfernen.

Weitere Informationen und Beispiele finden Sie unter Datenhandhabung.

JOIN-Operator

BigQuery unterstützt in jeder FROM-Klausel mehrere JOIN-Operatoren. Die nachfolgenden JOIN-Operationen verwenden die Ergebnisse der vorherigen JOIN-Operation als die linke JOIN-Eingabe. Felder aus einer vorhergehenden JOIN-Eingabe können in den ON-Klauseln nachfolgender JOIN-Operatoren als Schlüssel verwendet werden.

JOIN-Typen

BigQuery unterstützt die INNER-, [FULL|RIGHT|LEFT] OUTER- und CROSS JOIN-Operationen. Wird keine Operation angegeben, wird standardmäßig immer INNER verwendet.

In CROSS JOIN-Operationen dürfen keine ON-Klauseln verwendet werden. CROSS JOIN kann große Datenmengen zurückgeben, was zu einer langsamen und ineffizienten Abfrage oder einer Abfrage, die die maximal pro Abfrage erlaubte Ressourcenmenge überschreitet, führen kann. Diese Abfragen schlagen fehl. Wenn möglich, sollte stattdessen mit Abfragen gearbeitet werden, die CROSS JOIN nicht verwenden. Beispielsweise wird CROSS JOIN oft in Situationen verwendet, in denen Fensterfunktionen effizienter wären.

EACH-Modifikator

Der EACH-Modifikator ist ein Hinweis für BigQuery, die JOIN-Operation mithilfe mehrerer Partitionen durchzuführen. Dies ist insbesondere dann hilfreich, wenn bekannt ist, dass beide Seiten der JOIN-Operation groß sind. Der EACH-Modifikator kann nicht in CROSS JOIN-Klauseln verwendet werden.

In der Vergangenheit wurde die Verwendung von EACH oft empfohlen, doch dies hat sich geändert. Die JOIN-Operation sollte möglichst ohne den EACH-Modifikator verwendet werden, um eine bessere Leistung zu erreichen. Wenn die Abfrage fehlgeschlagen ist und eine Fehlermeldung ausgegeben wurde, dass die Ressourcen überschritten wurden, sollten Sie für Ihre Abfrage JOIN EACH verwenden.

Semi-Join und Anti-Join

BigQuery unterstützt nicht nur JOIN in der FROM-Klausel, sondern auch zwei Arten von Join-Operatoren in der WHERE-Klausel: Semi-Join und Anti-Semi-Join. Ein Semi-Join-Operator wird in einer Unterabfrage unter Verwendung des Keywords IN angegeben; Anti-Join unter Verwendung des Keywords NOT IN.

Beispiele

Die folgende Abfrage verwendet einen Semi-Join-Operator, um Ngrams zu finden, bei denen das erste Wort im Ngram auch das zweite Wort in einem anderen Ngram ist, dessen drittes Wort "AND" ist.

#legacySQL
SELECT
  ngram
FROM
  [bigquery-public-data:samples.trigrams]
WHERE
  first IN (SELECT
              second
            FROM
              [bigquery-public-data:samples.trigrams]
            WHERE
              third = "AND")
LIMIT 10;

Die folgende Abfrage verwendet eine Semi-Join-Operation, um die Anzahl der Frauen über 50 Jahre zurückzugeben, die in den 10 Staaten mit den meisten Geburten ein Kind zur Welt gebracht haben.

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state IN (SELECT
              state
            FROM
              (SELECT
                 state,
                 COUNT(state) total
               FROM
                 [bigquery-public-data:samples.natality]
               GROUP BY
                 state
               ORDER BY
                 total DESC
               LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

Zum Anzeigen der Zahlen der anderen 40 Staaten kann ein Anti-Join-Operator verwendet werden. Die folgende Abfrage ist fast identisch mit dem vorherigen Beispiel, nutzt jedoch NOT IN anstelle von IN, um die Anzahl der Frauen über 50 Jahre zurückzugeben, die in den 40 Staaten mit den wenigsten Geburten ein Kind zur Welt gebracht haben.

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state NOT IN (SELECT
                  state
                FROM
                  (SELECT
                     state,
                     COUNT(state) total
                   FROM
                     [bigquery-public-data:samples.natality]
                   GROUP BY
                     state
                   ORDER BY
                     total DESC
                   LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

Hinweise:

  • BigQuery unterstützt keine korrelierten Semi- oder Anti-Semi-Join-Operatoren. Die Unterabfrage kann auf keine Felder der äußeren Abfrage verweisen.
  • Die in einem Semi- oder Anti-Semi-Join-Operator verwendete Unterabfrage muss genau ein Feld auswählen.
  • Die Typen des ausgewählten Felds und des Felds, das von der äußeren Abfrage in der WHERE-Klausel verwendet wird, müssen genau übereinstimmen. BigQuery führt in Semi- oder Anti-Semi-Join-Operatoren keinen Typenzwang durch.

WHERE-Klausel

Die WHERE-Klausel, die manchmal als Prädikat bezeichnet wird, filtert Datensätze, die von der FROM-Klausel erzeugt werden, unter Verwendung eines booleschen Ausdrucks. Mehrere Bedingungen können durch boolesche AND- und OR-Klauseln verbunden werden, die wahlweise von Klammern umgeben sind ( ), um sie zu gruppieren. Die in einer WHERE-Klausel aufgeführten Felder brauchen in der zugehörigen SELECT-Klausel nicht ausgewählt zu werden und die WHERE-Klausel kann keine Ausdrücke referenzieren, die in der SELECT-Klausel der Abfrage berechnet werden, zu der die WHERE-Klausel gehört.

Hinweis: In der WHERE-Klausel können keine Aggregatfunktionen verwendet werden. Wenn die Ausgabe einer Aggregatfunktion gefiltert werden muss, können Sie dies mithilfe einer HAVING-Klausel und einer äußeren Abfrage tun.

Beispiel

Das folgende Beispiel verwendet in der WHERE-Klausel eine Disjunktion von booleschen Ausdrücken, wobei die beiden Ausdrücke durch einen OR-Operator zusammengeführt sind. Eingangsdatensätze passieren den WHERE-Filter, wenn einer der Ausdrücke true zurückgibt.

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  (word CONTAINS 'prais' AND word CONTAINS 'ing') OR
  (word CONTAINS 'laugh' AND word CONTAINS 'ed');

OMIT RECORD IF-Klausel

Die OMIT RECORD IF-Klausel ist ein Konstrukt, das es so nur in BigQuery gibt. Es ist besonders hilfreich, wenn es um den Umgang mit verschachtelten wiederholten Schemas geht. Es ähnelt einer WHERE -Klausel, unterscheidet sich aber in zwei entscheidenden Punkten. Zum einen verwendet die Klausel eine ausschließende Bedingung, das heißt, Datensätze werden weggelassen, wenn der Ausdruck true zurückgibt, und behalten, wenn der Ausdruck false oder null zurückgibt. Zum anderen kann die OMIT RECORD IF -Klausel in ihrer Bedingung Bereichsaggregatfunktionen verwenden (und tut dies normalerweise auch).

Zusätzlich zum Filtern von vollständigen Datensätzen kann mit OMIT...IF auch ein enger definierter Bereich angegeben werden, um nur einen Teil eines Datensatzes zu filtern. Hierzu wird im Schema der Name eines Nicht-Blatt-Knotens verwendet, statt RECORD in der OMIT...IF-Klausel. Diese Funktionalität wird von BigQuery-Nutzern nur selten verwendet. Im Abschnitt zu WITHIN sind weitere Informationen zu diesem erweiterten Verhalten verlinkt.

Wird OMIT...IF verwendet, um einen Teil eines Datensatzes in einem wiederkehrenden Feld auszuschließen, und wählt die Abfrage zusätzlich andere unabhängig wiederkehrende Felder aus, so lässt BigQuery einen Teil der anderen wiederkehrenden Datensätze in der Abfrage aus. Wenn der Fehler Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, ausgegeben wird, sollten Sie zum Standard-SQL-Dialekt wechseln. Weitere Informationen zum Migrieren von OMIT...IF-Anweisungen in den Standard-SQL-Dialekt finden Sie unter Zu Standard-SQL migrieren.

Beispiel

Nehmen wir das Beispiel, das für den WITHIN-Modifikator verwendet wurde. Mit OMIT RECORD IF kann das gleiche Ergebnis wie im Beispiel mit WITHIN und HAVING erreicht werden.

#legacySQL
SELECT
  repository.url
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

GROUP BY-Klausel

Mit der GROUP BY-Klausel können Zeilen, in denen in einem bestimmten Feld oder einem Satz von Feldern die gleichen Werte stehen, gruppiert werden, um Aggregationen verwandter Felder zu berechnen. Die Gruppierung findet nach der Filterung in der WHERE-Klausel, aber vor der Berechnung der Ausdrücke in der SELECT-Klausel statt. Die Ergebnisse des Ausdrucks können nicht als Gruppenschlüssel in der GROUP BY-Klausel verwendet werden.

Beispiel

Diese Abfrage findet die zehn häufigsten ersten Worte im Trigramm-Stichproben-Dataset. Sie zeigt nicht nur die Verwendung der GROUP BY-Klausel, sondern auch, wie in den GROUP BY- und ORDER BY-Klauseln positionale Indizes anstelle von Feldnamen verwendet werden können.

#legacySQL
SELECT
  first,
  COUNT(ngram)
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 10;

Mithilfe einer GROUP BY-Klausel durchgeführte Aggregationen werden als gruppierte Aggregation bezeichnet. Anders als die Bereichsaggregation wird die gruppierte Aggregation in den meisten SQL-Verarbeitungssystemen häufig verwendet.

Der EACH-Modifikator

Der EACH-Modifikator ist ein Hinweis für BigQuery, die GROUP BY-Operation mithilfe mehrerer Partitionen durchzuführen. Dies ist insbesondere dann hilfreich, wenn bereits bekannt ist, dass das Dataset große Mengen verschiedener Werten für die Gruppenschlüssel enthält.

In der Vergangenheit wurde die Verwendung von EACH oft empfohlen, doch dies hat sich geändert. Die GROUP BY-Operation sollte möglichst ohne den EACH-Modifikator verwendet werden, um eine bessere Leistung zu erreichen. Wenn die Abfrage fehlgeschlagen ist und eine Fehlermeldung ausgegeben wurde, dass die Ressourcen überschritten wurden, sollten Sie für Ihre Abfrage GROUP EACH BY verwenden.

Die ROLLUP-Funktion

Wenn die ROLLUP-Funktion verwendet wird, fügt BigQuery dem Abfrageergebnis zusätzliche Zeilen hinzu, die für zusammengefügte Aggregationen (rolled up) stehen. Alle Felder, die nach ROLLUP aufgeführt sind, müssen in einem einzigen Paar Klammern stehen. In Zeilen, die aufgrund der ROLLUP-Funktion hinzugefügt wurden, steht NULL für die Spalten, für die die Aggregation zusammengefügt wird.

Beispiel

Diese Abfrage generiert die jahresweisen Zahlen männlicher und weiblicher Geburten aus dem Stichproben-Geburten-Dataset.

#legacySQL
SELECT
  year,
  is_male,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

Dies sind die Ergebnisse der Abfrage. In einigen Zeilen sind einer oder beide Gruppenschlüssel NULL. Dies sind die Rollup-Zeilen.

+------+---------+----------+
| year | is_male |  count   |
+------+---------+----------+
| NULL |    NULL | 12122730 |
| 2000 |    NULL |  4063823 |
| 2000 |   false |  1984255 |
| 2000 |    true |  2079568 |
| 2001 |    NULL |  4031531 |
| 2001 |   false |  1970770 |
| 2001 |    true |  2060761 |
| 2002 |    NULL |  4027376 |
| 2002 |   false |  1966519 |
| 2002 |    true |  2060857 |
+------+---------+----------+

Bei Verwendung der ROLLUP-Funktion dient die GROUPING-Funktion dazu, zwischen Zeilen zu unterscheiden, die aufgrund der ROLLUP-Funktion hinzugefügt wurden, und Zeilen, deren Wert für den Gruppenschlüssel tatsächlich NULL ist.

Beispiel

Mit dieser Abfrage wird die GROUPING-Funktion zum vorherigen Beispiel hinzugefügt, um die Zeilen besser identifizieren zu können, die aufgrund der ROLLUP-Funktion hinzugefügt wurden.

#legacySQL
SELECT
  year,
  GROUPING(year) as rollup_year,
  is_male,
  GROUPING(is_male) as rollup_gender,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

Dies sind die Ergebnisse, die die neue Abfrage zurückgibt.

+------+-------------+---------+---------------+----------+
| year | rollup_year | is_male | rollup_gender |  count   |
+------+-------------+---------+---------------+----------+
| NULL |           1 |    NULL |             1 | 12122730 |
| 2000 |           0 |    NULL |             1 |  4063823 |
| 2000 |           0 |   false |             0 |  1984255 |
| 2000 |           0 |    true |             0 |  2079568 |
| 2001 |           0 |    NULL |             1 |  4031531 |
| 2001 |           0 |   false |             0 |  1970770 |
| 2001 |           0 |    true |             0 |  2060761 |
| 2002 |           0 |    NULL |             1 |  4027376 |
| 2002 |           0 |   false |             0 |  1966519 |
| 2002 |           0 |    true |             0 |  2060857 |
+------+-------------+---------+---------------+----------+

Hinweise:

  • Nicht aggregierte Felder in der SELECT-Klausel müssen in der GROUP BY-Klausel aufgelistet werden.
    #legacySQL
    SELECT
      word,
      corpus,
      COUNT(word)
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th"
    GROUP BY
      word,
      corpus; /* Succeeds because all non-aggregated fields are group keys. */
    
    #legacySQL
    SELECT
      word,
      corpus,
      COUNT(word)
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th"
    GROUP BY
      word;  /* Fails because corpus is not aggregated nor is it a group key. */
    
  • Ausdrücke, die in der SELECT-Klausel berechnet werden, können in der zugehörigen GROUP BY-Klausel nicht verwendet werden.
    #legacySQL
    SELECT
      word,
      corpus,
      COUNT(word) word_count
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th"
    GROUP BY
      word,
      corpus,
      word_count;  /* Fails because word_count is not visible to this GROUP BY clause. */
    
  • Eine Gruppierung nach Float- und Double-Werten wird nicht unterstützt, da die Gleichheitsfunktion für diese Typen nicht gut definiert ist.
  • Da das System interaktiv ist, könnten Abfragen, die eine große Anzahl an Gruppen ausgeben, fehlschlagen. Durch die Verwendung der TOP- anstelle der GROUP BY-Funktion könnten einige Skalierungsprobleme gelöst werden.

HAVING-Klausel

Die HAVING-Klausel verhält sich genau wie die WHERE-Klausel mit dem Unterschied, dass sie nach der SELECT-Klausel ausgewertet wird. Die HAVING-Klausel hat also Zugriff auf die Ergebnisse aller berechneten Ausdrücke. Die HAVING-Klausel kann sich nur auf die Ausgaben der zugehörigen SELECT-Klausel beziehen.

Beispiel

Diese Abfrage berechnet die häufigsten ersten Wörter im ngram-Stichproben-Dataset, die den Buchstaben "a" enthalten und maximal 10.000 Mal vorkommen.

#legacySQL
SELECT
  first,
  COUNT(ngram) ngram_count
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
HAVING
  first contains "a"
  AND ngram_count < 10000
ORDER BY
  2 DESC
LIMIT 10;

ORDER BY-Klausel

Die ORDER BY-Klausel sortiert die Ergebnisse einer Abfrage in aufsteigender oder absteigender Reihenfolge mithilfe von einem oder mehreren Schlüsselfeldern. Geben Sie mehrere Felder oder Aliase als durch Kommas getrennte Liste ein, um sie bei der Sortierung zu berücksichtigen. Die Ergebnisse werden in den Feldern in der Reihenfolge sortiert, in der sie aufgelistet sind. Die Sortierrichtung kann mit DESC (absteigend) oder ASC (aufsteigend) angegeben werden. Standardmäßig ist ASC ausgewählt. Für jeden Sortierschlüssel kann eine andere Sortierrichtung angegeben werden.

Die ORDER BY-Klausel wird nach der SELECT-Klausel ausgewertet und kann daher die Ausgabe jedes in der SELECT-Klausel berechneten Ausdrucks referenzieren. Erhält ein Feld in der SELECT-Klausel einen Alias, dann muss der Alias in der ORDER BY-Klausel verwendet werden.

LIMIT-Klausel

Die LIMIT-Klausel beschränkt die Anzahl an Zeilen im zurückgegebenen Ergebnissatz. Da BigQuery-Abfragen normalerweise auf eine große Anzahl von Zeilen ausgeführt werden, ist LIMIT eine gute Möglichkeit, lange dauernde Abfragen zu vermeiden, da hier nur ein Teil der Zeilen verarbeitet wird.

Hinweise:

  • Die LIMIT-Klausel beendet die Verarbeitung und gibt Ergebnisse zurück, sobald die definierten Anforderungen erfüllt wurden. Dies kann die Verarbeitungszeit einiger Abfragen verringern. Wenn jedoch Aggregatfunktionen wie COUNT oder ORDER BY-Klauseln angegeben werden, muss vor der Ausgabe von Ergebnissen weiterhin der gesamte Ergebnissatz verarbeitet werden. Die LIMIT-Klausel wird als Letztes verarbeitet.
  • Eine Abfrage mit einer LIMIT-Klausel kann weiterhin nicht-deterministisch sein, wenn die Abfrage keinen Operator enthält, der eine Sortierung des Ausgabeergebnissatzes garantiert. Dies liegt daran, dass BigQuery mit einer großen Anzahl von parallelen Jobs arbeitet. Es kann nicht garantiert werden, in welcher Reihenfolge die parallelen Jobs ausgegeben werden.
  • Die LIMIT-Klausel kann keine Funktionen enthalten; sie arbeitet nur mit einer numerischen Konstante.

Abfragegrammatik

Die verschiedenen Klauseln von BigQuery-SELECT-Anweisungen wurden oben detailliert beschrieben. Im Folgenden wird noch einmal die gesamte Grammatik der SELECT-Anweisungen in kompakter Form mit Links zu den jeweiligen Abschnitten präsentiert.

query:
    SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ]
    [ FROM from_body
      [ WHERE bool_expression ]
      [ OMIT RECORD IF bool_expression]
      [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ]
      [ HAVING bool_expression ]
      [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ]
      [ LIMIT n ]
    ];

from_body:
    {
      from_item [, ...] |  # Warning: Comma means UNION ALL here
      from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] |
      (FLATTEN({ table_name | (query) }, field_name_or_alias)) |
      table_wildcard_function
    }

from_item:
    { table_name | (query) } [ [ AS ] alias ]

join_type:
    { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS }

join_predicate:
    field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...]

expression:
    {
      literal_value |
      field_name_or_alias |
      function_call
    }

bool_expression:
    {
      expression_which_results_in_a_boolean_value |
      bool_expression AND bool_expression |
      bool_expression OR bool_expression |
      NOT bool_expression
    }

Notation:

  • Eckige Klammern "[ ]" stehen für optionale Klauseln.
  • Geschweifte Klammern "{ }" umschließen einen Satz von Optionen.
  • Der senkrechte Strich "|" zeigt ein logisches OR an.
  • Ein Komma gefolgt von Auslassungspunkten in eckigen Klammern "[, ... ]" zeigt an, dass das vorhergehende Element in einer Liste mit dem angegebenen Trennzeichen wiederholt werden kann.
  • Runde Klammern "( )" stehen für Literalklammern.

Aggregatfunktionen

Aggregatfunktionen geben Werte zurück, die Zusammenfassungen größerer Datasets darstellen. Dadurch sind diese Funktionen besonders nützlich für die Analyse von Logs. Eine Aggregatfunktion arbeitet mit einer Sammlung von Werten und gibt einen Wert pro Tabelle, Gruppe oder Bereich zurück:

  • Tabellenaggregation

    Verwendet eine Aggregatfunktion, um alle qualifizierenden Zeilen in der Tabelle zusammenzufassen. Beispiel:

    SELECT COUNT(f1) FROM ds.Table;

  • Gruppenaggregation

    Verwendet eine Aggregatfunktion und eine GROUP BY-Klausel, die ein nicht aggregiertes Feld angibt, um Zeilen gruppenweise zusammenzufassen. Beispiel:

    SELECT COUNT(f1) FROM ds.Table GROUP BY b1;

    Die TOP-Funktion steht für eine besondere Art von Gruppenaggregation.

  • Bereichsaggregation

    Diese Funktion ist nur anwendbar auf Tabellen mit verschachtelten Feldern.
    Sie verwendet eine Aggregatfunktion und das WITHIN-Keyword, um wiederkehrende Werte innerhalb eines festgelegten Bereichs zu aggregieren. Beispiel:

    SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;

    Der Bereich könnte RECORD sein, was einer ganzen Zeile oder einem Knoten (wiederholtes Feld in einer Zeile) entspricht. Aggregatfunktionen arbeiten mit den Werten innerhalb des Bereichs und geben für jeden Datensatz oder Knoten aggregierte Ergebnisse zurück.

Mithilfe der folgenden Optionen kann eine Einschränkung auf eine Aggregatfunktion angewendet werden:

  • Mit einem Alias in einer Subselect-Abfrage. Die Einschränkung wird in der äußeren WHERE-Klausel angegeben.

    #legacySQL
    SELECT corpus, count_corpus_words
    FROM
      (SELECT corpus, count(word) AS count_corpus_words
      FROM [bigquery-public-data:samples.shakespeare]
      GROUP BY corpus) AS sub_shakespeare
    WHERE count_corpus_words > 4000
    
  • Mit einem Alias in einer HAVING-Klausel.

    #legacySQL
    SELECT corpus, count(word) AS count_corpus_words
    FROM [bigquery-public-data:samples.shakespeare]
    GROUP BY corpus
    HAVING count_corpus_words > 4000;
    

Es kann auch auf einen Alias in der GROUP BY- oder ORDER BY-Klausel verwiesen werden.

Syntax

Aggregatfunktionen
AVG() Gibt den Durchschnittswert einer Gruppe von Zeilen zurück...
BIT_AND() Gibt das Ergebnis einer bitweisen AND-Operation zurück...
BIT_OR() Gibt das Ergebnis einer bitweisen OR-Operation zurück...
BIT_XOR() Gibt das Ergebnis einer bitweisen XOR-Operation zurück...
CORR() Gibt den Pearson-Korrelationskoeffizienten einer Reihe von Paaren zurück.
COUNT() Gibt die Gesamtanzahl an Werten zurück...
COUNT([DISTINCT]) Gibt die Gesamtanzahl an Nicht-NULL-Werten zurück...
COVAR_POP() Berechnet die Populationskovarianz der Werte...
COVAR_SAMP() Berechnet die Stichprobenkovarianz der Werte...
EXACT_COUNT_DISTINCT() Gibt die exakte Anzahl an unterschiedlichen Nicht-NULL-Werten für das angegebene Feld zurück.
FIRST() Gibt den ersten sequenziellen Wert im Bereich der Funktion zurück.
GROUP_CONCAT() Verkettet mehrere Strings zu einem String...
GROUP_CONCAT_UNQUOTED() Verkettet mehrere Strings zu einem String ... es werden keine Anführungszeichen hinzugefügt...
LAST() Gibt den letzten sequenziellen Wert zurück...
MAX() Gibt den Höchstwert zurück...
MIN() Gibt den Mindestwert zurück...
NEST() Führt alle Werte im aktuellen Aggregationsbereich zu einem wiederholten Feld zusammen.
NTH() Gibt den n-ten sequenziellen Wert zurück...
QUANTILES() Berechnet den ungefähren Mindestwert, Höchstwert und die Quantile...
STDDEV() Gibt die Standardabweichung zurück...
STDDEV_POP() Berechnet die Standardabweichung der Population...
STDDEV_SAMP() Berechnet die Standardabweichung der Stichprobe...
SUM() Gibt die Gesamtsumme der Werte zurück...
TOP() ... COUNT(*) Gibt die höchsten max_records-Datensätze nach Häufigkeit zurück.
UNIQUE() Gibt den Satz einzigartiger Nicht-NULL-Werte zurück...
VARIANCE() Berechnet die Varianz der Werte...
VAR_POP() Berechnet die Populationsvarianz der Werte...
VAR_SAMP() Berechnet die Stichprobenvarianz der Werte...
AVG(numeric_expr)
Gibt den Durchschnittswert einer Gruppe von Zeilen zurück (berechnet durch numeric_expr). Zeilen mit einem NULL-Wert werden bei der Berechnung nicht berücksichtigt.
BIT_AND(numeric_expr)
Gibt das Ergebnis eines bitweisen Vorgangs AND für alle Instanzen von numeric_expr in allen Zeilen zurück. NULL-Werte werden ignoriert. Diese Funktion gibt NULL zurück, wenn alle Instanzen von numeric_expr als NULL ausgewertet werden.
BIT_OR(numeric_expr)
Gibt das Ergebnis eines bitweisen Vorgangs OR für alle Instanzen von numeric_expr in allen Zeilen zurück. NULL-Werte werden ignoriert. Diese Funktion gibt NULL zurück, wenn alle Instanzen von numeric_expr als NULL ausgewertet werden.
BIT_XOR(numeric_expr)
Gibt das Ergebnis eines bitweisen Vorgangs XOR für alle Instanzen von numeric_expr in allen Zeilen zurück. NULL-Werte werden ignoriert. Diese Funktion gibt NULL zurück, wenn alle Instanzen von numeric_expr als NULL ausgewertet werden.
CORR(numeric_expr, numeric_expr)
Gibt den Pearson-Korrelationskoeffizienten einer Reihe von Zahlenpaaren zurück.
COUNT(*)
Gibt die Gesamtanzahl von Werten (NULL und Nicht-NULL) im Bereich der Funktion zurück. Sofern COUNT(*) nicht zusammen mit der TOP-Funktion verwendet wird, sollte das Feld, auf das die Operation angegebenen wird, explizit angegeben werden.
COUNT([DISTINCT] field [, n])
Gibt die Gesamtanzahl an Nicht-NULL-Werten im Bereich einer Funktion zurück.

Bei Verwendung des DISTINCT-Keywords gibt die Funktion die Anzahl an unterschiedlichen Werten für das angegebene Feld zurück. Beachten Sie, dass der für DISTINCT zurückgegebene Wert eine statistische Annäherung ist und keine Gewährleistung besteht, dass er exakt ist.

Muss das Ergebnis von COUNT(DISTINCT) genauer sein, kann ein zweiter Parameter, n, angegeben werden, der den Grenzwert definiert, unterhalb dessen exakte Ergebnisse garantiert sind. Standardmäßig ist der Wert für n gleich 1.000. Wird ein höherer Wert für n angegeben, erhalten Sie bis zu diesem für n definierten Wert exakte Ergebnisse für COUNT(DISTINCT). Durch die Angabe höherer Werte für n wird jedoch die Skalierbarkeit dieses Operators reduziert und die Ausführung der Abfrage könnte deutlich länger dauern. Auch könnte es passieren, dass die Abfrage fehlschlägt.

Zur Berechnung der exakten Anzahl verschiedener Werte sollte EXACT_COUNT_DISTINCT verwendet werden. Alternativ könnten Sie zuerst GROUP EACH BY auf die relevanten Felder und dann COUNT(*) anwenden. Dieser Ansatz ermöglicht mehr Skalierung. Der GROUP EACH BY-Ansatz ist skalierbarer, könnte aber dazu führen, dass die Leistung im Vorfeld etwas geringer ist.

COVAR_POP(numeric_expr1, numeric_expr2)
Berechnet die Populationskovarianz der von numeric_expr1 und numeric_expr2 berechneten Werte.
COVAR_SAMP(numeric_expr1, numeric_expr2)
Berechnet die Stichprobenkovarianz der von numeric_expr1 und numeric_expr2 berechneten Werte.
EXACT_COUNT_DISTINCT(field)
Gibt die exakte Anzahl an unterschiedlichen Nicht-NULL-Werten für das angegebene Feld zurück. Für eine bessere Skalierbarkeit und Leistung sollte COUNT(DISTINCT field) verwendet werden.
FIRST(expr)
Gibt den ersten sequenziellen Wert im Bereich der Funktion zurück.
GROUP_CONCAT('str' [, separator])

Verkettet mehrere Strings zu einem String, bei dem die einzelnen Werte durch den optionalen Parameter separator getrennt sind. Wird kein separator angegeben, gibt BigQuery einen durch Kommas getrennten String zurück.

Enthält ein String in den Quelldaten ein doppeltes Anführungszeichen, dann gibt GROUP_CONCAT den String mit hinzugefügten doppelten Anführungszeichen zurück. Beispielsweise würde der String a"b als "a""b" zurückgegeben werden. Verwenden Sie GROUP_CONCAT_UNQUOTED, wenn die Strings nicht mit hinzugefügten doppelten Anführungszeichen zurückgegeben werden sollen.

Beispiel:

#legacySQL
SELECT
  GROUP_CONCAT(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);
GROUP_CONCAT_UNQUOTED('str' [, separator])

Verkettet mehrere Strings zu einem String, bei dem die einzelnen Werte durch den optionalen Parameter separator getrennt sind. Wird kein separator angegeben, gibt BigQuery einen durch Kommas getrennten String zurück.

Anders als GROUP_CONCAT fügt diese Funktion zurückgegebenen Werten, die ein doppeltes Anführungszeichen enthalten, keine weiteren doppelten Anführungszeichen hinzu. So würde beispielsweise der String a"b als a"b zurückgegeben werden.

Beispiel:

#legacySQL
SELECT
  GROUP_CONCAT_UNQUOTED(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);
LAST(field)
Gibt den letzten sequenziellen Wert im Bereich der Funktion zurück.
MAX(field)
Gibt den höchsten Wert im Bereich der Funktion zurück.
MIN(field)
Gibt den niedrigsten Wert im Bereich der Funktion zurück.
NEST(expr)

Führt alle Werte im aktuellen Aggregationsbereich zu einem wiederholten Feld zusammen. So gibt beispielsweise die Abfrage "SELECT x, NEST(y) FROM ... GROUP BY x" einen Ausgabedatensatz für jeden unterschiedlichen Wert x zurück und enthält ein wiederkehrendes Feld für alle Werte y, die in der Abfrageeingabe ein Paar mit x bilden. Für die NEST-Funktion wird eine GROUP BY-Klausel benötigt.

BigQuery flacht Abfrageergebnisse automatisch ab, d. h., bei Anwendung der NEST-Funktion auf die Top-Level-Abfrage enthalten die Ergebnisse keine wiederholten Felder. Bei der Verwendung eines Subselect-Operators, der Zwischenergebnisse produziert, die sofort in derselben Abfrage verwendet werden können, sollte die NEST-Funktion verwendet werden.

NTH(n, field)
Gibt den n. sequenziellen Wert im Bereich der Funktion zurück, wobei n eine Konstante ist. Die Zählung der NTH-Funktion beginnt bei 1. Es gibt also keinen 0. Wert. Wenn der Bereich der Funktion weniger als n Werte umfasst, gibt die Funktion NULL zurück.
QUANTILES(expr[, buckets])

Berechnet den ungefähren Mindestwert, Höchstwert und die Quantile des Eingabeausdrucks. NULL-Eingabewerte werden ignoriert. Eine leere Eingabe oder eine Eingabe, deren Werte ausschließlich NULL sind, führt zu einer NULL-Ausgabe. Die Anzahl der berechneten Quantile wird durch die optionalen buckets-Parameter definiert; hierzu zählen auch der Mindest- und Höchstwert der Zählung. Für die Berechnung der angenäherten N-tile werden N+1 buckets verwendet. Der Standardwert für buckets ist 100. (Hinweis: Beim Standardwert 100 werden keine Perzentile geschätzt. Für eine Schätzung der Perzentile muss als Mindestwert 101 ­­buckets verwendet werden.) Werden sie spezifisch angegeben, muss der Wert für buckets mindestens 2 betragen.

Der kleinste Fehler pro Quantil ist Epsilon = 1 / buckets; dies bedeutet, dass der Fehler immer kleiner wird, je größer die Anzahl an Buckets ist. Beispiel:

QUANTILES(<expr>, 2) # computes min and max with 50% error.
QUANTILES(<expr>, 3) # computes min, median, and max with 33% error.
QUANTILES(<expr>, 5) # computes quartiles with 25% error.
QUANTILES(<expr>, 11) # computes deciles with 10% error.
QUANTILES(<expr>, 21) # computes vigintiles with 5% error.
QUANTILES(<expr>, 101) # computes percentiles with 1% error.

Die NTH-Funktion kann verwendet werden, um ein bestimmtes Quantil auszuwählen. Dabei darf jedoch nicht vergessen werden, dass NTH Eins-basiert ist und dass QUANTILES den Mindestwert ("0." Quantil) an erster Stelle und den Höchstwert ("100." Perzentil oder "N." N-til) an letzter Stelle zurückgibt. So schätzt NTH(11, QUANTILES(expr, 21)) beispielsweise den Medianwert von expr, während NTH(20, QUANTILES(expr, 21)) den 19. Vingtil (95. Perzentil) von expr schätzt. Beide Schätzungen besitzen einen 5-prozentigen Fehlerspielraum.

Mithilfe von weiteren Buckets können Sie die Genauigkeit weiter verbessern. Um beispielsweise in den vorherigen Berechnungen den Fehlerspielraum von 5 auf 0,1 Prozent zu verringern, müssen Sie anstelle von 21 Buckets 1001 Buckets verwenden und das Argument entsprechend mit der NTH-Funktion anpassen. Zum Berechnen des Medianwerts mit einem 0,1-prozentigen Fehlerspielraum müssen Sie NTH(501, QUANTILES(expr, 1001)) verwenden; für den 95. Perzentil mit einem 0,1-prozentigen Fehlerspielraum NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)
Gibt die Standardabweichung der von numeric_expr berechneten Werte zurück. Zeilen mit einem NULL-Wert werden bei der Berechnung nicht berücksichtigt. Die STDDEV-Funktion ist ein Alias für STDDEV_SAMP.
STDDEV_POP(numeric_expr)
Berechnet die Populationsstandardabweichung des von numeric_expr berechneten Werts. Verwenden Sie STDDEV_POP(), um die Standardabweichung eines Datasets zu berechnen, das die gesamte zu berücksichtigende Population umfasst. Wenn das Dataset nur eine repräsentative Stichprobe der Population umfasst, verwenden Sie stattdessen STDDEV_SAMP(). Weitere Informationen über den Unterschied zwischen Populations- und Stichproben-Standardabweichung finden Sie unter Standardabweichung in Wikipedia.
STDDEV_SAMP(numeric_expr)
Berechnet die Stichprobenstandardabweichung des von numeric_expr berechneten Werts. Verwenden Sie STDDEV_SAMP(), um die Standardabweichung einer gesamten Population basierend auf einer repräsentativen Stichprobe der Population zu berechnen. Wenn Ihr Dataset die gesamte Population umfasst, verwenden Sie stattdessen STDDEV_POP(). Weitere Informationen über den Unterschied zwischen Populations- und Stichproben-Standardabweichung finden Sie unter Standardabweichung in Wikipedia.
SUM(field)
Gibt die Gesamtsumme der Werte im Bereich der Funktion zurück. Kann nur mit numerischen Datentypen verwendet werden.
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
Gibt die höchsten max_records-Datensätze nach Häufigkeit zurück. Weitere Details finden Sie unter TOP-Beschreibung unten.
UNIQUE(expr)
Gibt das Set einzigartiger Nicht-NULL-Werte im Bereich der Funktionen in nicht-definierter Reihenfolge zurück. So wie eine große GROUP BY-Klausel ohne EACH-Keyword fehlschlägt, kommt es auch bei dieser Abfrage zu einer Fehlermeldung "Resources Exceeded" (Ressourcen überschritten), wenn zu viele unterschiedliche Werte vorhanden sind. Anders als GROUP BY kann die UNIQUE-Funktion jedoch auch in Zusammenarbeit mit der Bereichsaggregation verwendet werden. Dies ermöglicht ein effizientes Arbeiten mit verschachtelten Feldern mit einer eingeschränkten Anzahl an Werten.
VARIANCE(numeric_expr)
Berechnet die Abweichung der von numeric_expr berechneten Werte. Zeilen mit einem NULL-Wert werden bei der Berechnung nicht berücksichtigt. Die VARIANCE-Funktion ist ein Alias für VAR_SAMP.
VAR_POP(numeric_expr)
Berechnet die Populationsabweichung der von numeric_expr berechneten Werte. Weitere Informationen über den Unterschied zwischen Populations- und Stichproben-Standardabweichung finden Sie unter Standardabweichung auf Wikipedia.
VAR_SAMP(numeric_expr)
Berechnet die Stichprobenabweichung der von numeric_expr berechneten Werte. Weitere Informationen über den Unterschied zwischen Populations- und Stichproben-Standardabweichung finden Sie unter Standardabweichung auf Wikipedia.

TOP()-Funktion

TOP ist eine Funktion, die eine Alternative zur GROUP BY-Klausel darstellt. Sie wird als vereinfachte Syntax für GROUP BY ... ORDER BY ... LIMIT ... verwendet. Im Allgemeinen ist die Ausführung der TOP-Funktion schneller als die vollständige Abfrage ... GROUP BY ... ORDER BY ... LIMIT .... Die Funktion gibt möglicherweise aber nur annähernde Ergebnisse zurück. Die Syntax der TOP-Funktion sieht wie folgt aus:

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

Wird TOP in einer SELECT-Klausel verwendet, muss als eines der Felder COUNT(*) verwendet werden.

Eine Abfrage, die die TOP()-Funktion verwendet, kann nur zwei Felder zurückgeben: das TOP-Feld und den COUNT(*)-Wert.

field|alias
Das Feld oder der Alias, das bzw. der zurückgegeben werden soll.
max_values
[Optional] Die maximale Anzahl an zurückzugebenden Ergebnissen. Der Standardwert ist 20.
multiplier
Eine positive Ganzzahl, die die von COUNT(*) zurückgegebenen Werte um das angegebene Vielfache erhöht.

TOP()-Beispiele

  • Einfache Beispielabfragen mit TOP()

    Die folgenden Abfragen verwenden TOP(), um 10 Zeilen zurückzugeben.

    Beispiel 1:

    #legacySQL
    SELECT
      TOP(word, 10) as word, COUNT(*) as cnt
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th";
    

    Beispiel 2:

    #legacySQL
    SELECT
      word, left(word, 3)
    FROM
      (SELECT TOP(word, 10) AS word, COUNT(*)
         FROM [bigquery-public-data:samples.shakespeare]
         WHERE word CONTAINS "th");
    
  • Vergleich von TOP() mit GROUP BY...ORDER BY...LIMIT

    Die Abfrage gibt der Reihe nach die 10 am häufigsten verwendeten Wörter zurück, die "th" enthalten, und die Art der Dokumente, in denen die Wörter verwendet wurden. Die TOP-Abfrage wird viel schneller ausgeführt:

    Beispiel ohne TOP():

    #legacySQL
    SELECT
      word, COUNT(*) AS cnt
    FROM
      ds.Table
    WHERE
      word CONTAINS 'th'
    GROUP BY
      word
    ORDER BY
      cnt DESC LIMIT 10;
    

    Beispiel mit TOP():

    #legacySQL
    SELECT
      TOP(word, 10), COUNT(*)
    FROM
      ds.Table
    WHERE
      word contains 'th';
    
  • Unter Verwendung des multiplier-Parameters.

    Die folgenden Abfragen zeigen, wie sich der multiplier-Parameter auf das Abfrageergebnis auswirkt. Die erste Abfrage gibt die Anzahl an Geburten pro Monat in Wyoming an. Die zweite Abfrage verwendet den multiplier-Parameter, um die cnt-Werte mit 100 zu multiplizieren.

    Beispiel ohne den multiplier-Parameter:

    #legacySQL
    SELECT
      TOP(month,3) as month, COUNT(*) as cnt
    FROM
      [bigquery-public-data:samples.natality]
    WHERE
      state = "WY";

    Liefert:

    +-------+-------+
    | month |  cnt  |
    +-------+-------+
    |   7   | 19594 |
    |   5   | 19038 |
    |   8   | 19030 |
    +-------+-------+
    

    Beispiel mit dem multiplier-Parameter:

    #legacySQL
    SELECT
      TOP(month,3,100) as month, COUNT(*) as cnt
    FROM
      [bigquery-public-data:samples.natality]
    WHERE
      state = "WY";

    Liefert:

    +-------+---------+
    | month |   cnt   |
    +-------+---------+
    |   7   | 1959400 |
    |   5   | 1903800 |
    |   8   | 1903000 |
    +-------+---------+
    

Hinweis: Damit COUNT(*) verwendet werden kann, muss SELECT in die TOP-Klausel aufgenommen werden.

Erweiterte Beispiele

  • Mittelwert und Standardabweichung nach Bedingung gruppiert

    Die folgende Abfrage gibt den Mittelwert und die Standardabweichung der Geburtsgewichte in Ohio im Jahr 2003 gruppiert nach Müttern zurück, die rauchen und nicht rauchen.

    Beispiel:

    #legacySQL
    SELECT
      cigarette_use,
      /* Finds average and standard deviation */
      AVG(weight_pounds) baby_weight,
      STDDEV(weight_pounds) baby_weight_stdev,
      AVG(mother_age) mother_age
    FROM
      [bigquery-public-data:samples.natality]
    WHERE
      year=2003 AND state='OH'
    /* Group the result values by those */
    /* who smoked and those who didn't.  */
    GROUP BY
      cigarette_use;
    
  • Abfrageergebnisse unter Verwendung eines aggregrierten Werts filtern

    Verwenden Sie die HAVING-Funktion, um Abfrageergebnisse unter Verwendung eines aggregierten Werts zu filtern (zum Beispiel Filterung anhand des Werts einer SUM). HAVING vergleicht den Wert eines Ergebnisses, das mithilfe einer Aggregationsfunktion ermittelt wurde – anders als die WHERE-Operation, die vor der Aggregation auf die einzelnen Zeilen angewendet wird.

    Beispiel:

    #legacySQL
    SELECT
      state,
      /* If 'is_male' is True, return 'Male', */
      /* otherwise return 'Female' */
      IF (is_male, 'Male', 'Female') AS sex,
      /* The count value is aliased as 'cnt' */
      /* and used in the HAVING clause below. */
      COUNT(*) AS cnt
    FROM
      [bigquery-public-data:samples.natality]
    WHERE
      state != ''
    GROUP BY
      state, sex
    HAVING
      cnt > 3000000
    ORDER BY
      cnt DESC
    

    Liefert:

    +-------+--------+---------+
    | state |  sex   |   cnt   |
    +-------+--------+---------+
    | CA    | Male   | 7060826 |
    | CA    | Female | 6733288 |
    | TX    | Male   | 5107542 |
    | TX    | Female | 4879247 |
    | NY    | Male   | 4442246 |
    | NY    | Female | 4227891 |
    | IL    | Male   | 3089555 |
    +-------+--------+---------+
    

Arithmetische Operatoren

Arithmetische Operatoren arbeiten mit numerischen Argumenten und geben ein numerisches Ergebnis zurück. Bei jedem Argument kann es sich um einen numerischen Literal oder einen numerischen Wert handeln, der von einer Abfrage zurückgegeben wird. Wenn die arithmetische Operation zu einem undefinierten Ergebnis führt, gibt die Operation NULL zurück.

Syntax

Operator Beschreibung Beispiel
+ Addition

SELECT 6 + (5 - 1);

Liefert: 10

Subtraktion

SELECT 6 - (4 + 1);

Liefert: 1

* Multiplikation

SELECT 6 * (5 - 1);

Liefert: 24

/ Division

SELECT 6 / (2 + 2);

Liefert: 1.5

% Modulo

SELECT 6 % (2 + 2);

Liefert: 2

Bitweise Funktionen

Bitweise Funktionen arbeiten auf der Ebene einzelner Bit und erfordern numerische Argumente. Weitere Informationen über die bitweisen Funktionen finden Sie unter Bitweise Operationen.

Drei weitere bitweise Funktionen, BIT_AND, BIT_OR und BIT_XOR, werden unter Aggregatfunktionen beschrieben.

Syntax

Operator Beschreibung Beispiel
& Bitweises AND

SELECT (1 + 3) & 1

Liefert: 0

| Bitweises OR

SELECT 24 | 12

Liefert: 28

^ Bitweises XOR

SELECT 1 ^ 0

Liefert: 1

<< Bitweise Linksverschiebung

SELECT 1 << (2 + 2)

Liefert: 16

>> Bitweise Rechtsverschiebung

SELECT (6 + 2) >> 2

Liefert: 2

~ Bitweises NOT

SELECT ~2

Liefert: -3

BIT_COUNT(<numeric_expr>)

Liefert die Anzahl an Bit, die in <numeric_expr> festgelegt ist

SELECT BIT_COUNT(29);

Liefert: 4

Umwandlungsfunktionen

Umwandlungsfunktionen ändern den Datentyp eines numerischen Ausdrucks. Sie sind besonders nützlich, um sicherzustellen, dass die Argumente in einer Vergleichsfunktion über den gleichen Datentyp verfügen.

Syntax

Umwandlungsfunktionen
BOOLEAN() In boolesche Werte umwandeln.
BYTES() In Byte umwandeln.
CAST(expr AS type) expr in eine Variable des Typs type umwandeln.
FLOAT() In Double umwandeln.
HEX_STRING() In Hexadezimalstring umwandeln.
INTEGER() In Ganzzahl umwandeln.
STRING() In String umwandeln.
BOOLEAN(<numeric_expr>)
  • Gibt true zurück, wenn <numeric_expr> nicht 0 und nicht NULL ist.
  • Gibt false zurück, wenn <numeric_expr> 0 ist.
  • Gibt NULL zurück, wenn <numeric_expr> NULL ist.
BYTES(string_expr)
Gibt string_expr als Wert des Typs bytes zurück.
CAST(expr AS type)
Wandelt expr in eine Variable des Typs type um.
FLOAT(expr)
Gibt expr als Double zurück. expr kann ein String wie '45.78' sein. Die Funktion gibt jedoch für nicht numerische Werte NULL zurück.
HEX_STRING(numeric_expr)
Gibt numeric_expr als hexadezimalen String zurück.
INTEGER(expr)
Wandelt expr in eine 64-Bit-Ganzzahl um.
  • Gibt NULL zurück, wenn expr ein String ist, der keinem Ganzzahlwert entspricht.
  • Gibt die Anzahl an Mikrosekunden seit der Unix-Epoche zurück, wenn expr ein Zeitstempel ist.
STRING(numeric_expr)
Gibt numeric_expr als String zurück.

Vergleichsfunktionen

Vergleichsfunktionen geben basierend auf den folgenden Arten von Vergleichen true oder false zurück:

  • Bei einem Vergleich der beiden Ausdrücke.
  • Bei einem Vergleich eines Ausdrucks oder eines Satz von Ausdrücken mit einem spezifischem Kriterium, zum Beispiel ob er in einer angegebenen Liste vorhanden ist, als Wert NULL besitzt oder einen optionalen Nicht-Standardwert besitzt.

Einige der unten aufgeführten Funktionen geben Werte zurück, die nicht true oder false sind; diese basieren jedoch auf Vergleichsoperationen.

Für Vergleichsfunktionen können entweder numerische oder Stringausdrücke verwendet werden. (Stringausdrücke müssen von einfachen oder doppelten Anführungszeichen umschlossen sein.) Die Ausdrücke können Literale oder Werte sein, die von einer Abfrage abgerufen wurden. Vergleichsfunktionen werden zumeist in WHERE-Klauseln als Filterbedingungen verwendet, können aber auch in anderen Klauseln verwendet werden.

Syntax

Vergleichsfunktionen
expr1 = expr2 Gibt true zurück, wenn die Ausdrücke gleich sind.
expr1 != expr2
expr1 <> expr2
Gibt true zurück, wenn die Ausdrücke nicht gleich sind.
expr1 > expr2 Gibt true zurück, wenn expr1 größer als expr2 ist.
expr1 < expr2 Gibt true zurück, wenn expr1 kleiner als expr2 ist.
expr1 >= expr2 Gibt true zurück, wenn expr1 größer oder gleich expr2 ist.
expr1 <= expr2 Gibt true zurück, wenn expr1 kleiner oder gleich expr2 ist.
expr1 BETWEEN expr2 AND expr3 Gibt true zurück, wenn der Wert von expr1 im Bereich von expr2 bis expr3 liegt.
expr IS NULL Gibt true zurück, wenn expr NULL ist.
expr IN() Gibt true zurück, wenn expr dem Wert von expr1, expr2 oder einem anderen Wert in den Klammern entspricht.
COALESCE() Gibt das erste Argument zurück, das nicht NULL ist.
GREATEST() Gibt den größten Parameter numeric_expr zurück.
IFNULL() Gibt das Argument zurück, wenn es nicht null ist.
IS_INF() Gibt true zurück, wenn der Wert positiv oder negativ unendlich ist.
IS_NAN() Gibt true zurück, wenn das Argument NaN ist.
IS_EXPLICITLY_DEFINED() Verworfen: Verwenden Sie stattdessen expr IS NOT NULL.
LEAST() Gibt den kleinsten Argumentparameter numeric_expr zurück.
NVL() Wenn expr nicht null ist, wird expr zurückgegeben, ansonsten null_default.
expr1 = expr2
Gibt true zurück, wenn die Ausdrücke gleich sind.
expr1 != expr2
expr1 <> expr2
Gibt true zurück, wenn die Ausdrücke nicht gleich sind.
expr1 > expr2
Gibt true zurück, wenn expr1 größer als expr2 ist.
expr1 < expr2
Gibt true zurück, wenn expr1 kleiner als expr2 ist.
expr1 >= expr2
Gibt true zurück, wenn expr1 größer oder gleich expr2 ist.
expr1 <= expr2
Gibt true zurück, wenn expr1 kleiner oder gleich expr2 ist.
expr1 BETWEEN expr2 AND expr3

Gibt true zurück, wenn der Wert von expr1 größer als oder gleich expr2, aber kleiner als oder gleich expr3 ist.

expr IS NULL
Gibt true zurück, wenn expr NULL ist.
expr IN(expr1, expr2, ...)
Gibt true zurück, wenn expr dem Wert expr1, expr2 oder einem anderen Wert innerhalb der Klammern entspricht. Das IN-Keyword ist eine effiziente Abkürzung für (expr = expr1 || expr = expr2 || ...). Die im IN-Keyword verwendeten Ausdrücke müssen Konstanten sein und dem Datentyp von expr entsprechen. Die IN-Klausel kann auch verwendet werden, um Semi-Joins und Anti-Joins zu erstellen. Weitere Informationen finden Sie unter Semi-Join und Anti-Join.
COALESCE(<expr1>, <expr2>, ...)
Gibt das erste Argument zurück, das nicht NULL ist.
GREATEST(numeric_expr1, numeric_expr2, ...)

Gibt den größten Parameter numeric_expr zurück. Alle Parameter müssen numerisch und vom gleichen Typ sein. Wenn einer der Parameter NULL ist, gibt die Funktion NULL zurück.

Zum Ignorieren von NULL-Werten können Sie die IFNULL-Funktion verwenden, um NULL-Werte in einen Wert zu ändern, der keine Auswirkungen auf den Vergleich hat. Im folgenden Codebeispiel wird die IFNULL-Funktion verwendet, um NULL-Werte in -1 zu ändern. Dies wirkt sich nicht auf den Vergleich von positiven Werten aus.

SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);
IFNULL(expr, null_default)
Wenn expr nicht null ist, wird expr zurückgegeben, ansonsten null_default.
IS_INF(numeric_expr)
Gibt true zurück, wenn numeric_expr positiv oder negativ unendlich ist.
IS_NAN(numeric_expr)
Gibt true zurück, wenn numeric_expr der spezielle numerische Wert NaN ist.
IS_EXPLICITLY_DEFINED(expr)

Diese Funktion wurde verworfen. Verwenden Sie stattdessen expr IS NOT NULL.

LEAST(numeric_expr1, numeric_expr2, ...)

Gibt den kleinsten Parameter numeric_expr zurück. Alle Parameter müssen numerisch und vom gleichen Typ sein. Wenn einer der Parameter NULL ist, gibt die Funktion NULL zurück.

NVL(expr, null_default)
Wenn expr nicht null ist, wird expr zurückgegeben, ansonsten null_default. Die NVL-Funktion ist ein Alias für IFNULL.

Funktionen für Datum und Uhrzeit

Die folgenden Funktionen ermöglichen eine Bearbeitung des Datums und der Uhrzeit in UNIX-Zeitstempeln, Datumsstrings und TIMESTAMP-Datentypen. Weitere Informationen über die Arbeit mit dem TIMESTAMP-Datentyp finden Sie unter TIMESTAMP verwenden.

Funktionen für Datum und Uhrzeit, die mit UNIX-Zeitstempeln arbeiten, verwenden die UNIX-Zeit. Sie geben Werte basierend auf der UTC-Zeitzone zurück.

Syntax

Funktionen für Datum und Uhrzeit
CURRENT_DATE() Gibt das aktuelle Datum im Format %Y-%m-%d zurück.
CURRENT_TIME() Gibt die aktuelle Uhrzeit des Servers im Format %H:%M:%S zurück.
CURRENT_TIMESTAMP() Gibt die aktuelle Uhrzeit des Servers im Format %Y-%m-%d %H:%M:%S zurück.
DATE() Gibt das Datum im Format %Y-%m-%d zurück.
DATE_ADD() Fügt das angegebene Intervall zu einem TIMESTAMP-Datentyp hinzu.
DATEDIFF() Gibt die Anzahl von Tagen zwischen zwei TIMESTAMP-Datentypen zurück.
DAY() Gibt den Tag des Monats als Ganzzahl zwischen 1 und 31 zurück.
DAYOFWEEK() Gibt den Wochentag als Ganzzahl zwischen 1 (Sonntag) und 7 (Samstag) zurück.
DAYOFYEAR() Gibt den Tag des Jahres als Ganzzahl zwischen 1 und 366 an.
FORMAT_UTC_USEC() Gibt einen UNIX-Zeitstempel im Format YYYY-MM-DD HH:MM:SS.uuuuuu zurück.
HOUR() Gibt die Stunde eines TIMESTAMP als Ganzzahl zwischen 0 und 23 zurück.
MINUTE() Gibt die Minuten eines TIMESTAMP als Ganzzahl zwischen 0 und 59 zurück.
MONTH() Gibt den Monat eines TIMESTAMP als Ganzzahl zwischen 0 und 12 zurück.
MSEC_TO_TIMESTAMP() Verwandelt einen UNIX-Zeitstempel in einen TIMESTAMP.
NOW() Gibt den aktuellen UNIX-Zeitstempel in Mikrosekunden zurück.
PARSE_UTC_USEC() Verwandelt einen Datumsstring in einen UNIX-Zeitstempel in Mikrosekunden.
QUARTER() Gibt das Quartal eines TIMESTAMP als Ganzzahl zwischen 1 und 4 zurück.
SEC_TO_TIMESTAMP() Verwandelt einen UNIX-Zeitstempel in Sekunden in einen TIMESTAMP.
SECOND() Gibt die Sekunden eines TIMESTAMP als Ganzzahl zwischen 0 und 59 zurück.
STRFTIME_UTC_USEC() Gibt einen Datumsstring im Format date_format_str zurück.
TIME() Gibt einen TIMESTAMP im Format %H:%M:%S zurück.
TIMESTAMP() Verwandelt einen Datumsstring in einen TIMESTAMP.
TIMESTAMP_TO_MSEC() Verwandelt einen TIMESTAMP in einen UNIX-Zeitstempel in Millisekunden.
TIMESTAMP_TO_SEC() Verwandelt einen TIMESTAMP in einen UNIX-Zeitstempel in Sekunden.
TIMESTAMP_TO_USEC() Verwandelt einen TIMESTAMP in einen UNIX-Zeitstempel in Mikrosekunden.
USEC_TO_TIMESTAMP() Verwandelt einen UNIX-Zeitstempel in Mikrosekunden in einen TIMESTAMP.
UTC_USEC_TO_DAY() Verschiebt einen UNIX-Zeitstempel in Mikrosekunden an den Beginn des Tages, an dem er stattgefunden hat.
UTC_USEC_TO_HOUR() Verschiebt einen UNIX-Zeitstempel in Mikrosekunden an den Beginn der Stunde, in der er stattgefunden hat.
UTC_USEC_TO_MONTH() Verschiebt einen UNIX-Zeitstempel in Mikrosekunden an den Beginn des Monats, in dem er stattgefunden hat.
UTC_USEC_TO_WEEK() Gibt einen UNIX-Zeitstempel in Mikrosekunden zurück, der für einen Wochentag steht.
UTC_USEC_TO_YEAR() Gibt einen UNIX-Zeitstempel in Mikrosekunden zurück, der für das Jahr steht.
WEEK() Gibt die Woche eines TIMESTAMP als Ganzzahl zwischen 1 und 53 zurück.
YEAR() Gibt das Jahr eines TIMESTAMP zurück.

CURRENT_DATE()

Gibt einen menschenlesbaren String des aktuellen Datums im Format %Y-%m-%d zurück.

Beispiel:

SELECT CURRENT_DATE();

Liefert: 2013-02-01

CURRENT_TIME()

Gibt einen menschenlesbaren String der aktuellen Zeit des Servers im Format %H:%M:%S zurück.

Beispiel:

SELECT CURRENT_TIME();

Liefert: 01:32:56

CURRENT_TIMESTAMP()

Gibt die aktuelle Zeit des Servers als TIMESTAMP-Datentyp im Format %Y-%m-%d %H:%M:%S zurück.

Beispiel:

SELECT CURRENT_TIMESTAMP();

Liefert: 2013-02-01 01:33:35 UTC

DATE(<timestamp>)

Gibt einen menschenlesbaren String eines TIMESTAMP-Datentyps im Format %Y-%m-%d zurück.

Beispiel:

SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));

Liefert: 2012-10-01

DATE_ADD(<timestamp>,<interval>,
                 <interval_units>)

Fügt das angegebene Intervall zu einem TIMESTAMP-Datentyp hinzu. Mögliche interval_units-Werte sind YEAR, MONTH, DAY, HOUR, MINUTE und SECOND. Wenn interval eine negative Zahl ist, dann wird das Intervall vom TIMESTAMP-Datentyp subtrahiert.

Beispiel:

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");

Liefert: 2017-10-01 02:03:04 UTC

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");

Liefert: 2007-10-01 02:03:04 UTC

DATEDIFF(<timestamp1>,<timestamp2>)

Gibt die Anzahl von Tagen zwischen zwei TIMESTAMP-Datentypen zurück. Das Ergebnis ist positiv, wenn der erste TIMESTAMP-Datentyp nach dem zweiten TIMESTAMP-Datentyp kommt, andernfalls ist das Ergebnis negativ.

Beispiel:

SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));

Liefert: 466

Beispiel:

SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));

Liefert: -466

DAY(<timestamp>)

Gibt den Tag des Monats eines TIMESTAMP-Datentyps als Ganzzahl zwischen 1 und 31 (einschließlich) zurück.

Beispiel:

SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));

Liefert: 2

DAYOFWEEK(<timestamp>)

Gibt den Wochentag eines TIMESTAMP-Datentyps als Ganzzahl zwischen 1 (Sonntag) und 7 (Samstag) (einschließlich) zurück.

Beispiel:

SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));

Liefert: 2

DAYOFYEAR(<timestamp>)

Gibt den Tag des Jahres eines TIMESTAMP-Datentyps als Ganzzahl zwischen 1 und 366 (einschließlich) zurück. Die Ganzzahl 1 bezieht sich auf den 1. Januar.

Beispiel:

SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));

Liefert: 275

FORMAT_UTC_USEC(<unix_timestamp>)

Gibt eine menschenlesbare Stringdarstellung eines UNIX-Zeitstempels im Format YYYY-MM-DD HH:MM:SS.uuuuuu zurück.

Beispiel:

SELECT FORMAT_UTC_USEC(1274259481071200);

Liefert: 2010-05-19 08:58:01.071200

HOUR(<timestamp>)

Gibt die Stunde eines TIMESTAMP-Datentyps als Ganzzahl zwischen einen 1 und 23 (einschließlich) zurück.

Beispiel:

SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));

Liefert: 5

MINUTE(<timestamp>)

Gibt die Minuten eines TIMESTAMP-Datentyps als Ganzzahl zwischen 0 und 59 (einschließlich) zurück.

Beispiel:

SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));

Liefert: 23

MONTH(<timestamp>)

Gibt den Monat eines TIMESTAMP-Datentyps als Ganzzahl zwischen 1 und 12 (einschließlich) zurück.

Beispiel:

SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));

Liefert: 10

MSEC_TO_TIMESTAMP(<expr>)
Verwandelt einen UNIX-Zeitstempel in Millisekunden in einen TIMESTAMP-Datentyp.

Beispiel:

SELECT MSEC_TO_TIMESTAMP(1349053323000);

Liefert: 2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

Liefert: 2012-10-01 01:02:04 UTC

NOW()

Gibt den aktuellen UNIX-Zeitstempel in Mikrosekunden zurück.

Beispiel:

SELECT NOW();

Liefert: 1359685811687920

PARSE_UTC_USEC(<date_string>)

Wandelt einen Datumsstring in einen UNIX-Zeitstempel in Mikrosekunden um. date_string muss das Format YYYY-MM-DD HH:MM:SS[.uuuuuu] haben. Der Sekundenbruchteil kann bis zu 6 Ziffern lang sein oder weggelassen werden.

TIMESTAMP_TO_USEC ist eine gleichwertige Funktion, die anstelle eines Datumsstrings ein TIMESTAMP-Datentyp-Argument umwandelt.

Beispiel:

SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");

Liefert: 1349056984000000

QUARTER(<timestamp>)

Gibt das Quartal eines TIMESTAMP-Datentyps als Ganzzahl zwischen 1 und 4 (einschließlich) zurück.

Beispiel:

SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));

Liefert: 4

SEC_TO_TIMESTAMP(<expr>)

Wandelt einen UNIX-Zeitstempel in Sekunden in einen TIMESTAMP-Datentyp um.

Beispiel:

SELECT SEC_TO_TIMESTAMP(1355968987);

Liefert: 2012-12-20 02:03:07 UTC

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

Liefert: 2012-12-20 02:03:07 UTC

SECOND(<timestamp>)

Gibt die Sekunden eines TIMESTAMP-Datentyps als Ganzzahl zwischen 0 und 59 (einschließlich) zurück.

Während einer Schaltsekunde liegt der Ganzzahlenbereich zwischen 0 und 60 (einschließlich).

Beispiel:

SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));

Liefert: 48

STRFTIME_UTC_USEC(<unix_timestamp>,
                  <date_format_str>)

Gibt einen menschenlesbaren Datumsstring im Format date_format_str zurück. date_format_str kann datumsbezogene Zeichen (z. B. / und -) und Sonderzeichen enthalten, die von der strftime-Funktion in C++ akzeptiert werden (z. B. %d für den Tag des Monats).

Wenn Sie die Abfragedaten nach Zeitintervallen gruppieren möchten, um beispielsweise alle Daten eines bestimmten Monats abzurufen, verwenden Sie die UTC_USEC_TO_<function_name>-Funktionen, da sie effizienter sind.

Beispiel:

SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");

Liefert: 2010-05-19

TIME(<timestamp>)

Gibt einen menschenlesbaren String eines TIMESTAMP-Datentyps im Format %H:%M:%S zurück.

Beispiel:

SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));

Liefert: 02:03:04

TIMESTAMP(<date_string>)

Wandelt einen Datumsstring in einen TIMESTAMP-Datentyp um.

Beispiel:

SELECT TIMESTAMP("2012-10-01 01:02:03");

Liefert: 2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC(<timestamp>)

Wandelt einen TIMESTAMP-Datentyp in einen UNIX-Zeitstempel in Millisekunden um.

Beispiel:

SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));

Liefert: 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)
Wandelt einen TIMESTAMP-Datentyp in einen UNIX-Zeitstempel in Sekunden um.

Beispiel:

SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));

Liefert: 1349053323

TIMESTAMP_TO_USEC(<timestamp>)

Wandelt einen TIMESTAMP-Datentyp in einen UNIX-Zeitstempel in Mikrosekunden um.

PARSE_UTC_USEC ist eine gleichwertige Funktion, die anstelle eines TIMESTAMP-Datentyps ein Datumsstring-Argument umwandelt.

Beispiel:

SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));

Liefert: 1349053323000000

USEC_TO_TIMESTAMP(<expr>)

Wandelt einen UNIX-Zeitstempel in Mikrosekunden in einen TIMESTAMP-Datentyp um.

Beispiel:

SELECT USEC_TO_TIMESTAMP(1349053323000000);

Liefert: 2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

Liefert: 2012-10-01 01:02:04 UTC

UTC_USEC_TO_DAY(<unix_timestamp>)

Verschiebt einen UNIX-Zeitstempel in Mikrosekunden an den Beginn des Tages, an dem er stattgefunden hat.

Wenn unix_timestamp beispielsweise am 19. Mai um 08:58 geschieht, gibt diese Funktion einen UNIX-Zeitstempel für den 19. Mai um 00:00 (Mitternacht) zurück.

Beispiel:

SELECT UTC_USEC_TO_DAY(1274259481071200);

Liefert: 1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

Verschiebt einen UNIX-Zeitstempel in Mikrosekunden an den Beginn der Stunde, in der er stattgefunden hat.

Wenn unix_timestamp beispielsweise um 08:58 geschieht, gibt diese Funktion einen UNIX-Zeitstempel für 08:00 am selben Tag zurück.

Beispiel:

SELECT UTC_USEC_TO_HOUR(1274259481071200);

Liefert: 1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

Verschiebt einen UNIX-Zeitstempel in Mikrosekunden an den Beginn des Monats, in dem er stattgefunden hat

Wenn unix_timestamp beispielsweise am 19. März geschieht, gibt diese Funktion einen UNIX-Zeitstempel für den 1. März desselben Jahres zurück.

Beispiel:

SELECT UTC_USEC_TO_MONTH(1274259481071200);

Liefert: 1272672000000000

UTC_USEC_TO_WEEK(<unix_timestamp>,
                 <day_of_week>)

Gibt einen UNIX-Zeitstempel in Mikrosekunden zurück, der für einen Wochentag im unix_timestamp-Argument steht. Diese Funktion arbeitet mit zwei Argumenten: einem UNIX-Zeitstempel in Mikrosekunden und einem Wochentag zwischen 0 (Sonntag) und 6 (Samstag).

Wenn unix_timestamp beispielsweise am Freitag, 2008-04-11, geschieht und day_of_week auf 2 (Dienstag) gesetzt ist, gibt die Funktion einen UNIX-Zeitstempel für Dienstag, 2008-04-08 zurück.

Beispiel:

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

Liefert: 1207612800000000

UTC_USEC_TO_YEAR(<unix_timestamp>)

Gibt einen UNIX-Zeitstempel in Mikrosekunden zurück, der für das Jahr im unix_timestamp-Argument steht.

Wenn unix_timestamp beispielsweise 2010 geschieht, gibt diese Funktion 1274259481071200, die Mikrosekundendarstellung von 2010-01-01 00:00, zurück.

Beispiel:

SELECT UTC_USEC_TO_YEAR(1274259481071200);

Liefert: 1262304000000000

WEEK(<timestamp>)

Gibt die Woche eines TIMESTAMP-Datentyps als Ganzzahl zwischen 1 und 53 (einschließlich) zurück.

Wochen beginnen am Sonntag, d. h., wenn der 1. Januar kein Sonntag ist, besteht die Woche 1 aus weniger als 7 Tagen und der erste Sonntag des Jahres ist der erste Tag der Woche 2.

Beispiel:

SELECT WEEK(TIMESTAMP('2014-12-31'));

Liefert: 53

YEAR(<timestamp>)
Gibt das Jahr eines TIMESTAMP-Datentyps zurück.

Beispiel:

SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));

Liefert: 2012

Erweiterte Beispiele

  • Ganzzahlen-Zeitstempelergebnisse in menschenlesbares Format umwandeln

    Die folgende Abfrage findet die 5 Momente, an denen die meisten Überarbeitungen in Wikipedia stattfanden. Verwenden Sie die FORMAT_UTC_USEC()-Funktion aus BigQuery, die einen Zeitstempel (in Mikrosekunden) als Eingabe nutzt, um die Ergebnisse in einem menschenlesbaren Format zu präsentieren. Die Abfrage multipliziert die Zeitstempel im Wikipedia POSIX-Format (in Sekunden) mit 1.000.000, um den Wert in Mikrosekunden umzuwandeln.

    Beispiel:

    #legacySQL
    SELECT
      /* Multiply timestamp by 1000000 and convert */
      /* into a more human-readable format. */
      TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5)
        AS top_revision_time,
      COUNT (*) AS revision_count
    FROM
      [bigquery-public-data:samples.wikipedia];
    

    Liefert:

    +----------------------------+----------------+
    |     top_revision_time      | revision_count |
    +----------------------------+----------------+
    | 2002-02-25 15:51:15.000000 |          20976 |
    | 2002-02-25 15:43:11.000000 |          15974 |
    | 2010-02-02 03:34:51.000000 |              3 |
    | 2010-02-02 01:04:59.000000 |              3 |
    | 2010-02-01 23:55:05.000000 |              3 |
    +----------------------------+----------------+
    
  • Bucketing-Ergebnisse nach Zeitstempel

    Es ist sehr hilfreich, Datums- und Zeitfunktionen zu verwenden, um Abfrageergebnisse in Buckets zu gruppieren, die bestimmten Jahren, Monaten oder Tagen zugeordnet sind. Das folgende Beispiel verwendet die FunktionUTC_USEC_TO_MONTH(), um anzuzeigen, wie viele Zeichen jeder einzelne Wikipedia-Autor in seinen/ihren Änderungskommentaren pro Monat verwendet.

    Beispiel:

    #legacySQL
    SELECT
      contributor_username,
      /* Return the timestamp shifted to the
       * start of the month, formatted in
       * a human-readable format. Uses the
       * 'LEFT()' string function to return only
       * the first 7 characters of the formatted timestamp.
       */
      LEFT (FORMAT_UTC_USEC(
        UTC_USEC_TO_MONTH(timestamp * 1000000)),7)
        AS month,
      SUM(LENGTH(comment)) as total_chars_used
    FROM
      [bigquery-public-data:samples.wikipedia]
    WHERE
      (contributor_username != '' AND
       contributor_username IS NOT NULL)
      AND timestamp > 1133395200
      AND timestamp < 1157068800
    GROUP BY
      contributor_username, month
    ORDER BY
      total_chars_used DESC;
    

    Liefert (abgeschnitten):

    +--------------------------------+---------+-----------------------+
    |      contributor_username      |  month  | total_chars_used      |
    +--------------------------------+---------+-----------------------+
    | Kingbotk                       | 2006-08 |              18015066 |
    | SmackBot                       | 2006-03 |               7838365 |
    | SmackBot                       | 2006-05 |               5148863 |
    | Tawkerbot2                     | 2006-05 |               4434348 |
    | Cydebot                        | 2006-06 |               3380577 |
    etc ...
    

IP-Funktionen

IP-Funktionen wandeln IP-Adressen in menschenlesbare Form um und vice versa.

Syntax

IP-Funktionen
FORMAT_IP() Wandelt die 32 niedrigstwertigsten Bit von integer_value in einen menschenlesbaren IPv4-Adressstring um.
PARSE_IP() Wandelt einen String, der für eine IPv4-Adresse steht, in einen vorzeichenlosen Ganzzahlwert um.
FORMAT_PACKED_IP() Gibt eine menschenlesbare IP-Adresse im Format 10.1.5.23 oder 2620:0:1009:1:216:36ff:feef:3f zurück.
PARSE_PACKED_IP() Gibt eine IP-Adresse als BYTES zurück.
FORMAT_IP(integer_value)
Wandelt die 32 niedrigstwertigsten Bit von integer_value in einen menschenlesbaren IPv4-Adressstring um. FORMAT_IP(1) gibt beispielsweise den String '0.0.0.1' zurück.
PARSE_IP(readable_ip)
Wandelt einen String, der für eine IPv4-Adresse steht, in einen vorzeichenlosen Ganzzahlwert um. PARSE_IP('0.0.0.1') gibt beispielsweise 1 zurück. Wenn der String keine gültige IPv4-Adresse ist, gibt PARSE_IP den Wert NULL zurück.

BigQuery unterstützt das Schreiben von IPv4- und IPv6-Adressen in verpackte Strings, als 4- oder 16-Byte-Binärdaten in Netzwerk-Byte-Reihenfolge. Die nachfolgend beschriebenen Funktionen unterstützen das Parsen von Adressen in eine und aus einer menschenlesbaren Form. Die Funktionen können nur auf Stringfelder mit IPs angewendet werden.

Syntax

FORMAT_PACKED_IP(packed_ip)

Gibt eine menschenlesbare IP-Adresse im Format 10.1.5.23 oder 2620:0:1009:1:216:36ff:feef:3f zurück. Beispiele:

  • FORMAT_PACKED_IP('0123456789@ABCDE') liefert '3031:3233:3435:3637:3839:4041:4243:4445'
  • FORMAT_PACKED_IP('0123') liefert '48.49.50.51'
PARSE_PACKED_IP(readable_ip)

Liefert eine IP-Adresse im BYTES-Format. Wenn der Eingabestring keine gültige IPv4- oder IPv6-Adresse ist, gibt PARSE_PACKED_IP den Wert NULL zurück. Beispiele:

  • PARSE_PACKED_IP('48.49.50.51') liefert 'MDEyMw=='
  • PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445') liefert 'MDEyMzQ1Njc4OUBBQkNERQ=='

JSON-Funktionen

Mit den JSON-Funktionen in BigQuery können mithilfe von JSONPath-ähnlichen Ausdrücken Werte in gespeicherten JSON-Daten gefunden werden.

JSON-Daten zu speichern kann flexibler sein, als alle einzelnen Felder in einem Tabellenschema anzugeben; es kann jedoch auch zu höheren Kosten führen. Bei der Auswahl von Daten aus einem JSON-String wird das Scannen des gesamten Strings in Rechnung gestellt. Dies ist teurer als wenn sich jedes Feld in einer separaten Spalte befinden würde. Auch ist die Abfrage langsamer, da der gesamte String geparst werden muss. Im Fall von Ad-hoc- oder sich schnell verändernden Schemas kann es die Flexibilität von JSON jedoch wert sein, diesen Aufpreis zu zahlen.

Wenn Sie mit strukturierten Daten arbeiten, sollten Sie anstelle der Funktionen für reguläre Ausdrücke von BigQuery JSON-Funktionen verwenden, da sie benutzerfreundlicher sind.

Syntax

JSON-Funktionen
JSON_EXTRACT() Wählt einen Wert anhand des JSONPath-Ausdrucks aus und gibt einen JSON-String zurück.
JSON_EXTRACT_SCALAR() Wählt einen Wert anhand des JSONPath-Ausdrucks aus und gibt einen JSON-Skalar zurück.
JSON_EXTRACT(json, json_path)

Wählt einen Wert im json-Format gemäß dem JSONPath-Ausdruck json_path. Dabei muss json_path eine Stringkonstante sein. Gibt den Wert im JSON-Stringformat zurück.

Beispiel:

    SELECT
      JSON_EXTRACT('{"a": 1, "b": [4, 5]}', '$.b')
      AS str;
JSON_EXTRACT_SCALAR(json, json_path)

Wählt einen Wert im json-Format gemäß dem JSONPath-Ausdruck json_path. Dabei muss json_path eine Stringkonstante sein. Gibt einen skalaren JSON-Wert zurück.

Beispiel:

    SELECT
      JSON_EXTRACT_SCALAR('{"a": ["x", {"b":3}]}', '$.a[1].b')
      AS str;

Logische Operatoren

Logische Operatoren führen binäre oder ternäre Logik auf Ausdrücke aus. Binäre Logik gibt true oder false zurück. Ternäre Logik bietet zusätzlich Platz für NULL-Werte und gibt true, false oder NULL zurück.

Syntax

Logische Operatoren
expr AND expr Gibt true zurück, wenn beide Ausdrücke wahr sind.
expr OR expr Gibt true zurück, wenn einer oder beide Ausdrücke wahr sind.
NOT expr Gibt true zurück, wenn der Ausdruck falsch ist.
expr AND expr
  • Gibt true zurück, wenn beide Ausdrücke wahr sind.
  • Gibt false zurück, wenn einer oder beide Ausdrücke falsch sind.
  • Gibt NULL zurück, wenn beide Ausdrücke NULL sind oder einer der Ausdrücke wahr und der andere NULL ist.
expr OR expr
  • Gibt true zurück, wenn einer oder beide Ausdrücke wahr sind.
  • Gibt false zurück, wenn beide Ausdrücke falsch sind.
  • Gibt NULL zurück, wenn beide Ausdrücke NULL sind oder einer der Ausdrücke falsch und der andere NULL ist.
NOT expr
  • Gibt true zurück, wenn der Ausdruck falsch ist.
  • Gibt false zurück, wenn der Ausdruck wahr ist.
  • Gibt NULL zurück, wenn der Ausdruck NULL ist.

NOT kann zusammen mit anderen Funktionen als Negationsoperator verwendet werden. Zum Beispiel NOT IN(expr1, expr2) oder IS NOT NULL.

Mathematische Funktionen

Mathematische Funktionen arbeiten mit numerischen Argumenten und geben ein numerisches Ergebnis zurück. Bei jedem Argument kann es sich um einen numerischen Literal oder einen numerischen Wert handeln, der von einer Abfrage zurückgegeben wird. Wenn die mathematische Funktion zu einem undefinierten Ergebnis führt, gibt die Operation NULL zurück.

Syntax

Mathematische Funktionen
ABS() Gibt den absoluten Wert des Arguments zurück.
ACOS() Gibt den Arkuskosinus des Arguments zurück.
ACOSH() Gibt den hyperbolischen Arkuskosinus des Arguments zurück.
ASIN() Gibt den Arkussinus des Arguments zurück.
ASINH() Gibt den hyperbolischen Arkussinus des Arguments zurück.
ATAN() Gibt den Arkustangens des Arguments zurück.
ATANH() Gibt den hyperbolischen Arkustangens des Arguments zurück.
ATAN2() Gibt den Arkustangens der beiden Argumente zurück.
CEIL() Rundet das Argument auf die nächste ganze Zahl auf und gibt den gerundeten Wert zurück.
COS() Gibt den Kosinus des Arguments zurück.
COSH() Gibt den hyperbolischen Kosinus des Arguments zurück.
DEGREES() Wandelt von Radianten in Grad um.
EXP() Gibt die e. Potenz des Arguments zurück.
FLOOR() Rundet das Argument auf die nächste ganze Zahl ab.
LN()
LOG()
Gibt den natürlichen Logarithmus des Arguments zurück.
LOG2() Gibt den Logarithmus zur Basis 2 des Arguments zurück.
LOG10() Gibt den Logarithmus zur Basis 10 des Arguments zurück.
PI() Gibt die Konstante π zurück.
POW() Gibt das erste Argument zur Potenz des zweiten Arguments zurück.
RADIANS() Wandelt von Grad in Radianten um.
RAND() Gibt einen zufälligen Float-Wert im Bereich 0,0 <= Wert < 1,0 zurück.
ROUND() Rundet das Argument auf die nächste ganze Zahle auf oder ab.
SIN() Gibt den Sinus des Arguments zurück.
SINH() Gibt den hyperbolischen Sinus des Arguments zurück.
SQRT() Gibt die Quadratwurzel des Ausdrucks zurück.
TAN() Gibt den Tangens des Arguments zurück.
TANH() Gibt den hyperbolischen Tangens des Arguments zurück.
ABS(numeric_expr)
Gibt den absoluten Wert des Arguments zurück.
ACOS(numeric_expr)
Gibt den Arkuskosinus des Arguments zurück.
ACOSH(numeric_expr)
Gibt den hyperbolischen Arkuskosinus des Arguments zurück.
ASIN(numeric_expr)
Gibt den Arkussinus des Arguments zurück.
ASINH(numeric_expr)
Gibt den hyperbolischen Arkussinus des Arguments zurück.
ATAN(numeric_expr)
Gibt den Arkustangens des Arguments zurück.
ATANH(numeric_expr)
Gibt den hyperbolischen Arkustangens des Arguments zurück.
ATAN2(numeric_expr1, numeric_expr2)
Gibt den Arkustangens der beiden Argumente zurück.
CEIL(numeric_expr)
Rundet das Argument auf die nächste ganze Zahl auf und gibt den gerundeten Wert zurück.
COS(numeric_expr)
Gibt den Kosinus des Arguments zurück.
COSH(numeric_expr)
Gibt den hyperbolischen Kosinus des Arguments zurück.
DEGREES(numeric_expr)
Gibt numeric_expr von Radiant in Grad umgewandelt zurück.
EXP(numeric_expr)
Gibt das Ergebnis für Konstante "e" (der Basis des natürlichen Logarithmus) hoch numeric_expr zurück.
FLOOR(numeric_expr)
Rundet das Argument auf die nächste ganze Zahl ab und gibt den gerundeten Wert zurück.
LN(numeric_expr)
LOG(numeric_expr)
Gibt den natürlichen Logarithmus des Arguments zurück.
LOG2(numeric_expr)
Gibt den Logarithmus zur Basis 2 des Arguments zurück.
LOG10(numeric_expr)
Gibt den Logarithmus zur Basis 10 des Arguments zurück.
PI()
Gibt die Konstante π zurück. Für die PI()-Funktion sind Klammern erforderlich, die anzeigen, dass es sich um eine Funktion handelt. In diesen Klammern dürfen jedoch keine Argumente stehen. Sie können PI() wie eine Konstante mit mathematischen und arithmetischen Funktionen verwenden.
POW(numeric_expr1, numeric_expr2)
Gibt das Ergebnis von numeric_expr1 hoch numeric_expr2 zurück.
RADIANS(numeric_expr)
Gibt numeric_expr von Grad in Radiant umgewandelt zurück. (Bitte beachten Sie, dass π-Radianten 180 Grad entsprechen.)
RAND([int32_seed])
Gibt einen zufälligen Float-Wert im Bereich 0,0 <= Wert < 1,0 zurück. Solange keine int32_seed-Klausel verwendet wird, generiert jeder LIMIT-Wert innerhalb einer definierten Abfrage immer die gleiche Sequenz an zufälligen Zahlen. Wenn int32_seed nicht angegeben wird, verwendet BigQuery den aktuellen Zeitstempel als Seed-Wert.
ROUND(numeric_expr [, digits])
Rundet das Argument auf die nächste ganze Zahl (oder, sofern angegeben, auf die angegebene Anzahl an Ziffern) ab und gibt den gerundeten Wert zurück.
SIN(numeric_expr)
Gibt den Sinus des Arguments zurück.
SINH(numeric_expr)
Gibt den hyperbolischen Sinus des Arguments zurück.
SQRT(numeric_expr)
Gibt die Quadratwurzel des Ausdrucks zurück.
TAN(numeric_expr)
Gibt den Tangens des Arguments zurück.
TANH(numeric_expr)
Gibt den hyperbolischen Tangens des Arguments zurück.

Erweiterte Beispiele

  • Markierungsrahmenabfrage

    Die folgende Abfrage gibt eine Sammlung von Punkten in einem rechteckigen Markierungsrahmen rund um San Francisco (37.46, -122.50) zurück.

    Beispiel:

    #legacySQL
    SELECT
      year, month,
      AVG(mean_temp) avg_temp,
      MIN(min_temperature) min_temp,
      MAX(max_temperature) max_temp
    FROM
      [weather_geo.table]
    WHERE
      /* Return values between a pair of */
      /* latitude and longitude coordinates */
      lat / 1000 > 37.46 AND
      lat / 1000 < 37.65 AND
      long / 1000 > -122.50 AND
      long / 1000 < -122.30
    GROUP BY
      year, month
    ORDER BY
      year, month ASC;
    
  • Ungefähre Markierungskreisabfrage

    Gibt eine Sammlung von 100 Punkten innerhalb eines ungefähren Kreises rund um Denver Colorado (39.73, -104.98) zurück, der unter Verwendung des Sphärischen Kosinussatzes ermittelt wird. Die Abfrage verwendet mathematische und trigonometrische Funktionen aus BigQuery, darunter PI(), SIN() und COS().

    Da die Erde keine absolute Kugel ist und die Längen- und Breitengrade an den Polen zusammenlaufen, gibt die Abfrage eine Annäherung zurück, die für viele Datentypen hilfreich sein kann.

    Beispiel:

    #legacySQL
    SELECT
      distance, lat, long, temp
    FROM
      (SELECT
        ((ACOS(SIN(39.73756700 * PI() / 180) *
               SIN((lat/1000) * PI() / 180) +
               COS(39.73756700 * PI() / 180) *
               COS((lat/1000) * PI() / 180) *
               COS((-104.98471790 -
               (long/1000)) * PI() / 180)) *
               180 / PI()) * 60 * 1.1515)
          AS distance,
         AVG(mean_temp) AS temp,
         AVG(lat/1000) lat, AVG(long/1000) long
    FROM
      [weather_geo.table]
    WHERE
      month=1 GROUP BY distance)
    WHERE
      distance < 100
    ORDER BY
      distance ASC
    LIMIT 100;
    

Funktionen für reguläre Ausdrücke

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

Beachten Sie, dass es sich bei den regulären Ausdrücken um globale Übereinstimmungen handelt; um am Beginn eines Wortes mit der Suche nach einer Übereinstimmung zu beginnen, müssen Sie das Zeichen "^" verwenden.

Syntax

Funktionen für reguläre Ausdrücke
REGEXP_MATCH() Gibt "true" zurück, wenn das Argument mit dem regulären Ausdruck übereinstimmt.
REGEXP_EXTRACT() Gibt den Teil des Arguments zurück, der mit der Erfassungsgruppe innerhalb des regulären Ausdrucks übereinstimmt.
REGEXP_REPLACE() Ersetzt einen Teilstring, der mit einem regulären Ausdruck übereinstimmt.
REGEXP_MATCH('str', 'reg_exp')

Gibt "true" zurück, wenn str mit dem regulären Ausdruck übereinstimmt. Wenn Sie ohne reguläre Ausdrücke nach übereinstimmenden Strings suchen möchten, verwenden Sie anstelle von REGEXP_MATCH CONTAINS.

Beispiel:

#legacySQL
SELECT
   word,
   COUNT(word) AS count
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   (REGEXP_MATCH(word,r'\w\w\'\w\w'))
GROUP BY word
ORDER BY count DESC
LIMIT 3;

Liefert:

+-------+-------+
| word  | count |
+-------+-------+
| ne'er |    42 |
| we'll |    35 |
| We'll |    33 |
+-------+-------+
REGEXP_EXTRACT('str', 'reg_exp')

Gibt den Teil von str zurück, der mit der Erfassungsgruppe innerhalb des regulären Ausdrucks übereinstimmt.

Beispiel:

#legacySQL
SELECT
   REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment
FROM
   [bigquery-public-data:samples.shakespeare]
GROUP BY fragment
ORDER BY fragment
LIMIT 3;

Liefert:

+----------+
| fragment |
+----------+
| NULL     |
| Al'ce    |
| As'es    |
+----------+
REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')

Gibt einen String zurück, bei dem ein beliebiger Teilstring von orig_str, der mit reg_exp übereinstimmt, durch replace_str ersetzt wird. REGEXP_REPLACE ('Hello', 'lo', 'p') gibt beispielsweise "Help" zurück.

Beispiel:

#legacySQL
SELECT
  REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  REGEXP_MATCH(word, r'ne\'er')
GROUP BY expanded_word
ORDER BY expanded_word
LIMIT 5;

Liefert:

+---------------+
| expanded_word |
+---------------+
| Whenever      |
| never         |
| nevertheless  |
| whenever      |
+---------------+

Erweiterte Beispiele

  • Ergebnissatz nach Übereinstimmung mit regulärem Ausdruck filtern

    Die regulären Funktionen von BigQuery können verwendet werden, um Ergebnisse in einer WHERE-Klausel zu filtern und Ergebnisse in der SELECT-Klausel anzuzeigen. Im folgenden Beispiel werden die beiden Regulärer-Ausdruck-Anwendungsfälle zu einer gemeinsamen Abfrage zusammengefasst.

    Beispiel:

    #legacySQL
    SELECT
      /* Replace white spaces in the title with underscores. */
      REGEXP_REPLACE(title, r'\s+', '_') AS regexp_title, revisions
    FROM
      (SELECT title, COUNT(revision_id) as revisions
      FROM
        [bigquery-public-data:samples.wikipedia]
      WHERE
        wp_namespace=0
        /* Match titles that start with 'G', end with
         * 'e', and contain at least two 'o's.
         */
        AND REGEXP_MATCH(title, r'^G.*o.*o.*e$')
      GROUP BY
        title
      ORDER BY
        revisions DESC
      LIMIT 100);
  • Reguläre Ausdrücke auf Ganzzahlen- oder Float-Daten anwenden

    Die Funktionen von BigQuery für reguläre Ausdrücke können nur auf Stringdaten angewendet werden. Sie können aber mithilfe der STRING()-Funktion Ganzzahl- oder Float-Daten in das Stringformat umwandeln. In diesem Beispiel wird mit STRING() der Ganzzahlwert corpus_date in einen String umgewandelt, der dann von REGEXP_REPLACE bearbeitet wird.

    Beispiel:

    #legacySQL
    SELECT
      corpus_date,
      /* Cast the corpus_date to a string value  */
      REGEXP_REPLACE(STRING(corpus_date),
        '^16',
        'Written in the sixteen hundreds, in the year \''
        ) AS date_string
    FROM [bigquery-public-data:samples.shakespeare]
    /* Cast the corpus_date to string, */
    /* match values that begin with '16' */
    WHERE
      REGEXP_MATCH(STRING(corpus_date), '^16')
    GROUP BY
      corpus_date, date_string
    ORDER BY
      date_string DESC
    LIMIT 5;
    

Stringfunktionen

Stringfunktionen arbeiten mit Stringdaten. Stringkonstanten müssen von einfachen oder doppelten Anführungszeichen umschlossen sein. Bei den Stringfunktionen wird standardmäßig die Groß-/Kleinschreibung beachtet. IGNORE CASE kann an das Ende einer Abfrage angehängt werden, um ohne Berücksichtung der Groß-/Kleinschreibung nach Übereinstimmungen zu suchen. IGNORE CASE kann nur auf ASCII-Zeichen und auf oberster Ebene der Abfrage angewendet werden.

In diesen Funktionen werden keine Platzhalter unterstützt; wenn die Funktionalität für reguläre Ausdrücke verwendet werden soll, nutzen Sie die Funktionen für reguläre Ausdrücke.

Syntax

Stringfunktionen
CONCAT() Gibt die Verkettung von zwei oder mehr Strings oder NULL zurück, wenn einer der Werte NULL ist.
expr CONTAINS 'str' Gibt true zurück, wenn expr das angegebene Stringargument enthält.
INSTR() Gibt den auf Eins basierenden Index des ersten Vorkommens eines Strings zurück.
LEFT() Gibt die Zeichen zurück, die im String ganz links stehen.
LENGTH() Gibt die Länge des Strings zurück.
LOWER() Gibt den ursprünglichen String mit allen Zeichen in Kleinbuchstaben zurück.
LPAD() Fügt Zeichen links neben einem String ein.
LTRIM() Löscht Zeichen auf der linken Seite eines Strings.
REPLACE() Ersetzt alle Vorkommnisse eines Teilstrings.
RIGHT() Gibt die Zeichen zurück, die im String ganz rechts stehen.
RPAD() Fügt Zeichen rechts neben einem String ein.
RTRIM() Entfernt überflüssige Zeichen rechts neben einem String.
SPLIT() Teilt einen String in wiederholte Teilstrings auf.
SUBSTR() Gibt einen Teilstring zurück...
UPPER() Gibt den Originalstring mit allen Zeichen in Großbuchstaben zurück.
CONCAT('str1', 'str2', '...')
str1 + str2 + ...
Gibt die Verkettung von zwei oder mehr Strings oder NULL zurück, wenn einer der Werte NULL ist. Beispiel: Wenn str1 gleich Java und str2 gleich Script ist, gibt CONCAT als Ergebnis JavaScript zurück.
expr CONTAINS 'str'
Gibt true zurück, wenn expr das angegebene Stringargument enthält. Bei diesem Vergleich wird die Groß- und Kleinschreibung berücksichtigt.
INSTR('str1', 'str2')
Gibt den auf Eins basierenden Index des ersten Vorkommens von str2 in str1 oder 0 zurück, wenn str2 nicht in str1 vorkommt.
LEFT('str', numeric_expr)
Gibt die numeric_expr-Zeichen zurück, die ganz links von str stehen. Wenn die Zahl größer als die Anzahl der Zeichen in str ist, wird der gesamte String zurückgegeben. Beispiel: LEFT('seattle', 3) gibt sea zurück.
LENGTH('str')
Gibt einen numerischen Wert für die Länge des Strings zurück. Beispiel: Wenn str gleich '123456' ist, gibt LENGTH den Wert 6 zurück.
LOWER('str')
Gibt den ursprünglichen String mit allen Zeichen in Kleinbuchstaben zurück.
LPAD('str1', numeric_expr, 'str2')
Füllt str1 auf der linken Seite mit str2 auf, wobei str2 so oft wiederholt wird, bis der Ergebnisstring genau aus numeric_expr Zeichen besteht. Beispiel: LPAD('1', 7, '?') gibt Folgendes zurück: ??????1.
LTRIM('str1' [, str2])

Entfernt Zeichen auf der linken Seite von str1. Wenn str2 weggelassen wird, löscht LTRIM Leerzeichen links von str1. Ansonsten entfernt LTRIM alle Zeichen in str2 links von str1 (unter Berücksichtigung der Groß- und Kleinschreibung).

Beispiele:

SELECT LTRIM("Say hello", "yaS") gibt " hello" zurück.

SELECT LTRIM("Say hello", " ySa") gibt "hello" zurück.

REPLACE('str1', 'str2', 'str3')

Ersetzt alle Vorkommnisse von str2 in str1 durch str3.

Gibt die numeric_expr-Zeichen zurück, die ganz rechts von str stehen. Ist die Zahl größer als die Anzahl der Zeichen im String, so wird der gesamte String zurückgegeben. Beispiel: RIGHT('kirkland', 4) gibt land zurück.
RPAD('str1', numeric_expr, 'str2')
Füllt str1 auf der rechten Seite mit str2 auf, wobei str2 so oft wiederholt wird, bis der Ergebnisstring genau aus numeric_expr Zeichen besteht. Beispiel: RPAD('1', 7, '?') gibt 1?????? zurück.
RTRIM('str1' [, str2])

Entfernt überflüssige Zeichen rechts neben str1. Wenn str2 weggelassen wird, löscht RTRIM überflüssige Leerzeichen aus str1. Ansonsten entfernt RTRIM alle Zeichen in str2 rechts von str1 (unter Berücksichtigung der Groß- und Kleinschreibung).

Beispiele:

SELECT RTRIM("Say hello", "leo") gibt "Say h" zurück.

SELECT RTRIM("Say hello ", " hloe") gibt "Say" zurück.

SPLIT('str' [, 'delimiter'])
Teilt einen String in wiederholte Teilstrings auf. Wenn delimiter angegeben ist, wird SPLIT durch die str-Funktion in Teilstrings aufgeteilt. Dabei dient delimiter als Trennzeichen.
SUBSTR('str', index [, max_len])
Gibt einen Teilstring von str aus, beginnend mit index. Wenn der optionale Parameter max_len verwendet wird, ist der zurückgegebene String maximal max_len Zeichen lang. Die Zählung startet bei 1. Das erste Zeichen im String befindet sich also an Position 1 (nicht null). Wenn index den Wert 5 hat, beginnt der Teilstring mit dem fünften Zeichen von links in str. Wenn index den Wert -4 hat, beginnt der Teilstring mit dem vierten Zeichen von rechts in str. Beispiel: SUBSTR('awesome', -4, 4) gibt den Teilstring some zurück.
UPPER('str')
Gibt den Originalstring mit allen Zeichen in Großbuchstaben zurück.

Escaping von Sonderzeichen in Strings

Verwenden Sie zum Escaping von Sonderzeichen in Strings eine der folgenden Methoden:

  • Verwenden Sie die Notation '\xDD', wobei auf '\x' die zweistellige hexadezimale Darstellung des Zeichens folgt.
  • Verwenden Sie vor Schrägstrichen, einfachen Anführungszeichen und Anführungszeichen einen Escaping-Schrägstrich.
  • Verwenden Sie für die anderen Zeichen Sequenzen im C-Stil ('\a', '\b', '\f', '\n', '\r', '\t', und '\v').

Einige Beispiele für die Nutzung von Escaping:

'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported

Tabellenplatzhalter-Funktionen

Tabellenplatzhalter-Funktionen sind eine bequeme Möglichkeit, eine Abfrage auf Daten aus einem bestimmten Satz Tabellen auszuführen. Eine Tabellenplatzhalter-Funktion entspricht einer durch Kommas getrennten Zusammenführung aller Tabellen, die der Platzhalterfunktion entsprechen. Bei Verwendung einer Tabellenplatzhalter-Funktion greift BigQuery nur auf Tabellen zu, die mit dem Platzhalter übereinstimmen, und stellt auch nur diese in Rechnung. Tabellenplatzhalter-Funktionen werden in der FROM-Klausel der Abfrage angeben.

Werden die Tabellenplatzhalter-Funktionen in einer Abfrage verwendet, brauchen die Funktionen nicht mehr in Klammern gesetzt zu werden. Beispielsweise verwenden einige der folgenden Beispiele Klammern, während andere es nicht tun.

Im Cache gespeicherte Ergebnisse werden bei Abfragen auf mehrere Tabellen, die eine Platzhalterfunktion verwenden, nicht unterstützt (auch wenn die Option Im Cache gespeicherte Ergebnisse verwenden aktiviert ist). Wenn Sie die gleiche Platzhalterabfrage mehrmals ausführen, wird Ihnen jede Abfrage in Rechnung gestellt.

Syntax

Tabellenplatzhalter-Funktionen
TABLE_DATE_RANGE() Führt Abfragen auf mehrere Tagestabellen durch, die einen bestimmten Datumsbereich abdecken.
TABLE_DATE_RANGE_STRICT() Führt Abfragen auf mehrere Tagestabellen durch, die einen bestimmten Datumsbereich abdecken; ohne fehlende Daten.
TABLE_QUERY() Fragt Tabellen ab, deren Namen mit einem bestimmten Prädikat übereinstimmen.
TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

Fragt Tagestabellen ab, bei denen es eine Überschneidung mit dem Zeitraum zwischen <timestamp1> und <timestamp2> gibt.

Die Tabellennamen müssen das folgende Format haben: <prefix><day>, wobei <day> das YYYYMMDD-Format hat.

Zum Generieren der Zeitstempelparameter können die Funktionen für Datum und Uhrzeit verwendet werden. Beispiel:

  • TIMESTAMP('2012-10-01 02:03:04')
  • DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

Beispiel: Tabellen zwischen zwei Tagen abrufen

In diesem Beispiel wird davon ausgegangen, dass die folgenden Tabellen existieren:

  • mydata.people20140325
  • mydata.people20140326
  • mydata.people20140327
#legacySQL
SELECT
  name
FROM
  TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27'))
WHERE
  age >= 35

Stimmt mit den folgenden Tabellen überein:

  • mydata.people20140325
  • mydata.people20140326
  • mydata.people20140327

Beispiel: Tabellen in einem zweitägigen Datumsbereich bis "heute"

In diesem Beispiel wird davon ausgegangen, dass die folgenden Tabellen in einem Projekt namens myproject-1234 existieren:

  • mydata.people20140323
  • mydata.people20140324
  • mydata.people20140325
#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'),
                    CURRENT_TIMESTAMP()))
WHERE
  age >= 35

Stimmt mit den folgenden Tabellen überein:

  • mydata.people20140323
  • mydata.people20140324
  • mydata.people20140325
TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)

Diese Funktion entspricht TABLE_DATE_RANGE. Der einzige Unterschied liegt darin, dass TABLE_DATE_RANGE_STRICT fehlschlägt und der Fehler Not Found: Table <table_name> zurückgegeben wird, wenn in der Sequenz eine Tagestabelle fehlt.

Beispiel: Fehler aufgrund fehlender Tabelle

In diesem Beispiel wird davon ausgegangen, dass die folgenden Tabellen existieren:

  • people20140325
  • people20140327
#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27')))
WHERE age >= 35

Das Beispiel oben gibt für die Tabelle "people20140326" den Fehler "Not Found" (Nicht gefunden) zurück.

TABLE_QUERY(dataset, expr)

Fragt Tabellen ab, deren Namen mit dem angegebenen expr übereinstimmen. Der expr-Parameter muss als String dargestellt werden und einen Ausdruck zur Auswertung enthalten. Beispiel: 'length(table_id) < 3'.

Beispiel: Abgleich von Tabellen, deren Namen "oo" enthalten und deren Länge größer als 4 ist

In diesem Beispiel wird davon ausgegangen, dass die folgenden Tabellen existieren:

  • mydata.boo
  • mydata.fork
  • mydata.ooze
  • mydata.spoon
#legacySQL
SELECT
  speed
FROM (TABLE_QUERY([myproject-1234:mydata],
                  'table_id CONTAINS "oo" AND length(table_id) >= 4'))

Stimmt mit den folgenden Tabellen überein:

  • mydata.ooze
  • mydata.spoon

Beispiel: Abgleich von Tabellen, deren Namen mit "boo" beginnen, gefolgt von 3 bis 5 Ziffern

In diesem Beispiel wird davon ausgegangen, dass die folgenden Tabellen in einem Projekt namens myproject-1234 existieren:

  • mydata.book4
  • mydata.book418
  • mydata.boom12345
  • mydata.boom123456789
  • mydata.taboo999
#legacySQL
SELECT
  speed
FROM
  TABLE_QUERY([myproject-1234:mydata],
               'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')

Stimmt mit den folgenden Tabellen überein:

  • mydata.book418
  • mydata.boom12345

URL-Funktionen

Syntax

URL-Funktionen
HOST() Gibt nach der Angabe einer URL den Hostnamen als String zurück.
DOMAIN() Gibt nach der Angabe einer URL die Domain als String zurück.
TLD() Gibt nach der Angabe einer URL die Top-Level-Domain und jede beliebige Länderdomain in der URL zurück.
HOST('url_str')
Gibt nach der Angabe einer URL den Hostnamen als String zurück. Beispiel: HOST('http://www.google.com:80/index.html') gibt 'www.google.com' zurück
DOMAIN('url_str')
Gibt nach der Angabe einer URL die Domain als String zurück. Beispiel: DOMAIN('http://www.google.com:80/index.html') gibt 'google.com' zurück.
TLD('url_str')
Gibt nach der Angabe einer URL die Top-Level-Domain und jede beliebige Länderdomain in der URL zurück. Beispiel: TLD('http://www.google.com:80/index.html') gibt '.com' zurück. TLD('http://www.google.co.uk:80/index.html') gibt '.co.uk' zurück.

Hinweise:

  • Diese Funktionen führen kein umgekehrtes DNS-Lookup durch; wenn sie also mithilfe einer IP-Adresse aufgerufen werden, geben die Funktionen Teile der IP-Adresse und nicht des Hostnamens zurück.
  • Alle URL-Parsing-Funktionen erwarten Kleinbuchstaben. Großbuchstaben in der URL führen zu einem NULL- oder ansonsten inkorrekten Ergebnis. Ziehen Sie in Betracht, die Eingabe über LOWER() an diese Funktion weiterzugeben, wenn die Daten sowohl aus Groß- als auch Kleinschreibung bestehen.

Erweitertes Beispiel

Domain-Namen aus URL-Daten parsen

Diese Abfrage verwendet die DOMAIN()-Funktion, um die beliebtesten Domains zurückzugeben, die auf GitHub als Repository-Startseiten aufgeführt sind. Sie können auch HAVING verwenden, um Datensätze mithilfe der DOMAIN()-Funktion zu filtern. Es handelt sich um eine nützliche Funktion zum Ermitteln der Referrer-Informationen aus den URL-Daten.

Beispiele:

#legacySQL
SELECT
  DOMAIN(repository_homepage) AS user_domain,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_domain
HAVING
  user_domain IS NOT NULL AND user_domain != ''
ORDER BY
  activity_count DESC
LIMIT 5;

Liefert:

+-----------------+----------------+
|   user_domain   | activity_count |
+-----------------+----------------+
| github.com      |         281879 |
| google.com      |          34769 |
| khanacademy.org |          17316 |
| sourceforge.net |          15103 |
| mozilla.org     |          14091 |
+-----------------+----------------+

Zur spezifischen Anzeige von TLD-Daten verwenden Sie die TLD()-Funktion. Dieses Beispiel zeigt die obersten TLDs an, die sich nicht in der Liste der häufigen Beispiele befinden.

#legacySQL
SELECT
  TLD(repository_homepage) AS user_tld,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_tld
HAVING
  /* Only consider TLDs that are NOT NULL */
  /* or in our list of common TLDs */
  user_tld IS NOT NULL AND NOT user_tld
  IN ('','.com','.net','.org','.info','.edu')
ORDER BY
  activity_count DESC
LIMIT 5;

Liefert:

+----------+----------------+
| user_tld | activity_count |
+----------+----------------+
| .de      |          22934 |
| .io      |          17528 |
| .me      |          13652 |
| .fr      |          12895 |
| .co.uk   |           9135 |
+----------+----------------+

Fensterfunktionen

Fensterfunktionen, die auch als Analysefunktionen bezeichnet werden, ermöglichen Berechnungen für ein spezifisches Teilset oder "Fenster" eines Ergebnissatzes. Fensterfunktionen erleichtern das Erstellen von Berichten, die komplexe Analysen umfassen, wie den Durchschnitt der vergangenen 12 Monate und laufende Summen.

Jede Fensterfunktion erfordert eine OVER-Klausel, die das obere und untere Ende des Fensters angibt. Die drei Komponenten der OVER-Klausel (Partitionierung, Sortierung und Framing) ermöglichen eine zusätzliche Kontrolle über das Fenster. Mithilfe der Partitionierung können die Eingabedaten in logische Gruppen aufgeteilt werden, die über ein gemeinsames Merkmal verfügen. Mithilfe der Sortierung können die Ergebnisse innerhalb einer Partition sortiert werden. Mithilfe von Framing kann ein Schiebefensterrahmen innerhalb einer Partition erstellt werden, der sich relativ zur aktuellen Zeile bewegt. Die Größe des beweglichen Fensterrahmens kann basierend auf einer Anzahl von Zeilen oder einem Wertebereich wie dem Zeitintervall konfiguriert werden.

#legacySQL
SELECT <window_function>
  OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr> [ASC | DESC]]
      [<window-frame-clause>]
     )
PARTITION BY
Definiert die Basispartition, mit der diese Funktion arbeitet. Geben Sie eine oder mehrere kommagetrennte Spaltennamen an; für jeden einzelnen Satz von Werten in diesen Spalten wird eine Partition erstellt, ähnlich wie bei der GROUP BY-Klausel. Wenn PARTITION BY weggelassen wird, verwendet die Fensterfunktion als Eingabe für die Basispartition alle Zeilen.
Die PARTITION BY-Klausel ermöglicht es Fensterfunktionen auch, Daten zu partitionieren und die Ausführung zu parallelisieren. Wenn Sie möchten, dass eine Fensterfunktion in allowLargeResults verwendet oder weitere Join-Operationen oder Aggregationen auf die Ausgabe der Fensterfunktion angewendet werden sollen, kann die Ausführung mit PARTITION BY parallelisiert werden.
Die JOIN EACH- und GROUP EACH BY-Klauseln können nicht auf die Ausgabe von Fensterfunktion angewendet werden. Wenn mit Fensterfunktionen große Abfrageergebnisse generiert werden sollen, muss PARTITION BY verwendet werden.
ORDER BY
Sortiert die Partition. Wenn ORDER BY nicht vorhanden ist, kann eine Standardsortierreihenfolge nicht garantiert werden. Die Sortierung erfolgt auf Partitionsebene, bevor eine Window-Framing-Klausel angewendet wird. Wenn ein RANGE-Window angegeben wird, sollte eine ORDER BY-Klausel hinzugefügt werden. Die Standardreihenfolge ist ASC.
In einigen Fällen ist ORDER BY optional. In bestimmten Fensterfunktionen wie rank() oder dense_rank() ist die Klausel aber erforderlich.
Wird ORDER BY ohne die Angabe von ROWS oder RANGE verwendet, impliziert ORDER BY, dass das Fenster vom Anfang der Partition bis zur aktuellen Zeile reicht. Fehlt eine ORDER BY-Klausel, wird das gesamte Fenster als Partition angesehen.
<window-frame-clause>
{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}
Eine Teilmenge der verwendeten Partition. Die Größe kann der Partition entsprechen oder kleiner sein. Wird ORDER BY ohne eine window-frame-clause verwendet, entspricht der Standardfensterrahmen dem RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Werden sowohl ORDER BY als auch window-frame-clause ausgelassen, entspricht der Standardfensterrahmen der gesamten Partition.
  • ROWS: definiert ein Fenster über die Zeilenposition in Bezug auf die aktuelle Zeile. Um beispielsweise eine Spalte hinzuzufügen, die die Summe der vorherigen 5 Gehaltswert-Zeilen anzeigt, müsste nach SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) gesucht werden. Der Satz an Zeilen umfasst normalerweise die aktuelle Zeile, braucht aber nicht angegeben zu werden.
  • RANGE: definiert ein Fenster über einen Wertebereich in einer definierten Spalte in Bezug zum Wert dieser Spalte in der aktuellen Zeile. Wird nur auf Zahlen und Daten angewendet, wobei es sich bei Datumswerten um einfache Ganzzahlen handelt (Mikrosekunden seit der Epoche). Benachbarte Zeilen mit demselben Wert werden als Peer-Zeilen bezeichnet. Peer-Zeilen der CURRENT ROW sind in einem Fensterrahmen enthalten, der CURRENT ROW angibt. Wird beispielsweise CURRENT ROW als Fensterende angegeben und verfügt die folgende Zeile im Fenster über den gleichen Wert, wird dieser in der Funktionsberechnung berücksichtigt.
  • BETWEEN <start> AND <end>: ein Bereich, inklusive der Anfangs- und Endzeilen. Der Bereich braucht die aktuelle Zeile nicht zu enthalten; <start> muss aber vor <end> liegen oder dem Wert entsprechen.
  • <start>: gibt den Start-Offset dieses Fensters in Bezug auf die aktuelle Zeile an. Die folgenden Optionen werden unterstützt:
    {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
    wobei <expr> eine positive Ganzzahl ist, PRECEDING eine vorangehende Zeilennummer oder einen Bereichswert und FOLLOWING eine nachfolgende Zeilennummer oder einen Bereichswert angibt. UNBOUNDED PRECEDING steht für die erste Zeile der Partition. Wenn der Beginn vor dem Beginn des Fensters liegt, wird er auf die erste Zeile der Partition gesetzt.
  • <end>: gibt den End-Offset dieses Fensters in Bezug auf die aktuelle Zeile an. Die folgenden Optionen werden unterstützt:
    {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
    wobei <expr> eine positive Ganzzahl ist, PRECEDING eine vorangehende Zeilennummer oder einen Bereichswert und FOLLOWING eine nachfolgende Zeilennummer oder einen Bereichswert angibt. UNBOUNDED FOLLOWING steht für die letzte Zeile der Partition. Wenn das Ende nach dem Ende des Fensters liegt, wird es auf die letzte Zeile der Partition gesetzt.

Anders als bei Aggregatfunktionen, bei denen viele Eingabezeilen zu einer Ausgabezeile zusammengeführt werden, geben Fensterfunktionen für jede Eingabezeile eine Ausgabezeile zurück. Mit dieser Funktion ist es einfacher, Abfragen zu erstellen, die laufende Summen und gleitende Mittelwerte berechnen. Die folgende Abfrage gibt beispielsweise eine laufende Summe für ein kleines, aus fünf Zeilen bestehendes Dataset zurück, das durch SELECT-Anweisungen definiert wird:

#legacySQL
SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value);

Rückgabewert:

+------+-------+--------------+
| name | value | RunningTotal |
+------+-------+--------------+
| a    |     0 |            0 |
| b    |     1 |            1 |
| c    |     2 |            3 |
| d    |     3 |            6 |
| e    |     4 |           10 |
+------+-------+--------------+

Im folgenden Beispiel wird ein gleitender Mittelwert der Werte in der aktuellen Zeile und der davorstehenden Zeile berechnet. Der Fensterrahmen besteht aus zwei Zeilen, die sich zusammen mit der aktuellen Zeile bewegen.

#legacySQL
SELECT
  name,
  value,
  AVG(value)
    OVER (ORDER BY value
          ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    AS MovingAverage
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value);

Rückgabewert:

+------+-------+---------------+
| name | value | MovingAverage |
+------+-------+---------------+
| a    |     0 |           0.0 |
| b    |     1 |           0.5 |
| c    |     2 |           1.5 |
| d    |     3 |           2.5 |
| e    |     4 |           3.5 |
+------+-------+---------------+

Syntax

Fensterfunktionen
AVG()
COUNT(*)
COUNT([DISTINCT])
MAX()
MIN()
STDDEV()
SUM()
Die gleichen Operationen wie die zugehörigen Aggregatfunktionen; sie werden jedoch für ein von der OVER-Klausel definiertes Fenster berechnet.
CUME_DIST() Gibt einen Double-Wert zurück, der die kumulative Verteilung eines Werts in einer Gruppe von Werten angibt...
DENSE_RANK() Gibt den Ganzzahlrang eines Werts in einer Gruppe von Werten an.
FIRST_VALUE() Gibt den ersten Wert des angegebenen Felds im Fenster an.
LAG() Ermöglicht das Lesen der Daten aus einer vorherigen Zeile in einem Fenster.
LAST_VALUE() Gibt den letzten Wert des angegebenen Felds im Fenster zurück.
LEAD() Ermöglicht das Lesen der Daten aus einer folgenden Zeile innerhalb eines Fensters.
NTH_VALUE() Gibt den Wert von <expr> an der Position <n> im Fensterrahmen zurück...
NTILE() Teilt das Fenster in die angegebene Anzahl von Buckets auf.
PERCENT_RANK() Gibt den Rang der aktuellen Zeile relativ zu den anderen Zeilen in der Partition an.
PERCENTILE_CONT() Gibt einen interpolierten Wert zurück, der unter Berücksichtigung des Fensters auf das Perzentil-Argument abgebildet wird...
PERCENTILE_DISC() Gibt den Wert zurück, der dem Perzentil des Arguments für das Fenster am nächsten kommt.
RANK() Gibt den Ganzzahlrang eines Werts in einer Gruppe von Werten an.
RATIO_TO_REPORT() Gibt das Verhältnis jeden Werts zur Summe der Werte an.
ROW_NUMBER() Gibt die aktuelle Zeilennummer des Abfrageergebnisses für das Fenster zurück.
AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)
Diese Fensterfunktionen führen dieselben Operationen wie die entsprechenden Aggregatfunktionen durch. Sie werden aber für ein von der Klausel OVER definiertes Fenster berechnet.

Ein anderer wichtiger Unterschied liegt darin, dass die COUNT([DISTINCT] field)-Funktion zu exakten Ergebnissen führt, wenn sie als Fensterfunktion verwendet wird. Ihre Verhaltensweise ähnelt dann der Aggregatfunktion EXACT_COUNT_DISTINCT().

In der Beispielabfrage sorgt die ORDER BY-Klausel dafür, dass das Fenster vom Beginn der Partition bis zur aktuellen Zeile berechnet wird. Auf diese Weise wird für das Jahr eine kumulative Summe generiert.

#legacySQL
SELECT
   corpus_date,
   corpus,
   word_count,
   SUM(word_count) OVER (
     PARTITION BY corpus_date
     ORDER BY word_count) annual_total
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   word='love'
ORDER BY
   corpus_date, word_count
        

Liefert:

corpus_date Korpus word_count annual_total
0 various 37 37
0 sonnets 157 194
1590 2kinghenryvi 18 18
1590 1kinghenryvi 24 42
1590 3kinghenryvi 40 82
CUME_DIST()

Gibt einen Double-Wert zurück, der die kumulative Verteilung eines Werts in einer Gruppe von Werten angibt und mit der Formel <number of rows preceding or tied with the current row> / <total rows> berechnet wird. Verknüpfte Werte geben den gleichen kumulierten Verteilungswert zurück.

Diese Fensterfunktion erfordert die Angabe von ORDER BY in der OVER-Klausel.

#legacySQL
SELECT
   word,
   word_count,
   CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Liefert:

Wort word_count cume_dist
handkerchief 29 0,2
satisfaction 5 0,4
displeasure 4 0,8
instruments 4 0,8
circumstance 3 1,0
DENSE_RANK()

Gibt den Ganzzahlrang eines Werts in einer Gruppe von Werten an. Der Rang wird basierend auf dem Vergleich mit anderen Werten in der Gruppe berechnet.

Verknüpfte Werte werden genauso wie der Rang angezeigt. Der Rang des nächsten Werts wird um 1 erhöht. Wenn beispielsweise zwei Werte den Rang 2 erhalten, erhält der nächste Wert den Rang 3. Soll die Rangliste eine Leerstelle enthalten, muss rank() verwendet werden.

Diese Fensterfunktion erfordert die Angabe von ORDER BY in der OVER-Klausel.

#legacySQL
SELECT
   word,
   word_count,
   DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
Liefert:
Wort word_count dense_rank
handkerchief 29 1
satisfaction 5 2
displeasure 4 3
instruments 4 3
circumstance 3 4
FIRST_VALUE(<field_name>)

Gibt den ersten Wert von <field_name> im Fenster zurück.

#legacySQL
SELECT
   word,
   word_count,
   FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1
Liefert:
Wort word_count fv
imperfectly 1 imperfectly
LAG(<expr>[, <offset>[, <default_value>]])

Ermöglicht das Lesen der Daten aus einer vorherigen Zeile in einem Fenster. LAG() gibt dabei den Wert von <expr> für die Zeile zurück, die sich <offset> Zeilen über der aktuellen Zeile befindet. Gibt es diese Zeile nicht, wird der Wert <default_value> zurückgegeben.

#legacySQL
SELECT
   word,
   word_count,
   LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Liefert:

Wort word_count Verzögerung
handkerchief 29 Null
satisfaction 5 handkerchief
displeasure 4 satisfaction
instruments 4 displeasure
circumstance 3 instruments
LAST_VALUE(<field_name>)

Gibt den letzten Wert von <field_name> im Fenster zurück.

#legacySQL
SELECT
   word,
   word_count,
   LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1

Liefert:

Wort word_count LV
imperfectly 1 imperfectly

LEAD(<expr>[, <offset>[, <default_value>]])

Ermöglicht das Lesen der Daten aus einer folgenden Zeile innerhalb eines Fensters. LEAD() gibt dabei den Wert von <expr> für die Zeile zurück, die sich <offset> Zeilen unter der aktuellen Zeile befindet. Gibt es diese Zeile nicht, wird der Wert <default_value> zurückgegeben.

#legacySQL
SELECT
   word,
   word_count,
   LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
Liefert:
Wort word_count Lead
handkerchief 29 satisfaction
satisfaction 5 displeasure
displeasure 4 instruments
instruments 4 circumstance
circumstance 3 Null
NTH_VALUE(<expr>, <n>)

Gibt den Wert von <expr> an der Position <n> im Fensterrahmen zurück, wobei <n> ein auf eins basierender Index ist.

NTILE(<num_buckets>)

Teilt eine Reihe von Zeilen in <num_buckets> Buckets auf und weist jeder Zeile eine zugehörige Bucket-Nummer als Ganzzahl zu. Die Funktion ntile() weist die Bucket-Nummern so gleichmäßig wie möglich zu und gibt für jede Zeile einen Wert von 1 bis <num_buckets> zurück.

#legacySQL
SELECT
   word,
   word_count,
   NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
Liefert:
Wort word_count Ntil
handkerchief 29 1
satisfaction 5 1
displeasure 4 1
instruments 4 2
circumstance 3 2
PERCENT_RANK()

Gibt den Rang der aktuellen Zeile relativ zu den anderen Zeilen in der Partition an. Der Bereich der zurückgegebenen Werte reicht von 0 bis 1 (einschließlich). Der erste zurückgegebene Wert ist 0,0.

Diese Fensterfunktion erfordert die Angabe von ORDER BY in der OVER-Klausel.

#legacySQL
SELECT
   word,
   word_count,
   PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
Liefert:
Wort word_count p_rank
handkerchief 29 0,0
satisfaction 5 0,25
displeasure 4 0,5
instruments 4 0,5
circumstance 3 1,0
PERCENTILE_CONT(<percentile>)

Gibt einen interpolierten Wert zurück, der unter Berücksichtigung des Fensters auf das Perzentil-Argument abgebildet wird, nachdem eine Sortierung durch die ORDER BY-Klausel stattfand.

<percentile> muss zwischen 0 und 1 liegen.

Diese Fensterfunktion erfordert die Angabe von ORDER BY in der OVER-Klausel.

#legacySQL
SELECT
   word,
   word_count,
   PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
Liefert:
Wort word_count p_cont
handkerchief 29 4
satisfaction 5 4
displeasure 4 4
instruments 4 4
circumstance 3 4
PERCENTILE_DISC(<percentile>)

Gibt den Wert zurück, der dem Perzentil des Arguments für das Fenster am nächsten kommt.

<percentile> muss zwischen 0 und 1 liegen.

Diese Fensterfunktion erfordert die Angabe von ORDER BY in der OVER-Klausel.

#legacySQL
SELECT
   word,
   word_count,
   PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
Liefert:
Wort word_count p_disc
handkerchief 29 4
satisfaction 5 4
displeasure 4 4
instruments 4 4
circumstance 3 4
RANK()

Gibt den Ganzzahlrang eines Werts in einer Gruppe von Werten an. Der Rang wird basierend auf dem Vergleich mit anderen Werten in der Gruppe berechnet.

Verknüpfte Werte werden genauso wie der Rang angezeigt. Der Rang des nächsten Werts wird abhängig davon erhöht, wie viele verknüpfte Werte zuvor vorgekommen sind. Wenn beispielsweise zwei Werte den Rang 2 erhalten, erhält der nächste Wert den Rang 4, nicht 3. Soll die Rangliste keine Leerstelle enthalten, muss dense_rank() verwendet werden.

Diese Fensterfunktion erfordert die Angabe von ORDER BY in der OVER-Klausel.

#legacySQL
SELECT
   word,
   word_count,
   RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
Liefert:
Wort word_count Rang
handkerchief 29 1
satisfaction 5 2
displeasure 4 3
instruments 4 3
circumstance 3 5
RATIO_TO_REPORT(<column>)

Gibt das Verhältnis jeden Werts zur Summe der Werte als Double-Wert zwischen 0 und 1 zurück.

#legacySQL
SELECT
   word,
   word_count,
   RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
Liefert:
Wort word_count r_to_r
handkerchief 29 0,6444444444444445
satisfaction 5 0,1111111111111111
displeasure 4 0,08888888888888889
instruments 4 0,08888888888888889
circumstance 3 0,06666666666666667
ROW_NUMBER()

Gibt die aktuelle Zeilennummer des Abfrageergebnisses für das Fenster zurück (beginnend mit 1).

#legacySQL
SELECT
   word,
   word_count,
   ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
Liefert:
Wort word_count row_num
handkerchief 29 1
satisfaction 5 2
displeasure 4 3
instruments 4 4
circumstance 3 5

Andere Funktionen

Syntax

Andere Funktionen
CASE WHEN ... THEN Mit CASE kann aus zwei oder mehr alternativen Ausdrücken in einer Abfrage gewählt werden.
CURRENT_USER() Gibt die E-Mail-Adresse des Nutzers zurück, der die Abfrage ausführt.
EVERY() Gibt "true" zurück, wenn das Argument für alle Eingaben wahr ist.
FROM_BASE64() Wandelt den Eingabestring in Base-64-Verschlüsselung in das BYTES-Format um.
HASH() Berechnet und gibt einen signierten 64-Bit-Hash-Wert zurück...
FARM_FINGERPRINT() Berechnet und gibt einen signierten 64-Bit-Fingerabdruckwert zurück...
IF() Gibt das zweite Argument zurück, wenn das erste Argument wahr ist. Ansonsten wird das dritte Argument zurückgegeben.
POSITION() Gibt die auf Eins basierende sequenzielle Position des Arguments zurück.
SHA1() Gibt einen SHA1-Hash-Wert im BYTES-Format zurück.
SOME() Gibt "true" zurück, wenn das Argument für mindestens eine Ausgabe wahr ist.
TO_BASE64() Wandelt das BYTES-Argument in einen String in Base-64-Verschlüsselung um.
CASE WHEN when_expr1 THEN then_expr1
  WHEN when_expr2 THEN then_expr2 ...
  ELSE else_expr END
Mit CASE kann aus zwei oder mehr alternativen Ausdrücken in einer Abfrage gewählt werden. WHEN-Ausdrücke müssen boolesch sein und alle Ausdrücke in den THEN-Klauseln und der ELSE-Klausel müssen kompatible Typen sein.
CURRENT_USER()
Gibt die E-Mail-Adresse des Nutzers zurück, der die Abfrage ausführt.
EVERY(<condition>)
Gibt true zurück, wenn condition für alle Eingaben "true" ist. Wird diese Funktion zusammen mit der OMIT IF-Klausel verwendet, ist sie nützlich für Abfragen, die wiederkehrende Felder beinhalten.
FROM_BASE64(<str>)
Wandelt den base64-codierten Eingabestring str in das BYTES-Format um. BYTES kann mithilfe von TO_BASE64() in einen base64-codierten String umgewandelt werden.
HASH(expr)
Gibt einen signierten 64-Bit-Hash-Wert der Byte von expr zurück, gemäß der CityHash-Bibliothek (Version 1.0.3.). Es werden alle String- und Ganzzahlausdrücke unterstützt. Die Funktion akzeptiert IGNORE CASE für Strings, wodurch fallunveränderliche Werte zurückgegeben werden.
FARM_FINGERPRINT(expr)
Gibt einen signierten 64-Bit-Fingerabdruckwert der STRING- oder BYTES-Eingabe mit der Fingerprint64-Funktion aus der Open-Source-FarmHash-Bibliothek zurück. Die Ausgabe dieser Funktion für eine bestimmte Eingabe ändert sich nie und stimmt mit der Ausgabe der FARM_FINGERPRINT-Funktion überein, wenn Standard-SQL verwendet wird. Die Funktion berücksichtigt IGNORE CASE für Strings, wodurch von der Groß-/Kleinschreibung unabhängige Werte zurückgegeben werden.
IF(condition, true_return, false_return)
Gibt abhängig davon, ob true_return "true" oder "false" ist, entweder false_return oder condition zurück. Die Rückgabewerte können entweder Literale oder von Feldern abgeleitete Werte sein. Es muss sich jedoch um denselben Datentyp handeln. Feldabgeleitete Werte brauchen nicht in die SELECT-Klausel aufgenommen zu werden.
POSITION(field)
Gibt die auf Eins basierende sequenzielle Position von field in einem Satz wiederholter Felder zurück.
SHA1(<str>)
Gibt einen SHA1-Hash-Wert des Eingabestrings str im BYTES-Format zurück. Das Ergebnis kann mithilfe von TO_BASE64() in die Base-64-Verschlüsselung umgewandelt werden. Beispiel:
#legacySQL
SELECT
  TO_BASE64(SHA1(corpus))
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT
  100;
SOME(<condition>)
Gibt true zurück, wenn condition für mindestens eine der Eingaben "true" ist. Wird diese Funktion zusammen mit der OMIT IF-Klausel verwendet, ist sie nützlich für Abfragen, die wiederkehrende Felder beinhalten.
TO_BASE64(<bin_data>)
Wandelt die BYTES-Eingabe bin_data in einen base64-codierten String um. Beispiel:
#legacySQL
SELECT
  TO_BASE64(SHA1(title))
FROM
  [bigquery-public-data:samples.wikipedia]
LIMIT
  100;
BYTES können mithilfe von FROM_BASE64() in einen String mit Base-64-Verschlüsselung umgewandelt werden.

Erweiterte Beispiele

  • Ergebnisse mithilfe von Bedingungen in Kategorien zusammenführen

    Die folgende Abfrage verwendet einen CASE/WHEN-Block, um Ergebnisse basierend auf einer Liste von Zuständen in "Regionen"-Kategorien zusammenzuführen. Wird der Zustand nicht als Option in einem der WHEN-Ausdrücke angezeigt, wird der Statuswert standardmäßig auf "None" gesetzt.

    Beispiel:

    #legacySQL
    SELECT
      CASE
        WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID',
                       'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM')
          THEN 'West'
        WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL',
                       'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV',
                       'MD', 'DC', 'DE')
          THEN 'South'
        WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA',
                       'MO', 'WI', 'IL', 'IN', 'MI', 'OH')
          THEN 'Midwest'
        WHEN state IN ('NY', 'PA', 'NJ', 'CT',
                       'RI', 'MA', 'VT', 'NH', 'ME')
          THEN 'Northeast'
        ELSE 'None'
      END as region,
      average_mother_age,
      average_father_age,
      state, year
    FROM
      (SELECT
         year, state,
         SUM(mother_age)/COUNT(mother_age) as average_mother_age,
         SUM(father_age)/COUNT(father_age) as average_father_age
       FROM
         [bigquery-public-data:samples.natality]
       WHERE
         father_age < 99
       GROUP BY
         year, state)
    ORDER BY
      year
    LIMIT 5;
    

    Liefert:

    +--------+--------------------+--------------------+-------+------+
    | region | average_mother_age | average_father_age | state | year |
    +--------+--------------------+--------------------+-------+------+
    | South  | 24.342600163532296 | 27.683769419460344 | AR    | 1969 |
    | West   | 25.185041908446163 | 28.268214055448098 | AK    | 1969 |
    | West   | 24.780776677578217 | 27.831181063905248 | CA    | 1969 |
    | West   | 25.005834769924412 | 27.942978384829598 | AZ    | 1969 |
    | South  | 24.541730952905738 | 27.686430093306885 | AL    | 1969 |
    +--------+--------------------+--------------------+-------+------+
    
  • Pivot-Tabelle simulieren

    Mithilfe von bedingten Anweisungen können die Ergebnisse einer Subselect-Abfrage in Zeilen und Spalten organisiert werden. Im folgenden Beispiel werden die Ergebnisse einer Suche nach den am häufigsten überarbeiteten Wikipedia-Artikeln, die mit dem Wert "Google" beginnen, als Spalten organisiert, wobei die Anzahl an Überarbeitungen angezeigt wird, wenn verschiedene Kriterien erfüllt werden.

    Beispiel:

    #legacySQL
    SELECT
      page_title,
      /* Populate these columns as True or False, */
      /*  depending on the condition */
      IF (page_title CONTAINS 'search',
          INTEGER(total), 0) AS search,
      IF (page_title CONTAINS 'Earth' OR
          page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo,
    FROM
      /* Subselect to return top revised Wikipedia articles */
      /* containing 'Google', followed by additional text. */
      (SELECT
        TOP (title, 5) as page_title,
        COUNT (*) as total
       FROM
         [bigquery-public-data:samples.wikipedia]
       WHERE
         REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0
      );
    

    Liefert:

    +---------------+--------+------+
    |  page_title   | search | geo  |
    +---------------+--------+------+
    | Google search |   4261 |    0 |
    | Google Earth  |      0 | 3874 |
    | Google Chrome |      0 |    0 |
    | Google Maps   |      0 | 2617 |
    | Google bomb   |      0 |    0 |
    +---------------+--------+------+
    
  • HASH zum Auswählen einer zufälligen Stichprobe Ihrer Daten verwenden

    Einige Abfragen können ein brauchbares Ergebnis liefern, indem sie mit zufälligen Teilstichproben des Ergebnissatzes arbeiten. Zum Abrufen zufälliger Stichproben aus den Werten können mithilfe der HASH-Funktion Ergebnisse zurückgegeben werden, in denen der Modulo "n" des Hashwerts Null ist.

    Die folgende Abfrage findet beispielsweise den HASH()-Wert des "title"-Werts und prüft dann, ob der Modulo-Wert "2" Null ist. Dies sollte dazu führen, dass ca. 50 Prozent der Werte das Label "sampled" bekommen. Erhöhen Sie den Wert der Modulo-Operation von "2" auf einen höheren Wert, um weniger Werte zu samplen. Die Abfrage verwendet die ABS-Funktion zusammen mit HASH, da HASH negative Werte zurückgeben kann und die Anwendung des Modulo-Operators auf einen negativen Wert zu einem negativen Wert führt.

    Beispiel:

    #legacySQL
    SELECT
      title,
      HASH(title) AS hash_value,
      IF(ABS(HASH(title)) % 2 == 1, 'True', 'False')
        AS included_in_sample
    FROM
      [bigquery-public-data:samples.wikipedia]
    WHERE
      wp_namespace = 0
    LIMIT 5;
    
Hat Ihnen diese Seite weitergeholfen? Teilen Sie uns Ihr Feedback mit:

Feedback geben zu...