Bigqueryでループ処理して取得したレコードを利用して次のデータを取得する #SQL

この記事はPTAで開催しているCyberAgent PTA Advent Calendar 2021、10日目の記事です。

去年のアドベントカレンダーはこちらです、
CyberAgent PTA Advent Calendar 2020

みなさんは、あるレコードから取得した条件を元に別のテーブルのレコードを取得したいことはありますか？
例えば広告の場合、ある申込からその期間のログを取得したいというようなケースだ。
このような場合、すべてのログをJOINしてから集計すれば一見良さそうなのですが、日付パーティション等が使われていると、先にパーティションを確定する事ができず、最悪フルスキャンする事になります。

そこで今回はBigQuery ScriptingのWileを使って実現してみました。

# 変数の宣言
DECLARE x INT64 DEFAULT 1;
DECLARE f,t DATE; 

# 申込ID
create temporary function ids() as (
    ['AD001','AD003']
);

# 結果格納用の一時テーブル
CREATE OR REPLACE TEMP TABLE _SESSION.ad_summery(
    dt DATE,
    order_id STRING,
    ad STRING,
    user_id STRING,
);

# ループ処理
# 申込IDの長さまで繰り返し広告ログを取得
WHILE x <= array_length(ids()) DO
SET f = (
SELECT from_dt FROM `order`
WHERE
    _PARTITIONDATE = CURRENT_DATE()-1
    AND order_id = ids()[ORDINAL(x)]
);
SET t = (
SELECT to_date FROM `order`
WHERE
    _PARTITIONDATE = CURRENT_DATE()-1
    AND order_id = ids()[ORDINAL(x)]
);

INSERT _SESSION.ad_summery (
  SELECT
    time,
    order_id STRING,
    ad STRING,
    user_id STRING,
  WHERE
    _PARTITIONDATE BETWEEN DATE_ADD(f, INTERVAL -1 DAY) AND t --パーティションは広めに取りたい
    AND time BETWEEN f AND t --期間内のログだけ取得したい
    AND order_id = ids()[ORDINAL(x)]
);
SET x = x + 1;
END WHILE;
# ループ処理終了

SELECT
*
FROM _SESSION.ad_summery

変数の宣言

はじめにループ処理で利用する変数を用意します。
ループのカウンターXと一時的に日付を格納するfとtを用意しました。

申込ID

次に実際に手動で指定する部分です。
クエリを発行する人がここをいじるだけで結果を取得できる状態を目指します。

結果格納用の一時テーブル

ループ内のデータをループ外の領域に保存する必要があります。
一時テーブルを利用してセッション期間中だけ存在する領域を生成します。

ループ処理

ループ処理では上部で指定したIDの数だけループを行います。
必要な変数に値を格納していきます。（ここの処理が冗長なので、もっといい方法があったら教えてほしいです。）
そしてその変数を利用してInsert処理を行います。

ループ処理終了

あとは生成した一時テーブルから必要な情報を取り出すだけです。

BigQuery Scripting、なかなか便利ですね！