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')


def dw_pitcher_trend():
    sql="""select a.dt,pitcher,
       sum(cost),
       sum(order_amount),
       sum(reg_order_amount),
       sum(r1) d1,sum(r2) d2,sum(r3) d3,sum(r4) d4,sum(r5) d5,sum(r6) d6,sum(r7) d7,sum(r8) d8,sum(r9) d9,
       sum(r10) d10,sum(r11) d11,sum(r12) d12,
       sum(r13) d13,sum(r14) d14,sum(r15) d15,sum(r16) d16,sum(r17) d17,sum(r18) d18,sum(r19) d19,
       sum(r20) d20,sum(r21) d21,sum(r22) d22,sum(r23) d23,sum(r24) d24,sum(r25) d25,
       sum(r26) d26,sum(r27) d27,sum(r28) d28,sum(r29) d29,sum(r30) d30
from dw_daily_channel a
left outer join
(select toDate(formatDateTime(reg_time,'%Y-%m-%d')) dt,channel,
       sum(if(toDate(formatDateTime(reg_time,'%Y-%m-%d'))=date,amount,0)) as r1,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),1)>=date,amount,0)) as r2,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),2)>=date,amount,0)) as r3,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),3)>=date,amount,0)) as r4,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),4)>=date,amount,0)) as r5,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),5)>=date,amount,0)) as r6,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),6)>=date,amount,0)) as r7,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),7)>=date,amount,0)) as r8,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),8)>=date,amount,0)) as r9,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),9)>=date,amount,0)) as r10,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),10)>=date,amount,0)) as r11,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),11)>=date,amount,0)) as r12,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),12)>=date,amount,0)) as r13,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),13)>=date,amount,0)) as r14,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),14)>=date,amount,0)) as r15,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),15)>=date,amount,0)) as r16,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),16)>=date,amount,0)) as r17,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),17)>=date,amount,0)) as r18,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),18)>=date,amount,0)) as r19,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),19)>=date,amount,0)) as r20,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),20)>=date,amount,0)) as r21,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),21)>=date,amount,0)) as r22,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),22)>=date,amount,0)) as r23,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),23)>=date,amount,0)) as r24,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),24)>=date,amount,0)) as r25,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),25)>=date,amount,0)) as r26,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),26)>=date,amount,0)) as r27,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),27)>=date,amount,0)) as r28,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),28)>=date,amount,0)) as r29,
       sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),29)>=date,amount,0)) as r30
from order group by channel,dt) b on a.dt=b.dt and a.channel=b.channel
group by pitcher,a.dt"""

    data=ck.execute(sql)
    db.quchen_text.execute("truncate table dw_pitcher_trend")
    db.quchen_text.executeMany("replace into dw_pitcher_trend values (%s,%s,%s,%s,%s,%s,"
                           "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,"
                           "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)




if __name__ == '__main__':
    # dw_pitcher_daily()
    dw_pitcher_trend()