はじめに
BigQuery Scriptingの2回目です。
前回
Qiitaにせよ、海外サイトにせよあまり情報をみない気がします。（検索の仕方が悪い？）
あんまり人気ないのでしょうか。たしかに、もう少しなところはありますが、β版でなくなってしまうのも惜しい機能なので、色々検証して使い所を見つけていきたいと思います。
IF文
公式では、次のようなIF文の書き方が紹介されていました。
DECLARE target_product_id INT64 DEFAULT 103;
IF EXISTS (SELECT 1 FROM dataset.products
WHERE product_id = target_product_id) THEN
SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
ELSE
SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
END IF;
SQL外で宣言した変数を用いて、条件分岐させてSQLを実行することができるようです。
前回ループを回して、店舗別に売上結果を出力するということを行いましたが、店舗番号によって処理を変えてみましょう。（特に処理に意味はありませんが）
# 変数の宣言
DECLARE stores ARRAY<STRING>; # stores という配列を作ってループを回します
DECLARE x INT64 DEFAULT 1; # stores で使う引数（デフォルト値＝1）
# 変数への代入
# ARRAYの値（店舗一覧）を自動的に作りたかったので、ARRAY_AGGで作成
SET stores = (
SELECT ARRAY_AGG(store_cd) as list
FROM (SELECT store_cd FROM `myproject.mydataset.mytable` GROUP BY store_cd ORDER BY store_cd)
);
# ループ処理
# storesの長さまで繰り返し
WHILE x <= array_length(stores) DO
# store_cd が10以上だと合計金額をそのまま出力
IF (store_cd=stores [ORDINAL(x)] >= 10 ) THEN
SELECT store_cd, SUM(購入金額) AS 合計金額
FROM `myproject.mydataset.mytable`
WHERE store_cd=stores [ORDINAL(x)] # storesのX番目の値を取り出す
GROUP BY store_cd;
# それ以外は合計金額を2倍に
ELSE
SELECT store_cd, SUM(購入金額)*2 AS 合計金額
FROM `myproject.mydataset.mytable`
WHERE store_cd=stores [ORDINAL(x)]
GROUP BY store_cd;
END IF;
SET x = x + 1;
END WHILE;
当初、SET x = x + 1;をIF文の中に入れるという凡ミスをして、処理を回してしまい、いつまで経ってもSQLが止まらないという悲劇を招いてしまいました。
ループ処理をBigQuery Scriptingでやるときは、気をつけましょう。。。
おわりに
今回は、IF文で条件分岐を行いました。
まあ、今回の内容であれば、SQL内でCASE WHENでも書けますが、工夫によってはSQLの実行を分岐するというのは使いみちがありそうです。
と、書いてきましたが、出力した結果をBQのテーブルとして保存する方法が未だに見つけられていません。
DDLで出力すれば上手くいくのではと思ったのですが、DDL内では変数が上手く機能しないようです。
# 変数の宣言
DECLARE stores ARRAY<STRING>; # stores という配列を作ってループを回します
DECLARE x INT64 DEFAULT 1; # stores で使う引数（デフォルト値＝1）
# 変数への代入
# ARRAYの値（店舗一覧）を自動的に作りたかったので、ARRAY_AGGで作成
SET stores = (
SELECT ARRAY_AGG(store_cd) as list
FROM (SELECT store_cd FROM `myproject.mydataset.mytable` GROUP BY store_cd ORDER BY store_cd)
);
# ループ処理
# storesの長さまで繰り返し
WHILE x <= array_length(stores) DO
CREATE TABLE `D001_dataset.output_x` AS #ココが上手く「x」を変数として認識してくれない
CREATE TABLE
SELECT store_cd, SUM(購入金額) AS 合計金額
FROM `myproject.mydataset.mytable`
WHERE store_cd=stores [ORDINAL(x)] # storesのX番目の値を取り出す
GROUP BY store_cd;
)
SET x = x + 1;
END WHILE;
これができると、BigQuery Scriptingももっと使いどころが増えてくるのですが、それまではPythonで外部からループ・条件分岐をするしかないんですかね。。。
方法見つかったら、また更新したいと思います。