1、数据导入及数据清洗
(1)将原始数据文件(txt.)导入到Mysql,表结构自定
导入的方式:1、通过mysql的可视化工具进行导入(navicat等);2、通过代码实现导入(python等)
(2)清除标题或者内容是404的数据
SET SQL_SAFE_UPDATES = 0; //如出现安全权限问题可以如左设置
delete from train_data
WHERE title="404" or content=null;
SET SQL_SAFE_UPDATES = 1;
(3)清除内容是null或者是字符串null的数据
delete from train_data
WHERE publishtime is null;
2、数据统计
(1)计算该网站每天的pv、uv,按各自的大小从高到低排序。
- data中的clicktime为unix时间戳,须进行转化,并修改更新时间格式。
update train_data set clicktime = from_unixtime(clicktime,'%Y-%m-%d');
alter table train_data add column clickt varchar(255) not null;
update train_data set clickt=from_unixtime(clicktime);
- 最后统计pv, uv
//pv:每天各页面的浏览量
select count(date(clickt)),date(clickt)
from train_data
group by date(clickt)
order by count(date(clickt)) DESC;
//uv:电脑的IP的访问总量
select date(clickt),count(distinct userid)
from train_data
group by date(clickt)
order by count(distinct userid) DESC;
(2)查询3月份十大热点新闻,列出ID及标题
注:热点新闻:访问用户最多(非访问量)的新闻。
select newsid,title,count(newsid)
from train_data
group by newsid
order by count(newsid) desc;
(3)查询3月份的排名前三的忠实用户
注:忠实用户:每天都访问网站的用户
select userid,count(userid)
from
(select distinct date(clickt),userid
from train_data) as loyaluser
group by userid
order by count(userid) desc;
(4)请根据3月份的数据计算24小时内各时段(以小时为单位)的总访问量(自己定义什么是访问量),给出该网站的用户访问习惯结论
select time_format(time(clickt), '%H:00:00')as time,count(time_format(time(clickt), '%H:00:00'))as view
from
(select time_format(time(clickt), '%H:00:00'),clickt
from train_data)as Houradd
group by time_format(time(clickt), '%H:00:00')
order by time_format(time(clickt), '%H:00:00');
(5)附加题:同时查询具有特定词语的新闻如:“存款”和“余额宝”,“中国”和“工行”,“马航”和“飞机”等等,有什么样的实现方式(SQL、优化等等)。因为数据量不足原因,理论上的优化也可以。(个人拙见,不保证正确!)
~ 使用locate或position函数
select *
from train_data
where locate('飞机',title)>0;
~ 字符串函数 find_in_set(str,strlist)不可用,此函数针对以逗号分隔的字符串列
SELECT content FROM train_data WHERE find_in_set('飞机', ‘飞机,巴士,汽车');