pitcher_panel.py 46 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810
  1. from model.DateUtils import DateUtils
  2. from model.DataBaseUtils import *
  3. from model.log import logger
  4. from model.CommonUtils import *
  5. du = DateUtils()
  6. log = logger()
  7. """根据公众号获取投手"""
  8. def get_channel_belong_pitcher(channel):
  9. sql = f"select pitcher,channel from dw_daily_channel_cost where dt='{du.get_n_days(-1)}' and pitcher!='' and channel!=''"
  10. data=ck.execute(sql)
  11. di={}
  12. for i in data:
  13. di[i[1]]=i[0]
  14. return di.get(channel,'')
  15. """根据名字获取其归属的公众号"""
  16. def get_channels_from_user(user):
  17. sql=f"""SELECT a.nick_name FROM t_mp_account a
  18. left join t_sys_user b on a.oper_user_id=b.user_id where b.nick_name='{user}'"""
  19. return tuple(MysqlUtils().zx.getOneList(sql))
  20. def get_pitcher_panel_channel(pitcher,channel,start,end,page,page_size,order_by,order):
  21. ck=CkUtils()
  22. sql=f"""select channel,stage,platform,book,
  23. formatDateTime(dt,'%Y-%m-%d') as date,
  24. toDecimal32(cost,2),
  25. toDecimal32(first_order_amount,2),
  26. toDecimal32(if(cost=0,0,first_order_amount/cost),4) first_roi,
  27. first_order_user,first_order_count,
  28. toDecimal32(if(first_order_user=0,0,cost/first_order_user),2) first_per_cost,
  29. view_count,click_count,follow_user,
  30. toDecimal32(if(click_count=0,0,follow_user/click_count),4) follow_rate,
  31. toDecimal32(if(follow_user=0,0,cost/follow_user),2) follow_per_cost,
  32. toDecimal32(total_cost,2),
  33. toDecimal32(if(total_cost=0,0,total_amount/total_cost),2) back_rate
  34. from dw_daily_channel where dt>='{start}' and dt<='{end}' """
  35. if pitcher!='all':
  36. sql += f" and pitcher='{pitcher}' "
  37. if channel!='':
  38. sql += f" and channel='{channel}' "
  39. total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
  40. sql += f" order by {order_by} {order} limit {(page-1)*page_size},{page_size} "
  41. print(sql)
  42. data=ck.execute(sql)
  43. key=['channel','stage','platform','book','date','cost','first_order_amount','first_roi','first_order_user',
  44. 'first_order_count','first_per_cost','view_count','click_count','follow_user','follow_rate','follow_per_cost',
  45. 'total_cost','back_rate']
  46. return get_dict_list(key,get_round(data,4)),total
  47. def get_pitcher_panel_daily(pitcher, start, end, page, page_size, order_by, order):
  48. ck = CkUtils()
  49. sql=f"""
  50. select formatDateTime(dt,'%Y-%m-%d') date,'{pitcher}' pitcher,cost,first_order_amount,first_roi,
  51. order_amount,today_roi,total_amount,total_cost,total_amount-total_cost total_profit,
  52. if(total_cost=0,0,total_amount/total_cost) total_roi from
  53. (select dt,
  54. round(sum(cost),2) cost,
  55. round(sum(first_order_amount),2) first_order_amount,
  56. round(if(cost=0,0,first_order_amount/cost),4) first_roi ,
  57. round(sum(order_amount),2) order_amount,
  58. round(if(cost=0,0,sum(reg_order_amount)/cost),4) today_roi
  59. from dw_daily_channel
  60. where dt>='{start}' and dt<='{end}' and pitcher='{pitcher}'
  61. group by dt) a
  62. left outer join (
  63. select dt,total_amount,total_cost from dw_daily_pitcher where pitcher='{pitcher}'
  64. ) b on a.dt=b.dt
  65. """
  66. print(sql)
  67. total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
  68. sql += f" order by {order_by} {order} limit {(page-1)*page_size},{page_size} "
  69. print(sql)
  70. data = ck.execute(sql)
  71. key=['date','pitcher','cost','first_order_amount','first_roi','order_amount','today_roi','total_amount','total_cost','total_profit','total_roi']
  72. print(get_dict_list(key,get_round(data)))
  73. return get_dict_list(key,get_round(data)),total
  74. def get_pitcher_panel_overview(pitcher):
  75. ck = CkUtils()
  76. sql=f"""select pitcher,
  77. cost cost,
  78. amount amount,
  79. roi roi,
  80. channel_count,on_channel_count,
  81. off_channel_count,
  82. this_month_cost this_month_cost,
  83. this_month_amount this_month_amount,
  84. this_month_roi this_month_roi,
  85. last_month_cost last_month_cost,
  86. last_month_amount last_month_amount,
  87. last_month_roi last_month_roi,
  88. last_month_far_amount last_month_far_amount,
  89. follow_user,
  90. last_month_far_roi last_month_far_roi
  91. from dm_pitcher_daily_page_total where dt='{du.get_n_days(0)}'"""
  92. if pitcher != 'all':
  93. sql += f" and pitcher='{pitcher}' "
  94. print(sql)
  95. data=ck.execute(sql)
  96. print(data)
  97. key=['pitcher','cost','amount','roi','channel_count','on_channel_count','off_channel_count','this_month_cost','this_month_amount','this_month_roi',
  98. 'last_month_cost','last_month_amount','last_month_roi','last_month_far_amount','follow_user','last_month_far_roi']
  99. return get_dict_list(key,get_round(data))
  100. def get_channel_overview(channel,pitcher,start,end,page,page_size,order_by,order):
  101. ck = CkUtils()
  102. sql="""select channel,toString(dt) date,
  103. view_count,click_count,
  104. toDecimal32(if(view_count=0,0,click_count/view_count),4) click_rate,
  105. follow_user,
  106. toDecimal32(if(click_count=0,0,follow_user/click_count),4) follow_rate,
  107. toDecimal32(if(follow_user=0,0,cost/follow_user),2) follow_per_cost,
  108. toDecimal32(if(click_count=0,0,first_order_count/click_count),4) order_rate,
  109. toDecimal32(if(first_order_user=0,0,cost/first_order_user),2) order_per_cost,
  110. toDecimal32(cost,2),
  111. first_order_count,first_order_user,
  112. toDecimal32(first_order_amount,2),
  113. order_count,order_user,
  114. toDecimal32(order_amount,2),
  115. toDecimal32(order_amount-first_order_amount,2),
  116. toDecimal32(if(first_order_user=0,0,first_order_amount/first_order_user),2) first_amount_per_user,
  117. toDecimal32(if(follow_user=0,0,first_order_amount/follow_user),2) amount_per_follow,
  118. toDecimal32(if(first_order_user=0,0,cost/first_order_user),2) first_cost_per_user,
  119. toDecimal32(if(follow_user=0,0,first_order_user/follow_user),4) new_user_order_rate,
  120. toDecimal32(reg_order_amount,2),
  121. toDecimal32(total_cost,2),
  122. toDecimal32(total_amount,2),
  123. toDecimal32(if(cost=0,0,first_order_amount/cost),4) day_roi,
  124. toDecimal32(if(total_cost=0,0,total_amount/total_cost),4) all_roi,
  125. if(total_cost=0,0,total_first_amount/total_cost) avg_new_order_rate,
  126. if(reg_order_user=0,0,reg_order_user_again/reg_order_user) old_user_once_order_rate,
  127. pitcher from dw_daily_channel where 1=1 """
  128. if channel!='':
  129. sql+=f" and channel='{channel}'"
  130. if start!='':
  131. sql+=f" and dt>='{start}'"
  132. if end!='':
  133. sql+=f" and dt<='{end}'"
  134. if pitcher!='':
  135. if pitcher!='赞象 admin':
  136. if get_channels_from_user(pitcher).__len__()==0:
  137. sql+=f" and pitcher='{pitcher}'"
  138. else:
  139. sql+=f" and channel in {get_channels_from_user(pitcher)}"
  140. print(sql)
  141. total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
  142. sql += f" order by {order_by} {order} limit {(page-1)*page_size},{page_size} "
  143. key=['channel','date','view_count','click_count','click_rate','follow_user','follow_rate','follow_per_cost','order_rate','order_per_cost','cost',
  144. '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',
  145. 'new_user_order_rate','reg_user_amount','total_cost','total_amount','day_roi','all_roi','avg_new_order_rate','old_user_once_order_rate','pitcher']
  146. return get_dict_list(key,get_round(ck.execute(sql))),total
  147. def get_channel_again_order_trend(channel,date,pitcher):
  148. ck = CkUtils()
  149. # if get_channel_belong_pitcher(channel)!=pitcher:
  150. # return []
  151. sql=f"""select toString(dt) date,
  152. channel,book,
  153. toDecimal32(cost,2),
  154. toDecimal32(reg_order_amount,2),
  155. toDecimal32(if(cost=0,0,reg_order_amount/cost),4) roi,
  156. follow_user new_follow,
  157. toDecimal32(if(follow_user=0,0,cost/follow_user),2) new_follow_per_cost,
  158. reg_order_count reg_count,reg_order_user reg_user,
  159. toDecimal32(if(reg_order_user=0,0,cost/reg_order_user),2) cost_per_user,
  160. toDecimal32(if(reg_order_user=0,0,reg_order_amount/reg_order_user),2) avg_order_amount,
  161. toDecimal32(if(reg_order_user=0,0,reg_order_user_again/reg_order_user),4) avg_again_order_rate,
  162. if(reg_order_user=0,0,order_count/reg_order_user) order_count
  163. from dw_daily_channel where channel='{channel}' and dt='{date}'
  164. """
  165. print(sql)
  166. data=ck.execute(sql)
  167. print(data)
  168. key1=['date','channel','book','cost','reg_amount','roi','new_follow','new_follow_per_cost',
  169. 'reg_count','reg_user','cost_per_user','avg_order_amount','avg_again_order_rate','order_count']
  170. json1=get_dict_list(key1,get_round(data))[0]
  171. # print(json1)
  172. sql2=f"""select
  173. 1 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3,
  174. sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6
  175. from (select count(1) count
  176. from order where channel='{channel}' and date='{date}'
  177. and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id,date) a
  178. union all
  179. select
  180. 2 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3,
  181. sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6
  182. from (
  183. select count(1) count
  184. from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),1)
  185. and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a
  186. union all
  187. select
  188. 3 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3,
  189. sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6
  190. from (
  191. select count(1) count
  192. from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),2)
  193. and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a
  194. union all
  195. select
  196. 4 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3,
  197. sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6
  198. from (
  199. select count(1) count
  200. from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),3)
  201. and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a
  202. union all
  203. select
  204. 5 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3,
  205. sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6
  206. from (
  207. select count(1) count
  208. from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),4)
  209. and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a
  210. union all
  211. select
  212. 6 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3,
  213. sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6
  214. from (
  215. select count(1) count
  216. from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),5)
  217. and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a
  218. union all
  219. select
  220. 7 as ddf,count(1) c1,sum(if(count>1,1,0)) c2,sum(if(count>2,1,0)) c3,
  221. sum(if(count>3,1,0)) c4,sum(if(count>4,1,0)) c5,sum(if(count>5,1,0)) c6
  222. from (
  223. select count(1) count
  224. from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),6)
  225. and formatDateTime(reg_time,'%Y-%m-%d')='{date}' group by user_id) a"""
  226. df=ck.execute(sql2)
  227. print(df)
  228. # 补全
  229. # xx=[i[0] for i in df]
  230. # for i in range(1,8):
  231. # if i not in xx:
  232. # df.append((i,0,0,0,0,0,0))
  233. # 排序
  234. import operator
  235. df.sort(key=operator.itemgetter(0))
  236. print(df)
  237. reg_user=json1["reg_user"]
  238. li=[]
  239. for i in range(1,6):
  240. print(i)
  241. d = {}
  242. d["user_order_count"] = i
  243. d1={}
  244. d1["origin"]=df[0][i]
  245. d1["new"]=0
  246. d1["move"]=df[0][i+1]
  247. d1["now"]=d1["origin"]+d1['new']-d1["move"]
  248. d1["follow_order_rate"]=round(d1["now"]/df[0][1],2) if df[0][i]!=0 else 0
  249. d['d1'] = d1
  250. d2={}
  251. d2["origin"] = d1["now"]
  252. d2["new"] = df[1][i]-df[0][i]
  253. d2["move"] = df[1][i+1]-df[0][i+1]
  254. d2["now"] = df[1][i]-df[1][i+1]
  255. d2["follow_order_rate"] = round(d2["now"] /df[1][1], 2) if df[1][i]!=0 else 0
  256. d['d2'] = d2
  257. d3={}
  258. d3["origin"] = d2["now"]
  259. d3["new"] = df[2][i]-df[1][i]
  260. d3["move"] =df[2][i+1]-df[1][i+1]
  261. d3["now"] = df[2][i]-df[2][i+1]
  262. d3["follow_order_rate"] = round(d3["now"] / df[2][1], 2) if df[2][i]!=0 else 0
  263. d['d3'] = d3
  264. d4={}
  265. d4["origin"] = d3["now"]
  266. d4["new"] = df[3][i]-df[2][i]
  267. d4["move"] = df[3][i+1]-df[2][i+1]
  268. d4["now"] = df[3][i]-df[3][i+1]
  269. d4["follow_order_rate"] = round(d4["now"] / df[3][1], 2) if df[3][i]!=0 else 0
  270. d['d4'] = d4
  271. d5={}
  272. d5["origin"] = d4["now"]
  273. d5["new"] = df[4][i]-df[3][i]
  274. d5["move"] = df[4][i+1]-df[3][i+1]
  275. d5["now"] = df[4][i]-df[4][i+1]
  276. d5["follow_order_rate"] = round(d4["now"] / df[4][1], 2) if df[3][i]!=0 else 0
  277. d['d5'] = d5
  278. d6 = {}
  279. d6["origin"] = d5["now"]
  280. d6["new"] = df[5][i]-df[4][i]
  281. d6["move"] = df[5][i+1]-df[4][i+1]
  282. d6["now"] = df[5][i]-df[5][i+1]
  283. d6["follow_order_rate"] = round(d6["now"] / df[5][1], 2) if df[5][i]!=0 else 0
  284. d['d6'] = d6
  285. d7 = {}
  286. d7["origin"] = d6["now"]
  287. d7["new"] = df[6][i]-df[5][i]
  288. d7["move"] = df[6][i+1]-df[5][i+1]
  289. d7["now"] = df[6][i]-df[6][i+1]
  290. d7["follow_order_rate"] = round(d7["now"] / df[6][1], 2) if df[6][i]!=0 else 0
  291. d['d7'] = d7
  292. li.append(d)
  293. print(li)
  294. json1['data']=li
  295. print([json1])
  296. return [json1]
  297. def get_channel_active(channel,pitcher,start,end,page,page_size,order_by,order):
  298. ck = CkUtils()
  299. channel_op=f" and channel='{channel}' " if channel!='' else ''
  300. if pitcher != '赞象 admin':
  301. if get_channels_from_user(pitcher).__len__() == 0:
  302. pitcher_op = f" and pitcher='{pitcher}'"
  303. else:
  304. pitcher_op = f" and channel in {get_channels_from_user(pitcher)}"
  305. sql=f"""select 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,
  306. 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),
  307. toDecimal32(act_per_cost,2),
  308. 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))),
  309. 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))),
  310. 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))),
  311. 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))),
  312. 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))),
  313. 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))),
  314. 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))),
  315. 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))),
  316. 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))),
  317. 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))),
  318. 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))),
  319. 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))),
  320. 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))),
  321. 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))),
  322. 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))),
  323. 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))),
  324. 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))),
  325. 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))),
  326. 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))),
  327. 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))),
  328. 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))),
  329. 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))),
  330. 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))),
  331. 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))),
  332. 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))),
  333. 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))),
  334. 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))),
  335. 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))),
  336. 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))),
  337. 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)))
  338. from (
  339. select dt,book,cost,pitcher,channel,
  340. reg_order_amount reg_amount,
  341. if(cost=0,0,reg_order_amount/cost) roi,
  342. follow_user new_follow_user,
  343. if(follow_user=0,0,cost/follow_user) new_follow_per_cost,
  344. reg_order_user order_user,
  345. reg_order_count order_count,
  346. if(reg_order_user=0,0,cost/reg_order_user) order_user_per_cost,
  347. if(follow_user=0,0,reg_order_user7/follow_user) day7_avg_act_rate ,
  348. if(reg_order_user7=0,0,cost/reg_order_user7) day7_avg_act_per_cost,
  349. if(follow_user=0,0,reg_order_user30/follow_user) day30_avg_act_rate,
  350. if(reg_order_user30=0,0,cost/reg_order_user30) ay30_avg_act_cost,
  351. if(reg_order_user=0,0,cost/reg_order_user) act_per_cost
  352. from dw_daily_channel where 1=1 {channel_op} and dt>='{start}' and dt<='{end}' {pitcher_op}) a
  353. left outer join (
  354. select toDate(formatDateTime(reg_time,'%Y-%m-%d')) dt,channel,
  355. count(distinct if(toDate(formatDateTime(reg_time,'%Y-%m-%d'))=date,user_id,NULL)) reg_order_user1,
  356. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),1)>=date,user_id,NULL)) reg_order_user2,
  357. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),2)>=date,user_id,NULL)) reg_order_user3,
  358. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),3)>=date,user_id,NULL)) reg_order_user4,
  359. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),4)>=date,user_id,NULL)) reg_order_user5,
  360. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),5)>=date,user_id,NULL)) reg_order_user6,
  361. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),6)>=date,user_id,NULL)) reg_order_user7,
  362. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),7)>=date,user_id,NULL)) reg_order_user8,
  363. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),8)>=date,user_id,NULL)) reg_order_user9,
  364. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),9)>=date,user_id,NULL)) reg_order_user10,
  365. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),10)>=date,user_id,NULL)) reg_order_user11,
  366. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),11)>=date,user_id,NULL)) reg_order_user12,
  367. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),12)>=date,user_id,NULL)) reg_order_user13,
  368. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),13)>=date,user_id,NULL)) reg_order_user14,
  369. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),14)>=date,user_id,NULL)) reg_order_user15,
  370. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),15)>=date,user_id,NULL)) reg_order_user16,
  371. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),16)>=date,user_id,NULL)) reg_order_user17,
  372. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),17)>=date,user_id,NULL)) reg_order_user18,
  373. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),18)>=date,user_id,NULL)) reg_order_user19,
  374. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),19)>=date,user_id,NULL)) reg_order_user20,
  375. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),20)>=date,user_id,NULL)) reg_order_user21,
  376. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),21)>=date,user_id,NULL)) reg_order_user22,
  377. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),22)>=date,user_id,NULL)) reg_order_user23,
  378. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),23)>=date,user_id,NULL)) reg_order_user24,
  379. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),24)>=date,user_id,NULL)) reg_order_user25,
  380. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),25)>=date,user_id,NULL)) reg_order_user26,
  381. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),26)>=date,user_id,NULL)) reg_order_user27,
  382. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),27)>=date,user_id,NULL)) reg_order_user28,
  383. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),28)>=date,user_id,NULL)) reg_order_user29,
  384. count(distinct if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),29)>=date,user_id,NULL)) reg_order_user30
  385. 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
  386. """
  387. print(sql)
  388. total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
  389. sql+=f" order by {order_by} {order} limit {(page-1)*page_size},{page_size} "
  390. data = ck.execute(sql)
  391. print(sql)
  392. print(data)
  393. key=['pitcher','date','channel','book','cost','reg_amount','roi','new_follow_user','new_follow_per_cost','order_user','order_count','order_user_per_cost',
  394. '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',
  395. 'd11','d12','d13','d14','d15','d16','d17','d18','d19','d20','d21','d22','d23','d24','d25','d26','d27','d28','d29','d30']
  396. x=get_dict_list(key,get_round(data))
  397. print(x)
  398. li=[]
  399. for i in x:
  400. di = {}
  401. for j in i:
  402. if j in ['d1','d2','d3','d4','d5','d6','d7','d8','d9','d10',
  403. 'd11','d12','d13','d14','d15','d16','d17','d18','d19','d20','d21','d22','d23','d24','d25','d26','d27','d28','d29','d30']:
  404. if i[j]:
  405. k=i[j].split(",")
  406. k[0]=round(float(k[0]),0)
  407. k[1]=round(float(k[1]),2)
  408. k[2]=round(float(k[2]),4)
  409. else:
  410. k=[0,0,0]
  411. di[j] = dict(zip(['act_user', 'act_cost_per_cost', 'act_rate'], k))
  412. else:
  413. di[j]=i[j]
  414. li.append(di)
  415. return li,total
  416. def get_channel_order_trend(channel,pitcher,start,end,page,page_size,order_by,order):
  417. ck = CkUtils()
  418. channel_op =f" and channel='{channel}' " if channel!='' else ''
  419. if pitcher != '赞象 admin':
  420. if get_channels_from_user(pitcher).__len__() == 0:
  421. pitcher_op = f" and pitcher='{pitcher}'"
  422. else:
  423. pitcher_op = f" and channel in {get_channels_from_user(pitcher)}"
  424. sql=f"""select pitcher,channel,toString(dt) date,book,toDecimal32(cost,2),toDecimal32(reg_order_amount,2),roi,new_follow_user,new_follow_per_cost,order_user,order_count,
  425. toDecimal32(order_user_per_cost,2),
  426. concat(toString(r1),',',toString(r1/cost),',', toString(a1/cost),',',toString(b1)),
  427. concat(toString(a2),',',toString(r2/cost),',', toString(a2/cost),',',toString(b2)),
  428. concat(toString(a3),',',toString(r3/cost),',', toString(a3/cost),',',toString(b3)),
  429. concat(toString(a4),',',toString(r4/cost),',', toString(a4/cost),',',toString(b4)),
  430. concat(toString(a5),',',toString(r5/cost),',', toString(a5/cost),',',toString(b5)),
  431. concat(toString(a6),',',toString(r6/cost),',', toString(a6/cost),',',toString(b6)),
  432. concat(toString(a7),',',toString(r7/cost),',', toString(a7/cost),',',toString(b7)),
  433. concat(toString(a8),',',toString(r8/cost),',', toString(a8/cost),',',toString(b8)),
  434. concat(toString(a9),',',toString(r9/cost),',', toString(a9/cost),',',toString(b9)),
  435. concat(toString(a10),',',toString(r10/cost),',', toString(a10/cost),',',toString(b10)),
  436. concat(toString(a11),',',toString(r11/cost),',', toString(a11/cost),',',toString(b11)),
  437. concat(toString(a12),',',toString(r12/cost),',', toString(a12/cost),',',toString(b12)),
  438. concat(toString(a13),',',toString(r13/cost),',', toString(a13/cost),',',toString(b13)),
  439. concat(toString(a14),',',toString(r14/cost),',', toString(a14/cost),',',toString(b14)),
  440. concat(toString(a15),',',toString(r15/cost),',', toString(a15/cost),',',toString(b15)),
  441. concat(toString(a16),',',toString(r16/cost),',', toString(a16/cost),',',toString(b16)),
  442. concat(toString(a17),',',toString(r17/cost),',', toString(a17/cost),',',toString(b17)),
  443. concat(toString(a18),',',toString(r18/cost),',', toString(a18/cost),',',toString(b18)),
  444. concat(toString(a19),',',toString(r19/cost),',', toString(a19/cost),',',toString(b19)),
  445. concat(toString(a20),',',toString(r20/cost),',', toString(a20/cost),',',toString(b20)),
  446. concat(toString(a21),',',toString(r21/cost),',', toString(a21/cost),',',toString(b21)),
  447. concat(toString(a22),',',toString(r22/cost),',', toString(a22/cost),',',toString(b22)),
  448. concat(toString(a23),',',toString(r23/cost),',', toString(a23/cost),',',toString(b23)),
  449. concat(toString(a24),',',toString(r24/cost),',', toString(a24/cost),',',toString(b24)),
  450. concat(toString(a25),',',toString(r25/cost),',', toString(a25/cost),',',toString(b25)),
  451. concat(toString(a26),',',toString(r26/cost),',', toString(a26/cost),',',toString(b26)),
  452. concat(toString(a27),',',toString(r27/cost),',', toString(a27/cost),',',toString(b27)),
  453. concat(toString(a28),',',toString(r28/cost),',', toString(a28/cost),',',toString(b28)),
  454. concat(toString(a29),',',toString(r29/cost),',', toString(a29/cost),',',toString(b29)),
  455. concat(toString(a30),',',toString(r30/cost),',', toString(a30/cost),',',toString(b30)),
  456. concat(toString(a31),',',toString(r31/cost),',', toString(a31/cost),',',toString(b31)),
  457. concat(toString(a32),',',toString(r32/cost),',', toString(a32/cost),',',toString(b32)),
  458. concat(toString(a33),',',toString(r33/cost),',', toString(a33/cost),',',toString(b33)),
  459. concat(toString(a34),',',toString(r34/cost),',', toString(a34/cost),',',toString(b34)),
  460. concat(toString(a35),',',toString(r35/cost),',', toString(a35/cost),',',toString(b35)),
  461. concat(toString(a36),',',toString(r36/cost),',', toString(a36/cost),',',toString(b36)),
  462. concat(toString(a37),',',toString(r37/cost),',', toString(a37/cost),',',toString(b37)),
  463. concat(toString(a38),',',toString(r38/cost),',', toString(a38/cost),',',toString(b38)),
  464. concat(toString(a39),',',toString(r39/cost),',', toString(a39/cost),',',toString(b39)),
  465. concat(toString(a40),',',toString(r40/cost),',', toString(a40/cost),',',toString(b40)),
  466. concat(toString(a41),',',toString(r41/cost),',', toString(a41/cost),',',toString(b41)),
  467. concat(toString(a42),',',toString(r42/cost),',', toString(a42/cost),',',toString(b42)),
  468. concat(toString(a43),',',toString(r43/cost),',', toString(a43/cost),',',toString(b43)),
  469. concat(toString(a44),',',toString(r44/cost),',', toString(a44/cost),',',toString(b44)),
  470. concat(toString(a45),',',toString(r45/cost),',', toString(a45/cost),',',toString(b45)),
  471. concat(toString(a46),',',toString(r46/cost),',', toString(a46/cost),',',toString(b46)),
  472. concat(toString(a47),',',toString(r47/cost),',', toString(a47/cost),',',toString(b47)),
  473. concat(toString(a48),',',toString(r48/cost),',', toString(a48/cost),',',toString(b48)),
  474. concat(toString(a49),',',toString(r49/cost),',', toString(a49/cost),',',toString(b49)),
  475. concat(toString(a50),',',toString(r50/cost),',', toString(a50/cost),',',toString(b50)),
  476. concat(toString(a51),',',toString(r51/cost),',', toString(a51/cost),',',toString(b51)),
  477. concat(toString(a52),',',toString(r52/cost),',', toString(a52/cost),',',toString(b52)),
  478. concat(toString(a53),',',toString(r53/cost),',', toString(a53/cost),',',toString(b53)),
  479. concat(toString(a54),',',toString(r54/cost),',', toString(a54/cost),',',toString(b54)),
  480. concat(toString(a55),',',toString(r55/cost),',', toString(a55/cost),',',toString(b55)),
  481. concat(toString(a56),',',toString(r56/cost),',', toString(a56/cost),',',toString(b56)),
  482. concat(toString(a57),',',toString(r57/cost),',', toString(a57/cost),',',toString(b57)),
  483. concat(toString(a58),',',toString(r58/cost),',', toString(a58/cost),',',toString(b58)),
  484. concat(toString(a59),',',toString(r59/cost),',', toString(a59/cost),',',toString(b59)),
  485. concat(toString(a60),',',toString(r60/cost),',', toString(a60/cost),',',toString(b60)),
  486. concat(toString(a90),',',toString(r90/cost),',', toString(a90/cost),',',toString(b90)),
  487. concat(toString(a120),',',toString(r120/cost),',', toString(a120/cost),',',toString(b120)),
  488. concat(toString(a150),',',toString(r150/cost),',', toString(a150/cost),',',toString(b150))
  489. from
  490. (
  491. select pitcher,channel,dt,book,cost,reg_order_amount,
  492. if(cost=0,0,reg_order_amount/cost) roi,
  493. follow_user new_follow_user,
  494. if(follow_user=0,0,cost/follow_user) new_follow_per_cost,
  495. reg_order_user order_user, reg_order_count order_count,
  496. if(reg_order_user=0,0,cost/reg_order_user) order_user_per_cost
  497. from dw_daily_channel where dt>='{start}' and dt<='{end}' {channel_op} {pitcher_op} and cost>0) a
  498. left outer join (
  499. select toDate(formatDateTime(reg_time,'%Y-%m-%d')) dt,channel,
  500. sum(if(toDate(formatDateTime(reg_time,'%Y-%m-%d'))=date,amount,0)) as r1,
  501. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),1)>=date,amount,0)) as r2,
  502. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),2)>=date,amount,0)) as r3,
  503. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),3)>=date,amount,0)) as r4,
  504. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),4)>=date,amount,0)) as r5,
  505. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),5)>=date,amount,0)) as r6,
  506. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),6)>=date,amount,0)) as r7,
  507. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),7)>=date,amount,0)) as r8,
  508. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),8)>=date,amount,0)) as r9,
  509. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),9)>=date,amount,0)) as r10,
  510. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),10)>=date,amount,0)) as r11,
  511. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),11)>=date,amount,0)) as r12,
  512. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),12)>=date,amount,0)) as r13,
  513. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),13)>=date,amount,0)) as r14,
  514. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),14)>=date,amount,0)) as r15,
  515. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),15)>=date,amount,0)) as r16,
  516. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),16)>=date,amount,0)) as r17,
  517. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),17)>=date,amount,0)) as r18,
  518. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),18)>=date,amount,0)) as r19,
  519. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),19)>=date,amount,0)) as r20,
  520. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),20)>=date,amount,0)) as r21,
  521. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),21)>=date,amount,0)) as r22,
  522. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),22)>=date,amount,0)) as r23,
  523. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),23)>=date,amount,0)) as r24,
  524. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),24)>=date,amount,0)) as r25,
  525. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),25)>=date,amount,0)) as r26,
  526. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),26)>=date,amount,0)) as r27,
  527. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),27)>=date,amount,0)) as r28,
  528. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),28)>=date,amount,0)) as r29,
  529. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),29)>=date,amount,0)) as r30,
  530. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),30)>=date,amount,0)) as r31,
  531. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),31)>=date,amount,0)) as r32,
  532. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),32)>=date,amount,0)) as r33,
  533. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),33)>=date,amount,0)) as r34,
  534. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),34)>=date,amount,0)) as r35,
  535. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),35)>=date,amount,0)) as r36,
  536. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),36)>=date,amount,0)) as r37,
  537. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),37)>=date,amount,0)) as r38,
  538. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),38)>=date,amount,0)) as r39,
  539. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),39)>=date,amount,0)) as r40,
  540. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),40)>=date,amount,0)) as r41,
  541. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),41)>=date,amount,0)) as r42,
  542. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),42)>=date,amount,0)) as r43,
  543. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),43)>=date,amount,0)) as r44,
  544. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),44)>=date,amount,0)) as r45,
  545. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),45)>=date,amount,0)) as r46,
  546. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),46)>=date,amount,0)) as r47,
  547. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),47)>=date,amount,0)) as r48,
  548. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),48)>=date,amount,0)) as r49,
  549. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),49)>=date,amount,0)) as r50,
  550. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),50)>=date,amount,0)) as r51,
  551. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),51)>=date,amount,0)) as r52,
  552. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),52)>=date,amount,0)) as r53,
  553. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),53)>=date,amount,0)) as r54,
  554. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),54)>=date,amount,0)) as r55,
  555. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),55)>=date,amount,0)) as r56,
  556. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),56)>=date,amount,0)) as r57,
  557. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),57)>=date,amount,0)) as r58,
  558. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),58)>=date,amount,0)) as r59,
  559. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),59)>=date,amount,0)) as r60,
  560. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),89)>=date,amount,0)) as r90,
  561. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),119)>=date,amount,0)) as r120,
  562. sum(if(addDays(toDate(formatDateTime(reg_time,'%Y-%m-%d')),149)>=date,amount,0)) as r150,
  563. 0 a1,1 b1,
  564. r2-r1 a2,if(r1=0,0,r2/r1) b2,
  565. r3-r2 a3,if(r1=0,0,r3/r1) b3,
  566. r4-r3 a4,if(r1=0,0,r4/r1) b4,
  567. r5-r4 a5,if(r1=0,0,r5/r1) b5,
  568. r6-r5 a6,if(r1=0,0,r6/r1) b6,
  569. r7-r6 a7,if(r1=0,0,r7/r1) b7,
  570. r8-r7 a8,if(r1=0,0,r8/r1) b8,
  571. r9-r8 a9,if(r1=0,0,r9/r1) b9,
  572. r10-r9 a10,if(r1=0,0,r10/r1) b10,
  573. r11-r10 a11,if(r1=0,0,r11/r1) b11,
  574. r12-r11 a12,if(r1=0,0,r12/r1) b12,
  575. r13-r12 a13,if(r1=0,0,r13/r1) b13,
  576. r14-r13 a14,if(r1=0,0,r14/r1) b14,
  577. r15-r14 a15,if(r1=0,0,r15/r1) b15,
  578. r16-r15 a16,if(r1=0,0,r16/r1) b16,
  579. r17-r16 a17,if(r1=0,0,r17/r1) b17,
  580. r18-r17 a18,if(r1=0,0,r18/r1) b18,
  581. r19-r18 a19,if(r1=0,0,r19/r1) b19,
  582. r20-r19 a20,if(r1=0,0,r20/r1) b20,
  583. r21-r20 a21,if(r1=0,0,r21/r1) b21,
  584. r22-r21 a22,if(r1=0,0,r22/r1) b22,
  585. r23-r22 a23,if(r1=0,0,r23/r1) b23,
  586. r24-r23 a24,if(r1=0,0,r24/r1) b24,
  587. r25-r24 a25,if(r1=0,0,r25/r1) b25,
  588. r26-r25 a26,if(r1=0,0,r26/r1) b26,
  589. r27-r26 a27,if(r1=0,0,r27/r1) b27,
  590. r28-r27 a28,if(r1=0,0,r28/r1) b28,
  591. r29-r28 a29,if(r1=0,0,r29/r1) b29,
  592. r30-r29 a30,if(r1=0,0,r30/r1) b30,
  593. r31-r30 a31,if(r1=0,0,r31/r1) b31,
  594. r32-r31 a32,if(r1=0,0,r32/r1) b32,
  595. r33-r32 a33,if(r1=0,0,r33/r1) b33,
  596. r34-r33 a34,if(r1=0,0,r34/r1) b34,
  597. r35-r34 a35,if(r1=0,0,r35/r1) b35,
  598. r36-r35 a36,if(r1=0,0,r36/r1) b36,
  599. r37-r36 a37,if(r1=0,0,r37/r1) b37,
  600. r38-r37 a38,if(r1=0,0,r38/r1) b38,
  601. r39-r38 a39,if(r1=0,0,r39/r1) b39,
  602. r40-r39 a40,if(r1=0,0,r40/r1) b40,
  603. r41-r40 a41,if(r1=0,0,r41/r1) b41,
  604. r42-r41 a42,if(r1=0,0,r42/r1) b42,
  605. r43-r42 a43,if(r1=0,0,r43/r1) b43,
  606. r44-r43 a44,if(r1=0,0,r44/r1) b44,
  607. r45-r44 a45,if(r1=0,0,r45/r1) b45,
  608. r46-r45 a46,if(r1=0,0,r46/r1) b46,
  609. r47-r46 a47,if(r1=0,0,r47/r1) b47,
  610. r48-r47 a48,if(r1=0,0,r48/r1) b48,
  611. r49-r48 a49,if(r1=0,0,r49/r1) b49,
  612. r50-r49 a50,if(r1=0,0,r50/r1) b50,
  613. r51-r50 a51,if(r1=0,0,r51/r1) b51,
  614. r52-r51 a52,if(r1=0,0,r52/r1) b52,
  615. r53-r52 a53,if(r1=0,0,r53/r1) b53,
  616. r54-r53 a54,if(r1=0,0,r54/r1) b54,
  617. r55-r54 a55,if(r1=0,0,r55/r1) b55,
  618. r56-r55 a56,if(r1=0,0,r56/r1) b56,
  619. r57-r56 a57,if(r1=0,0,r57/r1) b57,
  620. r58-r57 a58,if(r1=0,0,r58/r1) b58,
  621. r59-r58 a59,if(r1=0,0,r59/r1) b59,
  622. r60-r59 a60,if(r1=0,0,r60/r1) b60,
  623. r90-r60 a90,if(r1=0,0,r90/r1) b90,
  624. r120-r90 a120,if(r1=0,0,r120/r1) b120,
  625. r150-r120 a150,if(r1=0,0,r150/r1) b150
  626. from order where dt>='{start}' {channel_op} group by formatDateTime(reg_time,'%Y-%m-%d'),channel
  627. ) b on a.dt=b.dt and a.channel=b.channel
  628. """
  629. print(sql)
  630. if start==end:
  631. total=1
  632. else:
  633. total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
  634. sql += f" order by {order_by} {order} limit {(page-1)*page_size},{page_size} "
  635. data=get_round(ck.execute(sql))
  636. # print(data)
  637. def parse(str):
  638. li=str.split(',')
  639. li[0]=round(float(li[0]),2)
  640. li[1]=round(float(li[1]),4)
  641. li[2]=round(float(li[2]),4)
  642. li[3]=round(float(li[3]),2)
  643. return dict(zip(['order','roi','add','mult'],li))
  644. for i in data:
  645. for x,y in enumerate(i):
  646. if x>=12:
  647. i[x]=parse(y)
  648. print(data)
  649. key=['pitcher','channel','date','book','cost','reg_amount','roi','new_follow_user','new_follow_per_cost','order_user','order_count',
  650. 'order_user_per_cost','d1','d2','d3','d4','d5','d6','d7','d8','d9','d10','d11','d12','d13','d14','d15','d16','d17','d18',
  651. 'd19','d20','d21','d22','d23','d24','d25','d26','d27','d28','d29','d30','d31','d32','d33','d34','d35','d36','d37','d38',
  652. 'd39','d40','d41','d42','d43','d44','d45','d46','d47','d48','d49','d50','d51','d52','d53','d54','d55','d56','d57','d58','d59',
  653. 'd60','m3','m4','m5']
  654. data=get_dict_list(key,data)
  655. cost=reg_amount=roi=new_follow_user=new_follow_per_cost=order_user=order_count=order_user_per_cost=0
  656. for i in data:
  657. cost+= i['cost']
  658. reg_amount+=i['reg_amount']
  659. roi+=i['roi']
  660. new_follow_user+=i['new_follow_user']
  661. new_follow_per_cost+=i['new_follow_per_cost']
  662. order_user+=i['order_user']
  663. order_count+=i['order_count']
  664. order_user_per_cost+=i['order_user_per_cost']
  665. count= len(data)
  666. if count==0:
  667. return [],0
  668. di={'cost':round(cost,2),'reg_amount':round(reg_amount,2),'roi':round(reg_amount/cost,4), 'new_follow_user':new_follow_user,
  669. 'new_follow_per_cost':round(new_follow_per_cost/count,2),'order_user':order_user,'order_count':order_count,
  670. 'order_user_per_cost':round(order_user_per_cost/count,2),'channel':'-','date':'总计','book':'-'
  671. }
  672. data.reverse()
  673. data.append(di)
  674. data.reverse()
  675. # print(data)
  676. return data,total
  677. def get_channel_summary(channel,pitcher,page,page_size,order_by,order,state):
  678. ck = CkUtils()
  679. channel_op=f" and channel='{channel}'" if channel!='' else ''
  680. if pitcher!='赞象 admin':
  681. if get_channels_from_user(pitcher).__len__()==0:
  682. pitcher_op= f" and pitcher='{pitcher}'"
  683. else:
  684. pitcher_op=f" and channel in {get_channels_from_user(pitcher)}"
  685. sql = f"""select a.channel channel,state,type location,toString(a.start_d) start,toString(a.end_d) end,
  686. toDecimal32(total_cost,2),toDecimal32(total_amount,2),
  687. toDecimal32(profit,2),roi,follow_user,toDecimal32(follow_per_cost,2),order_user,
  688. if(follow_user=0,0,order_user/follow_user) order_tran_rate,toDecimal32(if(order_user=0,0,total_cost/order_user),2) order_tran_cost,
  689. pitcher
  690. from
  691. (
  692. select channel,
  693. min(if(cost>0,dt,null)) start_d,max(if(cost>0,dt,null) ) end_d,
  694. if(end_d>subtractDays(today(),10),'在投','停投') state,case when type ='vx' then 'MP' when type ='qq' then 'GDT' end type
  695. from dw_daily_channel_cost where 1=1 {pitcher_op} {channel_op} group by channel,type) a
  696. left outer join(
  697. select channel,sum(follow_user) follow_user,
  698. if(follow_user=0,0,sum(cost)/follow_user) follow_per_cost
  699. from dw_daily_channel where 1=1 {pitcher_op} {channel_op} group by channel) b on a.channel=b.channel
  700. left outer join(
  701. select sum(reg_order_user) order_user,channel
  702. from dw_daily_channel where 1=1 {pitcher_op} {channel_op} group by channel) c on a.channel=c.channel
  703. left outer join (
  704. select total_cost,
  705. total_amount,
  706. channel,
  707. total_amount-total_cost profit,
  708. if(total_cost=0,0,total_amount/total_cost) roi
  709. from dw_channel_daily_total where dt=today() {channel_op}) d on a.channel=d.channel
  710. left outer join (
  711. select pitcher,channel from dw_daily_channel_cost where dt=today() group by pitcher,channel
  712. ) e on a.channel=e.channel
  713. """
  714. print(sql)
  715. if channel!='':
  716. total=1
  717. else:
  718. sql+=f" having state='{state}' " if state!='' else ''
  719. total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
  720. sql += f' order by {order_by} {order} limit {(page-1)*page_size},{page_size} '
  721. print(sql)
  722. key=['channel','state','location','start','end','total_cost','total_amount','profit','roi',
  723. 'follow_user','follow_per_cost','order_user','order_tran_rate','order_tran_cost','pitcher']
  724. print(ck.execute(sql))
  725. return get_dict_list(key,get_round(ck.execute(sql))),total
  726. if __name__ == '__main__':
  727. # a=get_channel_overview('','','',1,10,'date','desc')
  728. # a=get_channel_summary('','')
  729. # a=get_channel_again_order_trend('龙鳞文学','2021-01-01','宋刚')
  730. # a=get_channel_active('玉龙书社','2020-09-15','2020-09-20',1,10,'date','desc')
  731. # a,b =get_channel_order_trend("玉龙书社",'王俊杰','2020-12-01','2020-12-12',1,5,'date','desc')
  732. # print(a)
  733. get_channels_from_user('叶静')