はじめに

BigQuery Scriptingの2回目です。

前回

BigQuery ScriptingでPythonっぽいループ処理をしてみた

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で外部からループ・条件分岐をするしかないんですかね。。。
方法見つかったら、また更新したいと思います。