【BigQuery】変数を使ってSQLの可用性を上げる

administrator

2年前

状況

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;

-> 200

DEFAULT句で初期値を明示しなかった場合、変数の初期値は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 ...
;