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
|