from model.DateUtils import DateUtils from model.DataBaseUtils import * from model.log import logger from model.CommonUtils import * du = DateUtils() log = logger() def book_rank(start,end,book,stage,type,page,page_size,order_by,order): db=MysqlUtils() op1=f" and book='{book}'" if book 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 '' op5=f" and stage='{stage}'" if stage else '' op6=f" and type='{type}'" if type else '' sql=f"""select concat(DATE_FORMAT(min(dt),'%Y/%m/%d') ,'~',DATE_FORMAT(max(dt),'%Y/%m/%d')) date, pitcher,book,stage,type, sum(cost) cost, sum(view_count) view_count, sum(click_count) click_count, round(sum(click_count)/sum(view_count),4) click_rate, round(sum(cost)*1000/sum(view_count),2) thousand_view_cost, sum(reg_order_user) reg_user, sum(reg_order_count) reg_count, sum(first_order_amount) first_amount, sum(reg_order_amount) reg_amount, round(sum(first_order_amount)/sum(cost),4) first_roi, round(sum(reg_order_amount)/sum(cost),4) roi from dw_channel where 1=1 {op1} {op2} {op3} {op5} {op6} GROUP BY channel,book,pitcher,stage,type {op4}""" print(sql) return getLimitData(db.dm,sql,page,page_size) def book_trend(start,end,book,type,platform,page,page_size,order_by,order): op1 = f" and book='{book}'" if book else '' op2 = f" and type='{type}' " if type else '' # op3 = f" and platform='{platform}' " if platform else '' op4 = f" and dt>='{start}' " if start else '' op5 = f" and dt<='{end}' " if end else '' op6 = f" order by {order_by} {order}" if order_by and order else '' db=MysqlUtils() sql=f""" select dt,book,type,cost,first_order_amount,reg_order_amount, round(first_order_amount/cost,4) first_roi, a7,a30, round(a7/cost,4) roi7, round(a30/cost,4) roi30, round(reg_order_amount/cost,4) roi, order_amount, reg_order_amount-cost profit, round(click_count/view_count,4) click_rate, round(follow_user/click_count,4) follow_rate, round(first_order_user/click_count,4) first_order_rate, view_count, click_count, follow_user, reg_order_user, first_order_user, round(cost/follow_user,2) follow_cost, round(cost/reg_order_user,2) order_cost, round(cost/first_order_user,2) first_order_cost, concat(a1,',',0,',',a1/cost,',',1) d1, concat(a2-a1,',',(a2-a1)/cost,',',a2/cost,',',a2/a1) d2, concat(a3-a2,',',(a3-a2)/cost,',',a3/cost,',',a3/a1) d3, concat(a4-a3,',',(a4-a3)/cost,',',a4/cost,',',a4/a1) d4, concat(a5-a4,',',(a5-a4)/cost,',',a5/cost,',',a5/a1) d5, concat(a6-a5,',',(a6-a5)/cost,',',a6/cost,',',a6/a1) d6, concat(a7-a6,',',(a7-a6)/cost,',',a7/cost,',',a7/a1) d7, concat(a8-a7,',',(a8-a7)/cost,',',a8/cost,',',a8/a1) d8, concat(a9-a8,',',(a9-a8)/cost,',',a9/cost,',',a9/a1) d9, concat(a10-a9,',',(a10-a9)/cost,',',a10/cost,',',a10/a1) d10, concat(a11-a10,',',(a11-a10)/cost,',',a11/cost,',',a11/a1) d11, concat(a12-a11,',',(a12-a11)/cost,',',a12/cost,',',a12/a1) d12, concat(a13-a12,',',(a13-a12)/cost,',',a13/cost,',',a13/a1) d13, concat(a14-a13,',',(a14-a13)/cost,',',a14/cost,',',a14/a1) d14, concat(a15-a14,',',(a15-a14)/cost,',',a15/cost,',',a15/a1) d15, concat(a16-a15,',',(a16-a15)/cost,',',a16/cost,',',a16/a1) d16, concat(a17-a16,',',(a17-a16)/cost,',',a17/cost,',',a17/a1) d17, concat(a18-a17,',',(a18-a17)/cost,',',a18/cost,',',a18/a1) d18, concat(a19-a18,',',(a19-a18)/cost,',',a19/cost,',',a19/a1) d19, concat(a20-a19,',',(a20-a19)/cost,',',a20/cost,',',a20/a1) d20, concat(a21-a20,',',(a21-a20)/cost,',',a21/cost,',',a21/a1) d21, concat(a22-a21,',',(a22-a21)/cost,',',a22/cost,',',a22/a1) d22, concat(a23-a22,',',(a23-a22)/cost,',',a23/cost,',',a23/a1) d23, concat(a24-a23,',',(a24-a23)/cost,',',a24/cost,',',a24/a1) d24, concat(a25-a24,',',(a25-a24)/cost,',',a25/cost,',',a25/a1) d25, concat(a26-a25,',',(a26-a25)/cost,',',a26/cost,',',a26/a1) d26, concat(a27-a26,',',(a27-a26)/cost,',',a27/cost,',',a27/a1) d27, concat(a28-a27,',',(a28-a27)/cost,',',a28/cost,',',a28/a1) d28, concat(a29-a28,',',(a29-a28)/cost,',',a29/cost,',',a29/a1) d29, concat(a30-a29,',',(a30-a29)/cost,',',a30/cost,',',a30/a1) d30, concat(a31-a30,',',(a31-a30)/cost,',',a31/cost,',',a31/a1) d31, concat(a32-a31,',',(a32-a31)/cost,',',a32/cost,',',a32/a1) d32, concat(a33-a32,',',(a33-a32)/cost,',',a33/cost,',',a33/a1) d33, concat(a34-a33,',',(a34-a33)/cost,',',a34/cost,',',a34/a1) d34, concat(a35-a34,',',(a35-a34)/cost,',',a35/cost,',',a35/a1) d35, concat(a36-a35,',',(a36-a35)/cost,',',a36/cost,',',a36/a1) d36, concat(a37-a36,',',(a37-a36)/cost,',',a37/cost,',',a37/a1) d37, concat(a38-a37,',',(a38-a37)/cost,',',a38/cost,',',a38/a1) d38, concat(a39-a38,',',(a39-a38)/cost,',',a39/cost,',',a39/a1) d39, concat(a40-a39,',',(a40-a39)/cost,',',a40/cost,',',a40/a1) d40, concat(a41-a40,',',(a41-a40)/cost,',',a41/cost,',',a41/a1) d41, concat(a42-a41,',',(a42-a41)/cost,',',a42/cost,',',a42/a1) d42, concat(a43-a42,',',(a43-a42)/cost,',',a43/cost,',',a43/a1) d43, concat(a44-a43,',',(a44-a43)/cost,',',a44/cost,',',a44/a1) d44, concat(a45-a44,',',(a45-a44)/cost,',',a45/cost,',',a45/a1) d45, concat(a46-a45,',',(a46-a45)/cost,',',a46/cost,',',a46/a1) d46, concat(a47-a46,',',(a47-a46)/cost,',',a47/cost,',',a47/a1) d47, concat(a48-a47,',',(a48-a47)/cost,',',a48/cost,',',a48/a1) d48, concat(a49-a48,',',(a49-a48)/cost,',',a49/cost,',',a49/a1) d49, concat(a50-a49,',',(a50-a49)/cost,',',a50/cost,',',a50/a1) d50, concat(a51-a50,',',(a51-a50)/cost,',',a51/cost,',',a51/a1) d51, concat(a52-a51,',',(a52-a51)/cost,',',a52/cost,',',a52/a1) d52, concat(a53-a52,',',(a53-a52)/cost,',',a53/cost,',',a53/a1) d53, concat(a54-a53,',',(a54-a53)/cost,',',a54/cost,',',a54/a1) d54, concat(a55-a54,',',(a55-a54)/cost,',',a55/cost,',',a55/a1) d55, concat(a56-a55,',',(a56-a55)/cost,',',a56/cost,',',a56/a1) d56, concat(a57-a56,',',(a57-a56)/cost,',',a57/cost,',',a57/a1) d57, concat(a58-a57,',',(a58-a57)/cost,',',a58/cost,',',a58/a1) d58, concat(a59-a58,',',(a59-a58)/cost,',',a59/cost,',',a59/a1) d59, concat(a60-a59,',',(a60-a59)/cost,',',a60/cost,',',a60/a1) d60, concat(m3-a60,',',(m3-a60)/cost,',',m3/cost,',',m3/a1) m3, concat(m4-m3,',',(m4-m3)/cost,',',m4/cost,',',m4/a1) m4, concat(m5-m4,',',(m5-m4)/cost,',',m5/cost,',',m5/a1) m5 from book_trend where 1=1 {op1} {op2} {op4} {op5} {op6} """ sum_sql=f"""select concat(date_format(min(dt),'%Y/%m/%d'),'~',date_format(max(dt),'%Y/%m/%d')) dt, sum(cost) cost, sum(first_order_amount) first_order_amount, sum(reg_order_amount) reg_order_amount, round(sum(first_order_amount)/sum(cost),4) first_roi, round(sum(a7)/sum(cost),4) roi7, round(sum(a30)/sum(cost),4) roi30, round(sum(reg_order_amount)/sum(cost),4) roi, sum(order_amount) order_amount, sum(reg_order_amount-cost) profit, round(sum(click_count)/sum(view_count),4) click_rate, round(sum(follow_user)/sum(click_count),4) follow_rate, round(sum(first_order_user)/sum(click_count),4) first_order_rate, sum(view_count) view_count, sum(click_count) click_count, sum(follow_user) follow_user, sum(reg_order_user) reg_order_user, sum(first_order_user) first_order_user, round(sum(cost)/sum(follow_user),2) follow_cost, round(sum(cost)/sum(reg_order_user)) order_cost, round(sum(cost)/sum(first_order_user)) first_order_cost from ({sql}) a """ data, total, total_data = getLimitSumData(db.dm, sql, sum_sql, 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(['amount','add','roi','mult'],li)) # print(data) 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','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[k]= parse(v) if v else {} return data, total, total_data def book_overview(start, end, book, type, page, page_size, order_by, order): op1 = f" and book='{book}'" if book else '' op2 = f" and type='{type}' " if type 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 '' db = MysqlUtils() sql = f""" select book,type, DATE_FORMAT(min(dt),'%Y%m%d') start_date, DATE_FORMAT(max(dt),'%Y%m%d') end_date, sum(cost) cost, sum(reg_order_amount) amount, sum(first_order_amount) first_amount, round(sum(reg_order_amount)/sum(cost),4) roi, round(sum(first_order_amount)/sum(cost),4) first_roi, round(sum(a7)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,cost)),4) roi7, round(sum(a30)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 28 day),0,cost)),4) roi30, sum(reg_order_amount-cost) profit, round(sum(click_count)/sum(view_count),4) click_rate, round(sum(follow_user)/sum(click_count),4) follow_rate, round(sum(reg_order_user)/sum(click_count),4) order_rate, round(sum(first_order_user)/sum(click_count),4) first_order_rate, sum(view_count) view_count, sum(click_count) click_count, sum(follow_user) follow_user, sum(reg_order_user) order_user, sum(first_order_user) first_order_user, round(sum(cost)/sum(follow_user),2) follow_cost, round(sum(cost)/sum(reg_order_user),2) order_cost, round(sum(cost)/sum(first_order_user),2) first_order_cost, round(sum(reg_order_amount)/sum(reg_order_user),2) avg_amount, sum(ba1) td_amount, sum(ba2) yd_amount, sum(ba3) byd_amount, concat(sum(a1),',',0,',',sum(a1)/sum(cost),',',1) d1, concat(sum(a2-a1),',',sum(a2-a1)/sum(if(dt=CURRENT_DATE,0,cost)),',',sum(a2)/sum(if(dt=CURRENT_DATE,0,cost)),',',sum(a2)/sum(if(dt=CURRENT_DATE,0,a1))) d2, concat(sum(a3-a2),',',sum(a3-a2)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 1 day),0,cost)),',',sum(a3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 1 day),0,cost)),',',sum(a3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 1 day),0,a1))) d3, concat(sum(a4-a3),',',sum(a4-a3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 2 day),0,cost)),',',sum(a4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 2 day),0,cost)),',',sum(a4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 2 day),0,a1))) d4, concat(sum(a5-a4),',',sum(a5-a4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 3 day),0,cost)),',',sum(a5)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 3 day),0,cost)),',',sum(a5)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 3 day),0,a1))) d5, concat(sum(a6-a5),',',sum(a6-a5)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 4 day),0,cost)),',',sum(a6)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 4 day),0,cost)),',',sum(a6)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 4 day),0,a1))) d6, concat(sum(a7-a6),',',sum(a7-a6)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,cost)),',',sum(a7)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,cost)),',',sum(a7)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,a1))) d7, concat(sum(a8-a7),',',sum(a8-a7)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 6 day),0,cost)),',',sum(a8)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 6 day),0,cost)),',',sum(a8)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 6 day),0,a1))) d8, concat(sum(a9-a8),',',sum(a9-a8)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 7 day),0,cost)),',',sum(a9)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 7 day),0,cost)),',',sum(a9)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 7 day),0,a1))) d9, concat(sum(a10-a9),',',sum(a10-a9)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 8 day),0,cost)),',',sum(a10)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 8 day),0,cost)),',',sum(a10)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 8 day),0,a1))) d10, concat(sum(a11-a10),',',sum(a11-a10)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 9 day),0,cost)),',',sum(a11)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 9 day),0,cost)),',',sum(a11)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 9 day),0,a1))) d11, concat(sum(a12-a11),',',sum(a12-a11)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 10 day),0,cost)),',',sum(a12)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 10 day),0,cost)),',',sum(a12)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 10 day),0,a1))) d12, concat(sum(a13-a12),',',sum(a13-a12)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 11 day),0,cost)),',',sum(a13)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 11 day),0,cost)),',',sum(a13)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 11 day),0,a1))) d13, concat(sum(a14-a13),',',sum(a14-a13)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 12 day),0,cost)),',',sum(a14)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 12 day),0,cost)),',',sum(a14)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 12 day),0,a1))) d14, concat(sum(a15-a14),',',sum(a15-a14)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 13 day),0,cost)),',',sum(a15)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 13 day),0,cost)),',',sum(a15)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 13 day),0,a1))) d15, concat(sum(a16-a15),',',sum(a16-a15)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 14 day),0,cost)),',',sum(a16)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 14 day),0,cost)),',',sum(a16)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 14 day),0,a1))) d16, concat(sum(a17-a16),',',sum(a17-a16)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 15 day),0,cost)),',',sum(a17)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 15 day),0,cost)),',',sum(a17)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 15 day),0,a1))) d17, concat(sum(a18-a17),',',sum(a18-a17)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 16 day),0,cost)),',',sum(a18)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 16 day),0,cost)),',',sum(a18)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 16 day),0,a1))) d18, concat(sum(a19-a18),',',sum(a19-a18)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 17 day),0,cost)),',',sum(a19)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 17 day),0,cost)),',',sum(a19)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 17 day),0,a1))) d19, concat(sum(a20-a19),',',sum(a20-a19)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 18 day),0,cost)),',',sum(a20)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 18 day),0,cost)),',',sum(a20)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 18 day),0,a1))) d20, concat(sum(a21-a20),',',sum(a21-a20)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 19 day),0,cost)),',',sum(a21)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 19 day),0,cost)),',',sum(a21)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 19 day),0,a1))) d21, concat(sum(a22-a21),',',sum(a22-a21)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 20 day),0,cost)),',',sum(a22)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 20 day),0,cost)),',',sum(a22)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 20 day),0,a1))) d22, concat(sum(a23-a22),',',sum(a23-a22)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 21 day),0,cost)),',',sum(a23)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 21 day),0,cost)),',',sum(a23)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 21 day),0,a1))) d23, concat(sum(a24-a23),',',sum(a24-a23)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 22 day),0,cost)),',',sum(a24)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 22 day),0,cost)),',',sum(a24)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 22 day),0,a1))) d24, concat(sum(a25-a24),',',sum(a25-a24)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 23 day),0,cost)),',',sum(a25)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 23 day),0,cost)),',',sum(a25)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 23 day),0,a1))) d25, concat(sum(a26-a25),',',sum(a26-a25)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 24 day),0,cost)),',',sum(a26)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 24 day),0,cost)),',',sum(a26)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 24 day),0,a1))) d26, concat(sum(a27-a26),',',sum(a27-a26)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 25 day),0,cost)),',',sum(a27)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 25 day),0,cost)),',',sum(a27)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 25 day),0,a1))) d27, concat(sum(a28-a27),',',sum(a28-a27)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 26 day),0,cost)),',',sum(a28)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 26 day),0,cost)),',',sum(a28)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 26 day),0,a1))) d28, concat(sum(a29-a28),',',sum(a29-a28)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 27 day),0,cost)),',',sum(a29)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 27 day),0,cost)),',',sum(a29)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 27 day),0,a1))) d29, concat(sum(a30-a29),',',sum(a30-a29)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 28 day),0,cost)),',',sum(a30)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 28 day),0,cost)),',',sum(a30)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 28 day),0,a1))) d30, concat(sum(a31-a30),',',sum(a31-a30)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 29 day),0,cost)),',',sum(a31)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 29 day),0,cost)),',',sum(a31)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 29 day),0,a1))) d31, concat(sum(a32-a31),',',sum(a32-a31)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 30 day),0,cost)),',',sum(a32)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 30 day),0,cost)),',',sum(a32)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 30 day),0,a1))) d32, concat(sum(a33-a32),',',sum(a33-a32)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 31 day),0,cost)),',',sum(a33)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 31 day),0,cost)),',',sum(a33)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 31 day),0,a1))) d33, concat(sum(a34-a33),',',sum(a34-a33)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 32 day),0,cost)),',',sum(a34)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 32 day),0,cost)),',',sum(a34)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 32 day),0,a1))) d34, concat(sum(a35-a34),',',sum(a35-a34)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 33 day),0,cost)),',',sum(a35)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 33 day),0,cost)),',',sum(a35)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 33 day),0,a1))) d35, concat(sum(a36-a35),',',sum(a36-a35)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 34 day),0,cost)),',',sum(a36)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 34 day),0,cost)),',',sum(a36)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 34 day),0,a1))) d36, concat(sum(a37-a36),',',sum(a37-a36)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 35 day),0,cost)),',',sum(a37)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 35 day),0,cost)),',',sum(a37)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 35 day),0,a1))) d37, concat(sum(a38-a37),',',sum(a38-a37)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 36 day),0,cost)),',',sum(a38)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 36 day),0,cost)),',',sum(a38)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 36 day),0,a1))) d38, concat(sum(a39-a38),',',sum(a39-a38)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 37 day),0,cost)),',',sum(a39)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 37 day),0,cost)),',',sum(a39)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 37 day),0,a1))) d39, concat(sum(a40-a39),',',sum(a40-a39)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 38 day),0,cost)),',',sum(a40)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 38 day),0,cost)),',',sum(a40)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 38 day),0,a1))) d40, concat(sum(a41-a40),',',sum(a41-a40)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 39 day),0,cost)),',',sum(a41)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 39 day),0,cost)),',',sum(a41)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 39 day),0,a1))) d41, concat(sum(a42-a41),',',sum(a42-a41)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 40 day),0,cost)),',',sum(a42)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 40 day),0,cost)),',',sum(a42)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 40 day),0,a1))) d42, concat(sum(a43-a42),',',sum(a43-a42)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 41 day),0,cost)),',',sum(a43)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 41 day),0,cost)),',',sum(a43)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 41 day),0,a1))) d43, concat(sum(a44-a43),',',sum(a44-a43)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 42 day),0,cost)),',',sum(a44)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 42 day),0,cost)),',',sum(a44)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 42 day),0,a1))) d44, concat(sum(a45-a44),',',sum(a45-a44)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 43 day),0,cost)),',',sum(a45)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 43 day),0,cost)),',',sum(a45)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 43 day),0,a1))) d45, concat(sum(a46-a45),',',sum(a46-a45)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 44 day),0,cost)),',',sum(a46)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 44 day),0,cost)),',',sum(a46)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 44 day),0,a1))) d46, concat(sum(a47-a46),',',sum(a47-a46)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 45 day),0,cost)),',',sum(a47)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 45 day),0,cost)),',',sum(a47)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 45 day),0,a1))) d47, concat(sum(a48-a47),',',sum(a48-a47)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 46 day),0,cost)),',',sum(a48)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 46 day),0,cost)),',',sum(a48)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 46 day),0,a1))) d48, concat(sum(a49-a48),',',sum(a49-a48)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 47 day),0,cost)),',',sum(a49)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 47 day),0,cost)),',',sum(a49)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 47 day),0,a1))) d49, concat(sum(a50-a49),',',sum(a50-a49)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 48 day),0,cost)),',',sum(a50)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 48 day),0,cost)),',',sum(a50)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 48 day),0,a1))) d50, concat(sum(a51-a50),',',sum(a51-a50)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 49 day),0,cost)),',',sum(a51)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 49 day),0,cost)),',',sum(a51)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 49 day),0,a1))) d51, concat(sum(a52-a51),',',sum(a52-a51)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 50 day),0,cost)),',',sum(a52)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 50 day),0,cost)),',',sum(a52)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 50 day),0,a1))) d52, concat(sum(a53-a52),',',sum(a53-a52)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 51 day),0,cost)),',',sum(a53)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 51 day),0,cost)),',',sum(a53)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 51 day),0,a1))) d53, concat(sum(a54-a53),',',sum(a54-a53)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 52 day),0,cost)),',',sum(a54)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 52 day),0,cost)),',',sum(a54)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 52 day),0,a1))) d54, concat(sum(a55-a54),',',sum(a55-a54)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 53 day),0,cost)),',',sum(a55)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 53 day),0,cost)),',',sum(a55)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 53 day),0,a1))) d55, concat(sum(a56-a55),',',sum(a56-a55)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 54 day),0,cost)),',',sum(a56)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 54 day),0,cost)),',',sum(a56)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 54 day),0,a1))) d56, concat(sum(a57-a56),',',sum(a57-a56)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 55 day),0,cost)),',',sum(a57)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 55 day),0,cost)),',',sum(a57)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 55 day),0,a1))) d57, concat(sum(a58-a57),',',sum(a58-a57)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 56 day),0,cost)),',',sum(a58)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 56 day),0,cost)),',',sum(a58)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 56 day),0,a1))) d58, concat(sum(a59-a58),',',sum(a59-a58)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 57 day),0,cost)),',',sum(a59)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 57 day),0,cost)),',',sum(a59)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 57 day),0,a1))) d59, concat(sum(a60-a59),',',sum(a60-a59)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 58 day),0,cost)),',',sum(a60)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 58 day),0,cost)),',',sum(a60)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 58 day),0,a1))) d60, concat(sum(m3-a60),',',sum(m3-a60)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 88 day),0,cost)),',',sum(m3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 88 day),0,cost)),',',sum(m3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 88 day),0,a1))) m3, concat(sum(m4-m3),',',sum(m4-m3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 118 day),0,cost)),',',sum(m4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 118 day),0,cost)),',',sum(m4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 118 day),0,a1))) m4, concat(sum(m5-m4),',',sum(m5-m4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 148 day),0,cost)),',',sum(m5)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 148 day),0,cost)),',',sum(m5)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 148 day),0,a1))) m5 from book_trend where cost>0 {op1} {op2} {op3} {op4} group by book,type {op5} """ data, total = getLimitData(db.dm, sql, page, page_size) def parse(str): li = str.decode('utf-8').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(['amount', 'add', 'roi', 'mult'], li)) # print(data) 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', '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[k] = parse(v) if v else {} return data, total def pitcher_overview(start, end, pitcher, page, page_size, order_by, order): 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 '' db = MysqlUtils() sql=f""" select pitcher, DATE_FORMAT(min(dt),'%Y%m%d') start_date, DATE_FORMAT(max(dt),'%Y%m%d') end_date, sum(cost) cost, sum(reg_amount) amount, sum(first_order_amount) first_amount, round(sum(reg_amount)/sum(cost),4) roi, round(sum(first_order_amount)/sum(cost),4) first_roi, round(sum(d7)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,cost)),4) roi7, round(sum(d30)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,cost)),4) roi30, sum(reg_amount-cost) profit, round(sum(click_count)/sum(view_count),4) click_rate, round(sum(follow_user)/sum(click_count),4) follow_rate, round(sum(reg_order_user)/sum(click_count),4) order_rate, round(sum(first_order_user)/sum(click_count),4) first_order_rate, sum(view_count) view_count, sum(click_count) click_count, sum(follow_user) follow_user, sum(reg_order_user) order_user, sum(first_order_user) first_order_user, round(sum(cost)/sum(follow_user),2) follow_cost, round(sum(cost)/sum(reg_order_user),2) order_cost, round(sum(cost)/sum(first_order_user),2) first_order_cost, round(sum(reg_amount)/sum(reg_order_user),2) avg_amount, sum(ba1) td_amount, sum(ba2) yd_amount, sum(ba3) byd_amount,sum(d7) d7 ,sum(d30) d30 from dw_pitcher_trend where cost>0 and pitcher!='' {op1} {op2} {op3} group by pitcher {op4} """ sum_sql=f"""select '总计' as pitcher, sum(cost) cost, sum(amount) amount, sum(first_amount) first_amount, round(sum(amount)/sum(cost),4) roi, round(sum(first_amount)/sum(cost),4) first_roi, round(sum(d7)/sum(cost),4) roi7, round(sum(d30)/sum(cost),4) roi30, sum(amount-cost) profit, round(sum(click_count)/sum(view_count),4) click_rate, round(sum(follow_user)/sum(click_count),4) follow_rate, round(sum(order_user)/sum(click_count),4) order_rate, round(sum(first_order_user)/sum(click_count),4) first_order_rate, sum(view_count) view_count, sum(click_count) click_count, sum(follow_user) follow_user, sum(order_user) order_user, sum(first_order_user) first_order_user, round(sum(cost)/sum(follow_user),2) follow_cost, round(sum(cost)/sum(order_user),2) order_cost, round(sum(cost)/sum(first_order_user),2) first_order_cost, round(sum(amount)/sum(order_user),2) avg_amount, sum(td_amount) td_amount, sum(yd_amount) yd_amount, sum(byd_amount) byd_amount from ({sql}) a """ return getLimitSumDataV2(db.dm, sql,sum_sql, page, page_size) def boss_panel_summary(start,end,pitcher, type, stage, page, page_size, order_by, order): 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" and type='{type}' " if type else '' op5= f" and stage='{stage}' " if stage else '' op6 = f" order by {order_by} {order}" if order_by and order else 'order by dt desc' dm = MysqlUtils().dm sql=f"""select sum(order_amount) order_amount,sum(cost) cost,dt from dw_channel where 1=1 {op1} {op2} {op3} {op4} {op5} GROUP BY dt HAVING order_amount+cost>0 {op6} """ # print(sql) return getLimitData(dm, sql, page, page_size) def image_rank(start, end, type,book, page, page_size, order_by, order): op1 = f" and book='{book}' " if book else '' op2 = f" and dt>='{start}' " if start else '' op3 = f" and dt<='{end}' " if end else '' op4 = f" and type='{type}' " if type else '' op5 = f" order by {order_by} {order}" if order_by and order else 'order by dt desc' db = MysqlUtils().quchen_text sql =f"""select preview_url, sum(cost), round(sum(click_count)/sum(view_count),4) ctr, round(sum(cost)/sum(click_count),2) cpc from dm_image_cost_day where preview_url!='' {op1} {op2} {op3} {op4} GROUP BY signature,preview_url {op5} """ return getLimitData(db, sql, page, page_size) """广告排行榜""" def advertisement_rank(start,end,ad_id,channel,pitcher,stage,site,type,page,page_size,order,order_by,book): op1=f" and ad_id='{ad_id}' " if ad_id else '' op2=f" and dt>='{start}' " if start else '' op3=f" and dt<='{end}' " if end else '' op4=f" and channel='{channel}' " if channel else '' op5=f" and pitcher='{pitcher}' " if pitcher else '' op6=f" and stage='{stage}'" if stage else '' op7=f" and site='{site}'" if site else '' op8=f" and type='{type}'" if type else '' op9=f" order by {order_by} {order}" if order_by and order else '' op10=f" and book='{book}'" if book else '' db =MysqlUtils().dm sql=f"""select dt,channel,pitcher,stage,platform,book,ad_name,ad_id,`type`,site, cost, view_count, click_count, follow_count, round((cost/view_count)*1000,4) cpm, round(click_count/view_count,4) ctr, round(cost/click_count,2) cpc, order_count, order_amount, round(order_count/click_count,4) order_rate, round(order_amount/order_count,2) unit_price, round(cost/order_count,2) order_cost, round(order_amount/cost,4) roi, title, description, image_id, preview_url from dw_ad_day where 1=1 {op1} {op2} {op3} {op4} {op5} {op6} {op7} {op8} {op10} {op9} """ sum_sql = f"""select '总计' as pitcher, sum(cost) cost, sum(view_count) view_count, sum(click_count) click_count, sum(follow_count) follow_count, round((sum(cost)/sum(view_count))*1000,4) cpm, round(sum(click_count)/sum(view_count),4) ctr, round(sum(cost)/sum(click_count),2) cpc, sum(order_count), sum(order_amount), round(sum(order_count)/sum(click_count),4) order_rate, round(sum(order_amount)/sum(order_count),2) unit_price, round(sum(cost)/sum(order_count),2) order_cost, round(sum(order_amount)/sum(cost),4) ROI from ({sql}) a """ return getLimitSumData(db,sql,sum_sql, page, page_size)