对比消耗.sql 2.0 KB

123456789101112131415161718192021222324252627282930313233
  1. select project_name,thrteenday.signature,thrteenday.video_name,photo_url, sum30charge, sum3charge
  2. from (select a.signature, video_name, round(sum(charge), 2) as sum30charge,photo_url,project_name
  3. from (select signature, frast_cost_time
  4. from application.material_farst_cost_time
  5. where frast_cost_time >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
  6. and frast_cost_time<=date_format(date_sub(20220706, interval 3 day), '%Y%m%d')) a
  7. left join(
  8. select stat_date, signature, video_name,photo_url,project_name, sum(charge) as charge
  9. from application.kuaishou_material_video_report_daily_dw
  10. where stat_date >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
  11. group by signature, video_name, stat_date
  12. ) b
  13. on a.signature = b.signature
  14. where stat_date between frast_cost_time and date_format(date_sub(frast_cost_time, interval -30 day), '%Y%m%d')
  15. group by a.signature) thrteenday
  16. left join(
  17. select a.signature, video_name, round(sum(charge), 2) as sum3charge
  18. from (select signature, frast_cost_time
  19. from application.material_farst_cost_time
  20. where frast_cost_time >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
  21. and frast_cost_time<=date_format(date_sub(20220706, interval 3 day), '%Y%m%d')) a
  22. left join(
  23. select stat_date, signature, video_name, sum(charge) as charge
  24. from application.kuaishou_material_video_report_daily_dw
  25. where stat_date >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
  26. group by signature, video_name, stat_date
  27. ) b
  28. on a.signature = b.signature
  29. where stat_date between frast_cost_time and date_format(date_sub(frast_cost_time, interval -3 day), '%Y%m%d')
  30. group by a.signature
  31. ) thrnday
  32. on thrteenday.signature = thrnday.signature
  33. where sum30charge = sum3charge