| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287 | 
							- --明细
 
- select concat(:date,'-',:enddate)                                                                                   as `日期`,
 
-        channle.project_name                                                                           as `项目名称`,
 
-        round(ifnull(sum(channle.in_charge), 0), 2)                                                    as `内部素材消耗`,
 
-        ifnull(sum(inner_num), 0)                                                                      as `内部消耗素材数`,
 
-        round(ifnull(sum(channle.in_charge) / sum(inner_num), 0), 2)                                   as `内部素材平均消耗`,
 
-        ifnull(sum(inner_first_cost_num), 0)                                                           as `内部昨日素材上新数`,
 
-        round(ifnull(sum(inner_first_cost_charge) / sum(inner_first_cost_num), 0), 2)                  as `内部昨日素材平均消耗`,
 
-        round(ifnull(sum(out_charge), 0), 2)                                                           as `外部素材消耗`,
 
-        ifnull(sum(outer_num), 0)                                                                      as `外部消耗素材数`,
 
-        round(ifnull(sum(out_charge) / sum(outer_num), 0), 2)                                          as `外部素材平均消耗`,
 
-        sum(first_cost_num)                                                                            as `外部昨日素材上新数`,
 
-        round(ifnull(sum(first_cost_charge) / sum(first_cost_num), 0), 2)                              as `外部昨日素材平均消耗`,
 
-        round(ifnull(sum(sum.sum_charge), 0), 2)                                                       as `总消耗`,
 
-        concat(round(ifnull(sum(channle.in_charge) / sum(sum.sum_charge) * 100, 0), 2), '%')           as `内部渗透率`,
 
-        concat(round(ifnull((sum(channle.in_charge) / sum(sum.sum_charge) -
 
-                             sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) /
 
-                            (sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) * 100, 0), 2), '%') as `环比`
 
- from (select project_id,
 
-              project_name,
 
-              count(distinct a.signature)                 as inner_num,
 
-              sum(charge)                                 as in_charge,
 
-              count(distinct c.signature)                 as inner_first_cost_num,
 
-              sum(if(c.signature is not null, charge, 0)) as inner_first_cost_charge
 
-       from application.kuaishou_material_video_report_daily_dw a
 
-                inner join (
 
-           select account_id
 
-           from `jeecg-boot`.ctop_user_allocation
 
-           where user_id in (
 
-               select user_id
 
-               from `jeecg-boot`.user_company
 
-               where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
 
-           )
 
-       ) b
 
-                           on a.account_id = b.account_id
 
-                left join (select signature
 
-                           from application.material_farst_cost_time
 
-                           where frast_cost_time = date_format(:date, '%Y%m%d')) c
 
-                          on a.signature = c.signature
 
-       where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
 
-         and clip_company_id in
 
-             ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
 
-              '1648c7ceb4d449a9a13fbed7a8d9976c')
 
-         and channel_name = '内部'
 
-         and a.charge > 0
 
-       group by channel_name, project_id, project_name) channle
 
-          left join(
 
-     select project_id,
 
-            project_name,
 
-            sum(charge) as sum_charge
 
-     from application.kuaishou_material_video_report_daily_dw a
 
-              inner join (
 
-         select account_id
 
-         from `jeecg-boot`.ctop_user_allocation
 
-         where user_id in (
 
-             select user_id
 
-             from `jeecg-boot`.user_company
 
-             where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
 
-         )
 
-     ) b
 
-                         on a.account_id = b.account_id
 
-     where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
 
-       and a.charge > 0
 
-     group by project_id, project_name
 
- ) sum
 
-                   on channle.project_id = sum.project_id
 
