BigQuery Scripting

|| BigQuery Scripting

| 変数宣言

# 集計期間
decleare TERM_START string default '2020-01-01';
decleare TERM_END   string default '2020-12-31';
decleare PREP_TS string;

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_TABLE_SUFFIX
    )
    , 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