AppKuaishouAccountTodayBaseInfoD.py 3.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384
  1. # Author zhaohailiang
  2. # coding=utf-8
  3. # @Time : 2022/01/19 21:10 下午
  4. # @Site :
  5. # @File : AppKuaishouAccountTodayBaseInfoD.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 AppKuaishouAccountTodayBaseInfoD:
  14. def __init__(self):
  15. self.conn = TidbConn.get_connection()
  16. self.date_time = (datetime.date.today() + datetime.timedelta(0)).strftime("%Y%m%d")
  17. def taskHandler(self):
  18. try:
  19. sql = """
  20. replace into application.app_kuaishou_account_today_base_info_d(stat_date, hour, project_id, account_id, user_id,
  21. user_name, leader_id, leader_name, new_all_unit_num,
  22. new_unit_num, new_trush_unit_num, account_name,
  23. project_name)
  24. select stat_date,
  25. hour,
  26. project_id,
  27. a.advertiser_id,
  28. info.user_id,
  29. user.realname as user_name,
  30. user.leader_id,
  31. leader.realname,
  32. all_unit_num,
  33. unit_num,
  34. trush_unit_num,
  35. auth_name as acount_name,
  36. pro.project_name
  37. from (select stat_date,
  38. advertiser_id,
  39. hour,
  40. count(distinct unit_id) as all_unit_num,
  41. sum(unit_num) as unit_num,
  42. sum(trush_unit_num) as trush_unit_num
  43. from (
  44. select date_format(create_time, '%Y%m%d') as stat_date,
  45. advertiser_id,
  46. unit_id,
  47. create_time,
  48. hour(create_time) as hour,
  49. if(unit_source = 0, 1, 0) as unit_num,
  50. if(unit_source = 1, 1, 0) as trush_unit_num
  51. from media_api.kuaishou_ad_unit_list
  52. where date_format(create_time, '%Y%m%d') = {date_time}) t
  53. group by advertiser_id, hour) a
  54. left join `jeecg-boot`.ctop_user_allocation info
  55. on a.advertiser_id = info.account_id
  56. left join `jeecg-boot`.ctop_project pro
  57. on info.project_id = pro.id
  58. left join `jeecg-boot`.sys_user user
  59. on info.user_id = user.id
  60. left join `jeecg-boot`.sys_user leader
  61. on user.leader_id = leader.id
  62. """.format(date_time=self.date_time)
  63. print(sql)
  64. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  65. self.conn.commit()
  66. TidbConn.conn_close(conn=self.conn)
  67. return 0
  68. except Exception as e:
  69. logging.error(e)
  70. return -1
  71. if __name__ == '__main__':
  72. status = AppKuaishouAccountTodayBaseInfoD().taskHandler()
  73. if status == 0:
  74. print("作业执行成功")
  75. exit(0)
  76. else:
  77. print("作业执行失败")
  78. exit(-1)