🔍BigQuery Scripting

|| BigQuery Scripting

| 変数宣言

# config
decleare TERM_START string default '2020-01-01'; --集計期間開始値
decleare TERM_END   string default '2020-12-31'; --集計期間終了値
decleare PREP_TS string;                         --Tablea Suffixの処理

set PREP_TS = (
    select format_date('%Y%m%d', current_date('Asia/Tokyo')) as TODAY
);


with
    , rsv as (
        select * from `pj.ds.hoge_reservation_*` where _TABLE_SUFFIX = PREP_TS
    )
    , output as (
        select
            MENU_ID
            , count(distinct LOG_NO) as CV
        from 
            rsv
        where
            RSV_DATE_TRUNC between datetime(TERM_START) and datetime(TERM_END)
    )
select * from output;

cf.手続き型言語※1

|| Reference

• 手続き型言語 - GoogleCloud (※1)
• BigQuery ScriptingでPythonっぽいループ処理をしてみた - Qiita
• BigQuery ScriptingがBetaリリースされたので軽くウォークスルーしてみる - medium https://www.yasuhisay.info/entry/2022/03/14/093500
• BigQuery Scriptingの便利な使い方をまとめてみた - yasuhisa's blog