Syntax, Funktionen und Operatoren in Legacy-SQL

In diesem Dokument werden die Abfragesyntax, Funktionen und Operatoren von Legacy-SQL beschrieben. Die bevorzugte Abfragesyntax für BigQuery ist GoogleSQL. Weitere Informationen zu GoogleSQL finden Sie unter GoogleSQL-Abfragesyntax.

Abfragesyntax

Hinweis: Bei Keywords 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 Aufrufe von Aggregatfunktionen und Fensterfunktionen) sowie Kombinationen dieser drei Elemente enthalten. Die Liste der Ausdrücke ist kommagetrennt.

Jeder Ausdruck kann einen Alias erhalten. Dazu wird hinter dem Ausdruck ein Leerzeichen, gefolgt von einer Kennung, hinzugefügt. Für eine bessere Lesbarkeit kann zwischen dem Ausdruck und dem Alias optional das Keyword AS hinzugefügt werden. In einer SELECT-Klausel definierte Aliasse 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 einer 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 maskiert werden, um sie als Feldnamen und Aliasse 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 Aliasse in der SELECT-Klausel und referenziert dann einen dieser Aliasse in der ORDER BY-Klausel. Die Spalte word kann nicht über den Alias word_alias in der WHERE-Klausel referenziert werden. Dies muss über den Namen erfolgen. Auch der Alias len ist in der WHERE-Klausel nicht sichtbar. Er wäre aber für eine HAVING-Klausel sichtbar.

#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 Keyword WITHIN sorgt dafür, dass die Aggregatfunktion innerhalb jedes Datensatzes für wiederkehrende Werte Aggregationen ausfü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 erstellt, können nicht aggregierte Ausdrücke zusammen mit Ausdrücken von Bereichsaggregationen ausgewählt werden, ohne dass eine GROUP BY-Klausel verwendet werden muss.

Für die Bereichsaggregation wird in der Regel der Bereich RECORD verwendet. Bei einem sehr komplex verschachtelten wiederkehrenden Schema kann es erforderlich sein, Aggregationen innerhalb von Teildatensatzbereichen auszuführen. Dazu wird das Keyword RECORD in der oben aufgeführten Syntax durch den Namen des Knotens in dem Schema ersetzt, in dem die Aggregation ausgeführt werden soll. Weitere Informationen zu diesem erweiterten Verhalten finden Sie unter WITHIN-Klausel.

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 spezieller Operatoren (siehe weiter unten) geändert wurden, angewendet werden. Mit einem Komma, dem UNION ALL-Operator in BigQuery, lassen sich Kombinationen dieser Datenquellen abfragen.

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 ist, verwendet BigQuery standardmäßig 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. Dazu wird dem Tabellennamen am Ende ein Leerzeichen, gefolgt von einer Kennung, hinzugefügt. Zur besseren Lesbarkeit kann zwischen tableId und dem Alias optional das Keyword AS eingefügt werden.

Beim Referenzieren von Spalten einer Tabelle können Sie den einfachen Spaltennamen verwenden oder dem Spaltennamen den Alias (sofern vorhanden) oder datasetId und tableId als Präfix voranstellen. Dies gilt aber nur, wenn project_name nicht angegeben wurde. Der Parameter project_name darf nicht im Spaltenpräfix enthalten sein, da in Feldnamen kein Doppelpunkt zulässig 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 Parameter 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;

Nach Ganzzahlbereich partitionierte Tabellen

Legacy-SQL unterstützt jedoch die Verwendung von Tabellen-Decorators, um eine bestimmte Partition in einer nach Ganzzahlbereich partitionierten Tabelle anzusprechen. Der Schlüssel zum Ansprechen einer Bereichspartition ist der Anfang des Bereichs.

Im folgenden Beispiel wird die Bereichspartition abgefragt, die mit 30 beginnt.

#legacySQL
SELECT
  *
FROM
  dataset.table$30;

Sie können Legacy-SQL nicht zum Abfragen einer gesamten nach Ganzzahlbereich partitionierten Tabelle verwenden. Stattdessen gibt die Abfrage einen Fehler wie den folgenden zurück:

Querying tables partitioned on a field is not supported in Legacy SQL

Unterabfragen verwenden

Eine Unterabfrage ist eine verschachtelte SELECT-Anweisung, die in Klammern gesetzt ist. Die in der SELECT-Klausel der Unterabfrage berechneten Ausdrücke sind für die äußere Abfrage wie die Spalten einer Tabelle verfügbar.

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 z. B. selbst Unterabfragen enthalten, Joins ausführen und Aggregationen gruppieren können.

Komma als UNION ALL

