DataCenterGame/app/etl/dw/dw_pitcher_daily.py

"""
@desc 投手维度全量表
@auth ck
"""
import time
from model.DateUtils import DateUtils
from model.DataBaseUtils import MysqlUtils,CkUtils
du = DateUtils()
db= MysqlUtils()
ck = CkUtils()


def dw_pitcher_daily():
    sql="""
    select dt,pitcher,sum(order_amount),sum(cost),sum(reg_order_amount),sum(first_order_amount),0,0
    from dw_daily_channel group by dt,pitcher
    """
    data = ck.execute(sql)
    # db.quchen_text.execute('truncate table dw_pitcher_daily')
    db.quchen_text.executeMany('replace into dw_pitcher_daily values (%s,%s,%s,%s,%s,%s,%s,%s)',data)

    add_total()


def add_total():
    sql='select pitcher from dw_pitcher_daily group by pitcher'
    pitchers= [i[0] for i in db.quchen_text.getData(sql)]

    sql2="""replace into dw_pitcher_daily
    SELECT x.dt,x.pitcher,amount,cost,reg_amount,first_reg_amount,x.total_cost,x.total_amount FROM
    
    (SELECT dt,pitcher,@total_sum:=@total_sum+reg_amount total_amount,@total_cost:=@total_cost+cost total_cost from 
        (SELECT dt,pitcher,reg_amount,cost FROM dw_pitcher_daily where pitcher='{}' order by dt) b,(SELECT @total_sum:=0,@total_cost:=0) a 
    ) x 
    left join dw_pitcher_daily y on x.dt=y.dt and x.pitcher=y.pitcher"""

    for i in pitchers:
        db.quchen_text.execute(sql2.format(i))

    db.quchen_text.execute('delete from dw_pitcher_daily where total_cost=0')


if __name__ == '__main__':
    dw_pitcher_daily()