一：数据来源
数据集为2019年九月至今的哔哩哔哩 日视频排行榜数据信息，通过Python爬虫采集，具体采集过程见：WebBiliapi
共近两万条视频数据，每一行数据集共23个字段名：
- aid :视频编号 cid:弹幕编号 href:视频链接
- times:时间 weeks:星期 dates:日期 rank:排名
- score:评分 title:标题 tname:类型 owner:up主
- ownid:作者编号 owurl:作者url ctime:发布时间
- views:观看人数 likes:喜欢人数 reply:回复人数
- favou:收藏人数 coins:投币人数 share:分享人数
- length:视频长度 imgurl:视频封面链接地址
二：提出问题
1 ：整体视频的分布情况
类型分布（占有率多少）、视频评分区间、视频发布时间段
时长分布、视频 UP主霸榜情况
2 ：用户行为转化成漏斗
喜欢-评论-收藏-投币各环节转换率如何
3 ：排名和评分高的视频有什么特征
4 ：基于时间维度了解视频发布情况
三：数据清洗
- ctime时间戳改为标准日期格式
ALTER TABLE Video ADD release_time VARCHAR(20) NOT NULL
AFTER ctime;
UPDATE Video SET release_time = ctime;
UPDATE Video SET release_time=REPLACE(release_time,release_time,
FROM_UNIXTIME(date));
2.由于数据采集程序一天执行两遍，为的是选取单个视频当日最大评分信息。所以在分析前需要进行清洗来确保单日内aid的唯一性，保留评分最高的行。
SELECT * FROM Video_copy a WHERE NOT EXISTS(
SELECT 1 FROM Video_copy WHERE aid=a.aid AND dates=a.dates
AND score<a.score)
四：模型构建和分析问题
1.总体视频分布情况
- 类型分布
SELECT tname,COUNT(tname) AS counts FROM Video
GROUP BY tname
ORDER BY counts DESC
- 评分分布
SELECT scores,COUNT(scores) from
(SELECT Ceiling(score/10000) AS scores FROM Video) a
GROUP BY scores ORDER BY COUNT(scores) DESC
-
发布时间段与时长分布
- 总UP主数
SELECT COUNT(DISTINCT ownid) AS 总用户数 FROM Video
- UP霸榜排名
SELECT owner,COUNT(owner) FROM Video
GROUP BY owner
ORDER BY COUNT(owner) DESC
- UP主复榜率:上榜两次或两次以上的UP主占中UP主的比例
SELECT CONCAT(ROUND(COUNT(*)/(
SELECT COUNT(*) FROM Video),2)*100,'%')
FROM( SELECT owner,COUNT(owner) AS counts
FROM Videos GROUP BY owner HAVING COUNT(owner) >2
ORDER BY counts DESC) as b
>>>4%
- UP主得分情况总览：
CREATE VIEW Owner_info AS
SELECT owner,SUM(views) AS 观看,SUM(likes)
AS 喜欢,SUM(share) AS 评论,SUM(favou)
AS 收藏,SUM(coins) AS 投币,sum(score) AS 总分,CEILING(SUM(score)/COUNT(owner)) AS 均分
FROM Videos GROUP BY owner ORDER BY 均分 DESC;
SELECT * FROM Owner_info
2.用户行为转化率
在观看完视频后，用户行为之间的转换也是一个很好的衡量标准。得到用户行为各环节转化率，如下：
CREATE VIEW Owner_pere AS
SELECT owner,
CONCAT(ROUND(喜欢/观看,4)*100,'%')
AS 喜欢_观看比,
CONCAT(ROUND(收藏/观看,4)*100,'%')
AS 收藏_观看比,
CONCAT(ROUND(分享/观看,4)*100,'%')
AS 分享_观看比,
CONCAT(ROUND(投币/观看,4)*100,'%')
AS 投币_观看比,
均分
FROM Owner_info;
SELECT * FROM Owner_pere;
总体上均分靠前的，喜欢观看、投币观看转换比较高
3.从时间维度来分析视频分布情况
下午是高分视频的频发期，而一般视频大体会在1-5分钟 。视频下午发布后，受众无论是上班族还是学生党回到家里时都可以第一时间接收到推送 。而视频时间的短小也符合社会流行快视频的趋势。
五 ：总结
1 在哔哩哔哩日排行榜中，视频集中产生于下午3-5点中，且短视频居多 。视频主要类型为日常、搞笑、美食、电子竞技等青少年关注的圈子，与哔哩哔哩受众有很大关系
2 视频分数受喜欢、投币数影响作用大，且受UP主热度影响，其它因素不是很明显。
网友评论