-          left join(
 
-     select project_id,
 
-            project_name,
 
-            count(distinct a.signature)                 as outer_num,
 
-            sum(charge)                                 as out_charge,
 
-            count(distinct c.signature)                 as first_cost_num,
 
-            sum(if(c.signature is not null, charge, 0)) as first_cost_charge
 
-     from application.kuaishou_material_video_report_daily_dw a
 
-              inner join (
 
-         select account_id
 
-         from `jeecg-boot`.ctop_user_allocation
 
-         where user_id in (
 
-             select user_id
 
-             from `jeecg-boot`.user_company
 
-             where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
 
-         )
 
-     ) b
 
-                         on a.account_id = b.account_id
 
-              left join(select signature
 
-                        from application.material_farst_cost_time
 
-                        where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c
 
-                       on a.signature = c.signature
 
-     where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
 
-       and a.charge > 0
 
-       and (channel_type = 1 or channel_name = '素造')
 
-     group by channel_name, project_id, project_name
 
- ) outc
 
-                   on channle.project_id = outc.project_id
 
-          left join (select project_id,
 
-                            project_name,
 
-                            sum(charge) as in_charge
 
-                     from application.kuaishou_material_video_report_daily_dw a
 
-                              inner join (
 
-                         select account_id
 
-                         from `jeecg-boot`.ctop_user_allocation
 
-                         where user_id in (
 
-                             select user_id
 
-                             from `jeecg-boot`.user_company
 
-                             where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
 
-                         )
 
-                     ) b
 
-                                         on a.account_id = b.account_id
 
-                     where a.stat_date  between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
 
-                       and clip_company_id in
 
-                           ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
 
-                            '1648c7ceb4d449a9a13fbed7a8d9976c')
 
-                       and channel_name = '内部'
 
-                     group by channel_name, project_id, project_name) channle_yes
 
-                    on channle.project_id = channle_yes.project_id
 
-          left join(
 
-     select project_id,
 
-            project_name,
 
-            sum(charge) as sum_charge
 
-     from application.kuaishou_material_video_report_daily_dw a
 
-              inner join (
 
-         select account_id
 
-         from `jeecg-boot`.ctop_user_allocation
 
-         where user_id in (
 
-             select user_id
 
-             from `jeecg-boot`.user_company
 
-             where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
 
-         )
 
-     ) b
 
-                         on a.account_id = b.account_id
 
-     where a.stat_date  between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
 
-     group by project_id, project_name
 
- ) yes_sum
 
-                   on channle.project_id = yes_sum.project_id
 
- group by channle.project_name
 
- order by `总消耗` desc
 
- --汇总
 
- select concat(:date,'-',:enddate)                                                                                   as `日期`,
 
-        '汇总'                                                                          as `项目名称`,
 
-        round(ifnull(sum(channle.in_charge), 0), 2)                                                    as `内部素材消耗`,
 
-        ifnull(sum(inner_num), 0)                                                                      as `内部消耗素材数`,
 
-        round(ifnull(sum(channle.in_charge) / sum(inner_num), 0), 2)                                   as `内部素材平均消耗`,
 
-        ifnull(sum(inner_first_cost_num), 0)                                                           as `内部昨日素材上新数`,
 
-        round(ifnull(sum(inner_first_cost_charge) / sum(inner_first_cost_num), 0), 2)                  as `内部昨日素材平均消耗`,
 
-        round(ifnull(sum(out_charge), 0), 2)                                                           as `外部素材消耗`,
 
-        ifnull(sum(outer_num), 0)                                                                      as `外部消耗素材数`,
 
-        round(ifnull(sum(out_charge) / sum(outer_num), 0), 2)                                          as `外部素材平均消耗`,
 
-        sum(first_cost_num)                                                                            as `外部昨日素材上新数`,
 
-        round(ifnull(sum(first_cost_charge) / sum(first_cost_num), 0), 2)                              as `外部昨日素材平均消耗`,
 
-        round(ifnull(sum(sum.sum_charge), 0), 2)                                                       as 总消耗,
 
-        concat(round(ifnull(sum(channle.in_charge) / sum(sum.sum_charge) * 100, 0), 2), '%')           as `内部渗透率`,
 
-        concat(round(ifnull((sum(channle.in_charge) / sum(sum.sum_charge) -
 
-                             sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) /
 
-                            (sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) * 100, 0), 2), '%') as `环比`
 
