DataCenterGame/app/etl/dw/dw_book_trend.py

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



def book_trend():
    logging.info('书籍趋势数据处理,开始')
    sql = """
    truncate table book_trend;
    
    insert into book_trend
    select a.dt,book,type,
    sum(cost) cost,
    sum(order_count),
    sum(order_user),
    sum(order_amount),
    sum(reg_num),
    sum(create_user_num),
    
    sum(first_order_count) first_order_count,
    sum(first_order_user) first_order_user,
    sum(first_order_amount) first_order_amount,
    
    sum(reg_order_count) reg_order_count,
    sum(reg_order_user) reg_order_user,
    sum(reg_order_amount) reg_order_amount,
    sum(view_count) view_count,
    sum(click_count) click_count,
    sum(follow_user) follow_user,
    sum(da1),sum(da2),sum(da3),sum(da4),sum(da5),sum(da6),sum(da7),sum(da8),sum(da9),sum(da10),sum(da11),sum(da12),sum(da13),sum(da14),sum(da15),sum(da16),sum(da17),sum(da18),sum(da19),sum(da20),sum(da21),sum(da22),sum(da23),sum(da24),sum(da25),sum(da26),sum(da27),sum(da28),sum(da29),sum(da30),sum(da31),sum(da32),sum(da33),sum(da34),sum(da35),sum(da36),sum(da37),sum(da38),sum(da39),sum(da40),sum(da41),sum(da42),sum(da43),sum(da44),sum(da45),sum(da46),sum(da47),sum(da48),sum(da49),sum(da50),sum(da51),sum(da52),sum(da53),sum(da54),sum(da55),sum(da56),sum(da57),sum(da58),sum(da59),sum(da60),sum(dm3),sum(dm4),sum(dm5),
    sum(ba1),sum(ba2),sum(ba3),sum(ba4),sum(ba5),sum(ba6),sum(ba7)
    
    from dw_channel a 
    left join dw_channel_amount_daily b on a.dt=b.dt and a.channel=b.channel 
    left join dw_channel_amount_daily_reverse c on a.dt=c.dt and a.channel=c.channel 
    where book !=''
    GROUP BY dt,book,type"""


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


if __name__ == '__main__':
    book_trend()