DataCenterGame/app/etl/dw/dw_pitcher_daily.py

"""
@desc 投手维度全量表
@auth ck
"""
from model.sql_models import DB
from config import using_config
import logging


def dw_pitcher_trend():
    logging.info('投手趋势数据处理,开始')
    logging.info('run> dw_pitcher_trend')
    sql = """
    truncate table dw_pitcher_trend;
    
    insert into dw_pitcher_trend 
    select a.dt,pitcher,
       sum(cost),
       sum(order_amount),
       sum(reg_order_amount),
       sum(first_order_amount),
       sum(first_order_amount*100/node-cost) inva_cost,
       sum(first_order_user),
       sum(reg_order_user),
       sum(view_count),
       sum(click_count),
       sum(follow_user),
       sum(da1) d1,sum(da2) d2,sum(da3) d3,sum(da4) d4,sum(da5) d5,sum(da6) d6,sum(da7) d7,sum(da8) d8,sum(da9) d9,
       sum(da10) d10,sum(da11) d11,sum(da12) d12,
       sum(da13) d13,sum(da14) d14,sum(da15) d15,sum(da16) d16,sum(da17) d17,sum(da18) d18,sum(da19) d19,
       sum(da20) d20,sum(da21) d21,sum(da22) d22,sum(da23) d23,sum(da24) d24,sum(da25) d25,
       sum(da26) d26,sum(da27) d27,sum(da28) d28,sum(da29) d29,sum(da30) d30,
       sum(ba1),sum(ba2),sum(ba3),sum(ba4),sum(ba5),sum(ba6),sum(ba7),
       sum(first_order_amount*annual_mult-cost) annual_expect_profit
from dw_channel a
left join dw_channel_amount_daily b on a.dt=b.dt and a.channel=b.channel
left join src_book_info c on a.book=c.book and a.platform=c.platform and a.type=c.type and a.dt=c.dt 
left join dw_channel_amount_daily_reverse d on a.dt=d.dt and a.channel=d.channel 
group by pitcher,a.dt"""

    dm_db = DB(config=using_config.dm)
    dm_db.session.execute(sql)
    dm_db.session.commit()
    logging.info('投手趋势数据处理,结束')


if __name__ == '__main__':
    dw_pitcher_trend()