Im Unterschied zu GoogleSQL wird in Legacy-SQL das Komma als UNION ALL-Operator und nicht als CROSS JOIN-Operator verwendet. Dies ist ein Legacy-Verhalten, das darauf zurückgeht, dass CROSS JOIN von BigQuery ursprünglich nicht unterstützt wurde und BigQuery-Nutzer häufig UNION ALL-Abfragen schreiben mussten. In GoogleSQL sind Abfragen, die Union-Operationen ausfü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 abzufragen. 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, wiederkehrende Daten zu verarbeiten. Deshalb müssen BigQuery-Nutzer manchmal Abfragen schreiben, die die Struktur wiederkehrender Datensätze ändern. Dazu steht u. a. der FLATTEN-Operator zur Verfügung.

FLATTEN wandelt einen Knoten im Schema von "wiederkehrend" in "optional" um. Bei einem Datensatz mit einem oder mehreren Werten für ein wiederkehrendes Feld generiert FLATTEN mehrere Datensätze und zwar jeweils einen für jeden Wert im wiederkehrenden Feld. Alle anderen im Datensatz ausgewählten Felder werden in jedem neuen Ausgabedatensatz dupliziert. FLATTEN kann mehrfach angewendet werden, um mehrere Ebenen wiederkehrender Felder zu entfernen.

Weitere Informationen und Beispiele finden Sie unter FLATTEN.

JOIN-Operator

BigQuery unterstützt in jeder FROM-Klausel mehrere JOIN-Operatoren. Die nachfolgenden JOIN-Operationen verwenden die Ergebnisse der vorherigen JOIN-Operation für 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 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. Mit CROSS JOIN werden eventuell große Datenmengen zurückgegeben. Dies kann zu einer langsamen und ineffizienten Abfrage führen oder zu einer Abfrage, die den maximal pro Abfrage erlaubten Umfang an Ressourcen überschreitet. Solche Abfragen schlagen fehl. Wenn möglich, sollten stattdessen Abfragen ohne CROSS JOIN verwendet werden. Beispielsweise wird CROSS JOIN oft in Fällen angewendet, in denen Fensterfunktionen effizienter wären.

EACH-Modifikator

Der EACH-Modifikator gibt für BigQuery an, dass die JOIN-Operation mithilfe mehrerer Partitionen ausgeführt werden soll. Dies ist insbesondere dann hilfreich, wenn bekannt ist, dass beide Seiten der JOIN-Operation sehr groß sind. Der EACH-Modifikator kann nicht in CROSS JOIN-Klauseln verwendet werden.

In der Vergangenheit wurde die Verwendung von EACH in vielen Fällen empfohlen. Dies ist aber nicht mehr der Fall. 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 wird, dass die Ressourcen überschritten wurden, sollten Sie 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 wird in einer Unterabfrage mithilfe des Keywords IN angegeben, ein Anti-Join mit dem Keyword 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

Zur Anzeige der Zahlen für die 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-Join oder Anti-Semi-Join 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 identisch sein. In BigQuery wird der Typ für Semi-Joins und Anti-Semi-Joins nicht erzwungen.

WHERE-Klausel

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

Hinweis: In der WHERE-Klausel können keine Aggregatfunktionen verwendet werden. Wenn Sie die Ausgabe einer Aggregatfunktion filtern möchten, verwenden Sie dazu eine HAVING-Klausel und eine äußere Abfrage.

Beispiel

Das folgende Beispiel verwendet in der WHERE-Klausel eine Disjunktion von booleschen Ausdrücken. Die beiden Ausdrücke werden durch einen OR-Operator verknüpft. Eingabedatensätze entsprechen dem 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 so nur in BigQuery vorhanden ist. Es ist besonders hilfreich für die Handhabung von verschachtelten wiederkehrenden Schemas. Sie ä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 beibehalten, 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 kompletten Datensätzen kann mit OMIT...IF auch ein eingeschränkter Bereich angegeben werden, um nur einen bestimmten Teil eines Datensatzes zu filtern. Dazu wird im Schema statt RECORD in der OMIT...IF-Klausel der Name eines Nicht-Blatt-Knotens verwendet. Diese Funktionalität wird von BigQuery-Nutzern nur selten verwendet. Im obigen Abschnitt zu WITHIN finden Sie unter den entsprechenden Links weitere Informationen zu diesem erweiterten Verhalten.

Wird mit OMIT...IF ein bestimmter Teil eines Datensatzes in einem wiederkehrenden Feld ausgeschlossen und wählt die Abfrage zusätzlich andere unabhängig wiederkehrende Felder aus, dann unterdrückt BigQuery einen Teil der anderen wiederkehrenden Datensätze in der Abfrage. Wenn die Fehlermeldung Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, ausgegeben wird, empfehlen wir, zu GoogleSQL zu wechseln. Informationen zum Migrieren von OMIT...IF-Anweisungen zu GoogleSQL finden Sie unter Zu GoogleSQL 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 erzielt 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 in einem Satz von Feldern die gleichen Werte enthalten sind, gruppiert werden, um Aggregationen verwandter Felder zu berechnen. Die Gruppierung wird nach der Filterung in der WHERE-Klausel ausgeführt, aber vor der Auswertung der Ausdrücke in der SELECT-Klausel. Die Ergebnisse des Ausdrucks können nicht als Gruppenschlüssel in der GROUP BY-Klausel verwendet werden.

