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_trend():
    sql="""select a.dt,pitcher,
       sum(cost),
       sum(order_amount),
       sum(reg_order_amount),
       sum(first_order_amount),
       sum(first_order_amount*require_mult-cost) inva_cost,
       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.dm.execute("truncate table dw_pitcher_trend")
    db.dm.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,%s,%s)",data)

    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,%s,%s)", data)


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