BigQueryのテーブル関数の使い道を考えてみる

# 概要
Google BigQueryに、「テーブル関数（TVF）」という機能がプレビューで公開されてたので試しました。
https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions

パラメータを受け取って、関数内で実行したクエリの結果を返してくるといった機能みたいです。
パラメータを持ったViewみたいなものでしょうか。


# 今回使うデータセット、テーブル
以降の例にあるデータセット、テーブルは、私の個人プロジェクトにある以下のものを利用しています。
データセット：`truefly`
テーブル：`truefly.census`
※「令和２年国勢調査　人口速報集計」（[総務省統計局](https://www.e-stat.go.jp/stat-search/files?page=1&layout=datalist&toukei=00200521&tstat=000001136464&cycle=0&tclass1=000001136465&tclass2=000001154388&result_page=1&tclass3val=0)） を加工して作成しています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398404/a25bd13b-6f5e-f3cc-cb7b-c8073767cd28.png)



# テーブル関数の作り方
`CREATE OR REPLACE TABLE FUNCTION`ステートメントで、テーブル関数を作成できます。
試しに、都道府県名をパラメータとして受け取り、市区町村の人口を出力する関数を作ってみます。

```sql
CREATE OR REPLACE TABLE FUNCTION truefly.get_city_population(prefecture_name STRING) AS
SELECT
    city,
    population,
    male_population,
    female_population,
FROM
    truefly.census
WHERE
    prefecture = prefecture_name
```

作成されました。UDFと同じアイコンですね。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398404/51d05425-12fa-fada-d911-e6b4e15074e1.png)

執筆(2021/07/17)時点では、`CREATE TEMPORARY TABLE FUNCTION`ステートメントで、SQL実行時に定義することはできませんでした。
WITH句でやればOKということですね。

# テーブル関数の実行
FROM句に`truefly.get_city_population('都道府県名')`を書くことで呼び出しができます。
他のテーブルとJOINもできるようですが、今回は単体で埼玉県の市区町村の人口を取得してみます。

```sql
SELECT * FROM truefly.get_city_population('埼玉県') ORDER BY population DESC LIMIT 5
```

このように`truefly.census`テーブルから取得した結果が出力されます。

|city|population|male_population|female_population
|---|---|---|---|
|埼玉県|7346836|3651934|3694902
|さいたま市|1324591|652965|671626
|川口市|594461|299265|295196
|川越市|354680|177393|177287
|所沢市|342535|168168|174367

そして試して気が付きました。
パラメータを単にそのままWHERE句にいれるだけなら、わざわざテーブル関数など介さず、そのままテーブル参照でええやん、と。

# テーブル関数の有効的な活用方法を検討する
テーブル関数を活用方法を見出すために、もっと複雑なテーブル関数を実装してみます。

## テーブルの結合条件をパラメータで渡すテーブル関数
例えば、特定の都道府県の人口よりも多い人口の都道府県を取得したいとき、
`truefly.census`と、特定の都道府県の`truefly.census`を比較して値をとってくることになるので、SQLはやや複雑になります。
これをテーブル関数にするとこんな感じ。

```sql
CREATE OR REPLACE TABLE FUNCTION truefly.get_larger_population(prefecture_name STRING) AS
SELECT
    a.prefecture,
    a.population,
FROM
    truefly.census a
    INNER JOIN truefly.census b
        ON a.population > b.population
        AND b.city = prefecture_name
WHERE
    -- 都道府県単位の人口を取得
    a.city != '全国'
    AND a.prefecture = a.city
```

テーブル関数から、埼玉県よりも人口の多い都道府県を取得してみます。

```sql
SELECT * FROM truefly.get_larger_population('埼玉県') ORDER BY population
```

|prefecture|population|
|---|---
|東京都|14064696
|神奈川県|9240411
|大阪府|8842523
|愛知県|7546192

このようにJOINの結合条件となっている値をパラメータで外から渡すことで、シンプルな関数を呼び出すだけで実現できました。

## 取得する項目をパラメータで渡すテーブル関数
今回のサンプルテーブル、`truefly.census`には、
- 人口：`population`
- 男性人口：`male_population`
- 女性人口：`female_population`
といった項目があります。この項目をパラメータで指定して、都道府県毎のランキングを返すテーブル関数を作ってみます。

```sql
CREATE OR REPLACE TABLE FUNCTION truefly.get_ranking(class STRING) AS
SELECT
    RANK() OVER(ORDER BY value DESC) AS rank,
    prefecture,
    value,
FROM (
    SELECT
        prefecture,
        CASE
            WHEN class = '人口' THEN population
            WHEN class = '男性人口' THEN male_population
            WHEN class = '女性人口' THEN female_population
            ELSE population
        END AS value
    FROM
        truefly.census
    WHERE
        -- 都道府県単位の人口を取得
        city != '全国'
        AND prefecture = city
)       
ORDER BY
    rank
```

呼び出すときはどの項目のランキングがほしいかをパラメータで指定します。
今回は3項目だけなので、それぞれ上位3件を取得してみます。

```sql
SELECT * FROM (SELECT '1.人口' AS class, * FROM truefly.get_ranking('人口') LIMIT 3) UNION ALL
SELECT * FROM (SELECT '2.男性人口' AS class, * FROM truefly.get_ranking('男性人口')  LIMIT 3) UNION ALL
SELECT * FROM (SELECT '3.女性人口' AS class, * FROM truefly.get_ranking('女性人口')  LIMIT 3)
ORDER BY 1
```

class|rank|prefecture|value
---|---|---|---
1.人口|1|東京都|14064696
1.人口|2|神奈川県|9240411
1.人口|3|大阪府|8842523
2.男性人口|1|東京都|6902188
2.男性人口|2|神奈川県|4587059
2.男性人口|3|大阪府|4236023
3.女性人口|1|東京都|7162508
3.女性人口|2|神奈川県|4653352
3.女性人口|3|大阪府|4606500

当然全体の人口が多い都道府県が、男女別でも上位でした。
このようにパラメータでどの値をとってくるか指定することで、
内部でSQLを共通化して、実装の手間を省くことができるんじゃないかなと思いました。

# まとめ
以上、BigQueryのテーブル関数について、使い道を検討してみました。

[公式ドキュメント](https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions)にあるような、単にWHERE句で書ける条件をパラメータで渡すといった使い方よりは、
JOINの結合条件や、SELECT句のCASE文内の条件をパラメータで渡して、中身のSQLは共通処理としてテーブル関数化する、
といった使い方で有効活用ができそうです。

個人的には、テーブルをSELECTした結果を元に、スカラー値を返してくれるUDFが実現できると嬉しいんですが、いつかできるようになるのかなあ。