--明细
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


