一：数据来源

数据集为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;