Beispiel

Diese Abfrage ermittelt 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 Indexe 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;

Mit einer GROUP BY-Klausel ausgeführte Aggregationen werden als gruppierte Aggregation bezeichnet. Anders als die Bereichsaggregation wird die gruppierte Aggregation in den meisten SQL-Verarbeitungssystemen verwendet.

Der EACH-Modifikator

Der EACH-Modifikator gibt für BigQuery an, dass die GROUP BY-Operation mithilfe mehrerer Partitionen ausgeführt werden soll. Dies ist insbesondere dann hilfreich, wenn bereits bekannt ist, dass das Dataset viele verschiedene Werte für die Gruppenschlüssel enthält.

In der Vergangenheit wurde die Verwendung von EACH in vielen Fällen empfohlen. Dies ist aber nicht mehr der Fall. 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 GROUP EACH BY verwenden.

Die ROLLUP-Funktion

Wenn die ROLLUP-Funktion verwendet wird, fügt BigQuery dem Abfrageergebnis zusätzliche Zeilen hinzu, die zusammengefügte Aggregationen (Rollups) darstellen. Alle Felder, die nach ROLLUP aufgeführt sind, müssen in einem einzigen Paar von Klammern enthalten sein. 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 Geburten-Datasetder .

#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;

Im Folgenden sind die Ergebnisse der Abfrage aufgeführt. In einigen Zeilen sind einer oder beide Gruppenschlüssel NULL. Dies sind die Rollup-Zeilen der Zusammenfügung.

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

Im Folgenden sind die Ergebnisse dargestellt, 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 ausgewertet 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 Verwendung der TOP-Funktion anstelle von GROUP BY lassen sich bestimmte Skalierungsprobleme eventuell lösen.

HAVING-Klausel

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

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. Zum Sortieren nach mehreren Feldern oder Aliassen geben Sie diese als durch Kommas getrennte Liste ein. 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 ausgewerteten Ausdrucks referenzieren. Erhält ein Feld in der SELECT-Klausel einen Alias, dann muss dieser 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 für eine große Anzahl an Zeilen ausgeführt werden, können Sie mit LIMIT Abfragen mit langer Ausführungszeit vermeiden. Damit lässt sich festlegen, dass 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 ausgeführt wird. Die Ausgabe dieser parallelen Jobs erfolgt in zufälliger Reihenfolge.
• Die LIMIT-Klausel kann keine Funktionen enthalten; sie arbeitet nur mit einer numerischen Konstante.
• Wenn die Klausel LIMIT verwendet wird, können die Gesamtzahl der verarbeiteten und die in Rechnung gestellten Byte für dieselbe Abfrage variieren.

Abfragegrammatik

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

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.

Unterstützte Funktionen und Operatoren

Die meisten SELECT-Klauseln für Anweisungen unterstützen Funktionen. Felder, auf die in einer Funktion verwiesen wird, müssen nicht in einer SELECT-Klausel aufgelistet werden. Daher ist die folgende Abfrage gültig, obwohl das Feld clicks nicht direkt angegeben 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 eindeutiger Nicht-NULL-Werte zurück.
VARIANCE()	Gibt die Varianz der Werte zurück.
VAR_POP()	Berechnet die Populationsabweichung der Werte.
VAR_SAMP()	Berechnet die Stichprobenabweichung 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)	Konvertiert expr in eine Variable des Typs type.
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 zwischen expr2 und expr3 (einschließlich) liegt.
expr IS NULL	Gibt true zurück, wenn expr NULL ist.
expr IN()	Gibt true zurück, wenn expr mit expr1, expr2 oder mit einem beliebigen Wert in den Klammern übereinstimmt.
COALESCE()	Gibt das erste Argument zurück, das nicht NULL ist.
GREATEST()	Gibt den größten numeric_expr-Parameter 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 numeric_expr-Argumentparameter zurück.
NVL()	Wenn expr nicht null ist, wird expr zurückgegeben, andernfalls 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()	Konvertiert die 32 niedrigstwertigen Bits von integer_value in einen menschenlesbaren IPv4-Adressstring.
PARSE_IP()	Wandelt einen String, der für eine IPv4-Adresse steht, in einen vorzeichenlosen Ganzzahlwert um.
FORMAT_PACKED_IP()	Gibt eine für Menschen lesbare 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 e hoch das Argument 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 Vorkommen 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.

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

Aggregatfunktionen

