状況
BigQueryでデータ抽出をしていると、既存のSQLを微調整して再利用したいことがあるかと思います。
自作の簡単なSQLを元にするならともかく、元のSQLが複雑だったり他人の作ったものだったりすると、つど確認の手間がかかりますし、修正箇所を見落として出力ミスに繋がるおそれもあります。
対策
こんなとき、他の言語であれば編集箇所を変数化しておくことが定石です。あらかじめ編集しそうな箇所を変数としたコードを作っておき、代入する値を変えることで出力を変えられるようにしておけば、再利用時の確認の手間も出力ミスのリスクも軽減できます。
御多分に洩れずStandardSQLでも変数を使った記述が可能です。今回はその概要と実用例を紹介します。
StandardSQLにおける変数宣言法
StandardSQLでは、DECLAREコマンドを使って変数を宣言します。
DECLARE {変数名} [{変数のデータ型}] [DEFAULT {変数のデフォルト値}];DECLARE hoge int64; のように変数名とデータ型を宣言し、宣言後に変数を使用したいSQLコマンドを続けて記述します。初期値は DECLARE hoge int64 DEFAULT 0 のように DEFAULT句で指定します。
# int64型の変数hogeを宣言、初期値は100
DECLARE hoge int64 DEFAULT 100;
# hogeを使った出力
SELECT 2 * hoge;
-> 200DEFAULT句で初期値を明示しなかった場合、変数の初期値はNULLになるようです。
後からSETコマンドで値を代入することも可能です。
# int64型の変数hogeを宣言、初期値はNULL
DECLARE hoge int64;
# hogeに1000を代入
SET hoge = 1000;
# hogeを使った出力
SELECT 2 * hoge;
-> 2000その他、データ型や初期値が共通の変数を複数同時に宣言したり、関数による出力を代入することもできます。
# DATE型の変数を複数宣言、初期値は1900/1/1
DECLARE date_1, date_2 date '1900-01-01';
# date_2に本日の日付を代入
SET date_2 = CURRENT_DATE();
# date_1とdate_2を比較
SELECT date_1 < date_2;
-> TRUEその他、細かな操作はGoogle公式も参照ください。
活用例
最後に、私がよく用いる記述パターンを2つ例示します。
取得する日付範囲を明示
複数の参照元から同一期間のログを取得するようなクエリの場合、開始時点と終了時点を変数化しておきます。SQL中の各所を手作業で修正しなくとも、DECLAREコマンドのDEFAULT句を変更するだけで取得期間を正常に変更できます。
#StandardSQL
declare date_from date default '2023-01-01';
declare date_to date default '2023-01-31';
with hoge_tbl as (
select
...
from
...
where
date between date_from and date_to
)
,fuga_tbl as (
select
...
from
...
where
date between date_from and date_to
)
,piyo_tbl as (
select
...
from
...
where
date between date_from and date_to
)
...
;応用編。開始時点と終了時点を可変にし、月次バッチで集計を追加するクエリなどにも活用できます。
#StandardSQL
/*月初に先月の月次集計を実施。集計結果は同一テーブルに保存する。*/
declare date_from date default
date_sub(
date_trunc(current_date('Asia/Tokyo'), month)
,interval 1 month
) # 前月1日
;
declare date_to date default
date_sub(
date_trunc(current_date('Asia/Tokyo'), month)
,interval 1 day
) # 前月末
;
# データ重複を避けるため、これから入力するデータが既に入っていたら削除しておく
delete
from
`monthly_report`
where
date between date_from and date_to
;
insert into `monthly_report`
select
date
,sum(amount) as amount
from
transaction
group by
date
;特定IDリストのデータを抽出
変数のデータ型には配列型も指定が可能です。DECLARE hoge array<int64> DEFAULT [0, 1, 2];のように、array<{配列内要素のデータ型}> のような記述でデータ型を指定してあげます。
私はSQLの可読性を向上させるために配列型変数を使うことが多いです。
たとえば”任意のID群のデータを抽出する”といったケース。集計コマンドの途中に大量のID群を記述してしまうと、見辛い記述になってしまいます。
#StandardSQL
/*
指定された大量のIDのデータを取り出す (変数未使用ver)
*/
with hoge_tbl as (...)
,fuga_tbl as (
select
..
from
..
where
id in (
1, 2, 3, 6,
...,
...,
...,
2000, 2002
) # クエリの途中に大量のID群が手打ちで明示されている状態
)
...
select ...
;このような場合、あらかじめDECLAREブロックでID配列を明示しておきます。
集計コマンドから冗長な記述を切り離せるので、集計内容をレビューしやすくなります。
#StandardSQL
/*
指定された大量のIDのデータを取り出す
変数使用ver
*/
# 記述の冒頭にID群を宣言しておく
declare arr_product_id array<int64> default
[
1, 2, 3, 6,
...,
...,
...,
2000, 2002
]
;
with hoge_tbl as (...)
,fuga_tbl as (
select
...
from
...
where
id in unnest(arr_product_id)
...
select ...
;
コメント