hivesql练习_视频热度问题
现有用户视频表（user_video_log）如下。
| uid (用户id) | video_id (视频id) | start_time (开始时间) | end_time (结束时间) | if_like (是否点赞) | if_retweet (是否转发) | comment_id (评论id) |
|---|---|---|---|---|---|---|
| 101 | 2001 | 2021-09-24 10:00:00 | 2021-09-24 10:00:20 | 1 | 0 | null |
| 105 | 2002 | 2021-09-25 11:00:00 | 2021-09-25 11:00:30 | 0 | 1 | null |
| 102 | 2002 | 2021-09-25 11:00:00 | 2021-09-25 11:00:30 | 1 | 1 | null |
| 101 | 2002 | 2021-09-26 11:00:00 | 2021-09-26 11:00:30 | 0 | 1 | null |
视频信息表(video_info) 如下：
| video_id (视频id) | author (作者id) | tag (标签) | duration (视频时长) |
|---|---|---|---|
| 2001 | 901 | 旅游 | 30 |
| 2002 | 901 | 旅游 | 60 |
| 2003 | 902 | 影视 | 90 |
| 2004 | 902 | 美女 | 90 |
找出近一个月发布的视频中热度最高的top3视频。
注：热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；
新鲜度=1/(最近无播放天数+1)；
当前配置的参数a,b,c,d分别为100、5、3、2。
最近播放日期以 end_time（结束观看时间）为准，假设为T，则最近一个月按 [T-29, T] 闭区间统计。
当天日期使用视频中最大的end_time
结果中热度保留为整数，并按热度降序排序。
期望结果如下：
| video_id <int> (视频id) | heat <decimal(16,2)> (热度) |
|---|---|
| 2002 | 80.36 |
| 2001 | 20.33 |
解答：
1 with tt as( 2 SELECT 3 uvl.video_id, 4 this_date, 5 datediff(this_date, max(end_time)) nodays, -- 最近无播放天数 6 sum(if(unix_timestamp(end_time) - unix_timestamp(start_time) = vi.duration, 1, 0)) / sum(1) uv_rate, --视频完播率 7 sum(if_like) if_like, --点赞数 8 sum(if_retweet) if_retweet, --转发数 9 sum(if(comment_id is null, 0, 1)) if_comment --评论数 10 FROM 11 user_video_log uvl 12 join (select max(end_time) as this_date -- 最新播放日期 13 from user_video_log) td 14 on uvl.end_time >= date_add(this_date, -29) and uvl.end_time <= this_date 15 join video_info vi on uvl.video_id = vi.video_id 16 group by uvl.video_id, this_date 17 ), 18 tt1 as( 19 select video_id, 20 cast((ceil(100 * uv_rate) + 5 * if_like + 3 * if_comment + 2 * if_retweet)/(nodays + 1) as decimal(16, 2)) heat 21 from tt 22 ), 23 tt2 as( 24 select video_id, heat, row_number() over(order by heat desc) rk 25 from tt1 26 ) 27 select video_id, heat from tt2 where rk <= 3;