Aggregatfunktionen geben Werte zurück, die Zusammenfassungen größerer Sätze von Daten 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 Keyword WITHIN, 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 (wiederkehrendes 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 eindeutiger Nicht-NULL-Werte zurück.
VARIANCE()	Gibt die Varianz der Werte zurück.
VAR_POP()	Berechnet die Populationsabweichung der Werte.
VAR_SAMP()	Berechnet die Stichprobenabweichung der Werte.

AVG(numeric_expr)

Gibt den Durchschnittswert für eine 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 einer bitweisen AND-Operation 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 einer bitweisen OR-Operation 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 einer bitweisen XOR-Operation 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 Gesamtzahl an 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 Gesamtzahl 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. Dabei ist zu beachten, dass der für DISTINCT zurückgegebene Wert eine statistische Näherung darstellt. Es ist nicht gewährleistet, dass der Wert genau ist.

Mit EXACT_COUNT_DISTINCT() erhalten Sie einen genauen Wert.

Muss das Ergebnis von COUNT(DISTINCT) genauer sein, kann ein zweiter Parameter, n, angegeben werden. Dieser definiert den Grenzwert, unterhalb dessen genaue 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 entsprechenden Felder und dann COUNT(*) anwenden. Dieser Ansatz ermöglicht eine bessere Skalierung. Der GROUP EACH BY-Ansatz ermöglicht eine bessere Skalierung, kann aber dazu führen, dass sich die Leistung zunächst etwas verschlechtert.

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 einzelnen String, bei dem die 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. So würde beispielsweise 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 einzelnen String, bei dem die 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 wiederkehrenden Feld zusammen. So gibt etwa 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 y-Werte, 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 wiederkehrenden 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 von buckets ist 100. (Hinweis: Beim Standardwert 100 werden keine Perzentile geschätzt. Für eine Schätzung der Perzentile müssen als Mindestwert 101 ­­buckets verwendet werden.) Wenn explizit 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)) das 19. Vigintil (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 mit der NTH-Funktion entsprechend anpassen. Zum Berechnen des Medianwerts mit einem 0,1-prozentigen Fehlerspielraum verwenden Sie NTH(501, QUANTILES(expr, 1001)), für das 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 zum Unterschied zwischen Populations- und Stichprobenstandardabweichung finden Sie unter Standardabweichung auf Wikipedia.

STDDEV_SAMP(numeric_expr)

Berechnet die Stichprobenstandardabweichung des von numeric_expr berechneten Werts. Verwenden Sie STDDEV_SAMP(), um die Standardabweichung einer gesamten Population anhand einer repräsentativen Stichprobe der Population zu berechnen. Wenn Ihr Dataset die gesamte Population umfasst, verwenden Sie stattdessen STDDEV_POP(). Weitere Informationen zum Unterschied zwischen Populations- und Stichprobenstandardabweichung finden Sie unter Standardabweichung auf 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 in der TOP-Beschreibung unten.

UNIQUE(expr)

Gibt das Set eindeutiger 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 bezüglich überschrittener Ressourcen, wenn zu viele unterschiedliche Werte vorhanden sind. Im Unterschied zu 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 von 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 zum Unterschied zwischen Populations- und Stichprobenstandardabweichung finden Sie unter Standardabweichung auf Wikipedia.

VAR_SAMP(numeric_expr)

Berechnet die Stichprobenabweichung der von numeric_expr berechneten Werte. Weitere Informationen zum Unterschied zwischen Populations- und Stichprobenstandardabweichung 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 genäherte Ergebnisse zurück. Die Syntax der TOP-Funktion sieht so 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 zurückzugebender Ergebnisse. 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, sowie die Anzahl der Dokumente, in denen die Wörter verwendet wurden. Die TOP-Abfrage wird sehr 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";

  Gibt Folgendes zurück:

  +-------+-------+
  | 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";

  Gibt Folgendes zurück:

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

Hinweis: Damit TOP verwendet werden kann, muss COUNT(*) in die SELECT-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 rauchenden bzw. nichtrauchenden Müttern zurück.

  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 mit einem aggregierten Wert filtern

  Zum Filtern von Abfrageergebnissen mithilfe eines aggregierten Werts (zum Beispiel Filterung anhand eines SUM-Werts) verwenden Sie die Funktion HAVING. HAVING vergleicht einen Wert mit einem Ergebnis, das mithilfe einer Aggregationsfunktion ermittelt wurde. Dies unterscheidet sich von der 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

  Gibt Folgendes zurück:

  +-------+--------+---------+
  | 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); Gibt Folgendes zurück: 10
–	Subtraktion	SELECT 6 - (4 + 1); Gibt Folgendes zurück: 1
*	Multiplikation	SELECT 6 * (5 - 1); Gibt Folgendes zurück: 24
/	Division	SELECT 6 / (2 + 2); Gibt Folgendes zurück: 1.5
%	Modulo	SELECT 6 % (2 + 2); Gibt Folgendes zurück: 2

Bitweise Funktionen

