SQL分析douban摇滚音乐人气分析步骤
对豆瓣摇滚音乐人在豆瓣音乐内的人气状况进行分析,找出在豆瓣最受欢迎的音乐人以及豆瓣音乐人整体的人气分布状况。
一、数据来源
https://music.douban.com/ 使用第三方爬虫工具“集搜客”抓去了“摇滚”分类下的全部艺人及他们的粉丝数。
二、分析工具
MySQL+Navicat
三、数据预处理
1、导入到数据库中,重命名为douban
SELECT* FROM douban; 结果显示有很多冗余字段。
2、显示所有字段名称及其数据类型
SHOW FULL COLUMNS FROM douban; 除字段“艺人”和“关注人数”以外,其余字段皆为冗余字段,需要对其进行清洗。另外,关注人数列也包含冗余内容如“%人喜欢
3、删除多余列
ALTER TABLE douban
DROP _clueid,
DROP _fullpath,
DROP _realpath,
DROP _theme,
DROP _middle,
DROP _createdate,
DROP _pagenp;
4、再次查询douban表,验证删除结果
SHOW FULL COLUMNS FROM douban; 冗余列都已被删除,仅剩“艺人”和“关注人数”列
5、检查表内是否存在空值
SELECT "艺人" FROM douban
WHERE "关注人数" is NULL;
6、数据规整
对“关注人数”列进行清洗,去除冗余内容,只留下数字
update douban
set "关注人数" = REPALCE("关注人数','(',' ');
update douban
set "关注人数"= REPLACE("关注人数",'人喜欢)',‘ ');
7、将关注人数列的数据类型转换为整数型
ALTER TABLE douban
MODIFY "关注人数" INT;
四、数据分析
1、艺人总数 ,查询表内艺人总数(去重)
SELECT COUNT (DISTINCT "艺人" ) FROM douban;
2、关注TOP20的艺人
按关注人数对艺人进行降序排列,并取前20条数据
SELECT*FROM douban
ORDER BY "艺人" DESC
LIMIT 20;
3、关注人数分布
先查询全体艺人的关注总数
SELECT SUM("关注人数")FROM
(SELECT DISTINCT 艺人,关注人数 FROM douban) AS t;
再单独查询“关注人数>1000”的艺人们的关注人数总和
SELECT SUM("关注人数")FROM
(SELECT DISTINCT 艺人,关注人数 FROM douban WHERE "关注人数">1000) AS t;
五、结论建议
1、算法的优化
2、活动运营