【SQL】每类视频近一个月的转发量/率

【问题】

统计在有用户互动的最近一个月(按包含当天在内的近30天算,比如10月31日的近30天为10.2~10.31之间的数据)中,每类视频的转发量和转发率(保留3位小数)。

【数据】

用户-视频互动表 tb_user_video_log
(uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID)

短视频信息表 tb_video_info
(video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长(秒), release_time-发布时间)

输出示例数据:tag,retweet_cut,retweet_rate

【要点】

  1. 获得两个时间戳之间相差多少天:datediff(x1,x2)
  2. 由题目中的例子看出,相差30天两个时间相减计算结果实际上是29天,很容易犯错
  3. 题目描述的比较模糊,“有用户互动的最近一个月” 是每类视频分别计算,还是所有的类型放一起看?面试中如果碰到这类题可以跟面试官沟通来明确需求。

【解答】

select b.tag,  
       sum(if_retweet) as retweet_cut,  
       round(sum(if_retweet)/count(*),3) as  retweet_rate
from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id
where datediff(a.start_time,(
    select start_time 
    from tb_user_video_log 
    order by id desc
    limit 1))<=29
group by b.tag
order by retweet_rate desc

【知识扩充】

sql 计算时间差 datediff & timestampdiff

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值