郭浩临时需求.sql 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175
  1. select date_format(current_date, '%Y-%m-%d') `日期`,
  2. company_name `公司名称`,
  3. project_name `项目名称`,
  4. ks_id `快手id`,
  5. cr.account_id `账户id`,
  6. material_name `素材名称`,
  7. code `素材唯一编码`,
  8. realname `运营人员`,
  9. clip_name `设计人员`,
  10. case when hc_status = 0 then '待审核' when hc_status = 1 then '审核通过' else '审核拒绝' end `汇创后台素材审核状态`,
  11. case when photo_id is null then '未推送' else '推送' end `素材推送状态`,
  12. concat('`', photo_id, '`') `视频id`,
  13. creative_create_time `素材首次关联创意时间`,
  14. creative_num `素材关联创意数`,
  15. error_num `审核拒绝创意数`
  16. from (select q.*,
  17. case when q.creative_create_time is null then 0 else s.creative_count end as creative_num,
  18. case when q.creative_create_time is null then 0 else w.creative_count end as error_num
  19. from (
  20. select code,
  21. material_name,
  22. a.status as hc_status,
  23. a.create_time as hc_create_time,
  24. clip_id,
  25. a.account_id,
  26. a.photo_id,
  27. min(b.creative_create_time) as creative_create_time
  28. from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time
  29. from (select code, material_name, status, create_time
  30. from `jeecg-boot`.ctop_material_info
  31. where create_time > date_sub('2021-10-12', INTERVAL 6 Day)) a
  32. left join(
  33. select material_id, clip_id
  34. from `jeecg-boot`.ctop_material_ascription
  35. ) b
  36. on a.code = b.material_id
  37. left join(
  38. select account_id, signature, photo_id, stat_date
  39. from ctop_kuaishou_video_get
  40. where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day)
  41. ) c
  42. on a.code = c.signature) a
  43. left join
  44. (select account_id, creative_id, photo_id, status, creative_create_time
  45. from `jeecg-boot`.ctop_kuaishou_creative
  46. where creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b
  47. on a.account_id = b.account_id and a.photo_id = b.photo_id
  48. group by a.status, material_name, code, a.create_time, clip_id, a.account_id, a.photo_id) q
  49. left join
  50. (
  51. select code,
  52. account_id,
  53. case when account_id is null then 0 else c end as creative_count
  54. from (
  55. select code,
  56. account_id,
  57. count(1) as c
  58. from (
  59. select code,
  60. material_name,
  61. a.status as hc_status,
  62. a.create_time as hc_create_time,
  63. clip_id,
  64. a.account_id,
  65. a.photo_id,
  66. b.creative_id,
  67. b.status as creative_status,
  68. b.creative_create_time as creative_create_time
  69. from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time
  70. from (select code, material_name, status, create_time
  71. from `jeecg-boot`.ctop_material_info
  72. where create_time >
  73. date_sub('2021-10-12', INTERVAL 6 Day)) a
  74. left join(
  75. select material_id, clip_id
  76. from `jeecg-boot`.ctop_material_ascription
  77. ) b
  78. on a.code = b.material_id
  79. left join(
  80. select account_id, signature, photo_id, stat_date
  81. from ctop_kuaishou_video_get
  82. where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day)
  83. ) c
  84. on a.code = c.signature) a
  85. left join
  86. (select account_id, creative_id, photo_id, status, creative_create_time
  87. from `jeecg-boot`.ctop_kuaishou_creative
  88. where creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b
  89. on a.account_id = b.account_id and a.photo_id = b.photo_id) t
  90. group by account_id, material_name, hc_status, hc_create_time, clip_id, code, photo_id) t
  91. ) s
  92. on q.account_id = s.account_id and q.code = s.code
  93. left join(
  94. select code,
  95. account_id,
  96. case when account_id is null then 0 else c end as creative_count
  97. from (
  98. select code,
  99. account_id,
  100. material_name,
  101. hc_status,
  102. hc_create_time,
  103. clip_id,
  104. photo_id,
  105. case when creative_id is null then 0 else count(1) end as c
  106. from (
  107. select code,
  108. material_name,
  109. a.status as hc_status,
  110. a.create_time as hc_create_time,
  111. clip_id,
  112. a.account_id,
  113. a.photo_id,
  114. b.creative_id,
  115. b.status as creative_status,
  116. b.creative_create_time as creative_create_time
  117. from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time
  118. from (select code, material_name, status, create_time
  119. from `jeecg-boot`.ctop_material_info
  120. where create_time > date_sub('2021-10-12', INTERVAL 6 Day)) a
  121. left join(
  122. select material_id, clip_id
  123. from `jeecg-boot`.ctop_material_ascription
  124. ) b
  125. on a.code = b.material_id
  126. left join(
  127. select account_id, signature, photo_id, stat_date
  128. from ctop_kuaishou_video_get
  129. where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day)
  130. ) c
  131. on a.code = c.signature) a
  132. left join
  133. (select account_id, creative_id, photo_id, status, creative_create_time
  134. from `jeecg-boot`.ctop_kuaishou_creative
  135. where status = 42
  136. and creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b
  137. on a.account_id = b.account_id and a.photo_id = b.photo_id) t
  138. group by account_id, code, material_name,
  139. hc_status,
  140. hc_create_time,
  141. clip_id,
  142. photo_id) t
  143. ) w
  144. on q.account_id = w.account_id and q.code = w.code) cr
  145. left join
  146. (
  147. select a.account_id, a.project_name, su.realname, c.user_id as ks_id
  148. from (select account_id, user_id, user_name, project_name from ctop_user_allocation) a
  149. left join
  150. (select account_id, user_id from ctop_kuaishou_advertiser_base_info) c
  151. on a.account_id = c.account_id
  152. left join sys_user su on su.id = a.user_id
  153. ) user
  154. on cr.account_id = user.account_id
  155. left join (
  156. select id, realname as clip_name
  157. from sys_user
  158. ) clip
  159. on cr.clip_id = clip.id
  160. left join
  161. (select user_id, company_name from user_company) com
  162. on cr.clip_id = com.user_id