123456789101112131415161718192021222324252627282930313233 |
- select project_name,thrteenday.signature,thrteenday.video_name,photo_url, sum30charge, sum3charge
- from (select a.signature, video_name, round(sum(charge), 2) as sum30charge,photo_url,project_name
- from (select signature, frast_cost_time
- from application.material_farst_cost_time
- where frast_cost_time >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
- and frast_cost_time<=date_format(date_sub(20220706, interval 3 day), '%Y%m%d')) a
- left join(
- select stat_date, signature, video_name,photo_url,project_name, sum(charge) as charge
- from application.kuaishou_material_video_report_daily_dw
- where stat_date >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
- group by signature, video_name, stat_date
- ) b
- on a.signature = b.signature
- where stat_date between frast_cost_time and date_format(date_sub(frast_cost_time, interval -30 day), '%Y%m%d')
- group by a.signature) thrteenday
- left join(
- select a.signature, video_name, round(sum(charge), 2) as sum3charge
- from (select signature, frast_cost_time
- from application.material_farst_cost_time
- where frast_cost_time >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
- and frast_cost_time<=date_format(date_sub(20220706, interval 3 day), '%Y%m%d')) a
- left join(
- select stat_date, signature, video_name, sum(charge) as charge
- from application.kuaishou_material_video_report_daily_dw
- where stat_date >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
- group by signature, video_name, stat_date
- ) b
- on a.signature = b.signature
- where stat_date between frast_cost_time and date_format(date_sub(frast_cost_time, interval -3 day), '%Y%m%d')
- group by a.signature
- ) thrnday
- on thrteenday.signature = thrnday.signature
- where sum30charge = sum3charge
|