正文

hive刷题——视频热度问题(代码片段)

滴水穿石不是靠力，而是因为不舍昼夜。  滴水穿石不是靠力，而是因为不舍昼夜。  2023-02-18  199

关键词：

需求描述

现有用户视频播放记录表（user_video_log）如下。

uid (球队名称)	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、先计算热度因子

select video_id,
       sum((cast(if_like as int)))       if_like,
       sum((cast(if_retweet as int)))    if_retweet,
       sum(if(comment_id is null, 0, 1)) if_comment
from user_video_log
group by video_id

2、每个视频看完用户

select vi.video_id,
       sum(if(unix_timestamp(end_time) - unix_timestamp(start_time) = duration, 1, 0)) / sum(1) uv_rate
from video_info vi
         join user_video_log uv on vi.video_id = uv.video_id
group by vi.video_id

参考SQL

select video_id,heat
from (select video_id, hot heat, rank() over (partition by video_id order by hot desc ) rn
      from (select t.video_id,
                   (ceil(100 * uv_rate) + 5 * if_like + 3 * if_comment + 2 * if_retweet) / (new_date + 1) hot
            from (select u1.video_id, datediff(this_date, date_format(max(end_time), \'yyyy-MM-dd\')) new_date
                  from user_video_log uv
                           join
                       (select video_id, date_format(max(end_time), \'yyyy-MM-dd\') this_date
                        from user_video_log
                        group by video_id) u1
                       on uv.video_id = u1.video_id
                  group by u1.video_id, this_date) t
                     join
                 (select video_id,
                         sum((cast(if_like as int)))       if_like,
                         sum((cast(if_retweet as int)))    if_retweet,
                         sum(if(comment_id is null, 0, 1)) if_comment
                  from user_video_log
                  group by video_id) t1 on t.video_id = t1.video_id
                     join
                 (select vi.video_id,
                         sum(if(unix_timestamp(end_time) - unix_timestamp(start_time) = duration, 1, 0)) /
                         sum(1) uv_rate
                  from video_info vi
                           join user_video_log uv on vi.video_id = uv.video_id
                  group by vi.video_id) t2 on t.video_id = t2.video_id) t) i
where rn <= 3;

hive从入门到实战五(代码片段)

第10章Hive实战之谷粒影音10.1需求描述统计硅谷影音视频网站的常规指标，各种TopN指标：统计视频观看数Top10统计视频类别热度Top10统计出视频观看数最高的20个视频的所属视频类别以及对应视频类别的个数统计视频观看数Top5... 查看详情

hive实战练习（包含数据集）(代码片段)

文章目录1.0数据说明2.0建表3.0实战练习3.1统计视频观看数Top103.2统计视频类别热度Top103.3统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数3.4统计每个类别中的视频热度Top10，以Music为例3.5统计每个类别... 查看详情

hive实战企业级项目之谷粒影音统计各种topn问题(代码片段)

1.需求描述统计硅谷影音视频网站的常规指标，各种TopN指标：--统计视频观看数Top10--统计视频类别热度Top10--统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数--统计视频观看数Top50所关联视频的所... 查看详情

基于hive的youtube电影数据分析(代码片段)

...om/s/10P1Bmjx-y17R8jmy4q685g提取码：79a0一、项目需求1.统计视频观看数Top102.统计视频类别热度Top103.统计出视频观看数最高的20个视频的所属类别以及类别包含这Top20视频的个数4.统计视频观看数Top50所关联视频的所属类别的热度排... 查看详情

热度实时计算(代码片段)

...oot项目整合微信支付>🔔博主推荐网站：牛客网刷题|面试|找工作神器>📣种一棵树最好的时间是十年前，其次是现在！查看详情

hivesql面试题52：近一个月发布的视频中热度最高的top3视频抖音面试题，不得不去吐槽的一个题目(代码片段)

...备 2问题分析 3小结0问题描述现有用户-视频互动表tb_user_video_logiduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id110120012021-09-2410:00:002021-09-2410:00:30111NULL210120012021-10-0110:00:002021-10-0110:00:31110NULL310220012021-10-0110:00:... 查看详情

leetcode刷题13.比特位计数——java版(代码片段)

⭐欢迎订阅《leetcode》专栏，每日一题，每天进步⭐这道题有两种位运算思路，都是利用数组前面已经算好的数来计算当前数的1的个数——leetcode此题热评前言哈喽，大家好，我是一条。糊涂算法，难得糊... 查看详情

hive面试题系列第一题-连续登录问题(代码片段)

视频讲解地址：https://www.bilibili.com/video/BV1iV4y1x7yo?spm_id_from=333.999.0.0&vd_source=aa4fb0436f6d978af872cafb81a01178Hive面试题系列第一题-连续登录问题题目：求连续7天登录的用户表结构:CREATETABLElogtable(u 查看详情

heatmap热度图(代码片段)

#!/usr/bin/envpython#-*-coding:utf-8-*-importnumpyasnpimportpandasaspdimportseabornassnsfromscipyimportstatsimportmatplotlibasmplimportmatplotlib.pyplotasplt#热度图heatmapnp.random.seed(0)sns.set()unifor 查看详情

hive面试题系列第三题-用户留存问题(代码片段)

视频讲解地址：https://www.bilibili.com/video/BV1Rd4y1T7iU/?spm_id_from=333.788&vd_source=aa4fb0436f6d978af872cafb81a01178Hive面试题系列第三题-用户留存问题题目：求用户1日、3日、7日留存率概念问题：第N日活跃用户留存率&# 查看详情

hive面试题系列第七题-同时在线问题(代码片段)

视频讲解地址：https://www.bilibili.com/video/BV1Tg411r7Jz/?spm_id_from=333.788&vd_source=aa4fb0436f6d978af872cafb81a01178Hive面试题系列第七题-同时在线问题题目：计算主播最高同时在线人数（pcu）表结构:cre 查看详情

hive面试题系列第七题-同时在线问题(代码片段)

hive面试题系列第六题-互为好友问题(代码片段)

视频讲解地址：https://www.bilibili.com/video/BV1at4y1J7Bq/?spm_id_from=333.788&vd_source=aa4fb0436f6d978af872cafb81a01178Hive面试题系列第六题-互为好友问题题目：根据用户好友列表user_table,求互为共同好友的人有多少对。表结构:c 查看详情

hive面试题系列第二题-行转列问题(代码片段)

视频讲解地址：https://www.bilibili.com/video/BV1BG4y1v7Ps/?spm_id_from=333.788&vd_source=aa4fb0436f6d978af872cafb81a01178Hive面试题系列第二题-行转列问题题目：求语文课程成绩大于英语课程成绩的学生的学号表结构:createtablesco 查看详情

hackerrank刷题(代码片段)

1.问题：Queryalistof CITY namesfrom STATION witheven ID numbersonly.Youmayprinttheresultsinanyorder,butmustexcludeduplicatesfromyouranswer.答案：SELECTDISTINCTcityFROMstationWH 查看详情

刷题注意点(代码片段)

1、我在使用Code::Blocks16.01写C++代码的时候，尽管我已经勾选了编译器"-std=c++11"的选项，但在使用stoi()函数时，仍然会报错“error:‘stoi‘wasnotdeclaredinthisscope”，查了一下，发现是这个版本编译器本身的问题，详见这里的说明，下... 查看详情

力扣刷题算法笔记（javascript版）(代码片段)

本文为js版本算法视频笔记找了很多算法这个up主讲的真的非常好适合小白去听算法都是力扣难度中等和简单的b站视频链接1、岛屿最大面积（求两数最大和）解析：提供一个数组，给了一个target要求是在数组中找... 查看详情

poj-2777线段树刷题(代码片段)

title:poj-2777线段树刷题date:2018-10-1620:01:07tags:acm刷题categories:ACM-线段树概述这道题是一道线段树的染色问题,,,,做了几道染色的问题，，好像渐渐的熟悉的染色问题的大概的解体思路，，，不再像刚开始做的时候那样一脸懵逼，，... 查看详情