Bitweise Funktionen arbeiten auf der Ebene einzelner Bits 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 Gibt Folgendes zurück: 0
|	Bitweises OR	SELECT 24 | 12 Gibt Folgendes zurück: 28
^	Bitweises XOR	SELECT 1 ^ 0 Gibt Folgendes zurück: 1
<<	Bitweise Linksverschiebung	SELECT 1 << (2 + 2) Gibt Folgendes zurück: 16
>>	Bitweise Rechtsverschiebung	SELECT (6 + 2) >> 2 Gibt Folgendes zurück: 2
~	Bitweises NOT	SELECT ~2 Gibt Folgendes zurück: -3
BIT_COUNT(<numeric_expr>)	Gibt die Anzahl der Bits zurück, die in <numeric_expr> festgelegt ist.	SELECT BIT_COUNT(29); Gibt Folgendes zurück: 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)	Konvertiert expr in eine Variable des Typs type.
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)

Konvertiert expr in eine Variable des Typs type.

FLOAT(expr)

Gibt expr als Double zurück. expr kann ein String wie '45.78' sein. Für nicht numerische Werte gibt die Funktion jedoch 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 Satzes von Ausdrücken mit einem spezifischen Kriterium, zum Beispiel, ob er in einer angegebenen Liste vorhanden ist, als Wert NULL ist 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 zwischen expr2 und expr3 (einschließlich) liegt.
expr IS NULL	Gibt true zurück, wenn expr NULL ist.
expr IN()	Gibt true zurück, wenn expr mit expr1, expr2 oder mit einem beliebigen Wert in den Klammern übereinstimmt.
COALESCE()	Gibt das erste Argument zurück, das nicht NULL ist.
GREATEST()	Gibt den größten numeric_expr-Parameter 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 numeric_expr-Argumentparameter zurück.
NVL()	Wenn expr nicht null ist, wird expr zurückgegeben, andernfalls 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 oder gleich expr2 und kleiner 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 mit expr1, expr2 oder einem beliebigen Wert in den Klammern übereinstimmt. Das IN-Keyword ist eine effiziente Abkürzung für (expr = expr1 || expr = expr2 || ...). Die im Keyword IN 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 numeric_expr-Parameter 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, andernfalls 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 numeric_expr-Parameter 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, andernfalls 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();

Gibt Folgendes zurück: 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();

Gibt Folgendes zurück: 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();

Gibt Folgendes zurück: 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'));

Gibt Folgendes zurück: 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, wird das Intervall vom TIMESTAMP-Datentyp subtrahiert.

Beispiel:

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

Gibt Folgendes zurück: 2017-10-01 02:03:04 UTC

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

Gibt Folgendes zurück: 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'));

Gibt Folgendes zurück: 466

Beispiel:

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

Gibt Folgendes zurück: -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'));

Gibt Folgendes zurück: 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"));

Gibt Folgendes zurück: 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"));

Gibt Folgendes zurück: 275

FORMAT_UTC_USEC(<unix_timestamp>)

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

Beispiel:

SELECT FORMAT_UTC_USEC(1274259481071200);

Gibt Folgendes zurück: 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'));

Gibt Folgendes zurück: 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'));

Gibt Folgendes zurück: 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'));

Gibt Folgendes zurück: 10

MSEC_TO_TIMESTAMP(<expr>)

Konvertiert einen UNIX-Zeitstempel in Millisekunden in einen TIMESTAMP-Datentyp.

Beispiel:

SELECT MSEC_TO_TIMESTAMP(1349053323000);

Gibt Folgendes zurück: 2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

Gibt Folgendes zurück: 2012-10-01 01:02:04 UTC

NOW()

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

Beispiel:

SELECT NOW();

Gibt Folgendes zurück: 1359685811687920

PARSE_UTC_USEC(<date_string>)

Konvertiert einen Datumsstring in einen UNIX-Zeitstempel in Mikrosekunden. 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 konvertiert.

Beispiel:

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

Gibt Folgendes zurück: 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"));

Gibt Folgendes zurück: 4

SEC_TO_TIMESTAMP(<expr>)

Konvertiert einen UNIX-Zeitstempel in Sekunden in einen TIMESTAMP-Datentyp.

Beispiel:

SELECT SEC_TO_TIMESTAMP(1355968987);

Gibt Folgendes zurück: 2012-12-20 02:03:07 UTC

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

Gibt Folgendes zurück: 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'));

Gibt Folgendes zurück: 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. Diese sind effizienter.

Beispiel:

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

Gibt Folgendes zurück: 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'));

Gibt Folgendes zurück: 02:03:04

TIMESTAMP(<date_string>)

Konvertiert einen Datumsstring in einen TIMESTAMP-Datentyp.

Beispiel:

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

Gibt Folgendes zurück: 2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC(<timestamp>)

Konvertiert einen TIMESTAMP-Datentyp in einen UNIX-Zeitstempel in Millisekunden.

Beispiel:

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

Gibt Folgendes zurück: 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)

Konvertiert einen TIMESTAMP-Datentyp in einen UNIX-Zeitstempel in Sekunden.

Beispiel:

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

Gibt Folgendes zurück: 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 Datenstring-Argument konvertiert.

Beispiel:

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

Gibt Folgendes zurück: 1349053323000000

USEC_TO_TIMESTAMP(<expr>)

