テーブル関数

テーブル関数（テーブル値関数、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
);

パラメータ名

テーブル関数パラメータがテーブル列の名前と一致していると、曖昧な参照が作成される場合があります。この場合、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)

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

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

テーブル関数を削除する

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

DROP TABLE FUNCTION mydataset.names_by_year

ルーティンを承認する

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

制限事項

  • パラメータはスカラー値でなければなりません。BigQuery のテーブル関数は、入力パラメータとしてテーブルを使用できません。

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

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

割り当て

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