- from (select project_id,
 
-              project_name,
 
-              count(distinct a.signature)                 as inner_num,
 
-              sum(charge)                                 as in_charge,
 
-              count(distinct c.signature)                 as inner_first_cost_num,
 
-              sum(if(c.signature is not null, charge, 0)) as inner_first_cost_charge
 
-       from application.kuaishou_material_video_report_daily_dw a
 
-                inner join (
 
-           select account_id
 
-           from `jeecg-boot`.ctop_user_allocation
 
-           where user_id in (
 
-               select user_id
 
-               from `jeecg-boot`.user_company
 
-               where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
 
-           )
 
-       ) b
 
-                           on a.account_id = b.account_id
 
-                left join (select signature
 
-                           from application.material_farst_cost_time
 
-                           where frast_cost_time  between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c
 
-                          on a.signature = c.signature
 
-       where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
 
-         and clip_company_id in
 
-             ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
 
-              '1648c7ceb4d449a9a13fbed7a8d9976c')
 
-         and channel_name = '内部'
 
-         and a.charge > 0
 
-       group by channel_name, project_id, project_name) channle
 
-          left join(
 
-     select project_id,
 
-            project_name,
 
-            sum(charge) as sum_charge
 
-     from application.kuaishou_material_video_report_daily_dw a
 
-              inner join (
 
-         select account_id
 
-         from `jeecg-boot`.ctop_user_allocation
 
-         where user_id in (
 
-             select user_id
 
-             from `jeecg-boot`.user_company
 
-             where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
 
-         )
 
-     ) b
 
-                         on a.account_id = b.account_id
 
-     where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
 
-       and a.charge > 0
 
-     group by project_id, project_name
 
- ) sum
 
-                   on channle.project_id = sum.project_id
 
-          left join(
 
-     select project_id,
 
-            project_name,
 
-            count(distinct a.signature)                 as outer_num,
 
-            sum(charge)                                 as out_charge,
 
-            count(distinct c.signature)                 as first_cost_num,
 
-            sum(if(c.signature is not null, charge, 0)) as first_cost_charge
 
-     from application.kuaishou_material_video_report_daily_dw a
 
-              inner join (
 
-         select account_id
 
-         from `jeecg-boot`.ctop_user_allocation
 
-         where user_id in (
 
-             select user_id
 
-             from `jeecg-boot`.user_company
 
-             where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
 
-         )
 
-     ) b
 
-                         on a.account_id = b.account_id
 
-              left join(select signature
 
-                        from application.material_farst_cost_time
 
-                        where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c
 
-                       on a.signature = c.signature
 
-     where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
 
-       and a.charge > 0
 
-       and (channel_type = 1 or channel_name = '素造')
 
-     group by channel_name, project_id, project_name
 
- ) outc
 
-                   on channle.project_id = outc.project_id
 
-          left join (select project_id,
 
-                            project_name,
 
-                            sum(charge) as in_charge
 
-                     from application.kuaishou_material_video_report_daily_dw a
 
-                              inner join (
 
-                         select account_id
 
-                         from `jeecg-boot`.ctop_user_allocation
 
-                         where user_id in (
 
-                             select user_id
 
-                             from `jeecg-boot`.user_company
 
-                             where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
 
-                         )
 
-                     ) b
 
-                                         on a.account_id = b.account_id
 
-                     where a.stat_date  between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
 
-                       and clip_company_id in
 
-                           ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
 
-                            '1648c7ceb4d449a9a13fbed7a8d9976c')
 
-                       and channel_name = '内部'
 
-                     group by channel_name, project_id, project_name) channle_yes
 
-                    on channle.project_id = channle_yes.project_id
 
-          left join(
 
-     select project_id,
 
-            project_name,
 
-            sum(charge) as sum_charge
 
-     from application.kuaishou_material_video_report_daily_dw a
 
-              inner join (
 
-         select account_id
 
-         from `jeecg-boot`.ctop_user_allocation
 
-         where user_id in (
 
-             select user_id
 
-             from `jeecg-boot`.user_company
 
-             where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
 
-         )
 
-     ) b
 
-                         on a.account_id = b.account_id
 
-     where a.stat_date  between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
 
-     group by project_id, project_name
 
- ) yes_sum
 
-                   on channle.project_id = yes_sum.project_id
 
 
  |