| 12345678910111213141516171819202122232425262728293031323334353637383940 |
- 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,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 ''
- sql=f"""select
- concat(DATE_FORMAT(min(dt),'%Y/%m/%d') ,'~',DATE_FORMAT(max(dt),'%Y/%m/%d')) date,
- pitcher,book,,stage,
- 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} GROUP BY channel,book,pitcher
- {op4}"""
- print(sql)
- return getLimitData(db.dm,sql,page,page_size)
|
