effhot.sql 6.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106
  1. #1.有效素材
  2. #创建表
  3. CREATE TABLE `effect_material` (
  4. `media_id` int(11) NOT NULL,
  5. `signature` varchar(50) NOT NULL,
  6. `effect_date` bigint(20) DEFAULT NULL,
  7. PRIMARY KEY (`media_id`,`signature`) /*T![clustered_index] NONCLUSTERED */
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='有效素材'
  9. #插入数据
  10. #快手
  11. REPLACE INTO application.effect_material
  12. select new.media_id,new.signature,new.effect_date from
  13. (select 2 media_id,signature,min(stat_date) effect_date from (
  14. select stat_date,signature,sum(charge) over (partition by signature order by stat_date) as sum from (
  15. select m.stat_date,n.signature,sum(m.charge) charge from
  16. (select stat_date,advertiser_id account_id,photo_id,charge from `media_api`.kuaishou_material_video_report_daily
  17. where stat_date between date_sub(${stat_date}, INTERVAL 6 DAY) and ${stat_date}
  18. group by stat_date,advertiser_id,photo_id)m
  19. INNER JOIN
  20. (select b.account_id,b.photo_id,a.signature from
  21. (select distinct signature from application.material_farst_cost_time where frast_cost_time>=date_sub(${stat_date}, INTERVAL 6 DAY)) a
  22. inner join
  23. (select account_id,photo_id,signature from `media_api`.kuaishou_video_list where date_format(stat_date,'%Y%m%d')>= date_format(date_sub(${stat_date}, INTERVAL 13 DAY),'%Y%m%d') group by account_id,photo_id,signature)b
  24. on a.signature=b.signature)n
  25. on m.account_id=n.account_id and m.photo_id=n.photo_id
  26. group by m.stat_date,n.signature)m) info where sum>=5000
  27. group by signature) new
  28. left join application.effect_material eff
  29. on new.signature=eff.signature where eff.signature is null
  30. union all
  31. ##头条
  32. select new.media_id,new.signature,new.effect_date from
  33. (select 1 media_id,signature,min(stat_date) effect_date from (
  34. select stat_date,signature,sum(cost) over (partition by signature order by stat_date) as sum from (
  35. select m.stat_date,n.signature,sum(m.cost) cost from
  36. (select stat_datetime stat_date ,advertiser_id account_id,material_id,sum(cost) cost from media_api.bytedance_material_report_daily
  37. WHERE image_mode IN (
  38. 'CREATIVE_IMAGE_MODE_VIDEO',
  39. 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',
  40. 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO') and stat_datetime between date_sub(${stat_date}, INTERVAL 6 DAY) and ${stat_date}
  41. group by stat_datetime,advertiser_id,material_id)m
  42. INNER JOIN
  43. (select b.account_id,b.material_id,a.signature from
  44. (select distinct signature from application.bytedance_material_farst_cost_time where frast_cost_time>=date_sub(${stat_date}, INTERVAL 6 DAY)) a
  45. inner join
  46. (select advertiser_id account_id,signature,material_id,filename,video_url from `media_api`.bytedance_file_video_get where date_format(create_time,'%Y%m%d')>= date_format(date_sub(${stat_date}, INTERVAL 13 DAY),'%Y%m%d') group by advertiser_id,signature,material_id,filename,video_url)b
  47. on a.signature=b.signature)n
  48. on m.account_id=n.account_id and m.material_id=n.material_id
  49. group by m.stat_date,n.signature)m) info where sum>=5000
  50. group by signature) new
  51. left join application.effect_material eff
  52. on new.signature=eff.signature where eff.signature is null;
  53. #2.爆款素材
  54. #创建表
  55. CREATE TABLE `hot_material` (
  56. `media_id` int(11) NOT NULL,
  57. `signature` varchar(50) NOT NULL,
  58. `hot_date` bigint(20) DEFAULT NULL,
  59. PRIMARY KEY (`media_id`,`signature`) /*T![clustered_index] NONCLUSTERED */
  60. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='爆款素材';
  61. #插入数据
  62. #快手
  63. REPLACE INTO application.hot_material
  64. select new.media_id,new.signature,new.hot_date from
  65. (select 2 media_id,signature,min(stat_date) hot_date from (
  66. select stat_date,signature,sum(charge) over (partition by signature order by stat_date) as sum from (
  67. select m.stat_date,n.signature,sum(m.charge) charge from
  68. (select stat_date,advertiser_id account_id,photo_id,charge from `media_api`.kuaishou_material_video_report_daily
  69. where stat_date between date_sub(${stat_date}, INTERVAL 13 DAY) and ${stat_date}
  70. group by stat_date,advertiser_id,photo_id)m
  71. INNER JOIN
  72. (select b.account_id,b.photo_id,a.signature from
  73. (select distinct signature from application.material_farst_cost_time where frast_cost_time>=date_sub(${stat_date}, INTERVAL 13 DAY)) a
  74. inner join
  75. (select account_id,photo_id,signature from `media_api`.kuaishou_video_list where date_format(stat_date,'%Y%m%d')>= date_format(date_sub(${stat_date}, INTERVAL 20 DAY),'%Y%m%d') group by account_id,photo_id,signature)b
  76. on a.signature=b.signature)n
  77. on m.account_id=n.account_id and m.photo_id=n.photo_id
  78. group by m.stat_date,n.signature)m) info where sum>=50000
  79. group by signature) new
  80. left join application.effect_material eff
  81. on new.signature=eff.signature where eff.signature is null
  82. union all
  83. ##头条
  84. select new.media_id,new.signature,new.hot_date from
  85. (select 1 media_id,signature,min(stat_date) hot_date from (
  86. select stat_date,signature,sum(cost) over (partition by signature order by stat_date) as sum from (
  87. select m.stat_date,n.signature,sum(m.cost) cost from
  88. (select stat_datetime stat_date ,advertiser_id account_id,material_id,sum(cost) cost from media_api.bytedance_material_report_daily
  89. WHERE image_mode IN (
  90. 'CREATIVE_IMAGE_MODE_VIDEO',
  91. 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',
  92. 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO') and stat_datetime between date_sub(${stat_date}, INTERVAL 13 DAY) and ${stat_date}
  93. group by stat_datetime,advertiser_id,material_id)m
  94. INNER JOIN
  95. (select b.account_id,b.material_id,a.signature from
  96. (select distinct signature from application.bytedance_material_farst_cost_time where frast_cost_time>=date_sub(${stat_date}, INTERVAL 13 DAY)) a
  97. inner join
  98. (select advertiser_id account_id,signature,material_id,filename,video_url from `media_api`.bytedance_file_video_get where date_format(create_time,'%Y%m%d')>= date_format(date_sub(${stat_date}, INTERVAL 20 DAY),'%Y%m%d') group by advertiser_id,signature,material_id,filename,video_url)b
  99. on a.signature=b.signature)n
  100. on m.account_id=n.account_id and m.material_id=n.material_id
  101. group by m.stat_date,n.signature)m) info where sum>=50000
  102. group by signature) new
  103. left join application.effect_material eff
  104. on new.signature=eff.signature where eff.signature is null;