Bigqueryで定数作りたいよ

Bigqueryを使っていると、パーティションの日付等、定数化して管理したい値が結構でてきます。
という事でやってみました。ChatGPT。
この記事はPTAのアドベントカレンダー4日目の記事です。
ChatGPTについては2日目のこちらの記事で詳しく？解説されています！

ムムム・・・
なんかおしい？　DECLAREは使えそうですが、CREATE CONSTANTはBigqueryにないような？

ともあれ、Bigqueryではクエリの再利用を目的とした定数のようなものを定義する方法は、実はいくつかあります。
用途によっては別の方法のほうが便利な場合もあるので、いくつか紹介します。

Google標準SQL 手続き型言語のDECLAREを使用する

こちらがChatGPTで出てきた手法(DECLARE)です。

DECLARE START_DATE DATE DEFAULT DATE(
  '2022-11-19' 
);
DECLARE END_DATE DATE DEFAULT DATE(
  '2022-11-25' 
);

SELECT * FROM example WHERE _PARTITIONDATE BETWEEN START_DATE AND END_DATE

DECLAREは、まさに変数です。
SET句を使うとあとから値を代入することも可能です。
かなり柔軟なので複雑なこともできます。
しかし、定数が増えてくると、煩雑になる事もあります。

ユーザー定義関数を使う

ユーザー定義関数を使うことでスッキリ書くこともできます。

CREATE TEMPORARY FUNCTION const() AS (
  STRUCT(
    DATE("2022-03-15") AS start_date,
    DATE("2022-03-15") AS end_date
  )
);

SELECT * FROM example WHERE _PARTITIONDATE BETWEEN const().start_date AND const().end_date

まー好みの問題もあると思いますが、STRUCTの中にガンガン値を詰め込めるのは便利です。

しかし、これらの方法はTableauやGoogleスプレッドシート等のBIツールから呼び出したいときに使えないケースが多いです。
BIツールがこれらのステートメントに対応していない事が多いのです。

仮想テーブルを使う

そこでオススメなのがこの仮想テーブルを使う方法です。

WITH const AS
(
    SELECT
    DATE("2022-11-01") as start_day,
    DATE("2022-11-30") as end_day,
)

SELECT * FROM example WHERE _PARTITIONDATE BETWEEN (SELECT const FROM start_day) AND (SELECT const FROM end_day)

WITHは多くのSQL実行環境に対応しているのでBIツールからの利用もできる可能性が高いです。
ただ、この書き方だと値の取り出しがやや冗長なので、CROSS JOINを活用します。

WITH const AS
(
    SELECT
    DATE("2022-11-01") as start_day,
    DATE("2022-11-30") as end_day,
)

SELECT * FROM example, const WHERE _PARTITIONDATE BETWEEN start_day AND end_day

取り出したいテーブルの後ろに , テーブル名　でカンマクロス結合になり大幅に記述を省略できます。
BIツールでの活用を見越しているクエリの場合はこの記述方法がおすすめです。

おまけ　複数の値を詰め込みたい

WITH const AS
(
    SELECT
    DATE("2022-11-01") as start_day,
    DATE("2022-11-30") as end_day,
    ['iOS', 'Android', 'PC'] as target_os,
)
create temporary function target_os() as (
    ['iOS', 'Android', 'PC']
);

SELECT * FROM example, const WHERE os IN UNNEST(target_os)

配列で詰め込んでUNNESTを使えば取り出せます。

みなさんも、定数を使って快適なSQLLifeを送って頂けたら幸いです。