一：数据来源

数据集为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 ：基于时间维度了解视频发布情况

三：数据清洗

1. 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 在哔哩哔哩日排行榜中，视频集中产生于下午3-5点中，且短视频居多 。视频主要类型为日常、搞笑、美食、电子竞技等青少年关注的圈子，与哔哩哔哩受众有很大关系
2 视频分数受喜欢、投币数影响作用大，且受UP主热度影响，其它因素不是很明显。