備忘録：Big Query Scripting②

変数を代入するSET句

DECLAREで宣言した変数に対して、SET句を用いることで変数を代入することができる。
DEFAULT句を使わない場合、SET句がなければ変数はNULLとなるというのは前回も書いた。

ちょうどこんな感じ。

DECLARE hoge STRING ;
SET hoge = "ほげ";
SELECT * 
FROM hoge.foo.bar
WHERE name = hoge

ただ、驚きなのはSET句では複数の変数をまとめて扱えるし、クエリの結果も代入できること。そうSELECT AS STRUCT文を使えばね。

DECLARE foo INT64;
DECLARE bar INT64;
SET (foo,bar) = (
 SELECT AS STRUCT 
  SUM(price),
  COUNT(distinct item_id) 
 FROM hoge.foo.bar
)
--こうするとfooにpriceの合計値、barにitem_idのユニーク値が代入されるよ！
SELECT
　FORMAT(
　”売り上げ合計は%d円、商品の種類は%d個",foo,bar
) as result

すごく便利！

EXECUTE IMMEDIATE。よくわからん

つぎにBQのリファレンスに書いてたのはEXECUTE IMMEDIATE句。
変数の宣言、代入の後にくるってことは、すごく大事な考え方なんだろうけど、一見するとひじょーにややこしそうなのでしっかり調べる必要がある。

BQのリファレンスによると。。。

動的 SQL ステートメントをすぐに実行します。

https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language

いやいやいや、「すぐに」ってなんやねん。曖昧すぎるわヴォケ！
と思ったら、Google Cloudの別のページにあったわ。
なんでリファレンスに書かへんねん。。

動的 SQL は SQL のスクリプティングで使用できる強力な手法で、SQL を使って動的に SQL を生成して実行できます。EXECUTE IMMEDIATE は静的文字列と変数の組み合わせの式を受け取り、有効な SQL ステートメントをその場ですぐに生成して即座に実行します。セキュリティ上の理由から、1 回の EXECUTE IMMEDIATE の呼び出しごとに実行できる SQL ステートメントは 1 つだけです。

https://cloud.google.com/blog/ja/products/bigquery/smile-new-user-friendly-sql-capabilities-bigquery

非常にわかりやすい。
結局「すぐに」っていうのはよくわからんが。。。

文法としてはこんな感じらしい。

EXECUTE IMMEDIATE 
　　sql_expression 
　　[ INTO variable[, ...] ] 
　　[ USING identifier[, ...] ];

sql_expression:
  { "query_statement" | expression("query_statement") }

identifier:
  { variable | value } [ AS alias ]

　sql_expressionはクエリのことで、SELECTやCREATEは使えるが、WHILEやIFなどの制御はできないと書かれている。

つまり、IFやWHILEで制御しながら、各ノードにEXECUTE IMMEDIATE句のついたクエリを実行することができるってことかな。

INTOはクエリの結果を1つ以上の変数に代入するための句。Pythonの関数で言うreturnみたいな奴か。

一方USINGはクエリにつかう変数を入力する。関数の引数みたいなものか。

expressionには、関数、条件式、式サブクエリのいずれかが入力できる。

書くとこんな感じ

DECLARE hoge STRING ;
DECLARE foo INT64;
DECLARE bar INT 64;
SET hoge = "ほげ";
SET foo = "10";
--まず、わっしょいという謎のテーブルを作る
EXECUTE IMMIDIATE
 "CREATE TABLE wasshoi ( hoge_name STRING , hoge_score INT64) ";
--つぎにわっしょいテーブルにレコードを代入してみる。
 EXECUTE IMMIDIATE
 "INSERT INTO wasshoi (hoge_name,hoge_score) VALUES(?,?)"
 USING hoge, foo;

?ではなく、こんな感じで定義することもできるので1つの変数を複数回使うとかも大丈夫そう。

EXECUTE IMMIDIATE
 "INSERT INTO wasshoi (hoge_name,hoge_score) VALUES(@a,@b)"
 USING hoge as a, foo as b;

関数も例えばCONCAT文を使えばこんな感じでもできる.

EXECUTE IMMIDIATE
CONCAT( "INSERT INTO wasshoi (hoge_name,hoge_score)", "VALUES('わっしょい','100')"

また、INTO文(INSERT INTOのINTOとは違うよ！)を使うと値を代入できるので、この後に続くクエリに連動させられそう。

最初に定義したbarという変数にhoge_scoreの最大値を代入してみる。
EXECUTE IMMIDIATE
"SELECT max(hoge_score) from wasshoi"
INTO bar;

今日はここまで。