data_stat_task.py 7.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151
  1. from model.DataBaseUtils import MysqlUtils,CkUtils
  2. from model.DateUtils import DateUtils
  3. from model.log import logger
  4. log=logger()
  5. db = MysqlUtils()
  6. ck = CkUtils()
  7. dt = DateUtils()
  8. from datetime import datetime
  9. from sync_to_ck_task import dw_order_channel_sync_ck
  10. def dw_daily_channel(ymd):
  11. sql="""replace into dw_daily_channel
  12. select dt,x.channel,pitcher,stage,platform,book,ifnull(order_count,0) order_count,ifnull(order_user,0),ifnull(order_amount,0),ifnull(first_order_count,0),ifnull(first_order_user,0),
  13. ifnull(first_order_amount,0),ifnull(view_count,0),ifnull(click_count,0),ifnull(follow_user,0),ifnull(cost,0) as cost from
  14. (select dt,channel,stage,pitcher,platform,book from channel_info_daily where dt='{0}' and channel!='') x
  15. left join
  16. (select channel,count(1) as order_count,count(distinct user_id) as order_user,sum(amount) as order_amount,
  17. sum(if(date_format(reg_time,'%Y-%m-%d')=date_format(date,'%Y-%m-%d'),1,0)) as first_order_count,
  18. count(distinct if(date_format(reg_time,'%Y-%m-%d')=date_format(date,'%Y-%m-%d'),user_id,''))-1 as first_order_user,
  19. sum(if(date_format(reg_time,'%Y-%m-%d')=date_format(date,'%Y-%m-%d'),amount,0)) as first_order_amount
  20. from ods_order where date='{0}' group by channel) y on x.channel=y.channel
  21. left join
  22. (select channel,sum(cost) as cost,sum(view_count) as view_count,sum(valid_click_count) as click_count,sum(from_follow_uv) as follow_user from
  23. (select account_id,cost,view_count,valid_click_count,round(valid_click_count*official_account_follow_rate,0) as from_follow_uv from daily_vx where date='{0} 00:00:00'
  24. union
  25. select account_id,cost,view_count,valid_click_count,from_follow_uv from daily_qq where date='{0} 00:00:00') a
  26. left join
  27. (select account_id,channel from channel_by_account_daily where dt='{0}') b on a.account_id=b.account_id group by channel) z on x.channel=z.channel
  28. """.format(ymd)
  29. db.quchen_text.execute(sql)
  30. def channel_by_account_daily(ymd):
  31. """返回当天消耗账户对应的公众号表"""
  32. sql="""replace into channel_by_account_daily
  33. select '{0}' as dt,a.account_id as account_id, ifnull(ifnull(b.name,a.name),'') as channel from
  34. (select account_id,name from advertiser_qq
  35. union
  36. select account_id,name from advertiser_vx
  37. ) a
  38. left join
  39. (select b.account_id,b.name from
  40. (select min(end_time) as end_time,account_id from account_change where end_time>'{0}' GROUP BY account_id) a
  41. left join account_change b on a.end_time=b.end_time and a.account_id=b.account_id) b on a.account_id=b.account_id""".format(ymd)
  42. db.quchen_text.execute(sql)
  43. def channel_info_daily(ymd):
  44. """获取公众号某天的期数,投手,平台,书籍
  45. @ return [[]]
  46. """
  47. # 获取现在的全量公众号信息
  48. sql="""select '{}' as dt,a.name ,ifnull(stage,''),ifnull(pitcher,''),ifnull(platform,''),ifnull(book,'') from (
  49. select name from advertiser_vx where name is not null group by name-- 公众号全量表
  50. union
  51. select name from advertiser_qq where name is not null group by name
  52. union
  53. select name from account_change group by name
  54. union
  55. select channel as name from pitcher_change group by channel
  56. union
  57. select name from platform_change group by name
  58. union
  59. select name from book_change group by name) a left join (
  60. select name,ifnull(stage,'') stage,ifnull(pitcher,'') pitcher,ifnull(platform,'') platform,ifnull(book,'') book from advertiser_qq where name is not null group by name,stage,pitcher,platform,book
  61. union
  62. select name,ifnull(stage,'') stage,ifnull(pitcher,'') pitcher,ifnull(platform,'') platform,ifnull(book,'') book from advertiser_vx where name is not null and name !=''
  63. ) b on a.name=b.name""".format(ymd)
  64. data=db.quchen_text.get_data_list(sql)
  65. pitcher_change=db.quchen_text.getData(
  66. "select b.channel as channel,pitcher from "
  67. "(select max(start_time) as start_time,channel from pitcher_change "
  68. " where start_time<='{}' GROUP BY channel) a"
  69. " left join pitcher_change b on a.start_time=b.start_time and a.channel=b.channel".format(ymd))
  70. platform_change=db.quchen_text.getData("select b.name as channel,current_platform as platform from (select max(change_date) as change_date,name from platform_change "
  71. "where change_date<='{}' GROUP BY name) a "
  72. "left join platform_change b on a.change_date=b.change_date and a.name=b.name".format(ymd))
  73. book_change=db.quchen_text.getData("select b.name as channel,book from (select max(start_time) as start_time,name from book_change "
  74. "where start_time<='{}' GROUP BY name) a "
  75. "left join book_change b on a.start_time=b.start_time and a.name=b.name".format(ymd))
  76. for i in data:
  77. for j in pitcher_change:
  78. if i[1]==j[0]:
  79. i[3]=j[1]
  80. for k in platform_change:
  81. if i[1]==k[0]:
  82. i[4]=k[1]
  83. for h in book_change:
  84. if i[1]==h[0]:
  85. i[5]=h[1]
  86. print(data)
  87. insert_sql="replace into channel_info_daily values (%s,%s,%s,%s,%s,%s) "
  88. db.quchen_text.executeMany(insert_sql,data)
  89. def ods_order(dt):
  90. sql=""" replace into ods_order
  91. select
  92. case platform when '掌中云' then DATE_FORMAT(STR_TO_DATE(order_time,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d')
  93. when '掌读' then from_unixtime(order_time, '%Y-%m-%d')
  94. ELSE left(order_time,10) end date,
  95. stage,platform,channel,channel_id,user_id,
  96. case when platform='掌中云' then DATE_FORMAT(STR_TO_DATE(order_time,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s')
  97. when platform='掌读' then from_unixtime(order_time, '%Y-%m-%d %H:%i:%s')
  98. ELSE order_time end order_time,
  99. case when platform='掌中云' then DATE_FORMAT(STR_TO_DATE(reg_time,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s')
  100. when platform='掌读' then from_unixtime(reg_time, '%Y-%m-%d %H:%i:%s')
  101. ELSE reg_time end reg_time,
  102. amount,from_novel,order_id from `order` where date=UNIX_TIMESTAMP('{}')
  103. """.format(dt)
  104. db.quchen_text.execute(sql)
  105. def order_account_text():
  106. db.quchen_text.execute("truncate order_account_text")
  107. with open('./wending_account_config.csv',encoding='utf-8') as f:
  108. for i in f.readlines():
  109. db.quchen_text.execute("insert into order_account_text(platform,text) values ('文鼎','{}')".format(i))
  110. if __name__ == '__main__':
  111. ods_order('2019-03-18')
  112. for i in dt.getDateLists('2019-03-18','2020-12-17'):
  113. print(i)
  114. # ods_order(i)
  115. # channel_by_account_daily(i)
  116. # channel_info_daily(i)
  117. dw_daily_channel(i)
  118. dw_order_channel_sync_ck(i)