Konvertiert einen UNIX-Zeitstempel in Mikrosekunden in einen TIMESTAMP-Datentyp.

Beispiel:

SELECT USEC_TO_TIMESTAMP(1349053323000000);

Gibt Folgendes zurück: 2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

Gibt Folgendes zurück: 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);

Gibt Folgendes zurück: 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);

Gibt Folgendes zurück: 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);

Gibt Folgendes zurück: 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;

Gibt Folgendes zurück: 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);

Gibt Folgendes zurück: 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'));

Gibt Folgendes zurück: 53

YEAR(<timestamp>)

Gibt das Jahr eines TIMESTAMP-Datentyps zurück.

Beispiel:

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

Gibt Folgendes zurück: 2012

Erweiterte Beispiele

• Ganzzahlen-Zeitstempelergebnisse in menschenlesbares Format konvertieren

  Die folgende Abfrage ermittelt die 5 Zeitpunkte, an denen die meisten Überarbeitungen in Wikipedia stattgefunden haben. Mit der Funktion FORMAT_UTC_USEC() von BigQuery, die einen Zeitstempel (in Mikrosekunden) als Eingabe verwendet, stellen Sie die Ergebnisse in einem von Menschen lesbaren Format dar. Die Abfrage multipliziert die Zeitstempel im Wikipedia-POSIX-Format (in Sekunden) mit 1.000.000, um den Wert in Mikrosekunden zu konvertieren.

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

  Gibt Folgendes zurück:

  +----------------------------+----------------+
  |     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 Funktion UTC_USEC_TO_MONTH(), um anzuzeigen, wie viele Zeichen jeder einzelne Wikipedia-Autor in seinen Ä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;

  Gibt Folgendes zurück (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 konvertieren IP-Adressen in ein von Menschen lesbares Format und umgekehrt.

Syntax

IP-Funktionen
FORMAT_IP()	Konvertiert die 32 niedrigstwertigen Bits von integer_value in einen menschenlesbaren IPv4-Adressstring.
PARSE_IP()	Wandelt einen String, der für eine IPv4-Adresse steht, in einen vorzeichenlosen Ganzzahlwert um.
FORMAT_PACKED_IP()	Gibt eine für Menschen lesbare 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)

Konvertiert die 32 niedrigstwertigen Bits von integer_value in einen menschenlesbaren IPv4-Adressstring. Beispielsweise gibt FORMAT_IP(1) den String '0.0.0.1' zurück.

PARSE_IP(readable_ip)

Konvertiert einen String, der für eine IPv4-Adresse steht, in einen vorzeichenlosen Ganzzahlwert. 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 von Menschen lesbare 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') gibt '3031:3233:3435:3637:3839:4041:4243:4445' zurück
• FORMAT_PACKED_IP('0123') gibt '48.49.50.51' zurück

PARSE_PACKED_IP(readable_ip)

Gibt eine IP-Adresse im BYTES-Format zurück. 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') gibt 'MDEyMw==' zurück
• PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445') gibt 'MDEyMzQ1Njc4OUBBQkNERQ==' zurück

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 aus. Dabei muss json_path eine Stringkonstante sein. Gibt den Wert im JSON-Stringformat zurück.

JSON_EXTRACT_SCALAR(json, json_path)

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

Logische Operatoren

Logische Operatoren wenden binäre oder ternäre Logik auf Ausdrücke an. 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. 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 e hoch das Argument 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 konvertiert zurück.

EXP(numeric_expr)

