from model.DateUtils import DateUtils from model.DataBaseUtils import * from model.log import logger from model.CommonUtils import * du = DateUtils() log = logger() """根据公众号获取投手""" # def get_channel_belong_pitcher(channel): # # sql = f"select pitcher,channel from dw_daily_channel_cost where dt='{du.get_n_days(-1)}' and pitcher!='' and channel!=''" # data=ck.execute(sql) # di={} # for i in data: # di[i[1]]=i[0] # return di.get(channel,'') """根据名字获取其归属的公众号""" def get_channels_from_user(user): sql=f"""SELECT a.nick_name FROM t_mp_account a left join t_sys_user b on a.oper_user_id=b.user_id where b.nick_name='{user}'""" return tuple(MysqlUtils().zx.getOneList(sql)) def get_pitcher_panel_channel(pitcher,channel,start,end,page,page_size,order_by,order): db=MysqlUtils() op1=f" and pitcher='{pitcher}'" if pitcher else '' op2=f" and channel='{channel}'" if channel else '' sql=f"""select channel,stage,platform,book, dt as date,cost,first_order_amount, if(cost=0,0,first_order_amount/cost) first_roi, first_order_user,first_order_count, round(if(first_order_user=0,0,cost/first_order_user),2) first_per_cost, view_count,click_count,follow_user, round(if(click_count=0,0,follow_user/click_count),4) follow_rate, round(if(follow_user=0,0,cost/follow_user),2) follow_per_cost, total_cost, round(if(total_cost=0,0,total_amount/total_cost),4) back_rate from dw_channel_daily where dt>='{start}' and dt<='{end}' {op1} {op2} order by {order_by} {order} """ return getLimitData(db.quchen_text,sql,page,page_size) def get_pitcher_panel_daily(pitcher, start, end, page, page_size, order_by, order): db=MysqlUtils() op1= f" and pitcher='{pitcher}'" if pitcher else '' op2=f" and dt>='{start}' " if start else '' op3=f" and dt<='{end}' " if end else '' op4=f" order by {order_by} {order}" if order_by and order else '' sql=f""" select dt date, pitcher,cost, first_order_amount, reg_amount, round(if(cost=0,0,first_order_amount/cost),4) first_roi, amount order_amount, round(if(cost=0,0,reg_amount/cost),4) today_roi, reg_amount-cost profit from dw_pitcher_trend where 1=1 {op1} {op2} {op3} {op4} """ print(sql) sumSql=f""" select '总计' date,sum(cost) cost, sum(first_order_amount) first_order_amount,sum(reg_amount) reg_amount, round(if(sum(cost)=0,0,sum(first_order_amount)/sum(cost)),4) first_roi, sum(order_amount) order_amount, round(if(sum(cost)=0,0,sum(reg_amount)/sum(cost)),4) today_roi, sum(profit) profit from ({sql}) b """ print(sumSql) return getLimitSumData(db.dm,sql,sumSql,page,page_size) def get_pitcher_panel_overview(pitcher): db=MysqlUtils() op1= f" and pitcher='{pitcher}'" if pitcher else '' sql=f"""select pitcher, total_cost cost, total_amount amount, if(total_cost=0,0,total_amount/total_cost) roi, channel_count, on_channel_count, off_channel_count, this_month_cost this_month_cost, this_month_amount this_month_amount, this_month_roi this_month_roi, last_month_cost last_month_cost, last_month_amount last_month_amount, last_month_roi last_month_roi, last_month_far_amount last_month_far_amount, follow_user, last_month_far_roi last_month_far_roi from dm_pitcher_daily_overview a left join ( select sum(cost) total_cost,sum(reg_amount) total_amount,pitcher pitcher2 from dw_pitcher_trend group by pitcher) b on pitcher=pitcher2 where 1=1 {op1} """ print(sql) return db.dm.getData_json(sql) def get_channel_overview(channel,pitcher,stage,start,end,page,page_size,order_by,order): db=MysqlUtils() if pitcher: op1 = f" and pitcher='{pitcher}'" if get_channels_from_user( pitcher).__len__() == 0 else f" and channel in {get_channels_from_user(pitcher)}" else: op1 = '' op2 = f" and channel='{channel}'" if channel else '' op3 = f" and dt>='{start}' " if start else '' op4 = f" and dt<='{end}' " if end else '' op5 = f" order by {order_by} {order}" if order_by and order else '' op6=f" and stage='{stage}'" if stage else '' sql=f"""select channel,dt date, view_count,click_count, round(if(view_count=0,0,click_count/view_count),4) click_rate, follow_user, round(if(click_count=0,0,follow_user/click_count),4) follow_rate, round(if(follow_user=0,0,cost/follow_user),2) follow_per_cost, round(if(click_count=0,0,first_order_count/click_count),4) order_rate, round(if(first_order_user=0,0,cost/first_order_user),2) order_per_cost, cost, first_order_count,first_order_user, first_order_amount, order_count,order_user, order_amount, order_amount-first_order_amount old_order_amount, round(if(first_order_user=0,0,first_order_amount/first_order_user),2) first_amount_per_user, round(if(follow_user=0,0,first_order_amount/follow_user),2) amount_per_follow, round(if(first_order_user=0,0,cost/reg_order_user),2) first_cost_per_user, round(if(follow_user=0,0,first_order_user/follow_user),4) new_user_order_rate, reg_order_amount reg_user_amount, round(if(cost=0,0,first_order_amount/cost),4) day_roi, round(reg_order_amount/cost,4) roi, reg_order_user_again, reg_order_user, round(if(reg_order_user=0,0,reg_order_user_again/reg_order_user),4) old_user_once_order_rate, pitcher,stage,book,type, round(cost/first_order_user,2) first_order_user_per_cost, round(reg_order_amount/reg_order_user,2) user_per_amount, round(follow_user/click_count,4) click_follow_rate, round(reg_order_user/follow_user,4) follow_order_rate from dw_channel where 1=1 {op1} {op2} {op3} {op4} {op6} {op5} """ print(sql) sumsql=f"""select '总计' date, sum(view_count) view_count, sum(click_count) click_count, round(sum(click_count)/sum(view_count),4) click_rate, sum(follow_user) follow_user, round(sum(follow_user)/sum(click_count),4) follow_rate, round(sum(cost)/sum(follow_user),2) follow_per_cost, round(sum(first_order_count)/sum(click_count),4) order_rate, round(sum(cost)/sum(first_order_user),2) order_per_cost, sum(cost) cost, sum(first_order_count) first_order_count, sum(first_order_user) first_order_user, sum(first_order_amount) first_order_amount, sum(order_count) order_count, sum(order_user) order_user, sum(order_amount) order_amount, sum(old_order_amount) old_order_amount, round(sum(first_order_amount)/sum(first_order_user),2) first_amount_per_user, round(sum(first_order_amount)/sum(follow_user),2) amount_per_follow, round(sum(cost)/sum(first_order_user),2) first_cost_per_user, round(sum(first_order_user)/sum(follow_user),4) new_user_order_rate, sum(reg_user_amount) reg_user_amount, round(sum(first_order_amount)/sum(cost),4) day_roi, round(sum(reg_user_amount)/sum(cost),4) roi, round(sum(reg_order_user_again)/sum(reg_order_user),4) old_user_once_order_rate, round(sum(cost)/sum(first_order_user),2) first_order_user_per_cost, round(sum(reg_user_amount)/sum(reg_order_user),2) user_per_amount, round(sum(follow_user)/sum(click_count),4) click_follow_rate, round(sum(reg_order_user)/sum(follow_user),4) follow_order_rate from ({sql}) a """ return getLimitSumData(db.dm,sql,sumsql,page,page_size) def get_channel_again_order_trend(channel,date,pitcher): ck = CkUtils() # if get_channel_belong_pitcher(channel)!=pitcher: # return [] sql=f"""select toString(dt) date, channel,book,pitcher,stage, toDecimal32(cost,2), toDecimal32(reg_order_amount,2), toDecimal32(if(cost=0,0,reg_order_amount/cost),4) roi, follow_user new_follow, toDecimal32(if(follow_user=0,0,cost/follow_user),2) new_follow_per_cost, reg_order_count reg_count,reg_order_user reg_user, toDecimal32(if(reg_order_user=0,0,cost/reg_order_user),2) cost_per_user, toDecimal32(if(reg_order_user=0,0,reg_order_amount/reg_order_user),2) avg_order_amount, toDecimal32(if(reg_order_user=0,0,reg_order_user_again/reg_order_user),4) avg_again_order_rate, if(reg_order_user=0,0,order_count/reg_order_user) order_count from dw_daily_channel where channel='{channel}' and dt='{date}' """ # print(sql) data=ck.execute(sql) # print(data) key1=['date','channel','book','pitcher','stage','cost','reg_amount','roi','new_follow','new_follow_per_cost', 'reg_count','reg_user','cost_per_user','avg_order_amount','avg_again_order_rate','order_count'] json1=get_dict_list(key1,get_round(data))[0] # print(json1) # print(json1) sql2=f"""select 1 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3, sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6 from (select count(1) count from order where channel='{channel}' and date='{date}' and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id,date) a union all select 2 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3, sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6 from ( select count(1) count from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),1) and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a union all select 3 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3, sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6 from ( select count(1) count from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),2) and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a union all select 4 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3, sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6 from ( select count(1) count from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),3) and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a union all select 5 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3, sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6 from ( select count(1) count from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),4) and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a union all select 6 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3, sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6 from ( select count(1) count from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),5) and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a union all select 7 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3, sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6 from ( select count(1) count from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),6) and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a""" df=ck.execute(sql2) print(df) # 补全 # xx=[i[0] for i in df] # for i in range(1,8): # if i not in xx: # df.append((i,0,0,0,0,0,0)) # 排序 import operator df.sort(key=operator.itemgetter(0)) print(df) reg_user=json1["reg_user"] li=[] for i in range(1,6): print(i) d = {} d["user_order_count"] = i d1={} d1["origin"]=df[0][i] d1["new"]=0 d1["move"]=df[0][i+1] d1["now"]=d1["origin"]+d1['new']-d1["move"] d1["follow_order_rate"]=round(d1["now"]/df[0][1],2) if df[0][i]!=0 else 0 d['d1'] = d1 d2={} d2["origin"] = d1["now"] d2["new"] = df[1][i]-df[0][i] d2["move"] = df[1][i+1]-df[0][i+1] d2["now"] = df[1][i]-df[1][i+1] d2["follow_order_rate"] = round(d2["now"] /df[1][1], 2) if df[1][i]!=0 else 0 d['d2'] = d2 d3={} d3["origin"] = d2["now"] d3["new"] = df[2][i]-df[1][i] d3["move"] =df[2][i+1]-df[1][i+1] d3["now"] = df[2][i]-df[2][i+1] d3["follow_order_rate"] = round(d3["now"] / df[2][1], 2) if df[2][i]!=0 else 0 d['d3'] = d3 d4={} d4["origin"] = d3["now"] d4["new"] = df[3][i]-df[2][i] d4["move"] = df[3][i+1]-df[2][i+1] d4["now"] = df[3][i]-df[3][i+1] d4["follow_order_rate"] = round(d4["now"] / df[3][1], 2) if df[3][i]!=0 else 0 d['d4'] = d4 d5={} d5["origin"] = d4["now"] d5["new"] = df[4][i]-df[3][i] d5["move"] = df[4][i+1]-df[3][i+1] d5["now"] = df[4][i]-df[4][i+1] d5["follow_order_rate"] = round(d4["now"] / df[4][1], 2) if df[3][i]!=0 else 0 d['d5'] = d5 d6 = {} d6["origin"] = d5["now"] d6["new"] = df[5][i]-df[4][i] d6["move"] = df[5][i+1]-df[4][i+1] d6["now"] = df[5][i]-df[5][i+1] d6["follow_order_rate"] = round(d6["now"] / df[5][1], 2) if df[5][i]!=0 else 0 d['d6'] = d6 d7 = {} d7["origin"] = d6["now"] d7["new"] = df[6][i]-df[5][i] d7["move"] = df[6][i+1]-df[5][i+1] d7["now"] = df[6][i]-df[6][i+1] d7["follow_order_rate"] = round(d7["now"] / df[6][1], 2) if df[6][i]!=0 else 0 d['d7'] = d7 li.append(d) print(li) json1['data']=li # print([json1]) return [json1] def get_channel_active(channel,pitcher,start,end,page,page_size,order_by,order): db = MysqlUtils() if pitcher: op1 = f" and pitcher='{pitcher}'" if get_channels_from_user( pitcher).__len__() == 0 else f" and channel in {get_channels_from_user(pitcher)}" else: op1 = '' op2 = f" and channel='{channel}'" if channel else '' op3 = f" and dt>='{start}' " if start else '' op4 = f" and dt<='{end}' " if end else '' op5 = f" order by {order_by} {order}" if order_by and order else '' sql=f""" select channel,dt date,book,stage,pitcher,cost, reg_order_amount reg_amount, dc7,dc30, round(reg_order_amount/cost,4) roi, follow_user new_follow_user, round(cost/follow_user,2) new_follow_per_cost, reg_order_user order_user, reg_order_count order_count, round(cost/reg_order_user,2) order_user_per_cost, round(dc7/follow_user,4) day7_avg_act_rate, round(cost/dc7,2) day7_avg_act_per_cost, round(dc30/follow_user,4) day30_avg_act_rate, round(cost/dc30,2) day30_avg_act_cost, round(cost/reg_order_user,2) act_per_cost, concat(dc1,',',cost/dc1,',',dc1/reg_order_user) d1, concat(dc2-dc1,',',cost/dc2,',',(dc2-dc1)/reg_order_user) d2, concat(dc3-dc2,',',cost/dc3,',',(dc3-dc2)/reg_order_user) d3, concat(dc4-dc3,',',cost/dc4,',',(dc4-dc3)/reg_order_user) d4, concat(dc5-dc4,',',cost/dc5,',',(dc5-dc4)/reg_order_user) d5, concat(dc6-dc5,',',cost/dc6,',',(dc6-dc5)/reg_order_user) d6, concat(dc7-dc6,',',cost/dc7,',',(dc7-dc6)/reg_order_user) d7, concat(dc8-dc7,',',cost/dc8,',',(dc8-dc7)/reg_order_user) d8, concat(dc9-dc8,',',cost/dc9,',',(dc9-dc8)/reg_order_user) d9, concat(dc10-dc9,',',cost/dc10,',',(dc10-dc9)/reg_order_user) d10, concat(dc11-dc10,',',cost/dc11,',',(dc11-dc10)/reg_order_user) d11, concat(dc12-dc11,',',cost/dc12,',',(dc12-dc11)/reg_order_user) d12, concat(dc13-dc12,',',cost/dc13,',',(dc13-dc12)/reg_order_user) d13, concat(dc14-dc13,',',cost/dc14,',',(dc14-dc13)/reg_order_user) d14, concat(dc15-dc14,',',cost/dc15,',',(dc15-dc14)/reg_order_user) d15, concat(dc16-dc15,',',cost/dc16,',',(dc16-dc15)/reg_order_user) d16, concat(dc17-dc16,',',cost/dc17,',',(dc17-dc16)/reg_order_user) d17, concat(dc18-dc17,',',cost/dc18,',',(dc18-dc17)/reg_order_user) d18, concat(dc19-dc18,',',cost/dc19,',',(dc19-dc18)/reg_order_user) d19, concat(dc20-dc19,',',cost/dc20,',',(dc20-dc19)/reg_order_user) d20, concat(dc21-dc20,',',cost/dc21,',',(dc21-dc20)/reg_order_user) d21, concat(dc22-dc21,',',cost/dc22,',',(dc22-dc21)/reg_order_user) d22, concat(dc23-dc22,',',cost/dc23,',',(dc23-dc22)/reg_order_user) d23, concat(dc24-dc23,',',cost/dc24,',',(dc24-dc23)/reg_order_user) d24, concat(dc25-dc24,',',cost/dc25,',',(dc25-dc24)/reg_order_user) d25, concat(dc26-dc25,',',cost/dc26,',',(dc26-dc25)/reg_order_user) d26, concat(dc27-dc26,',',cost/dc27,',',(dc27-dc26)/reg_order_user) d27, concat(dc28-dc27,',',cost/dc28,',',(dc28-dc27)/reg_order_user) d28, concat(dc29-dc28,',',cost/dc29,',',(dc29-dc28)/reg_order_user) d29, concat(dc30-dc29,',',cost/dc30,',',(dc30-dc29)/reg_order_user) d30 from (select * from dw_channel where cost>0 {op1} {op2} {op3} {op4}) a left join dw_channel_user_daily b using(dt,channel) {op5} """ data,total=getLimitData(db.dm,sql,page,page_size) def parse(str): li=str.split(',') li[0]=round(float(li[0]),0) li[1]=round(float(li[1]),2) li[2]=round(float(li[2]),4) return dict(zip(['act_user', 'act_cost_per_cost', 'act_rate'],li)) for i in data: for x in i: if x in ['d1','d2','d3','d4','d5','d6','d7','d8','d9','d10', 'd11','d12','d13','d14','d15','d16','d17','d18','d19','d20','d21','d22','d23','d24','d25','d26','d27','d28','d29','d30']: i[x]=parse(i[x]) if i[x] is not None else {} return data,total def get_channel_order_trend(channel,pitcher,start,end,page,page_size,order_by,order): db=MysqlUtils() if pitcher: op1 = f" and pitcher='{pitcher}'" if get_channels_from_user( pitcher).__len__() == 0 else f" and channel in {get_channels_from_user(pitcher)}" else: op1 = '' op2 = f" and channel='{channel}'" if channel else '' op3 = f" and dt>='{start}' " if start else '' op4 = f" and dt<='{end}' " if end else '' op5 = f" order by {order_by} {order}" if order_by and order else '' sql=f"""select stage,pitcher,a.channel,a.dt date,a.book,cost, a.type, node/100 require_roi,round(100/node,2) require_mult, round(first_order_amount*100/node-cost,2) expect_profit, annual_mult, round(first_order_amount*annual_mult -cost,2) annual_expect_profit, first_order_amount first_amount, reg_order_amount reg_amount, reg_order_amount-cost profit, follow_user new_follow_user, round(reg_order_amount/cost,4) roi, round(first_order_amount/cost,4) first_roi, round(cost/follow_user,2) new_follow_per_cost, reg_order_user order_user, reg_order_count order_count, round(cost/reg_order_user ,2) order_user_per_cost, concat(da1,',',da1/cost,',', 0,',',1) d1, concat(da2-da1,',',da2/cost,',', (da2-da1)/cost,',',da2/da1) d2, concat(da3-da2,',',da3/cost,',', (da3-da2)/cost,',',da3/da1) d3, concat(da4-da3,',',da4/cost,',', (da4-da3)/cost,',',da4/da1) d4, concat(da5-da4,',',da5/cost,',', (da5-da4)/cost,',',da5/da1) d5, concat(da6-da5,',',da6/cost,',', (da6-da5)/cost,',',da6/da1) d6, concat(da7-da6,',',da7/cost,',', (da7-da6)/cost,',',da7/da1) d7, concat(da8-da7,',',da8/cost,',', (da8-da7)/cost,',',da8/da1) d8, concat(da9-da8,',',da9/cost,',', (da9-da8)/cost,',',da9/da1) d9, concat(da10-da9 ,',' ,da10/cost ,',', (da10-da9)/cost ,',' ,da10/da1) d10, concat(da11-da10 ,',' ,da11/cost ,',', (da11-da10)/cost ,',' ,da11/da1) d11, concat(da12-da11 ,',' ,da12/cost ,',', (da12-da11)/cost ,',' ,da12/da1) d12, concat(da13-da12 ,',' ,da13/cost ,',', (da13-da12)/cost ,',' ,da13/da1) d13, concat(da14-da13 ,',' ,da14/cost ,',', (da14-da13)/cost ,',' ,da14/da1) d14, concat(da15-da14 ,',' ,da15/cost ,',', (da15-da14)/cost ,',' ,da15/da1) d15, concat(da16-da15 ,',' ,da16/cost ,',', (da16-da15)/cost ,',' ,da16/da1) d16, concat(da17-da16 ,',' ,da17/cost ,',', (da17-da16)/cost ,',' ,da17/da1) d17, concat(da18-da17 ,',' ,da18/cost ,',', (da18-da17)/cost ,',' ,da18/da1) d18, concat(da19-da18 ,',' ,da19/cost ,',', (da19-da18)/cost ,',' ,da19/da1) d19, concat(da20-da19 ,',' ,da20/cost ,',', (da20-da19)/cost ,',' ,da20/da1) d20, concat(da21-da20 ,',' ,da21/cost ,',', (da21-da20)/cost ,',' ,da21/da1) d21, concat(da22-da21 ,',' ,da22/cost ,',', (da22-da21)/cost ,',' ,da22/da1) d22, concat(da23-da22 ,',' ,da23/cost ,',', (da23-da22)/cost ,',' ,da23/da1) d23, concat(da24-da23 ,',' ,da24/cost ,',', (da24-da23)/cost ,',' ,da24/da1) d24, concat(da25-da24 ,',' ,da25/cost ,',', (da25-da24)/cost ,',' ,da25/da1) d25, concat(da26-da25 ,',' ,da26/cost ,',', (da26-da25)/cost ,',' ,da26/da1) d26, concat(da27-da26 ,',' ,da27/cost ,',', (da27-da26)/cost ,',' ,da27/da1) d27, concat(da28-da27 ,',' ,da28/cost ,',', (da28-da27)/cost ,',' ,da28/da1) d28, concat(da29-da28 ,',' ,da29/cost ,',', (da29-da28)/cost ,',' ,da29/da1) d29, concat(da30-da29 ,',' ,da30/cost ,',', (da30-da29)/cost ,',' ,da30/da1) d30, concat(da31-da30 ,',' ,da31/cost ,',', (da31-da30)/cost ,',' ,da31/da1) d31, concat(da32-da31 ,',' ,da32/cost ,',', (da32-da31)/cost ,',' ,da32/da1) d32, concat(da33-da32 ,',' ,da33/cost ,',', (da33-da32)/cost ,',' ,da33/da1) d33, concat(da34-da33 ,',' ,da34/cost ,',', (da34-da33)/cost ,',' ,da34/da1) d34, concat(da35-da34 ,',' ,da35/cost ,',', (da35-da34)/cost ,',' ,da35/da1) d35, concat(da36-da35 ,',' ,da36/cost ,',', (da36-da35)/cost ,',' ,da36/da1) d36, concat(da37-da36 ,',' ,da37/cost ,',', (da37-da36)/cost ,',' ,da37/da1) d37, concat(da38-da37 ,',' ,da38/cost ,',', (da38-da37)/cost ,',' ,da38/da1) d38, concat(da39-da38 ,',' ,da39/cost ,',', (da39-da38)/cost ,',' ,da39/da1) d39, concat(da40-da39 ,',' ,da40/cost ,',', (da40-da39)/cost ,',' ,da40/da1) d40, concat(da41-da40 ,',' ,da41/cost ,',', (da41-da40)/cost ,',' ,da41/da1) d41, concat(da42-da41 ,',' ,da42/cost ,',', (da42-da41)/cost ,',' ,da42/da1) d42, concat(da43-da42 ,',' ,da43/cost ,',', (da43-da42)/cost ,',' ,da43/da1) d43, concat(da44-da43 ,',' ,da44/cost ,',', (da44-da43)/cost ,',' ,da44/da1) d44, concat(da45-da44 ,',' ,da45/cost ,',', (da45-da44)/cost ,',' ,da45/da1) d45, concat(da46-da45 ,',' ,da46/cost ,',', (da46-da45)/cost ,',' ,da46/da1) d46, concat(da47-da46 ,',' ,da47/cost ,',', (da47-da46)/cost ,',' ,da47/da1) d47, concat(da48-da47 ,',' ,da48/cost ,',', (da48-da47)/cost ,',' ,da48/da1) d48, concat(da49-da48 ,',' ,da49/cost ,',', (da49-da48)/cost ,',' ,da49/da1) d49, concat(da50-da49 ,',' ,da50/cost ,',', (da50-da49)/cost ,',' ,da50/da1) d50, concat(da51-da50 ,',' ,da51/cost ,',', (da51-da50)/cost ,',' ,da51/da1) d51, concat(da52-da51 ,',' ,da52/cost ,',', (da52-da51)/cost ,',' ,da52/da1) d52, concat(da53-da52 ,',' ,da53/cost ,',', (da53-da52)/cost ,',' ,da53/da1) d53, concat(da54-da53 ,',' ,da54/cost ,',', (da54-da53)/cost ,',' ,da54/da1) d54, concat(da55-da54 ,',' ,da55/cost ,',', (da55-da54)/cost ,',' ,da55/da1) d55, concat(da56-da55 ,',' ,da56/cost ,',', (da56-da55)/cost ,',' ,da56/da1) d56, concat(da57-da56 ,',' ,da57/cost ,',', (da57-da56)/cost ,',' ,da57/da1) d57, concat(da58-da57 ,',' ,da58/cost ,',', (da58-da57)/cost ,',' ,da58/da1) d58, concat(da59-da58 ,',' ,da59/cost ,',', (da59-da58)/cost ,',' ,da59/da1) d59, concat(da60-da59 ,',' ,da60/cost ,',', (da60-da59)/cost ,',' ,da60/da1) d60, concat(dm3-da60 ,',' ,dm3/cost ,',', (dm3-da60)/cost ,',' ,dm3/da1) m3, concat(dm4-dm3 ,',' ,dm4/cost ,',', (dm4-dm3)/cost ,',' ,dm4/da1) m4, concat(dm5-dm4 ,',' ,dm5/cost ,',', (dm5-dm4)/cost ,',' ,dm5/da1) m5 from ( select * from dw_channel where cost+reg_order_amount>0 {op1} {op2} {op3} {op4} ) a left join dw_channel_amount_daily b on a.channel=b.channel and a.dt=b.dt left join src_book_info c on a.dt=c.dt and a.book=c.book and a.type=c.type {op5} """ sumsql=f"""select concat(date_format(min(date),'%Y/%m/%d'),'~',date_format(max(date),'%Y/%m/%d')) date, sum(cost) cost, sum(reg_amount) reg_amount, sum(first_amount) first_amount, sum(profit) profit, sum(new_follow_user) new_follow_user, round(sum(reg_amount)/sum(cost),4) roi, round(sum(first_amount)/sum(cost),4) first_roi, round(sum(cost)/sum(new_follow_user),2) new_follow_per_cost, sum(order_user) order_user, sum(order_count) order_count, round(sum(cost)/sum(order_user),2) order_user_per_cost, round(avg(require_roi),4) require_roi, round(avg(require_mult),2) require_mult, round(sum(expect_profit),2) expect_profit, round(sum(annual_expect_profit),2) annual_expect_profit from ({sql}) a """ data,total,total_data=getLimitSumData(db.dm,sql,sumsql,page,page_size) def parse(str): li=str.split(',') li[0]=round(float(li[0]),2) li[1]=round(float(li[1]),4) li[2]=round(float(li[2]),4) li[3]=round(float(li[3]),2) return dict(zip(['order','roi','add','mult'],li)) for i in data: for x in i: if x in ['d1','d2','d3','d4','d5','d6','d7','d8','d9','d10','d11','d12','d13','d14','d15','d16','d17','d18', 'd19','d20','d21','d22','d23','d24','d25','d26','d27','d28','d29','d30','d31','d32','d33','d34','d35','d36','d37','d38', 'd39','d40','d41','d42','d43','d44','d45','d46','d47','d48','d49','d50','d51','d52','d53','d54','d55','d56','d57','d58','d59', 'd60','m3','m4','m5']: i[x]=parse(i[x]) if i[x] is not None else {} print(data) return data,total,total_data def get_channel_summary(channel,pitcher,page,page_size,order_by,order,state,location,start,end): db=MysqlUtils() op1=f" and a.channel='{channel}'" if channel else '' if pitcher: op2=f" and pitcher='{pitcher}'" if get_channels_from_user(pitcher).__len__()==0 else f" and a.channel in {get_channels_from_user(pitcher)}" else: op2='' op4=f" and location='{location}' " if location else '' op5=f" and state='{state}'" if state else '' op6=f" and a.dt>='{start}'" if start else '' op7=f" and a.dt<='{end}'" if end else '' sql = f"""SELECT channel, if(end>date_sub(now(),interval 10 day),'在投','停投') state, location,start,end,total_cost,total_amount, total_amount-total_cost profit, if(total_cost=0,0,round(total_amount/total_cost,4)) roi, follow_user, if(follow_user=0,0,round(total_cost/follow_user,2)) follow_per_cost, order_user, if(follow_user=0,0,round(order_user/follow_user,4)) order_tran_rate, if(order_user=0,0,round(total_cost/order_user,2)) order_tran_cost, pitcher,stage,td_amount,yd_amount,byd_amount FROM (select channel,pitcher,stage, type location, min(if(cost>0,dt,null)) start, max(if(cost>0,dt,null)) end, sum(cost) total_cost, sum(reg_order_amount) total_amount, sum(follow_user) follow_user, sum(reg_order_user) order_user, sum(ba1) td_amount,sum(ba2) yd_amount,sum(ba3) byd_amount from dw_channel a left join dw_channel_amount_daily_reverse b using (dt,channel) where 1=1 {op1} {op2} {op6} {op7} GROUP BY a.channel,type,pitcher,stage) x having 1=1 {op4} {op5} ORDER BY {order_by} {order} """ print(sql) sumsql=f"""select '总计' channel, sum(total_cost) total_cost, sum(total_amount) total_amount,sum(profit) profit, round(sum(total_amount)/sum(total_cost),4) roi, sum(follow_user) follow_user, sum(order_user) order_user, round(sum(total_cost)/sum(follow_user),2) follow_per_cost, round(sum(order_user)/sum(follow_user),4) order_tran_rate, round(sum(total_cost)/sum(order_user),2) order_tran_cost , sum(td_amount) td_amount,sum(yd_amount) yd_amount,sum(byd_amount) byd_amount from ({sql}) a """ return getLimitSumData(db.dm,sql,sumsql,page,page_size) def get_pitcher_trend(pitcher,start=None,end=None,page=None,page_size=None,order_by=None,order=None): db=MysqlUtils() op1=f" and pitcher='{pitcher}'" op2 = f" and dt>='{start}' " if start else '' op3 = f" and dt<='{end}' " if end else '' op4 = f" order by {order_by} {order}" if order_by and order else '' sql=f"""select dt,pitcher, cost, reg_amount, d1 first_amount, round(d1/cost,4) first_roi, round(reg_amount/cost,4) roi, d7 reg_amount7, round(d7/cost,4) roi7, d30 reg_amount30, round(d30/cost,4) roi30, reg_amount-cost profit, inva_cost expect_profit, annual_expect_profit, CONCAT(d1,",",0,',',round(d1/cost,4)) d1, CONCAT(d2-d1,",",round((d2-d1)/cost,4),',',round(d2/cost,4)) d2, CONCAT(d3-d2,",",round((d3-d2)/cost,4),',',round(d3/cost,4)) d3, CONCAT(d4-d3,",",round((d4-d3)/cost,4),',',round(d4/cost,4)) d4, CONCAT(d5-d4,",",round((d5-d4)/cost,4),',',round(d5/cost,4)) d5, CONCAT(d6-d5,",",round((d6-d5)/cost,4),',',round(d6/cost,4)) d6, CONCAT(d7-d6,",",round((d7-d6)/cost,4),',',round(d7/cost,4)) d7, CONCAT(d8-d7,",",round((d8-d7)/cost,4),',',round(d8/cost,4)) d8, CONCAT(d9-d8,",",round((d9-d8)/cost,4),',',round(d9/cost,4)) d9, CONCAT(d10-d9,",",round((d10-d9)/cost,4),',',round(d10/cost,4)) d10, CONCAT(d11-d10,",",round((d11-d10)/cost,4),',',round(d11/cost,4)) d11, CONCAT(d12-d11,",",round((d12-d11)/cost,4),',',round(d12/cost,4)) d12, CONCAT(d13-d12,",",round((d13-d12)/cost,4),',',round(d13/cost,4)) d13, CONCAT(d14-d13,",",round((d14-d13)/cost,4),',',round(d14/cost,4)) d14, CONCAT(d15-d14,",",round((d15-d14)/cost,4),',',round(d15/cost,4)) d15, CONCAT(d16-d15,",",round((d16-d15)/cost,4),',',round(d16/cost,4)) d16, CONCAT(d17-d16,",",round((d17-d16)/cost,4),',',round(d17/cost,4)) d17, CONCAT(d18-d17,",",round((d18-d17)/cost,4),',',round(d18/cost,4)) d18, CONCAT(d19-d18,",",round((d19-d18)/cost,4),',',round(d19/cost,4)) d19, CONCAT(d20-d19,",",round((d20-d19)/cost,4),',',round(d20/cost,4)) d20, CONCAT(d21-d20,",",round((d21-d20)/cost,4),',',round(d21/cost,4)) d21, CONCAT(d22-d21,",",round((d22-d21)/cost,4),',',round(d22/cost,4)) d22, CONCAT(d23-d22,",",round((d23-d22)/cost,4),',',round(d23/cost,4)) d23, CONCAT(d24-d23,",",round((d24-d23)/cost,4),',',round(d24/cost,4)) d24, CONCAT(d25-d24,",",round((d25-d24)/cost,4),',',round(d25/cost,4)) d25, CONCAT(d26-d25,",",round((d26-d25)/cost,4),',',round(d26/cost,4)) d26, CONCAT(d27-d26,",",round((d27-d26)/cost,4),',',round(d27/cost,4)) d27, CONCAT(d28-d27,",",round((d28-d27)/cost,4),',',round(d28/cost,4)) d28, CONCAT(d29-d28,",",round((d29-d28)/cost,4),',',round(d29/cost,4)) d29, CONCAT(d30-d29,",",round((d30-d29)/cost,4),',',round(d30/cost,4)) d30 from dw_pitcher_trend where 1=1 {op1} {op2} {op3} {op4} """ sumSql=f"""select concat(date_format(min(dt),'%Y/%m/%d'),'~',date_format(max(dt),'%Y/%m/%d')) dt,sum(cost) cost, sum(reg_amount) reg_amount, round(sum(first_amount)/sum(cost),4) first_roi, round(sum(reg_amount)/sum(cost),4) roi, sum(profit) profit, sum(first_amount) first_amount, round(sum(reg_amount7)/sum(cost),4) roi7, sum(reg_amount30) reg_amount30, round(sum(reg_amount30)/sum(cost),4) roi30, sum(expect_profit) expect_profit, sum(annual_expect_profit) annual_expect_profit from ({sql}) a """ data,total,total_data=getLimitSumData(db.dm,sql,sumSql,page,page_size) print(data) def parse(str): li=str.split(',') li[0]=round(float(li[0]),2) li[1]=round(float(li[1]),4) li[2]=round(float(li[2]),4) return dict(zip(['amount','add','roi'],li)) for i in data: for k,v in i.items(): if k in ['d1','d2','d3','d4','d5','d6','d7','d8','d9','d10', 'd11','d12','d13','d14','d15','d16','d17','d18','d19','d20','d21','d22','d23','d24','d25','d26','d27','d28','d29','d30']: i[k]= parse(v) if v else {} return data,total,total_data if __name__ == '__main__': get_pitcher_trend(pitcher="陈凯")