Tabellenfunktionen

Eine Tabellenfunktion, auch als Tabellenwertfunktion (Table-valued Function, TVF) bezeichnet, ist eine benutzerdefinierte Funktion, die eine Tabelle zurückgibt. Eine Tabellenfunktion kann überall dort verwendet werden, wo Sie eine Tabelle nutzen. Tabellenfunktionen verhalten sich ähnlich wie Ansichten, können aber auch Parameter verwenden.

Tabellenfunktionen erstellen

Zum Erstellen einer Tabellenfunktion verwenden Sie die Anweisung CREATE TABLE FUNCTION. Eine Tabellenfunktion enthält eine Abfrage, die eine Tabelle generiert. Die Funktion gibt das Abfrageergebnis zurück. Die folgende Tabellenfunktion verwendet einen INT64-Parameter in der WHERE-Klausel einer Abfrage für ein öffentliches Dataset mit dem Namen bigquery-public-data.usa_names.usa_1910_current:

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year(y INT64)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE year = y
  GROUP BY year, name
);

Wenn Sie auf andere Weise filtern möchten, können Sie einer Tabellenfunktion mehrere Parameter übergeben. Mit der folgenden Tabellenfunktion werden die Daten nach Jahr und Namenspräfix gefiltert:

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year_and_prefix(
  y INT64, z STRING)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    year = y
    AND STARTS_WITH(name, z)
  GROUP BY year, name
);

Tabellenparameter

Sie können TVF-Parameter als Tabellen festlegen. Nach dem Namen des Tabellenparameters müssen Sie das erforderliche Tabellenschema explizit angeben, so wie Sie die Felder einer Struktur angeben. Das Tabellenargument, das Sie an die TVF übergeben, kann neben den im Parameterschema angegebenen Spalten zusätzliche Spalten enthalten. Die Spalten können in beliebiger Reihenfolge angezeigt werden.

Die folgende Tabellenfunktion gibt eine Tabelle mit dem Gesamtumsatz für item_name aus der Tabelle orders zurück:

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

Parameternamen

Wenn ein Tabellenfunktionsparameter mit dem Namen einer Tabellenspalte übereinstimmt, kann dies zu einer mehrdeutigen Referenz führen. In diesem Fall interpretiert BigQuery den Namen als Referenz auf die Tabellenspalte und nicht als Parameter. Es wird empfohlen, Parameternamen zu verwenden, die sich von den Namen referenzierter Tabellenspalten unterscheiden.

Tabellenfunktionen verwenden

Sie können eine Tabellenfunktion in jedem Kontext aufrufen, in dem eine Tabelle gültig ist. Im folgenden Beispiel wird die Funktion mydataset.names_by_year in der FROM-Klausel einer SELECT-Anweisung aufgerufen:

SELECT * FROM mydataset.names_by_year(1950)
  ORDER BY total DESC
  LIMIT 5

Das Ergebnis sieht in etwa so aus:

+------+--------+-------+
| year |  name  | total |
+------+--------+-------+
| 1950 | James  | 86447 |
| 1950 | Robert | 83717 |
| 1950 | Linda  | 80498 |
| 1950 | John   | 79561 |
| 1950 | Mary   | 65546 |
+------+--------+-------+

Sie können die Ausgabe einer Tabellenfunktion mit einer anderen Tabelle verbinden:

SELECT *
  FROM `bigquery-public-data.samples.shakespeare` AS s
  JOIN mydataset.names_by_year(1950) AS n
  ON n.name = s.word

Außerdem haben Sie die Möglichkeit, eine Tabellenfunktion in einer Unterabfrage zu verwenden:

SELECT ARRAY(
  SELECT name FROM mydataset.names_by_year(1950)
  ORDER BY total DESC
  LIMIT 5)

Wenn Sie eine Tabellenfunktion mit einem Tabellenparameter aufrufen, müssen Sie das Schlüsselwort TABLE vor dem Namen des Tabellenarguments verwenden. Das Tabellenargument kann Spalten enthalten, die nicht im Tabellenparameterschema aufgeführt sind:

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

WITH my_orders AS (
    SELECT 1 AS sales, "apple" AS item, 0.99 AS price
    UNION ALL
    SELECT 2, "banana", 0.49
    UNION ALL
    SELECT 5, "apple", 0.99)
SELECT *
FROM mydataset.compute_sales(TABLE my_orders, "apple");

/*-------------+-------+
 | total_sales | item  |
 +-------------+-------+
 | 6           | apple |
 +-------------+-------*/

Tabellenfunktionen auflisten

Tabellenfunktionen sind ein routinemäßiger Ablauf. Informationen zum Auflisten aller Abläufe in einem Dataset finden Sie unter Routinen auflisten.

Tabellenfunktionen löschen

Zum Löschen einer Tabellenfunktion verwenden Sie die Anweisung DROP TABLE FUNCTION:

DROP TABLE FUNCTION mydataset.names_by_year

Routinen autorisieren

Sie können Tabellenfunktionen als Routinen autorisieren. Mit autorisierten Routinen können Sie Abfrageergebnisse für bestimmte Nutzer oder Gruppen freigeben, ohne ihnen Zugriff auf die zugrunde liegenden Tabellen zu gewähren, mit denen die Ergebnisse generiert wurden. Beispielsweise kann eine autorisierte Routine eine Aggregation von Daten berechnen oder einen Tabellenwert abrufen und diesen Wert in einer Berechnung verwenden. Weitere Informationen zu Autorisierte Routinen.

Beschränkungen

  • Der Abfragetext muss eine SELECT-Anweisung sein und darf keine Änderung ausführen. Beispielsweise sind Anweisungen in der Datendefinitionssprache (DDL) und in der Datenbearbeitungssprache (DML) in Tabellenfunktionen nicht zulässig. Für Nebeneffekte sollte stattdessen eine Prozedur geschrieben werden.

  • Tabellenfunktionen müssen am selben Ort gespeichert werden wie die Tabellen, auf die sie verweisen.

Kontingente

Weitere Informationen zu Kontingenten und Beschränkungen für Tabellenfunktionen finden Sie unter Kontingente und Limits.