# Bigquery 資料庫相關資料

dashboard_data_new.tw_transaction_lineID_all => 交易紀錄

select transaction_all.*, tw_line_member.lineID,tw_line_member.update_time
from(
    select 
        the_transaction_id,
        sum(product_turnover) as product_turnover, 
        sum(product_qty) as product_qty, 
        any_value(loyalty_card_num) as loyalty_card_num,
        any_value(tdt_date_event) as tdt_date_event,
        any_value(tdt_month_event) as tdt_month_event,
        any_value(tdt_year_event) as tdt_year_event,
        any_value(store) as store
    from `twdatawarehouse-bsv8.dashboard_data_new.tw_transaction_all_new`
    where tdt_date_event > '2018-01-01' and loyalty_card_num is not null and loyalty_card_num != ''
    group by the_transaction_id

) as transaction_all
left join (
    select * from `twdatawarehouse-bsv8.external_channel_data.tw_line_member` 
    where loyalty_card_num is not null
)tw_line_member using(loyalty_card_num)