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_reg_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,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, 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/first_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, 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, 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_daily where 1=1 {op1} {op2} {op3} {op4} {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_order_amount) reg_order_amount, round(sum(first_order_amount)/sum(cost),4) day_roi, round(sum(reg_order_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_order_amount)/sum(reg_order_user),2) user_per_amount, round(sum(click_count)/sum(follow_user),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): ck = CkUtils() channel_op=f" and channel='{channel}' " if channel!='' else '' if pitcher != '赞象 admin': if get_channels_from_user(pitcher).__len__() == 0: pitcher_op = f" and pitcher='{pitcher}'" else: pitcher_op = f" and channel in {get_channels_from_user(pitcher)}" sql=f"""select stage,pitcher,formatDateTime(a.dt,'%Y-%m-%d') date,channel,book,toDecimal32(cost,2),toDecimal32(reg_amount,2),roi,new_follow_user,toDecimal32(new_follow_per_cost,2),order_user,order_count, toDecimal32(order_user_per_cost,2),day7_avg_act_rate,toDecimal32(day7_avg_act_per_cost,2),day30_avg_act_rate,toDecimal32(ay30_avg_act_cost,2), toDecimal32(act_per_cost,2), concat(toString(reg_order_user1),',',toString(if(reg_order_user1=0,0,cost/reg_order_user1)),',',toString(if(order_user=0,0,reg_order_user1/order_user))), concat(toString(reg_order_user2-reg_order_user1),',',toString(if(reg_order_user2=0,0,cost/reg_order_user2)),',',toString(if(order_user=0,0,(reg_order_user2-reg_order_user1)/order_user))), concat(toString(reg_order_user3-reg_order_user2),',',toString(if(reg_order_user3=0,0,cost/reg_order_user3)),',',toString(if(order_user=0,0,(reg_order_user3-reg_order_user2)/order_user))), concat(toString(reg_order_user4-reg_order_user3),',',toString(if(reg_order_user4=0,0,cost/reg_order_user4)),',',toString(if(order_user=0,0,(reg_order_user4-reg_order_user3)/order_user))), concat(toString(reg_order_user5-reg_order_user4),',',toString(if(reg_order_user5=0,0,cost/reg_order_user5)),',',toString(if(order_user=0,0,(reg_order_user5-reg_order_user4)/order_user))), concat(toString(reg_order_user6-reg_order_user5),',',toString(if(reg_order_user6=0,0,cost/reg_order_user6)),',',toString(if(order_user=0,0,(reg_order_user6-reg_order_user5)/order_user))), concat(toString(reg_order_user7-reg_order_user6),',',toString(if(reg_order_user7=0,0,cost/reg_order_user7)),',',toString(if(order_user=0,0,(reg_order_user7-reg_order_user6)/order_user))), concat(toString(reg_order_user8-reg_order_user7),',',toString(if(reg_order_user8=0,0,cost/reg_order_user8)),',',toString(if(order_user=0,0,(reg_order_user8-reg_order_user7)/order_user))), concat(toString(reg_order_user9-reg_order_user8),',',toString(if(reg_order_user9=0,0,cost/reg_order_user9)),',',toString(if(order_user=0,0,(reg_order_user9-reg_order_user8)/order_user))), concat(toString(reg_order_user10-reg_order_user9),',',toString(if(reg_order_user10=0,0,cost/reg_order_user10)),',',toString(if(order_user=0,0,(reg_order_user10-reg_order_user9)/order_user))), concat(toString(reg_order_user11-reg_order_user10),',',toString(if(reg_order_user11=0,0,cost/reg_order_user11)),',',toString(if(order_user=0,0,(reg_order_user11-reg_order_user10)/order_user))), concat(toString(reg_order_user12-reg_order_user11),',',toString(if(reg_order_user12=0,0,cost/reg_order_user12)),',',toString(if(order_user=0,0,(reg_order_user12-reg_order_user11)/order_user))), concat(toString(reg_order_user13-reg_order_user12),',',toString(if(reg_order_user13=0,0,cost/reg_order_user13)),',',toString(if(order_user=0,0,(reg_order_user13-reg_order_user12)/order_user))), concat(toString(reg_order_user14-reg_order_user13),',',toString(if(reg_order_user14=0,0,cost/reg_order_user14)),',',toString(if(order_user=0,0,(reg_order_user14-reg_order_user13)/order_user))), concat(toString(reg_order_user15-reg_order_user14),',',toString(if(reg_order_user15=0,0,cost/reg_order_user15)),',',toString(if(order_user=0,0,(reg_order_user15-reg_order_user14)/order_user))), concat(toString(reg_order_user16-reg_order_user15),',',toString(if(reg_order_user16=0,0,cost/reg_order_user16)),',',toString(if(order_user=0,0,(reg_order_user16-reg_order_user15)/order_user))), concat(toString(reg_order_user17-reg_order_user16),',',toString(if(reg_order_user17=0,0,cost/reg_order_user17)),',',toString(if(order_user=0,0,(reg_order_user17-reg_order_user16)/order_user))), concat(toString(reg_order_user18-reg_order_user17),',',toString(if(reg_order_user18=0,0,cost/reg_order_user18)),',',toString(if(order_user=0,0,(reg_order_user18-reg_order_user17)/order_user))), concat(toString(reg_order_user19-reg_order_user18),',',toString(if(reg_order_user19=0,0,cost/reg_order_user19)),',',toString(if(order_user=0,0,(reg_order_user19-reg_order_user18)/order_user))), concat(toString(reg_order_user20-reg_order_user19),',',toString(if(reg_order_user20=0,0,cost/reg_order_user20)),',',toString(if(order_user=0,0,(reg_order_user20-reg_order_user19)/order_user))), concat(toString(reg_order_user21-reg_order_user20),',',toString(if(reg_order_user21=0,0,cost/reg_order_user21)),',',toString(if(order_user=0,0,(reg_order_user21-reg_order_user20)/order_user))), concat(toString(reg_order_user22-reg_order_user21),',',toString(if(reg_order_user22=0,0,cost/reg_order_user22)),',',toString(if(order_user=0,0,(reg_order_user22-reg_order_user21)/order_user))), concat(toString(reg_order_user23-reg_order_user22),',',toString(if(reg_order_user23=0,0,cost/reg_order_user23)),',',toString(if(order_user=0,0,(reg_order_user23-reg_order_user22)/order_user))), concat(toString(reg_order_user24-reg_order_user23),',',toString(if(reg_order_user24=0,0,cost/reg_order_user24)),',',toString(if(order_user=0,0,(reg_order_user24-reg_order_user23)/order_user))), concat(toString(reg_order_user25-reg_order_user24),',',toString(if(reg_order_user25=0,0,cost/reg_order_user25)),',',toString(if(order_user=0,0,(reg_order_user25-reg_order_user24)/order_user))), concat(toString(reg_order_user26-reg_order_user25),',',toString(if(reg_order_user26=0,0,cost/reg_order_user26)),',',toString(if(order_user=0,0,(reg_order_user26-reg_order_user25)/order_user))), concat(toString(reg_order_user27-reg_order_user26),',',toString(if(reg_order_user27=0,0,cost/reg_order_user27)),',',toString(if(order_user=0,0,(reg_order_user27-reg_order_user26)/order_user))), concat(toString(reg_order_user28-reg_order_user27),',',toString(if(reg_order_user28=0,0,cost/reg_order_user28)),',',toString(if(order_user=0,0,(reg_order_user28-reg_order_user27)/order_user))), concat(toString(reg_order_user29-reg_order_user28),',',toString(if(reg_order_user29=0,0,cost/reg_order_user29)),',',toString(if(order_user=0,0,(reg_order_user29-reg_order_user28)/order_user))), concat(toString(reg_order_user30-reg_order_user29),',',toString(if(reg_order_user30=0,0,cost/reg_order_user30)),',',toString(if(order_user=0,0,(reg_order_user30-reg_order_user29)/order_user))) from ( select dt,book,cost,pitcher,channel,stage, reg_order_amount reg_amount, if(cost=0,0,reg_order_amount/cost) roi, follow_user new_follow_user, if(follow_user=0,0,cost/follow_user) new_follow_per_cost, reg_order_user order_user, reg_order_count order_count, if(reg_order_user=0,0,cost/reg_order_user) order_user_per_cost, if(follow_user=0,0,reg_order_user7/follow_user) day7_avg_act_rate , if(reg_order_user7=0,0,cost/reg_order_user7) day7_avg_act_per_cost, if(follow_user=0,0,reg_order_user30/follow_user) day30_avg_act_rate, if(reg_order_user30=0,0,cost/reg_order_user30) ay30_avg_act_cost, if(reg_order_user=0,0,cost/reg_order_user) act_per_cost from dw_daily_channel where 1=1 {channel_op} and dt>='{start}' and dt<='{end}' {pitcher_op}) a left outer join ( select toDate(formatDateTime(reg_time,'%Y-%m-%d')) dt,channel, count(distinct if(toDate(formatDateTime(reg_time,'%Y-%m-%d'))=date,user_id,NULL)) reg_order_user1, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),1)>=date,user_id,NULL)) reg_order_user2, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),2)>=date,user_id,NULL)) reg_order_user3, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),3)>=date,user_id,NULL)) reg_order_user4, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),4)>=date,user_id,NULL)) reg_order_user5, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),5)>=date,user_id,NULL)) reg_order_user6, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),6)>=date,user_id,NULL)) reg_order_user7, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),7)>=date,user_id,NULL)) reg_order_user8, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),8)>=date,user_id,NULL)) reg_order_user9, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),9)>=date,user_id,NULL)) reg_order_user10, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),10)>=date,user_id,NULL)) reg_order_user11, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),11)>=date,user_id,NULL)) reg_order_user12, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),12)>=date,user_id,NULL)) reg_order_user13, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),13)>=date,user_id,NULL)) reg_order_user14, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),14)>=date,user_id,NULL)) reg_order_user15, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),15)>=date,user_id,NULL)) reg_order_user16, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),16)>=date,user_id,NULL)) reg_order_user17, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),17)>=date,user_id,NULL)) reg_order_user18, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),18)>=date,user_id,NULL)) reg_order_user19, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),19)>=date,user_id,NULL)) reg_order_user20, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),20)>=date,user_id,NULL)) reg_order_user21, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),21)>=date,user_id,NULL)) reg_order_user22, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),22)>=date,user_id,NULL)) reg_order_user23, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),23)>=date,user_id,NULL)) reg_order_user24, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),24)>=date,user_id,NULL)) reg_order_user25, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),25)>=date,user_id,NULL)) reg_order_user26, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),26)>=date,user_id,NULL)) reg_order_user27, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),27)>=date,user_id,NULL)) reg_order_user28, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),28)>=date,user_id,NULL)) reg_order_user29, count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),29)>=date,user_id,NULL)) reg_order_user30 from order where 1=1 {channel_op} and dt>='{start}' group by formatDateTime(reg_time,'%Y-%m-%d'),channel ) b on a.dt=b.dt and a.channel=b.channel """ print(sql) total = ck.execute(f"select count(1) from ({sql}) a")[0][0] sql+=f" order by {order_by} {order} limit {(page-1)*page_size},{page_size} " data = ck.execute(sql) print(sql) print(data) key=['stage','pitcher','date','channel','book','cost','reg_amount','roi','new_follow_user','new_follow_per_cost','order_user','order_count','order_user_per_cost', 'day7_avg_act_rate','day7_avg_act_per_cost','day30_avg_act_rate','day30_avg_act_cost','act_per_cost','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'] x=get_dict_list(key,get_round(data)) print(x) li=[] for i in x: di = {} for j in i: if j 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']: if i[j]: k=i[j].split(",") k[0]=round(float(k[0]),0) k[1]=round(float(k[1]),2) k[2]=round(float(k[2]),4) else: k=[0,0,0] di[j] = dict(zip(['act_user', 'act_cost_per_cost', 'act_rate'], k)) else: di[j]=i[j] li.append(di) return li,total def get_channel_order_trend(channel,pitcher,start,end,page,page_size,order_by,order): ck = CkUtils() channel_op =f" and channel='{channel}' " if channel!='' else '' if pitcher != '赞象 admin': if get_channels_from_user(pitcher).__len__() == 0: pitcher_op = f" and pitcher='{pitcher}'" else: pitcher_op = f" and channel in {get_channels_from_user(pitcher)}" sql=f"""select stage,pitcher,channel,toString(dt) date,book, require_roi,require_mult,r1*require_mult-cost, toDecimal32(cost,2), toDecimal32(reg_order_amount,2), toDecimal32(reg_order_amount-cost,2), roi,new_follow_user, new_follow_per_cost,order_user,order_count, toDecimal32(order_user_per_cost,2), concat(toString(r1),',',toString(r1/cost),',', toString(a1/cost),',',toString(b1)), concat(toString(a2),',',toString(r2/cost),',', toString(a2/cost),',',toString(b2)), concat(toString(a3),',',toString(r3/cost),',', toString(a3/cost),',',toString(b3)), concat(toString(a4),',',toString(r4/cost),',', toString(a4/cost),',',toString(b4)), concat(toString(a5),',',toString(r5/cost),',', toString(a5/cost),',',toString(b5)), concat(toString(a6),',',toString(r6/cost),',', toString(a6/cost),',',toString(b6)), concat(toString(a7),',',toString(r7/cost),',', toString(a7/cost),',',toString(b7)), concat(toString(a8),',',toString(r8/cost),',', toString(a8/cost),',',toString(b8)), concat(toString(a9),',',toString(r9/cost),',', toString(a9/cost),',',toString(b9)), concat(toString(a10),',',toString(r10/cost),',', toString(a10/cost),',',toString(b10)), concat(toString(a11),',',toString(r11/cost),',', toString(a11/cost),',',toString(b11)), concat(toString(a12),',',toString(r12/cost),',', toString(a12/cost),',',toString(b12)), concat(toString(a13),',',toString(r13/cost),',', toString(a13/cost),',',toString(b13)), concat(toString(a14),',',toString(r14/cost),',', toString(a14/cost),',',toString(b14)), concat(toString(a15),',',toString(r15/cost),',', toString(a15/cost),',',toString(b15)), concat(toString(a16),',',toString(r16/cost),',', toString(a16/cost),',',toString(b16)), concat(toString(a17),',',toString(r17/cost),',', toString(a17/cost),',',toString(b17)), concat(toString(a18),',',toString(r18/cost),',', toString(a18/cost),',',toString(b18)), concat(toString(a19),',',toString(r19/cost),',', toString(a19/cost),',',toString(b19)), concat(toString(a20),',',toString(r20/cost),',', toString(a20/cost),',',toString(b20)), concat(toString(a21),',',toString(r21/cost),',', toString(a21/cost),',',toString(b21)), concat(toString(a22),',',toString(r22/cost),',', toString(a22/cost),',',toString(b22)), concat(toString(a23),',',toString(r23/cost),',', toString(a23/cost),',',toString(b23)), concat(toString(a24),',',toString(r24/cost),',', toString(a24/cost),',',toString(b24)), concat(toString(a25),',',toString(r25/cost),',', toString(a25/cost),',',toString(b25)), concat(toString(a26),',',toString(r26/cost),',', toString(a26/cost),',',toString(b26)), concat(toString(a27),',',toString(r27/cost),',', toString(a27/cost),',',toString(b27)), concat(toString(a28),',',toString(r28/cost),',', toString(a28/cost),',',toString(b28)), concat(toString(a29),',',toString(r29/cost),',', toString(a29/cost),',',toString(b29)), concat(toString(a30),',',toString(r30/cost),',', toString(a30/cost),',',toString(b30)), concat(toString(a31),',',toString(r31/cost),',', toString(a31/cost),',',toString(b31)), concat(toString(a32),',',toString(r32/cost),',', toString(a32/cost),',',toString(b32)), concat(toString(a33),',',toString(r33/cost),',', toString(a33/cost),',',toString(b33)), concat(toString(a34),',',toString(r34/cost),',', toString(a34/cost),',',toString(b34)), concat(toString(a35),',',toString(r35/cost),',', toString(a35/cost),',',toString(b35)), concat(toString(a36),',',toString(r36/cost),',', toString(a36/cost),',',toString(b36)), concat(toString(a37),',',toString(r37/cost),',', toString(a37/cost),',',toString(b37)), concat(toString(a38),',',toString(r38/cost),',', toString(a38/cost),',',toString(b38)), concat(toString(a39),',',toString(r39/cost),',', toString(a39/cost),',',toString(b39)), concat(toString(a40),',',toString(r40/cost),',', toString(a40/cost),',',toString(b40)), concat(toString(a41),',',toString(r41/cost),',', toString(a41/cost),',',toString(b41)), concat(toString(a42),',',toString(r42/cost),',', toString(a42/cost),',',toString(b42)), concat(toString(a43),',',toString(r43/cost),',', toString(a43/cost),',',toString(b43)), concat(toString(a44),',',toString(r44/cost),',', toString(a44/cost),',',toString(b44)), concat(toString(a45),',',toString(r45/cost),',', toString(a45/cost),',',toString(b45)), concat(toString(a46),',',toString(r46/cost),',', toString(a46/cost),',',toString(b46)), concat(toString(a47),',',toString(r47/cost),',', toString(a47/cost),',',toString(b47)), concat(toString(a48),',',toString(r48/cost),',', toString(a48/cost),',',toString(b48)), concat(toString(a49),',',toString(r49/cost),',', toString(a49/cost),',',toString(b49)), concat(toString(a50),',',toString(r50/cost),',', toString(a50/cost),',',toString(b50)), concat(toString(a51),',',toString(r51/cost),',', toString(a51/cost),',',toString(b51)), concat(toString(a52),',',toString(r52/cost),',', toString(a52/cost),',',toString(b52)), concat(toString(a53),',',toString(r53/cost),',', toString(a53/cost),',',toString(b53)), concat(toString(a54),',',toString(r54/cost),',', toString(a54/cost),',',toString(b54)), concat(toString(a55),',',toString(r55/cost),',', toString(a55/cost),',',toString(b55)), concat(toString(a56),',',toString(r56/cost),',', toString(a56/cost),',',toString(b56)), concat(toString(a57),',',toString(r57/cost),',', toString(a57/cost),',',toString(b57)), concat(toString(a58),',',toString(r58/cost),',', toString(a58/cost),',',toString(b58)), concat(toString(a59),',',toString(r59/cost),',', toString(a59/cost),',',toString(b59)), concat(toString(a60),',',toString(r60/cost),',', toString(a60/cost),',',toString(b60)), concat(toString(a90),',',toString(r90/cost),',', toString(a90/cost),',',toString(b90)), concat(toString(a120),',',toString(r120/cost),',', toString(a120/cost),',',toString(b120)), concat(toString(a150),',',toString(r150/cost),',', toString(a150/cost),',',toString(b150)) from ( select pitcher,channel,dt,book,cost,reg_order_amount,stage, ifnull(require_roi,0) require_roi,ifnull(require_mult,0) require_mult, if(cost=0,0,reg_order_amount/cost) roi, follow_user new_follow_user, if(follow_user=0,0,cost/follow_user) new_follow_per_cost, reg_order_user order_user, reg_order_count order_count, if(reg_order_user=0,0,cost/reg_order_user) order_user_per_cost from dw_daily_channel where dt>='{start}' and dt<='{end}' {channel_op} {pitcher_op} and cost>0) 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, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),30)>=date,amount,0)) as r31, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),31)>=date,amount,0)) as r32, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),32)>=date,amount,0)) as r33, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),33)>=date,amount,0)) as r34, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),34)>=date,amount,0)) as r35, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),35)>=date,amount,0)) as r36, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),36)>=date,amount,0)) as r37, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),37)>=date,amount,0)) as r38, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),38)>=date,amount,0)) as r39, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),39)>=date,amount,0)) as r40, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),40)>=date,amount,0)) as r41, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),41)>=date,amount,0)) as r42, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),42)>=date,amount,0)) as r43, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),43)>=date,amount,0)) as r44, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),44)>=date,amount,0)) as r45, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),45)>=date,amount,0)) as r46, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),46)>=date,amount,0)) as r47, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),47)>=date,amount,0)) as r48, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),48)>=date,amount,0)) as r49, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),49)>=date,amount,0)) as r50, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),50)>=date,amount,0)) as r51, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),51)>=date,amount,0)) as r52, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),52)>=date,amount,0)) as r53, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),53)>=date,amount,0)) as r54, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),54)>=date,amount,0)) as r55, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),55)>=date,amount,0)) as r56, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),56)>=date,amount,0)) as r57, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),57)>=date,amount,0)) as r58, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),58)>=date,amount,0)) as r59, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),59)>=date,amount,0)) as r60, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),89)>=date,amount,0)) as r90, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),119)>=date,amount,0)) as r120, sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),149)>=date,amount,0)) as r150, 0 a1,1 b1, r2-r1 a2,if(r1=0,0,r2/r1) b2, r3-r2 a3,if(r1=0,0,r3/r1) b3, r4-r3 a4,if(r1=0,0,r4/r1) b4, r5-r4 a5,if(r1=0,0,r5/r1) b5, r6-r5 a6,if(r1=0,0,r6/r1) b6, r7-r6 a7,if(r1=0,0,r7/r1) b7, r8-r7 a8,if(r1=0,0,r8/r1) b8, r9-r8 a9,if(r1=0,0,r9/r1) b9, r10-r9 a10,if(r1=0,0,r10/r1) b10, r11-r10 a11,if(r1=0,0,r11/r1) b11, r12-r11 a12,if(r1=0,0,r12/r1) b12, r13-r12 a13,if(r1=0,0,r13/r1) b13, r14-r13 a14,if(r1=0,0,r14/r1) b14, r15-r14 a15,if(r1=0,0,r15/r1) b15, r16-r15 a16,if(r1=0,0,r16/r1) b16, r17-r16 a17,if(r1=0,0,r17/r1) b17, r18-r17 a18,if(r1=0,0,r18/r1) b18, r19-r18 a19,if(r1=0,0,r19/r1) b19, r20-r19 a20,if(r1=0,0,r20/r1) b20, r21-r20 a21,if(r1=0,0,r21/r1) b21, r22-r21 a22,if(r1=0,0,r22/r1) b22, r23-r22 a23,if(r1=0,0,r23/r1) b23, r24-r23 a24,if(r1=0,0,r24/r1) b24, r25-r24 a25,if(r1=0,0,r25/r1) b25, r26-r25 a26,if(r1=0,0,r26/r1) b26, r27-r26 a27,if(r1=0,0,r27/r1) b27, r28-r27 a28,if(r1=0,0,r28/r1) b28, r29-r28 a29,if(r1=0,0,r29/r1) b29, r30-r29 a30,if(r1=0,0,r30/r1) b30, r31-r30 a31,if(r1=0,0,r31/r1) b31, r32-r31 a32,if(r1=0,0,r32/r1) b32, r33-r32 a33,if(r1=0,0,r33/r1) b33, r34-r33 a34,if(r1=0,0,r34/r1) b34, r35-r34 a35,if(r1=0,0,r35/r1) b35, r36-r35 a36,if(r1=0,0,r36/r1) b36, r37-r36 a37,if(r1=0,0,r37/r1) b37, r38-r37 a38,if(r1=0,0,r38/r1) b38, r39-r38 a39,if(r1=0,0,r39/r1) b39, r40-r39 a40,if(r1=0,0,r40/r1) b40, r41-r40 a41,if(r1=0,0,r41/r1) b41, r42-r41 a42,if(r1=0,0,r42/r1) b42, r43-r42 a43,if(r1=0,0,r43/r1) b43, r44-r43 a44,if(r1=0,0,r44/r1) b44, r45-r44 a45,if(r1=0,0,r45/r1) b45, r46-r45 a46,if(r1=0,0,r46/r1) b46, r47-r46 a47,if(r1=0,0,r47/r1) b47, r48-r47 a48,if(r1=0,0,r48/r1) b48, r49-r48 a49,if(r1=0,0,r49/r1) b49, r50-r49 a50,if(r1=0,0,r50/r1) b50, r51-r50 a51,if(r1=0,0,r51/r1) b51, r52-r51 a52,if(r1=0,0,r52/r1) b52, r53-r52 a53,if(r1=0,0,r53/r1) b53, r54-r53 a54,if(r1=0,0,r54/r1) b54, r55-r54 a55,if(r1=0,0,r55/r1) b55, r56-r55 a56,if(r1=0,0,r56/r1) b56, r57-r56 a57,if(r1=0,0,r57/r1) b57, r58-r57 a58,if(r1=0,0,r58/r1) b58, r59-r58 a59,if(r1=0,0,r59/r1) b59, r60-r59 a60,if(r1=0,0,r60/r1) b60, r90-r60 a90,if(r1=0,0,r90/r1) b90, r120-r90 a120,if(r1=0,0,r120/r1) b120, r150-r120 a150,if(r1=0,0,r150/r1) b150 from order where dt>='{start}' {channel_op} group by formatDateTime(reg_time,'%Y-%m-%d'),channel ) b on a.dt=b.dt and a.channel=b.channel """ print(sql) if start==end: total=1 else: total = ck.execute(f"select count(1) from ({sql}) a")[0][0] sql += f" order by {order_by} {order} limit {(page-1)*page_size},{page_size} " data=get_round(ck.execute(sql)) # 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) li[3]=round(float(li[3]),2) return dict(zip(['order','roi','add','mult'],li)) for i in data: for x,y in enumerate(i): if x>=17: i[x]=parse(y) print(data) key=['stage','pitcher','channel','date','book','require_roi','require_mult','expect_profit','cost','reg_amount','profit','roi','new_follow_user','new_follow_per_cost','order_user','order_count', 'order_user_per_cost','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'] data=get_dict_list(key,data) cost=reg_amount=roi=new_follow_user=new_follow_per_cost=order_user=order_count=order_user_per_cost=profit=expect_profit=0 for i in data: cost+= i['cost'] reg_amount+=i['reg_amount'] profit+=i['profit'] roi+=i['roi'] new_follow_user+=i['new_follow_user'] new_follow_per_cost+=i['new_follow_per_cost'] order_user+=i['order_user'] order_count+=i['order_count'] order_user_per_cost+=i['order_user_per_cost'] expect_profit+=i['expect_profit'] count= len(data) if count==0: return [],0 total_data={'cost':round(cost,2),'reg_amount':round(reg_amount,2),'profit':round(profit,2),'expect_profit':round(expect_profit,2),'roi':round(reg_amount/cost,4), 'new_follow_user':new_follow_user, 'new_follow_per_cost':round(new_follow_per_cost/count,2),'order_user':order_user,'order_count':order_count, 'order_user_per_cost':round(order_user_per_cost/count,2),'channel':'-','date':'总计','book':'-' } 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 channel='{channel}'" if channel else '' if pitcher: op2=f" and pitcher='{pitcher}'" if get_channels_from_user(pitcher).__len__()==0 else f" and channel in {get_channels_from_user(pitcher)}" else: op2='' op3=f" and channel='{channel}'" if channel else '' op4=f" and location='{location}' " if location else '' op5=f" and state='{state}'" if state else '' op6=f" and dt>='{start}'" if start else '' op7=f" and 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 FROM (select channel,pitcher,stage, case when type ='vx' then 'MP' when type ='qq' then 'GDT' end 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 from dw_channel_daily where 1=1 {op1} {op2} {op3} {op4} {op6} {op7} GROUP BY channel,type,pitcher,stage) a where 1=1 {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 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, round(d7/cost,4) roi7, d30 reg_amount30, round(d30/cost,4) roi30, reg_amount-cost profit, inva_cost, 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 '总计' 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, round(sum(d7)/sum(cost),4) roi7, sum(reg_amount30) reg_amount30, round(sum(d30)/sum(cost),4) roi30 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 i["cost"]!=0 else {} return data,total,total_data if __name__ == '__main__': get_pitcher_trend(pitcher="陈凯")