HotMaterialTenThousand.py 4.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103
  1. # Author zhaohailiang
  2. # coding=utf-8
  3. # @Time : 2022/01/19 21:10 下午
  4. # @Site :
  5. # @File : HotMaterialTenThousand.py
  6. # @Software: PyCharm
  7. # @contact: zhaohailiang@c-top.com.cn
  8. # @Tel 18201631162
  9. import datetime
  10. import logging
  11. from Apietl.conn.TidbConn import TidbConn
  12. from Apietl.utlis.Utils import Utils
  13. class HotMaterialTenThousand:
  14. def __init__(self):
  15. self.conn = TidbConn.get_connection()
  16. self.date_time = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y%m%d")
  17. def taskHandler(self):
  18. try:
  19. sql = """
  20. #快手
  21. REPLACE INTO application.hot_material_ten_thousand
  22. select new.media_id, new.signature, new.hot_date
  23. from (select 2 media_id, signature, min(stat_date) hot_date
  24. from (
  25. select stat_date, signature, sum(charge) over (partition by signature order by stat_date) as sum
  26. from (
  27. select m.stat_date, n.signature, sum(m.charge) charge
  28. from (select stat_date, photo_id, charge
  29. from `media_api`.kuaishou_material_video_report_daily
  30. where stat_date >= date_format(date_sub({stat_date}, INTERVAL 13 DAY), '%Y%m%d') and stat_date<={stat_date}
  31. group by stat_date, photo_id) m
  32. INNER JOIN
  33. (select b.photo_id, a.signature
  34. from (select distinct signature
  35. from application.material_farst_cost_time
  36. where frast_cost_time >= date_format(date_sub({stat_date}, INTERVAL 13 DAY), '%Y%m%d')) a
  37. inner join
  38. (select photo_id, signature
  39. from `application`.kuaishou_video_list
  40. where stat_date >=
  41. date_format(date_sub({stat_date}, INTERVAL 20 DAY), '%Y-%m-%d')
  42. group by photo_id, signature) b
  43. on a.signature = b.signature) n
  44. on m.photo_id = n.photo_id
  45. group by m.stat_date, n.signature) m) info
  46. where sum >= 10000
  47. group by signature) new
  48. left join (select * from application.hot_material_ten_thousand where media_id = 2) eff
  49. on new.signature = eff.signature
  50. where eff.signature is null
  51. union all
  52. select new.media_id, new.signature, new.hot_date
  53. from (select 1 media_id, signature, min(stat_date) hot_date
  54. from (
  55. select stat_date, signature, sum(cost) over (partition by signature order by stat_date) as sum
  56. from (
  57. select m.stat_date, n.signature, sum(m.cost) cost
  58. from (select stat_datetime stat_date, material_id, sum(cost) cost
  59. from application.bytedance_material_video_report_daily_dw
  60. WHERE stat_datetime >= date_format(date_sub({stat_date}, INTERVAL 13 DAY), '%Y%m%d') and stat_datetime<= {stat_date}
  61. group by stat_datetime, material_id) m
  62. INNER JOIN
  63. (select b.material_id, a.signature
  64. from (select distinct signature
  65. from application.bytedance_material_farst_cost_time
  66. where frast_cost_time >= date_format(date_sub({stat_date}, INTERVAL 13 DAY), '%Y%m%d')) a
  67. inner join
  68. (select signature, material_id, filename, video_url
  69. from `application`.bytedance_video_get
  70. where create_time >=
  71. date_format(date_sub({stat_date}, INTERVAL 20 DAY), '%Y-%m-%d')
  72. group by signature, material_id) b
  73. on a.signature = b.signature) n
  74. on m.material_id = n.material_id
  75. group by m.stat_date, n.signature) m) info
  76. where sum >= 10000
  77. group by signature) new
  78. left join (select * from application.hot_material_ten_thousand where media_id = 1) eff
  79. on new.signature = eff.signature
  80. where eff.signature is null;
  81. """.format(stat_date=self.date_time)
  82. print(sql)
  83. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  84. self.conn.commit()
  85. TidbConn.conn_close(conn=self.conn)
  86. return 0
  87. except Exception as e:
  88. logging.error(e)
  89. return -1
  90. if __name__ == '__main__':
  91. status = HotMaterialTenThousand().taskHandler()
  92. if status == 0:
  93. print("作业执行成功")
  94. exit(0)
  95. else:
  96. print("作业执行失败")
  97. exit(-1)