题解 | #每类视频近一个月的转发量/率#

每类视频：

• group by tag

有用户互动的最近一个月:

• 结束时间：tuvl表start_time最新的那天

  • date((select max(start_time) from tuvl))，

  • 注意，select要被括号包住(select ...from...)，才能作为新的表

• 开始时间：29天前, DATE_SUB(end_day,INTERVAL 29, day)

• 表达式：

  • where DATEDIFF(date(select max(start_time)from tuvl),

    date(tuvl.start_time)) <= 29

转发量:

• sum(if_retweet)

转发率：

• 3位小数：round(x, 3)

• sum(if_retweet) / count(uid)

按转发率降序排序:

• order by 转发率 desc

select 
  tvi.tag,
  sum(tuvl.if_retweet) retweet_cut,
  round(sum(tuvl.if_retweet) / count(tuvl.uid),3) retweet_rate
from 
  tb_user_video_log tuvl
  left join tb_video_info tvi
  on tuvl.video_id = tvi.video_id
  where DATEDIFF(date((select max(start_time) from tb_user_video_log)), 
               date(tuvl.start_time)) <= 29
group by tvi.tag
order by retweet_rate desc