テーブル関数

テーブル関数（テーブル値関数、TVF とも呼ばれます）は、テーブルを返すユーザー定義関数です。テーブル関数は、テーブルを使用できる場所であればどこでも使用できます。テーブル関数はビューと似ていますが、テーブル関数ではパラメータを取得できます。

テーブル関数を作成する

テーブル関数を作成するには、CREATE TABLE FUNCTION ステートメントを使用します。テーブル関数には、テーブルを生成するクエリが含まれています。この関数は、クエリ結果を返します。次のテーブル関数は、INT64 パラメータを取得し、その値を bigquery-public-data.usa_names.usa_1910_current という一般公開データセットに対するクエリの WHERE 句で使用します。

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

他の方法でフィルタリングするには、テーブル関数に複数のパラメータを渡すことができます。次のテーブル関数は、年と名前の接頭辞でデータをフィルタリングします。

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

テーブル パラメータ

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

注: この機能に関するサポートのリクエストやフィードバックを行う場合は、[e-mail] 宛てにメールを送信してください。

TVF パラメータをテーブルに設定できます。テーブル パラメータ名の後に、必要なテーブル スキーマを明示的に指定する必要があります。これは、構造体のフィールドを指定する場合と同じ方法です。TVF に渡すテーブル引数には、パラメータ スキーマで指定された列に加えて、追加の列を含めることができます。また、列は任意の順序で表示できます。

次のテーブル関数は、orders テーブルの item_name の合計販売額を含むテーブルを返します。

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

パラメータ名

テーブル関数パラメータがテーブル列の名前と一致していると、曖昧な参照が作成される場合があります。この場合、BigQuery はこの名前をパラメータではなく、テーブル列の参照として解釈します。パラメータには、参照するテーブルの列とは異なる名前を使用することをおすすめします。

テーブル関数を使用する

テーブルが有効なコンテキストで関数を呼び出すことができます。次の例では、SELECT ステートメントの FROM 句で mydataset.names_by_year 関数を呼び出します。

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

結果は次のようになります。

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

テーブル関数の出力は別のテーブルと結合できます。

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

また、サブクエリでテーブル関数を使用することもできます。

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

テーブル パラメータを持つテーブル関数を呼び出す場合は、テーブル引数の名前の前に TABLE キーワードを使用する必要があります。テーブル引数には、テーブル パラメータ スキーマにリストされていない列を含めることができます。

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 |
 +-------------+-------*/

テーブル関数を一覧表示する

テーブル関数はルーティンの一種です。データセット内のすべてのルーティンを一覧表示するには、ルーティンを一覧表示をご覧ください。

テーブル関数を削除する

テーブル関数を削除するには、DROP TABLE FUNCTION ステートメントを使用します。

DROP TABLE FUNCTION mydataset.names_by_year

ルーティンを承認する

テーブル関数は、ルーティンとして承認できます。承認済みのルーティンを使用すると、結果を生成した基盤となるテーブルへのアクセス権を特定のユーザーまたはグループに付与せずに、そのユーザーまたはグループとクエリ結果を共有できます。たとえば、承認済みのルーティンを使用してデータ集計を実行し、またテーブル値を検索してその値を計算で使用できます。詳しくは、承認済みのルーティンをご覧ください。

制限事項

• クエリ本文は SELECT ステートメントにする必要があり、何も変更できません。たとえば、テーブル関数ではデータ定義言語（DDL）ステートメントとデータ操作言語（DML）のステートメントを使用できません。副作用が必要な場合は、代わりにプロシージャの作成を検討してください。

• テーブル関数は、参照するテーブルと同じ場所に保存する必要があります。

割り当て

テーブル関数の割り当てと上限の詳細については、割り当てと上限をご覧ください。