谨以此笔记献给浪费掉的两个小时。
此题存在多处疑点和表达错误的地方,如果你看到了这篇文章,劝你跳过该题。
该题对提升HSQL编写能力以及思维逻辑能力毫无帮助。文章来源:https://www.toymoban.com/news/detail-703945.html
实现一
with info as (
-- 将数据与 video_info 关联,取出 duration
select t1.video_id as video_id,
-- 计算观看时长(s)
unix_timestamp(end_time) - unix_timestamp(start_time) as view_time,
if_like,
if_retweet,
comment_id,
duration
from video_info t1
join
user_video_log t2
on t1.video_id = t2.video_id
)
select distinct video_id,
-- 计算热度
cast(ceil(100 * (seeding_num / video_num) + 5 * like_num + 3 * comment_num +
2 * retweet_num) as decimal(16, 1)) as heat
from (
-- 计算出每个视频的热度 项
select video_id,
-- 新鲜度(别问我为什么)
1 as freshness,
-- 完播数
sum(if(view_time = duration, 1, 0)) over (partition by video_id) as seeding_num,
-- 该视频播放次数
count(1) over (partition by video_id) as video_num,
-- 点赞数
sum(if_like) over (partition by video_id) as like_num,
-- 转发数
sum(if_retweet) over (partition by video_id) as retweet_num,
-- 评论数
sum(if(comment_id is not null, 1, 0)) over (partition by video_id) as comment_num
from info
) t1
order by heat desc
limit 3;
题目来源
http://practice.atguigu.cn/#/question/49/desc?qType=SQL文章来源地址https://www.toymoban.com/news/detail-703945.html
到了这里,关于49. 视频热度问题的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!