from model.DateUtils import DateUtils
from model.DataBaseUtils import *
from model.log import logger
from model.CommonUtils import *
du = DateUtils()
ck = CkUtils()
db = MysqlUtils()
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_pitcher_panel_channel(pitcher,channel,start,end,page,page_size,order_by,order):
sql=f"""select channel,stage,platform,book,
formatDateTime(dt,'%Y-%m-%d') as date,
cost,
first_order_amount,
toDecimal32(if(cost=0,0,first_order_amount/cost),4) first_roi,
first_order_user,first_order_count,
toDecimal32(if(first_order_user=0,0,cost/first_order_user),2) first_per_cost,
view_count,click_count,follow_user,
toDecimal32(if(click_count=0,0,follow_user/click_count),4) follow_rate,
toDecimal32(if(follow_user=0,0,cost/follow_user),2) follow_per_cost,
total_cost,
toDecimal32(if(total_cost=0,0,total_amount/total_cost),2) back_rate
from dw_daily_channel where dt>='{start}' and dt<='{end}' """
if pitcher!='all':
sql += f" and pitcher='{pitcher}' "
if channel!='':
sql += f" and channel='{channel}' "
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} "
print(sql)
data=ck.execute(sql)
key=['channel','stage','platform','book','date','cost','first_order_amount','first_roi','first_order_user',
'first_order_count','first_per_cost','view_count','click_count','follow_user','follow_rate','follow_per_cost',
'total_cost','back_rate']
return get_dict_list(key,get_round(data)),total
def get_pitcher_panel_daily(pitcher, start, end, page, page_size, order_by, order):
sql=f"""
select formatDateTime(dt,'%Y-%m-%d') date ,pitcher,
round(sum(cost),2) cost,
round(sum(first_order_amount),2) first_order_amount,
round(if(cost=0,0,first_order_amount/cost),4) first_roi ,
round(sum(order_amount),2) order_amount,
round(if(cost=0,0,sum(reg_order_amount)/cost),4) today_roi,
round(sum(total_cost),2) total_cost,
round(sum(total_amount),2) total_amount,
round(total_amount-total_cost,2) total_profit,
round(if(total_cost=0,0,total_amount/total_cost),4) total_roi
from dw_daily_channel where dt>='{start}' and dt<='{end}' """
if pitcher != '':
sql += f" and pitcher='{pitcher}' "
sql+=f" group by date,pitcher"
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} "
print(sql)
data = ck.execute(sql)
key=['date','pitcher','cost','first_order_amount','first_roi','order_amount','today_roi','total_cost','total_amount','total_profit','total_roi']
return get_dict_list(key,data),total
def get_pitcher_panel_overview(pitcher):
sql=f"""select pitcher,
cost cost,
amount amount,
roi 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_page_total where dt='{du.get_n_days(-1)}'"""
if pitcher != 'all':
sql += f" and pitcher='{pitcher}' "
print(sql)
data=ck.execute(sql)
print(data)
key=['pitcher','cost','amount','roi','channel_count','on_channel_count','off_channel_count','this_month_cost','this_month_amount','this_month_roi',
'last_month_cost','last_month_amount','last_month_roi','last_month_far_amount','follow_user','last_month_far_roi']
return get_dict_list(key,get_round(data))
def get_channel_overview(channel,pitcher,start,end,page,page_size,order_by,order):
sql="""select channel,toString(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,
round(cost,2) cost,
first_order_count,first_order_user,
round(first_order_amount,2) first_order_amount,order_count,order_user,
round(order_amount,2) order_amount,
round(order_amount-first_order_amount,2) 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,
round(reg_order_amount,2) reg_user_amount,
round(total_cost,2) total_cost,
round(total_amount,2) total_amount,
round(if(cost=0,0,first_order_amount/cost),4) day_roi,
round(if(cost=0,0,reg_order_amount/cost),4) all_roi,
0 avg_new_order_rate,
0 old_user_once_order_rate from dw_daily_channel where 1=1 """
if channel!='':
sql+=f" and channel='{channel}'"
if start!='':
sql+=f" and start>='{start}'"
if end!='':
sql+=f" and end<='{end}'"
if pitcher!='':
sql+=f" and pitcher='{pitcher}'"
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} "
print(sql)
key=['channel','date','view_count','click_count','click_rate','follow_user','follow_rate','follow_per_cost','order_rate','order_per_cost','cost',
'first_order_count','first_order_user','first_order_amount','order_count','order_user','order_amount','old_order_amount','first_amount_per_user','amount_per_follow','first_cost_per_user',
'new_user_order_rate','reg_user_amount','total_cost','total_amount','day_roi','all_roi','avg_new_order_rate','old_user_once_order_rate']
return get_dict_list(key,get_round(ck.execute(sql))),total
def get_channel_again_order_trend(channel,date,pitcher):
# if get_channel_belong_pitcher(channel)!=pitcher:
# return []
sql=f"""select toString(dt) date,
channel,book,
round(cost,2) cost,
round(reg_order_amount,2) reg_amount,
round(if(cost=0,0,reg_order_amount/cost),4) roi,
follow_user new_follow,
round(if(follow_user=0,0,cost/follow_user),2) new_follow_per_cost,
reg_order_count reg_count,reg_order_user reg_user,
round(if(reg_order_user=0,0,cost/reg_order_user),2) cost_per_user,
round(if(reg_order_user=0,0,reg_order_amount/reg_order_user),2) avg_order_amount,
round(if(reg_order_user=0,0,reg_order_user_again/reg_order_user),4) avg_again_order_rate,
order_count
from dw_daily_channel where channel='{channel}' and dt='{date}'
"""
# print(sql)
data=ck.execute(sql)
# print(data)
key1=['date','channel','book','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)
sql2=f"""select
dateDiff(day,toDate('{date}'),date)+1 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,date
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,date
) a group by date"""
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"]/reg_user,2) if reg_user!=0 else 0
d['d1'] = d1
d2={}
d2["origin"] = d1["now"]
d2["new"] = df[1][i]
d2["move"] = df[1][i+1]
d2["now"] = d2["origin"] +d2['new']- d2["move"]
d2["follow_order_rate"] = round(d2["now"] / reg_user, 2) if reg_user!=0 else 0
d['d2'] = d2
d3={}
d3["origin"] = d2["now"]
d3["new"] = df[2][i]
d3["move"] =df[2][i+1]
d3["now"] = d3["origin"] +d3['new']- d3["move"]
d3["follow_order_rate"] = round(d3["now"] / reg_user, 2) if reg_user!=0 else 0
d['d3'] = d3
d4={}
d4["origin"] = d3["now"]
d4["new"] = df[3][i]
d4["move"] = df[3][i+1]
d4["now"] = d4["origin"] +d4['new']- d4["move"]
d4["follow_order_rate"] = round(d4["now"] / reg_user, 2) if reg_user!=0 else 0
d['d4'] = d4
d5={}
d5["origin"] = d4["now"]
d5["new"] = df[4][i]
d5["move"] = df[4][i+1]
d5["now"] = d5["origin"] +d5['new']- d5["move"]
d5["follow_order_rate"] = round(d4["now"] / reg_user, 2) if reg_user!=0 else 0
d['d5'] = d5
d6 = {}
d6["origin"] = d5["now"]
d6["new"] = df[5][i]
d6["move"] = df[5][i+1]
d6["now"] = d6["origin"] +d6['new']- d6["move"]
d6["follow_order_rate"] = round(d6["now"] / reg_user, 2) if reg_user!=0 else 0
d['d6'] = d6
d7 = {}
d7["origin"] = d6["now"]
d7["new"] = df[6][i]
d7["move"] = df[6][i+1]
d7["now"] = d7["origin"] +d7['new']- d7["move"]
d7["follow_order_rate"] = round(d7["now"] / reg_user, 2) if reg_user!=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):
if get_channel_belong_pitcher(channel)!=pitcher:
return [],0
sql=f"""select formatDateTime(a.dt,'%Y-%m-%d') date, '{channel}' 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,ay30_avg_act_cost,
act_per_cost,
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,
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 channel='{channel}' and dt>='{start}' and dt<='{end}') a
left outer join (
select toDate(formatDateTime(reg_time,'%Y-%m-%d')) dt,
count(distinct if(subtractDays(date, 1)>=reg_time,NULL,user_id)) reg_order_user1,
count(distinct if(subtractDays(date, 2)>=reg_time,NULL,user_id)) reg_order_user2,
count(distinct if(subtractDays(date, 3)>=reg_time,NULL,user_id)) reg_order_user3,
count(distinct if(subtractDays(date, 4)>=reg_time,NULL,user_id)) reg_order_user4,
count(distinct if(subtractDays(date, 5)>=reg_time,NULL,user_id)) reg_order_user5,
count(distinct if(subtractDays(date, 6)>=reg_time,NULL,user_id)) reg_order_user6,
count(distinct if(subtractDays(date, 7)>=reg_time,NULL,user_id)) reg_order_user7,
count(distinct if(subtractDays(date, 8)>=reg_time,NULL,user_id)) reg_order_user8,
count(distinct if(subtractDays(date, 9)>=reg_time,NULL,user_id)) reg_order_user9,
count(distinct if(subtractDays(date, 10)>=reg_time,NULL,user_id)) reg_order_user10,
count(distinct if(subtractDays(date, 11)>=reg_time,NULL,user_id)) reg_order_user11,
count(distinct if(subtractDays(date, 12)>=reg_time,NULL,user_id)) reg_order_user12,
count(distinct if(subtractDays(date, 13)>=reg_time,NULL,user_id)) reg_order_user13,
count(distinct if(subtractDays(date, 14)>=reg_time,NULL,user_id)) reg_order_user14,
count(distinct if(subtractDays(date, 15)>=reg_time,NULL,user_id)) reg_order_user15,
count(distinct if(subtractDays(date, 16)>=reg_time,NULL,user_id)) reg_order_user16,
count(distinct if(subtractDays(date, 17)>=reg_time,NULL,user_id)) reg_order_user17,
count(distinct if(subtractDays(date, 18)>=reg_time,NULL,user_id)) reg_order_user18,
count(distinct if(subtractDays(date, 19)>=reg_time,NULL,user_id)) reg_order_user19,
count(distinct if(subtractDays(date, 20)>=reg_time,NULL,user_id)) reg_order_user20,
count(distinct if(subtractDays(date, 21)>=reg_time,NULL,user_id)) reg_order_user21,
count(distinct if(subtractDays(date, 22)>=reg_time,NULL,user_id)) reg_order_user22,
count(distinct if(subtractDays(date, 23)>=reg_time,NULL,user_id)) reg_order_user23,
count(distinct if(subtractDays(date, 24)>=reg_time,NULL,user_id)) reg_order_user24,
count(distinct if(subtractDays(date, 25)>=reg_time,NULL,user_id)) reg_order_user25,
count(distinct if(subtractDays(date, 26)>=reg_time,NULL,user_id)) reg_order_user26,
count(distinct if(subtractDays(date, 27)>=reg_time,NULL,user_id)) reg_order_user27,
count(distinct if(subtractDays(date, 28)>=reg_time,NULL,user_id)) reg_order_user28,
count(distinct if(subtractDays(date, 29)>=reg_time,NULL,user_id)) reg_order_user29,
count(distinct if(subtractDays(date, 30)>=reg_time,NULL,user_id)) reg_order_user30
from order where channel='{channel}' and dt>='{start}' group by formatDateTime(reg_time,'%Y-%m-%d')) b on a.dt=b.dt
"""
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=['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):
if get_channel_belong_pitcher(channel)!=pitcher:
return [],0
sql=f"""select '{channel}' channel,toString(dt) date,book,cost,reg_order_amount,roi,new_follow_user,new_follow_per_cost,order_user,order_count,
order_user_per_cost,
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 dt,book,cost,reg_order_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
from dw_daily_channel where dt>='{start}' and dt<='{end}' and channel='{channel}' and cost>0) a
left outer join (
select toDate(formatDateTime(reg_time,'%Y-%m-%d')) dt,
sum(if(subtractDays(date, 1)>=reg_time,0,amount)) as r1,
sum(if(subtractDays(date, 2)>=reg_time,0,amount)) as r2,
sum(if(subtractDays(date, 3)>=reg_time,0,amount)) as r3,
sum(if(subtractDays(date, 4)>=reg_time,0,amount)) as r4,
sum(if(subtractDays(date, 5)>=reg_time,0,amount)) as r5,
sum(if(subtractDays(date, 6)>=reg_time,0,amount)) as r6,
sum(if(subtractDays(date, 7)>=reg_time,0,amount)) as r7,
sum(if(subtractDays(date, 8)>=reg_time,0,amount)) as r8,
sum(if(subtractDays(date, 9)>=reg_time,0,amount)) as r9,
sum(if(subtractDays(date, 10)>=reg_time,0,amount)) as r10,
sum(if(subtractDays(date, 11)>=reg_time,0,amount)) as r11,
sum(if(subtractDays(date, 12)>=reg_time,0,amount)) as r12,
sum(if(subtractDays(date, 13)>=reg_time,0,amount)) as r13,
sum(if(subtractDays(date, 14)>=reg_time,0,amount)) as r14,
sum(if(subtractDays(date, 15)>=reg_time,0,amount)) as r15,
sum(if(subtractDays(date, 16)>=reg_time,0,amount)) as r16,
sum(if(subtractDays(date, 17)>=reg_time,0,amount)) as r17,
sum(if(subtractDays(date, 18)>=reg_time,0,amount)) as r18,
sum(if(subtractDays(date, 19)>=reg_time,0,amount)) as r19,
sum(if(subtractDays(date, 20)>=reg_time,0,amount)) as r20,
sum(if(subtractDays(date, 21)>=reg_time,0,amount)) as r21,
sum(if(subtractDays(date, 22)>=reg_time,0,amount)) as r22,
sum(if(subtractDays(date, 23)>=reg_time,0,amount)) as r23,
sum(if(subtractDays(date, 24)>=reg_time,0,amount)) as r24,
sum(if(subtractDays(date, 25)>=reg_time,0,amount)) as r25,
sum(if(subtractDays(date, 26)>=reg_time,0,amount)) as r26,
sum(if(subtractDays(date, 27)>=reg_time,0,amount)) as r27,
sum(if(subtractDays(date, 28)>=reg_time,0,amount)) as r28,
sum(if(subtractDays(date, 29)>=reg_time,0,amount)) as r29,
sum(if(subtractDays(date, 30)>=reg_time,0,amount)) as r30,
sum(if(subtractDays(date, 31)>=reg_time,0,amount)) as r31,
sum(if(subtractDays(date, 32)>=reg_time,0,amount)) as r32,
sum(if(subtractDays(date, 33)>=reg_time,0,amount)) as r33,
sum(if(subtractDays(date, 34)>=reg_time,0,amount)) as r34,
sum(if(subtractDays(date, 35)>=reg_time,0,amount)) as r35,
sum(if(subtractDays(date, 36)>=reg_time,0,amount)) as r36,
sum(if(subtractDays(date, 37)>=reg_time,0,amount)) as r37,
sum(if(subtractDays(date, 38)>=reg_time,0,amount)) as r38,
sum(if(subtractDays(date, 39)>=reg_time,0,amount)) as r39,
sum(if(subtractDays(date, 40)>=reg_time,0,amount)) as r40,
sum(if(subtractDays(date, 41)>=reg_time,0,amount)) as r41,
sum(if(subtractDays(date, 42)>=reg_time,0,amount)) as r42,
sum(if(subtractDays(date, 43)>=reg_time,0,amount)) as r43,
sum(if(subtractDays(date, 44)>=reg_time,0,amount)) as r44,
sum(if(subtractDays(date, 45)>=reg_time,0,amount)) as r45,
sum(if(subtractDays(date, 46)>=reg_time,0,amount)) as r46,
sum(if(subtractDays(date, 47)>=reg_time,0,amount)) as r47,
sum(if(subtractDays(date, 48)>=reg_time,0,amount)) as r48,
sum(if(subtractDays(date, 49)>=reg_time,0,amount)) as r49,
sum(if(subtractDays(date, 50)>=reg_time,0,amount)) as r50,
sum(if(subtractDays(date, 51)>=reg_time,0,amount)) as r51,
sum(if(subtractDays(date, 52)>=reg_time,0,amount)) as r52,
sum(if(subtractDays(date, 53)>=reg_time,0,amount)) as r53,
sum(if(subtractDays(date, 54)>=reg_time,0,amount)) as r54,
sum(if(subtractDays(date, 55)>=reg_time,0,amount)) as r55,
sum(if(subtractDays(date, 56)>=reg_time,0,amount)) as r56,
sum(if(subtractDays(date, 57)>=reg_time,0,amount)) as r57,
sum(if(subtractDays(date, 58)>=reg_time,0,amount)) as r58,
sum(if(subtractDays(date, 59)>=reg_time,0,amount)) as r59,
sum(if(subtractDays(date, 60)>=reg_time,0,amount)) as r60,
sum(if(subtractDays(date, 90)>=reg_time,0,amount)) as r90,
sum(if(subtractDays(date, 120)>=reg_time,0,amount)) as r120,
sum(if(subtractDays(date, 150)>=reg_time,0,amount)) 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 channel='{channel}' and dt>='{start}' group by formatDateTime(reg_time,'%Y-%m-%d')
) b on a.dt=b.dt
"""
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>=11:
i[x]=parse(y)
print(data)
key=['date','channel','book','cost','reg_amount','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']
return get_dict_list(key,data),total
def get_channel_summary(channel,pitcher,page,page_size,order_by,order,state):
pitcher_op=f" and pitcher='{pitcher}'" if pitcher!='' else ''
channel_op=f" and channel='{channel}'" if channel!='' else ''
sql = f"""select a.channel channel,state,'朋友圈' location,toString(start) start,toString(end) end,total_cost,total_amount,
profit,roi,follow_user,follow_per_cost,order_user,
if(follow_user=0,0,order_user/follow_user) order_tran_rate,if(order_user=0,0,total_cost/order_user) order_tran_cost
from
(
select channel,if(max(dt)>subtractDays(today(),10),'在投','停投') state,
min(dt) start,max(dt) end
from dw_daily_channel_cost where cost>0 {pitcher_op} {channel_op} group by channel) a
left outer join(
select channel,sum(follow_user) follow_user,
if(follow_user=0,0,sum(cost)/follow_user) follow_per_cost
from dw_daily_channel where 1=1 {pitcher_op} {channel_op} group by channel) b on a.channel=b.channel
left outer join(
select sum(reg_order_user) order_user,channel
from dw_daily_channel where dt=subtractDays(today(),1) {pitcher_op} {channel_op} group by channel) c on a.channel=c.channel
left outer join (
select total_cost,
total_amount,
channel,
total_amount-total_cost profit,
if(total_cost=0,0,total_amount/total_cost) roi
from dw_channel_daily_total where dt=subtractDays(today(),1) {channel_op}) d on a.channel=d.channel
"""
print(sql)
if channel!='':
total=1
else:
sql+=f" having state='{state}' " if state!='' 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} '
print(sql)
key=['channel','state','location','start','end','total_cost','total_amount','profit','roi',
'follow_user','follow_per_cost','order_user','order_tran_rate','order_tran_cost']
print(ck.execute(sql))
return get_dict_list(key,get_round(ck.execute(sql))),total
if __name__ == '__main__':
# a=get_channel_overview('','','',1,10,'date','desc')
# a=get_channel_summary('','')
a=get_channel_again_order_trend('龙鳞文学','2021-01-01','宋刚')
# a=get_channel_active('玉龙书社','2020-09-15','2020-09-20',1,10,'date','desc')
# a,b =get_channel_order_trend("玉龙书社",'王俊杰','2020-12-01','2020-12-12',1,5,'date','desc')
print(a)
pass