1001296.sql 3.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101
  1. create table `jeecg-boot`.ctop_material_top_cost
  2. (
  3. account_id bigint(20),
  4. md5 varchar(100),
  5. photo_id bigint(20),
  6. material_name varchar(255),
  7. 7day_cost decimal(19, 3),
  8. primary key (account_id, md5, photo_id)
  9. ) partition by hash ( account_id )
  10. partitions 8;
  11. create table `jeecg-boot`.ctop_history_material_catch
  12. (
  13. account_id bigint(20),
  14. md5 varchar(100),
  15. photo_id bigint(20),
  16. material_name varchar(255),
  17. sum_cost decimal(19, 3),
  18. creative_num int(10),
  19. primary key (account_id, md5, photo_id)
  20. ) partition by hash ( account_id )
  21. partitions 8;
  22. truncate table `jeecg-boot`.ctop_material_top_cost;
  23. insert into `jeecg-boot`.ctop_material_top_cost(account_id, md5, photo_id, material_name,7day_cost )
  24. select a.account_id,signature,a.photo_id,c.photo_name,a.charge from
  25. (select a.* from
  26. (select account_id,signature,photo_id,sum(charge) as charge
  27. from `application`.kuaishou_material_video_report_daily_dw where
  28. stat_date >=date_format(date_sub(${stat_date}, INTERVAL 60 Day), '%Y%m%d')
  29. group by account_id, signature, photo_id)a
  30. inner join(
  31. select account_id from `jeecg-boot`.ctop_ai_kuaishou_advertiser_strategy
  32. )b
  33. on a.account_id=b.account_id)a
  34. left join
  35. (select account_id,photo_id,photo_name,channel_type,material_type from `jeecg-boot`.ctop_kuaishou_video_get )c
  36. on a.account_id=c.account_id and a.photo_id=c.photo_id;
  37. truncate table `jeecg-boot`.ctop_history_material_catch;
  38. insert into `jeecg-boot`.ctop_history_material_catch(account_id, md5, photo_id, material_name, sum_cost,creative_num)
  39. select account_id,signature,photo_id,photo_name,charge,c from (
  40. select account_id, photo_id, signature,photo_name, charge, count(1) as c
  41. from (
  42. select q.account_id, q.photo_id, q.signature, q.charge, creative_id,q.photo_name
  43. from (select m.account_id,
  44. m.photo_id,
  45. m.signature,
  46. m.charge,
  47. m.photo_name
  48. from (select t.*, s.charge
  49. from (
  50. select account_id, signature, photo_name, photo_id
  51. from `jeecg-boot`.ctop_kuaishou_video_get
  52. where stat_date >= date_sub(date_format(${stat_date},'%Y-%m-%d'), INTERVAL 60 Day)
  53. ) t
  54. inner join
  55. (
  56. select account_id, photo_id, signature, sum(charge) as charge
  57. from `application`.kuaishou_material_video_report_daily_dw
  58. where stat_date >=
  59. date_format(date_sub(${stat_date}, INTERVAL 60 Day), '%Y%m%d')
  60. group by account_id, photo_id, signature) s
  61. on t.account_id = s.account_id and t.photo_id = s.photo_id) m
  62. where
  63. m.charge < 500
  64. limit 100) q
  65. left join
  66. (
  67. select account_id, creative_id, photo_id
  68. from `jeecg-boot`.ctop_kuaishou_creative
  69. where
  70. creative_create_time >= date_sub(date_format(${stat_date},'%Y-%m-%d'), INTERVAL 60 Day)
  71. and photo_id != 0
  72. ) w
  73. on q.account_id = w.account_id and q.photo_id = w.photo_id
  74. ) t
  75. group by account_id, photo_id, signature, charge,photo_name
  76. order by c desc )t
  77. where t.c<20 ;