Gibt das Ergebnis für die Konstante "e" (die 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 konvertiert 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 LIMIT-Klausel verwendet wird, generiert jeder int32_seed-Wert innerhalb einer definierten Abfrage immer die gleiche Sequenz an Zufallszahlen. 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 auf oder 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 wurde. Die Abfrage verwendet mathematische und trigonometrische Funktionen von BigQuery, darunter PI(), SIN() und COS().

  Da die Erde keine absolute Kugel ist und die Längen- und Breitengrade an den Polen zusammenlaufen, gibt diese Abfrage nur eine Annäherung zurück, die aber 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 CONTAINS anstelle von REGEXP_MATCH.

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;

Gibt Folgendes zurück:

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

Gibt Folgendes zurück:

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

Gibt Folgendes zurück:

+---------------+
| 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 Anwendungsfälle von regulären Ausdrücken 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. Um ohne Beachtung der Groß-/Kleinschreibung nach Übereinstimmung zu suchen, hängen Sie IGNORE CASE an das Ende einer Abfrage an. IGNORE CASE kann nur auf ASCII-Zeichen und nur auf der obersten 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 Vorkommen 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 ??????1 zurück.

LTRIM('str1' [, str2])

Entfernt Zeichen auf der linken Seite von str1. Wenn str2 weggelassen wird, löscht LTRIM Leerzeichen auf der linken Seite von str1. Ansonsten entfernt LTRIM alle Zeichen in str2 auf der linken Seite 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.

RIGHT('str', numeric_expr)

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 am Ende von str1. Ansonsten entfernt RTRIM alle Zeichen in str2 auf der rechten Seite 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 wiederkehrende Teilstrings auf. Wenn delimiter angegeben ist, wird str durch die SPLIT-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

Zum Escaping von Sonderzeichen in Strings verwenden Sie 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 von Daten aus einem bestimmten Satz von 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.

Tabellennamen müssen das folgende Format haben: <prefix><day>, wobei <day> das Format YYYYMMDD 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 für die angegebene 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 für die angegebene 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 für die angegebene URL die Top-Level-Domain und die 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 Funktion DOMAIN(), um die beliebtesten Domains zurückzugeben, die auf GitHub als Repository-Startseiten aufgeführt sind. Sie können auch mithilfe von HAVING Datensätze mit dem Ergebnis der Funktion DOMAIN() filtern. Dies ist eine hilfreiche Funktion zum Ermitteln der Verweisinformationen aus 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;

Gibt Folgendes zurück:

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

Zur Anzeige speziell von TLD-Daten verwenden Sie die Funktion TLD(). Mit diesem Beispiel werden die obersten TLDs angezeigt, die nicht in der Liste der gängigen Beispiele enthalten sind.

#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;

Gibt Folgendes zurück:

+----------+----------------+
| 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. Mit Fensterfunktionen lässt sich das Erstellen von Berichten mit komplexen Kennzahlen wie der Durchschnitt der vergangenen 12 Monate oder wie fortlaufende Gesamtwerte vereinfachen.

Jede Fensterfunktion erfordert eine OVER-Klausel, die die obere und die untere Grenze 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 ein gemeinsames Merkmal haben. 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, die diese Funktion verwendet. Geben Sie einen oder mehrere kommagetrennte Spaltennamen an. Für jeden 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 alle Zeilen als Eingabe für die Basispartition.

Mithilfe der Klausel PARTITION BY können mit Fensterfunktionen auch Daten partitioniert und die Ausführung parallelisiert werden. Wenn eine Fensterfunktion mit allowLargeResults verwendet werden soll oder wenn weitere Joins bzw. 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. Zum Generieren umfangreicher Abfrageergebnisse mit Fensterfunktionen müssen Sie PARTITION BY verwenden.

ORDER BY

Sortiert die Partition. Wenn ORDER BY nicht angegeben ist, kann keine Standardsortierreihenfolge garantiert werden. Die Sortierung erfolgt auf Partitionsebene, bevor eine Fensterrahmen-Klausel angewendet wird. Wenn ein RANGE-Fenster angegeben wird, muss eine ORDER BY-Klausel hinzugefügt werden. Die Standardreihenfolge ist ASC.

In einigen Fällen ist ORDER BY optional. Für bestimmte 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 Beginn 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 window-frame-clause verwendet, entspricht der Standardfensterrahmen dem Bereich RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Werden sowohl ORDER BY als auch window-frame-clause weggelassen, entspricht der Standardfensterrahmen der gesamten Partition.

• ROWS: Definiert ein Fenster über die Zeilenposition relativ zur aktuellen Zeile. Um beispielsweise eine Spalte hinzuzufügen, die die Summe der vorherigen 5 Zeilen mit Gehaltswerten enthält, muss mit SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) abgefragt werden. Der Satz an Zeilen umfasst in der Regel auch die aktuelle Zeile. Dies ist aber nicht erforderlich.
• RANGE: Definiert ein Fenster über einen Wertebereich in einer definierten Spalte relativ zum Wert dieser Spalte in der aktuellen Zeile. Wird nur auf Zahlen und Datumsangaben angewendet, wobei es sich bei Datumswerten um einfache Ganzzahlen handelt (Mikrosekunden seit der Epoche). Benachbarte Zeilen mit dem gleichen Wert werden als Peer-Zeilen bezeichnet. Peer-Zeilen von CURRENT ROW sind in einem Fensterrahmen enthalten, der CURRENT ROW angibt. Wird als Fensterende beispielsweise CURRENT ROW angegeben und hat die folgende Zeile im Fenster den gleichen Wert, wird dieser in der Funktionsberechnung berücksichtigt.
• BETWEEN <start> AND <end>: Ein Bereich, inklusive Anfangs- und Endzeile. Der Bereich muss die aktuelle Zeile nicht enthalten. <start> muss aber vor <end> liegen oder damit identisch sein.
• <start>: Gibt den Start-Offset dieses Fensters relativ zur aktuellen Zeile an. Es werden die folgenden Optionen unterstützt:

  {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  Dabei gilt: <expr> ist eine positive Ganzzahl, PRECEDING gibt eine vorangehende Zeilennummer oder einen Bereichswert an und FOLLOWING gibt eine nachfolgende Zeilennummer oder einen Bereichswert an. 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 relativ zur aktuellen Zeile an. Die folgenden Optionen werden unterstützt:

  {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  Dabei gilt: <expr> ist eine positive Ganzzahl, PRECEDING gibt eine vorangehende Zeilennummer oder einen Bereichswert an und FOLLOWING gibt eine nachfolgende Zeilennummer oder einen Bereichswert an. 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 diesem Feature lassen sich Abfragen einfacher 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 Funktionen führen die gleichen Operationen wie die entsprechenden Aggregatfunktionen aus. Sie werden aber für ein von der OVER-Klausel definiertes Fenster berechnet.

Ein anderer wichtiger Unterschied besteht darin, dass die Funktion COUNT([DISTINCT] field) 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. Damit wird für dieses 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
        

Gibt Folgendes zurück:

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.

Bei dieser Fensterfunktion muss ORDER BY in der OVER-Klausel angegeben werden.

#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

Gibt Folgendes zurück:

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

Bei dieser Fensterfunktion muss ORDER BY in der OVER-Klausel angegeben werden.

#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

Rückgabe:

word	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

Rückgabe:

word	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. Ohne diese Zeile 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

Gibt Folgendes zurück:

word	word_count	lag
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

Gibt Folgendes zurück:

word	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. Ohne diese Zeile 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

Rückgabe:

word	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 entsprechende Ganzzahl als Bucket-Nummer 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

Rückgabe:

word	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 im Verhältnis zu den anderen Zeilen in der Partition zurück. Der Bereich der zurückgegebenen Werte reicht von 0 bis 1 (einschließlich). Der erste zurückgegebene Wert ist 0,0.

Bei dieser Fensterfunktion muss ORDER BY in der OVER-Klausel angegeben werden.

#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

Rückgabe:

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

Bei dieser Fensterfunktion muss ORDER BY in der OVER-Klausel angegeben werden.

#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

Rückgabe:

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

Bei dieser Fensterfunktion muss ORDER BY in der OVER-Klausel angegeben werden.

#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

Rückgabe:

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

Bei dieser Fensterfunktion muss ORDER BY in der OVER-Klausel angegeben werden.

#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

Rückgabe:

word	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

Rückgabe:

word	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

Rückgabe:

word	word_count	row_num
handkerchief	29	1
satisfaction	5	2
displeasure	4	3
instruments	4	4
circumstance	3	5

Weitere Funktionen

Syntax

Weitere 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 die condition für alle Eingaben wahr 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>)

Konvertiert den base64-codierten Eingabestring str in das BYTES-Format. BYTES kann mithilfe von TO_BASE64() in einen base64-codierten String konvertiert werden.

HASH(expr)

Gibt einen signierten 64-Bit-Hash-Wert der Byte von expr zurück. Dies erfolgt 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 von der Groß-/Kleinschreibung unabhängige 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 entspricht der Ausgabe der FARM_FINGERPRINT-Funktion, wenn GoogleSQL verwendet wird. Die Funktion berücksichtigt IGNORE CASE für Strings, sodass von der Groß-/Kleinschreibung unabhängige Werte zurückgegeben werden.

IF(condition, true_return, false_return)

Gibt abhängig davon, ob die condition wahr oder falsch ist, entweder true_return oder false_return 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 wiederkehrender 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 base64 konvertiert werden. Beispiel:

#legacySQL
SELECT
  TO_BASE64(SHA1(corpus))
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT
  100;

SOME(<condition>)

Gibt true zurück, wenn die condition für mindestens eine der Eingaben wahr 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>)

