| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175 | 
							- select date_format(current_date, '%Y-%m-%d')                                             `日期`,
 
-        company_name                                                                      `公司名称`,
 
-        project_name                                                                      `项目名称`,
 
-        ks_id                                                                             `快手id`,
 
-        cr.account_id                                                                     `账户id`,
 
-        material_name                                                                     `素材名称`,
 
-        code                                                                              `素材唯一编码`,
 
-        realname                                                                          `运营人员`,
 
-        clip_name                                                                         `设计人员`,
 
-        case when hc_status = 0 then '待审核' when hc_status = 1 then '审核通过' else '审核拒绝' end `汇创后台素材审核状态`,
 
-        case when photo_id is null then '未推送' else '推送' end                               `素材推送状态`,
 
-        concat('`', photo_id, '`')                                                        `视频id`,
 
-        creative_create_time                                                              `素材首次关联创意时间`,
 
-        creative_num                                                                      `素材关联创意数`,
 
-        error_num                                                                         `审核拒绝创意数`
 
- from (select q.*,
 
-              case when q.creative_create_time is null then 0 else s.creative_count end as creative_num,
 
-              case when q.creative_create_time is null then 0 else w.creative_count end as error_num
 
-       from (
 
-                select code,
 
-                       material_name,
 
-                       a.status                    as hc_status,
 
-                       a.create_time               as hc_create_time,
 
-                       clip_id,
 
-                       a.account_id,
 
-                       a.photo_id,
 
-                       min(b.creative_create_time) as creative_create_time
 
-                from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time
 
-                      from (select code, material_name, status, create_time
 
-                            from `jeecg-boot`.ctop_material_info
 
-                            where create_time > date_sub('2021-10-12', INTERVAL 6 Day)) a
 
-                               left join(
 
-                          select material_id, clip_id
 
-                          from `jeecg-boot`.ctop_material_ascription
 
-                      ) b
 
-                                        on a.code = b.material_id
 
-                               left join(
 
-                          select account_id, signature, photo_id, stat_date
 
-                          from ctop_kuaishou_video_get
 
-                          where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day)
 
-                      ) c
 
-                                        on a.code = c.signature) a
 
-                         left join
 
-                     (select account_id, creative_id, photo_id, status, creative_create_time
 
-                      from `jeecg-boot`.ctop_kuaishou_creative
 
-                      where creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b
 
-                     on a.account_id = b.account_id and a.photo_id = b.photo_id
 
-                group by a.status, material_name, code, a.create_time, clip_id, a.account_id, a.photo_id) q
 
-                left join
 
-            (
 
-                select code,
 
-                       account_id,
 
-                       case when account_id is null then 0 else c end as creative_count
 
-                from (
 
-                         select code,
 
-                                account_id,
 
-                                count(1) as c
 
-                         from (
 
-                                  select code,
 
-                                         material_name,
 
-                                         a.status               as hc_status,
 
-                                         a.create_time          as hc_create_time,
 
-                                         clip_id,
 
-                                         a.account_id,
 
-                                         a.photo_id,
 
-                                         b.creative_id,
 
-                                         b.status               as creative_status,
 
-                                         b.creative_create_time as creative_create_time
 
-                                  from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time
 
-                                        from (select code, material_name, status, create_time
 
-                                              from `jeecg-boot`.ctop_material_info
 
-                                              where create_time >
 
-                                                    date_sub('2021-10-12', INTERVAL 6 Day)) a
 
-                                                 left join(
 
-                                            select material_id, clip_id
 
-                                            from `jeecg-boot`.ctop_material_ascription
 
-                                        ) b
 
-                                                          on a.code = b.material_id
 
-                                                 left join(
 
-                                            select account_id, signature, photo_id, stat_date
 
-                                            from ctop_kuaishou_video_get
 
-                                            where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day)
 
-                                        ) c
 
-                                                          on a.code = c.signature) a
 
-                                           left join
 
-                                       (select account_id, creative_id, photo_id, status, creative_create_time
 
-                                        from `jeecg-boot`.ctop_kuaishou_creative
 
-                                        where creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b
 
-                                       on a.account_id = b.account_id and a.photo_id = b.photo_id) t
 
-                         group by account_id, material_name, hc_status, hc_create_time, clip_id, code, photo_id) t
 
-            ) s
 
-            on q.account_id = s.account_id and q.code = s.code
 
-                left join(
 
-           select code,
 
-                  account_id,
 
-                  case when account_id is null then 0 else c end as creative_count
 
-           from (
 
-                    select code,
 
-                           account_id,
 
-                           material_name,
 
-                           hc_status,
 
-                           hc_create_time,
 
-                           clip_id,
 
-                           photo_id,
 
-                           case when creative_id is null then 0 else  count(1) end as c
 
-                    from (
 
-                             select code,
 
-                                    material_name,
 
-                                    a.status               as hc_status,
 
-                                    a.create_time          as hc_create_time,
 
-                                    clip_id,
 
-                                    a.account_id,
 
-                                    a.photo_id,
 
-                                    b.creative_id,
 
-                                    b.status               as creative_status,
 
-                                    b.creative_create_time as creative_create_time
 
-                             from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time
 
-                                   from (select code, material_name, status, create_time
 
-                                         from `jeecg-boot`.ctop_material_info
 
-                                         where create_time > date_sub('2021-10-12', INTERVAL 6 Day)) a
 
-                                            left join(
 
-                                       select material_id, clip_id
 
-                                       from `jeecg-boot`.ctop_material_ascription
 
-                                   ) b
 
-                                                     on a.code = b.material_id
 
-                                            left join(
 
-                                       select account_id, signature, photo_id, stat_date
 
-                                       from ctop_kuaishou_video_get
 
-                                       where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day)
 
-                                   ) c
 
-                                                     on a.code = c.signature) a
 
-                                      left join
 
-                                  (select account_id, creative_id, photo_id, status, creative_create_time
 
-                                   from `jeecg-boot`.ctop_kuaishou_creative
 
-                                   where status = 42
 
-                                     and creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b
 
-                                  on a.account_id = b.account_id and a.photo_id = b.photo_id) t
 
-                    group by account_id, code, material_name,
 
-                             hc_status,
 
-                             hc_create_time,
 
-                             clip_id,
 
-                             photo_id) t
 
-       ) w
 
-                         on q.account_id = w.account_id and q.code = w.code) cr
 
-          left join
 
-      (
 
-          select a.account_id, a.project_name, su.realname, c.user_id as ks_id
 
-          from (select account_id, user_id, user_name, project_name from ctop_user_allocation) a
 
-                   left join
 
-                   (select account_id, user_id from ctop_kuaishou_advertiser_base_info) c
 
-                   on a.account_id = c.account_id
 
-                   left join sys_user su on su.id = a.user_id
 
-      ) user
 
-      on cr.account_id = user.account_id
 
-          left join (
 
-     select id, realname as clip_name
 
-     from sys_user
 
- ) clip
 
-                    on cr.clip_id = clip.id
 
-          left join
 
-          (select user_id, company_name from user_company) com
 
-          on cr.clip_id = com.user_id
 
 
  |