#🔎 #BigQuery #BigQueryScripting #_TABLE_SUFFIX #ga_data

table_suffix.sql

#standaardSQL

# 集計期間
decleare term_start string default '2020-01-01';
decleare term_end string default '2020-12-31';
decleare prep_table_suffix string;

set prep_table_suffix = (
    select format_date('%Y%m%d', current_date('Asia/Tokyo')) as TODAY
    );
 
with 
    rsv_raw as (
        select * from `pj.ds.hoge_reservation_*` where _TABLE_SUFFIX = prep_table_suffix
    )
    ,rsv_prep as (
        # 前処理テーブル
        select
            SHOPOWNER_ID
            , RSV_DATE
            , date_trunc(RSV_DATE, day) as RSV_DATE_TRUNC 
            , APPOINT_DATETIME
            , RSV_VALID
            , LOG_NO
            , MENU_ID
        from 
            rsv_raw
        where
            RSV_VALID = '1' --有効予約
        
    )
    , info_raw as (
        select 1
    )
    , info_prep as (
        select 1
    )
    , ga_raw as (
        select 1
    )
    , ga_prep as (
        select 1
    )
    , output as (
        select
            MENU-ID
            , count(distinct LOG_NO) as CV
        from 
            rsv_prep
        where
            RSV_DATE_TRUNC between datetime(term_start) and datetime(term_end)
    )

select * from output
;