Konvertiert die BYTES-Eingabe bin_data in einen base64-codierten String. Beispiel:

#legacySQL
SELECT
  TO_BASE64(SHA1(title))
FROM
  [bigquery-public-data:samples.wikipedia]
LIMIT
  100;

Ein base64-codierter String kann mithilfe von FROM_BASE64() in das BYTES-Format konvertiert werden.

Erweiterte Beispiele

• Ergebnisse mithilfe von Bedingungen in Kategorien zusammenführen

  Die folgende Abfrage verwendet einen CASE/WHEN-Block, um Ergebnisse anhand einer Liste von Zuständen in "Regionen"-Kategorien zusammenzuführen. Wenn der Zustand nicht als Option in einem der WHEN-Ausdrücke angezeigt wird, ist 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;

  Gibt Folgendes zurück:

  +--------+--------------------+--------------------+-------+------+
  | 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, in Spalten organisiert. Dabei wird die Anzahl an Überarbeitungen angezeigt, 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
    );

  Gibt Folgendes zurück:

  +---------------+--------+------+
  |  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 liefern ein brauchbares Ergebnis, wenn 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 ermittelt beispielsweise den HASH()-Wert des Werts "title" und prüft dann, ob der Wert von Modulo "2" null ist. Dies sollte dazu führen, dass ca. 50 % der Werte das Label "sampled" bekommen. Für weniger Stichprobenwerte ändern Sie den Wert der Modulo-Operation von "2" auf einen höheren Wert. Die Abfrage verwendet die Funktion ABS 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;