一：數(shù)據(jù)來源
數(shù)據(jù)集為2019年九月至今的嗶哩嗶哩 日視頻排行榜數(shù)據(jù)信息犹撒，通過Python爬蟲采集奶段，具體采集過程見：WebBiliapi
共近兩萬條視頻數(shù)據(jù)购撼，每一行數(shù)據(jù)集共23個(gè)字段名：
- aid :視頻編號(hào) cid:彈幕編號(hào) href:視頻鏈接
- times:時(shí)間 weeks:星期 dates:日期 rank:排名
- score:評(píng)分 title:標(biāo)題 tname:類型 owner:up主
- ownid:作者編號(hào) owurl:作者url ctime:發(fā)布時(shí)間
- views:觀看人數(shù) likes:喜歡人數(shù) reply:回復(fù)人數(shù)
- favou:收藏人數(shù) coins:投幣人數(shù) share:分享人數(shù)
- length:視頻長(zhǎng)度 imgurl:視頻封面鏈接地址
二：提出問題
1 ：整體視頻的分布情況
???????類型分布（占有率多少）悍及、視頻評(píng)分區(qū)間、視頻發(fā)布時(shí)間段
???????時(shí)長(zhǎng)分布、視頻 UP主霸榜情況
2 ：用戶行為轉(zhuǎn)化成漏斗
???????喜歡-評(píng)論-收藏-投幣各環(huán)節(jié)轉(zhuǎn)換率如何
3 ：排名和評(píng)分高的視頻有什么特征
4 ：基于時(shí)間維度了解視頻發(fā)布情況
三：數(shù)據(jù)清洗
- ctime時(shí)間戳改為標(biāo)準(zhǔn)日期格式
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.由于數(shù)據(jù)采集程序一天執(zhí)行兩遍，為的是選取單個(gè)視頻當(dāng)日最大評(píng)分信息。所以在分析前需要進(jìn)行清洗來確保單日內(nèi)aid的唯一性焊虏，保留評(píng)分最高的行。
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)
四：模型構(gòu)建和分析問題
1.總體視頻分布情況
- 類型分布
SELECT tname,COUNT(tname) AS counts FROM Video
GROUP BY tname
ORDER BY counts DESC
- 評(píng)分分布
SELECT scores,COUNT(scores) from
(SELECT Ceiling(score/10000) AS scores FROM Video) a
GROUP BY scores ORDER BY COUNT(scores) DESC
-
發(fā)布時(shí)間段與時(shí)長(zhǎng)分布
- 總UP主數(shù)
SELECT COUNT(DISTINCT ownid) AS 總用戶數(shù) FROM Video
- UP霸榜排名
SELECT owner,COUNT(owner) FROM Video
GROUP BY owner
ORDER BY COUNT(owner) DESC
- UP主復(fù)榜率:上榜兩次或兩次以上的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 評(píng)論,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.用戶行為轉(zhuǎn)化率
在觀看完視頻后秕磷，用戶行為之間的轉(zhuǎn)換也是一個(gè)很好的衡量標(biāo)準(zhǔn)诵闭。得到用戶行為各環(huán)節(jié)轉(zhuǎn)化率，如下：
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 投幣_(tái)觀看比,
均分
FROM Owner_info;
SELECT * FROM Owner_pere;
總體上均分靠前的澎嚣，喜歡觀看疏尿、投幣觀看轉(zhuǎn)換比較高
3.從時(shí)間維度來分析視頻分布情況
下午是高分視頻的頻發(fā)期，而一般視頻大體會(huì)在1-5分鐘 易桃。視頻下午發(fā)布后褥琐，受眾無論是上班族還是學(xué)生黨回到家里時(shí)都可以第一時(shí)間接收到推送 。而視頻時(shí)間的短小也符合社會(huì)流行快視頻的趨勢(shì)晤郑。
五 ：總結(jié)
1 在嗶哩嗶哩日排行榜中敌呈，視頻集中產(chǎn)生于下午3-5點(diǎn)中，且短視頻居多 造寝。視頻主要類型為日常磕洪、搞笑、美食诫龙、電子競(jìng)技等青少年關(guān)注的圈子析显，與嗶哩嗶哩受眾有很大關(guān)系
2 視頻分?jǐn)?shù)受喜歡、投幣數(shù)影響作用大签赃，且受UP主熱度影響谷异，其它因素不是很明顯。
