数仓经典案例-各个视频平均完播率、平均播放进度

一只考考拉

已于 2022-08-18 09:59:39 修改

阅读量1.6k

点赞数

分类专栏： hive sql 文章标签：音视频数据库

于 2022-08-16 09:20:31 首次发布

本文链接：https://blog.csdn.net/weixin_38629422/article/details/126358781

版权

本文通过一系列问题展示了如何对视频数据进行深入分析，包括计算视频的完播率、平均播放进度，统计不同视频类别的转发量和转发率，以及创作者的涨粉率和总粉丝量。同时探讨了热度计算方法，涉及完播率、点赞数、评论数和转发数等指标，以及视频的新鲜度。

摘要由CSDN通过智能技术生成

用户视频互动表

短视频信息表

问题1：计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序

注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    video_id INT NOT NULL COMMENT '视频ID',
    start_time datetime COMMENT '开始观看时间',
    end_time datetime COMMENT '结束观看时间',
    if_follow TINYINT COMMENT '是否关注',
    if_like TINYINT COMMENT '是否点赞',
    if_retweet TINYINT COMMENT '是否转发',
    comment_id INT COMMENT '评论ID'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_video_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    video_id INT UNIQUE NOT NULL COMMENT '视频ID',
    author INT NOT NULL COMMENT '创作者ID',
    tag VARCHAR(16) NOT NULL COMMENT '类别标签',
    duration INT NOT NULL COMMENT '视频时长(秒数)',
    release_time datetime NOT NULL COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES
  (101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:30', 0, 1, 1, null),
  (