| 123456789101112131415161718192021222324252627282930313233343536373839 |
- 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,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 ''
- sql=f"""select
- concat(min(dt),'~',max(dt)) date,
- pitcher,book,
- 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) frist_roi,
- round(sum(reg_order_amount)/sum(cost),4) roi
- from dw_channel where 1=1 {op1} {op2} {op3} GROUP BY channel,book,pitcher
- {op4}"""
- print(sql)
- return getLimitData(db.dm,sql,page,page_size)
|
