头条大盘.sql 7.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176
  1. -- 运营
  2. select :date as date,
  3. realname as user_name,
  4. count as account_num,
  5. cost,
  6. concat(round((cost-yes_week_cost)/yes_week_cost*100,2),'%') as cost_rate,
  7. effect_num,
  8. new_campaign_num,
  9. new_ad_num,
  10. quarter_cost
  11. from (
  12. select user_id,count(1) as count,sum(cost) as cost from (
  13. select user_id, account_id, sum(cost) as cost
  14. from application.bytedance_advertiser_report_daily_dw
  15. where stat_datetime between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
  16. and cost > 0
  17. and company_id='d57fecdcf7a94d009736d9c850731582'
  18. and user_id in(
  19. select id from `jeecg-boot`.sys_user where leader_id='c52551687c524a96a3904658a35f0f26'
  20. union all
  21. select id from `jeecg-boot`.sys_user where id='c52551687c524a96a3904658a35f0f26'
  22. )
  23. group by user_id,account_id)t
  24. group by user_id) a
  25. left join (
  26. select user_id,
  27. sum(cost) as yes_week_cost
  28. from application.bytedance_advertiser_report_daily_dw
  29. where stat_datetime between date_format(date_sub(:date, interval 13 day), '%Y%m%d') and date_format(date_sub(:date, interval 7 day), '%Y%m%d')
  30. and cost > 0
  31. group by user_id
  32. ) b
  33. on a.user_id = b.user_id
  34. left join (
  35. select user_id,
  36. sum(new_effect_num) as effect_num,
  37. sum(new_campaign_num) as new_campaign_num,
  38. sum(new_ad_num) as new_ad_num
  39. from application.app_bytedance_account_base_info_d
  40. where stat_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
  41. group by user_id
  42. ) c
  43. on a.user_id = c.user_id
  44. left join (
  45. select user_id,
  46. sum(cost) as quarter_cost
  47. from application.app_bytedance_advertiser_report_busi_m
  48. where stat_mon between case
  49. when quarter(date_sub(now(), interval 1 day)) = 1 then 202201
  50. when quarter(date_sub(now(), interval 1 day)) = 2 then 202204
  51. when quarter(date_sub(now(), interval 1 day)) = 3 then 202207
  52. when quarter(date_sub(now(), interval 1 day)) = 4 then 202210
  53. end
  54. and
  55. case
  56. when quarter(date_sub(now(), interval 1 day)) = 1 then 202203
  57. when quarter(date_sub(now(), interval 1 day)) = 2 then 202206
  58. when quarter(date_sub(now(), interval 1 day)) = 3 then 202209
  59. when quarter(date_sub(now(), interval 1 day)) = 4 then 202212
  60. end
  61. group by user_id
  62. ) d
  63. on a.user_id = d.user_id
  64. left join `jeecg-boot`.sys_user user
  65. on a.user_id=user.id
  66. -- 项目
  67. select :date as date,
  68. project_name,
  69. account_count,
  70. user_count,
  71. a.cost,
  72. concat(round(ifnull((a.cost - c.cost) / (c.cost) * 100, 2), 0), '%') as cost_rate,
  73. d.tb_rate,
  74. e.hot_num
  75. from (select project_id, project_name, count(1) as account_count, sum(cost) as cost
  76. from (
  77. select project_id, project_name, account_id, sum(cost) as cost
  78. from application.bytedance_advertiser_report_daily_dw
  79. where stat_datetime between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
  80. and cost > 0
  81. and company_id = 'd57fecdcf7a94d009736d9c850731582'
  82. and user_id in(
  83. select id from `jeecg-boot`.sys_user where leader_id='c52551687c524a96a3904658a35f0f26'
  84. union all
  85. select id from `jeecg-boot`.sys_user where id='c52551687c524a96a3904658a35f0f26'
  86. )
  87. group by project_id, account_id) t
  88. group by project_id) a
  89. left join
  90. (select project_id, count(1) as user_count
  91. from (
  92. select project_id, user_id
  93. from `jeecg-boot`.ctop_user_allocation
  94. group by project_id, user_id) t
  95. group by project_id) b
  96. on a.project_id = b.project_id
  97. left join(
  98. select project_id, sum(cost) as cost
  99. from application.bytedance_advertiser_report_daily_dw
  100. where stat_datetime between date_format(date_sub(:date, interval 13 day), '%Y%m%d') and date_format(date_sub(:date, interval 7 day), '%Y%m%d')
  101. and cost > 0
  102. and company_id = 'd57fecdcf7a94d009736d9c850731582'
  103. group by project_id
  104. ) c
  105. on a.project_id = c.project_id
  106. left join (
  107. select a.project_id, concat(round((sum(a.cost) - sum(b.cost)) / sum(b.cost) * 100, 2), '%') as tb_rate
  108. from application.bytedance_advertiser_report_daily_dw a
  109. left join application.bytedance_advertiser_report_daily_dw b
  110. on a.project_id = b.project_id
  111. where a.stat_datetime = date_format(:date, '%Y%m%d')
  112. and b.stat_datetime = date_format(date_sub(:date, interval 6 day), '%Y%m%d')
  113. group by a.project_id,b.project_id
  114. ) d
  115. on a.project_id = d.project_id
  116. left join (
  117. select project_id, sum(new_hot_num) as hot_num
  118. from application.app_bytedance_account_base_info_d
  119. where stat_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
  120. group by project_id
  121. ) e
  122. on a.project_id = e.project_id
  123. -- 账户
  124. select :date as date,
  125. account_name,
  126. a.account_id,
  127. realname,
  128. cost,
  129. c.cost_rate,
  130. b.tb_rate
  131. from (select account_id, account_name, user_id, cost
  132. from application.bytedance_advertiser_report_daily_dw
  133. where stat_datetime = date_format(:date, '%Y%m%d')
  134. and cost > 0
  135. and company_id = 'd57fecdcf7a94d009736d9c850731582'
  136. and user_id in(
  137. select id from `jeecg-boot`.sys_user where leader_id='c52551687c524a96a3904658a35f0f26'
  138. union all
  139. select id from `jeecg-boot`.sys_user where id='c52551687c524a96a3904658a35f0f26'
  140. )) a
  141. left join (
  142. select a.account_id, concat(round((a.cost - b.cost) / b.cost * 100, 2), '%') as tb_rate
  143. from application.bytedance_advertiser_report_daily_dw a
  144. left join application.bytedance_advertiser_report_daily_dw b
  145. on a.account_id = b.account_id
  146. where a.stat_datetime = date_format(:date, '%Y%m%d')
  147. and b.stat_datetime = date_format(date_sub(:date, interval 6 day), '%Y%m%d')
  148. ) b
  149. on a.account_id = b.account_id
  150. left join(
  151. select t.account_id, concat(round((t.cost - t1.yes_week_cost) / t1.yes_week_cost * 100, 2), '%') as cost_rate
  152. from (select account_id, sum(cost) as cost
  153. from application.bytedance_advertiser_report_daily_dw
  154. where stat_datetime between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
  155. and cost > 0
  156. group by account_id) t
  157. left join
  158. (select account_id, sum(cost) as yes_week_cost
  159. from application.bytedance_advertiser_report_daily_dw
  160. where stat_datetime between date_format(date_sub(:date, interval 13 day), '%Y%m%d') and date_format(date_sub(:date, interval 7 day), '%Y%m%d')
  161. and cost > 0
  162. group by account_id) t1
  163. on t.account_id = t1.account_id
  164. ) c
  165. on a.account_id = c.account_id
  166. left join `jeecg-boot`.sys_user user
  167. on a.user_id = user.id