2021-12-01 数据库综合实验

先声明，本博客为个人作业不一定为标准答案，仅供参考，很有可能有错误，自己就发现了错误哈哈。。。

1、数据导入及数据清洗

（1）将原始数据文件导入到Mysql，表结构自定

导入的方式：1、通过mysql的可视化工具进行导入（navicat等）；2、通过代码实现导入（python等）

我选择navicat导入txt，步骤如下：

（1）单击右键，选择导入向导

（2）选择文本文件（保存的train_data为txt文件），点击下一步

（3）选择保存好的train_data文件，导入，点击下一步

（4）因train_data文件采用制表符将各个数据隔开，所以选择制表符作为字段分隔符，点击下一步

（5）train_data文件第一行为字段名，第二行为数据开始行，与默认行数一致，不修改，点击下一步

（6）源表名为train_data，目标表名也设置为train_data，点击下一步

（7）向导已对表结构进行猜测，而train_data文件title与content数据过长，修改为longtext类型，点击下一步

（8）点击下一步

（9）单击开始，将train_data文件导入Navicat

（10）当显示Finished successfully时，导入成功，关闭

（2）清除标题或者内容是404的数据

DELETE
FROM train_data
WHERE title='404' OR content='404'

（3）清除内容是null或者是字符串null的数据

DELETE
FROM train_data
WHERE content='NULL' OR content IS NULL

清洗完数据后表如下：

 （十多万行）

---

12.06更新：在开始之前我一定要说，clicktime是用户的点击时间！！不是次数！！（我之前理解错了写错了，大概也只有我会错。。）

2、数据统计

（1）计算该网站每天的pv、uv，按各自的大小从高到低排序。

pv:页面浏览量或点击量

SELECT newsid,FROM_UNIXTIME(clicktime,"%Y年%m月%d日") AS time,COUNT(userid) AS pv
FROM train_data
GROUP BY FROM_UNIXTIME(clicktime,"%Y年%m月%d日")
ORDER BY pv DESC
LIMIT 5

uv：浏览网站的访客个数，一天多次访问仅算一次

SELECT newsid,FROM_UNIXTIME(clicktime,"%Y年%m月%d日") AS time,COUNT(DISTINCT userid) AS pv
FROM train_data
GROUP BY FROM_UNIXTIME(clicktime,"%Y年%m月%d日")
ORDER BY pv DESC
LIMIT 5

FROM_UNIXTIME(unix_timestamp,format)

unix_timestamp为从'1970-01-01 00:00:00' GMT开始的秒数值

format格式：

%M 月名字(January……December)

%W 星期名字(Sunday……Saturday)

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）

%Y 年, 数字, 4 位

%y 年, 数字, 2 位

%a 缩写的星期名字(Sun……Sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m 月, 数字(01……12)

%c 月, 数字(1……12)

%b 缩写的月份名字(Jan……Dec)

%j 一年中的天数(001……366)

%H 小时(00……23)

%k 小时(0……23)

%h 小时(01……12)

%I 小时(01……12)

%l 小时(1……12)

%i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss [AP]M)

%T 时间,24 小时(hh:mm:ss)

%S 秒(00……59)

%s 秒(00……59)

%p AM或PM

%w 一个星期中的天数(0=Sunday ……6=Saturday ）

%U 星期(0……52), 这里星期天是星期的第一天

%u 星期(0……52), 这里星期一是星期的第一天

%% 一个文字“%”

LIMIT 起始位置，末尾位置  用于展示部分数据

（2）查询3月份十大热点新闻，列出ID及标题

注：热点新闻：访问用户最多（非访问量）的新闻。

SELECT newsid,title,COUNT(DISTINCT userid) AS uv
FROM train_data
WHERE FROM_UNIXTIME(clicktime,"%m")='03'
GROUP BY newsid
ORDER BY uv DESC
LIMIT 10

 （3）查询3月份的排名前三的忠实用户

注：忠实用户：每天都访问网站的用户

SELECT DISTINCT userid
FROM train_data
WHERE FROM_UNIXTIME(clicktime,"%m")='03' AND userid IN
(SELECT DISTINCT userid
FROM train_data
GROUP BY userid
HAVING COUNT(DISTINCT FROM_UNIXTIME(clicktime,"%Y年%m月%d日"))=31)
GROUP BY userid
ORDER BY COUNT(userid) DESC
LIMIT 3

（4）请根据3月份的数据计算24小时内各时段（以小时为单位）的总访问量（自己定义什么是访问量），给出该网站的用户访问习惯结论

SELECT FROM_UNIXTIME(clicktime,"%H") as 时间段,COUNT(userid) AS 访问量
FROM train_data
GROUP BY 时间段
ORDER BY 时间段

 

 （5）同时查询具有特定词语的新闻如：“存款”和“余额宝”，“中国”和“工行”，“马航”和“飞机”等等，有什么样的实现方式（SQL、优化等等）。因为数据量不足原因，理论上的优化也可以。

SELECT *
FROM train_data
WHERE content REGEXP "存款|余额宝"

SELECT *
FROM train_data
WHERE content REGEXP "中国|工行"

SELECT *
FROM train_data
WHERE content REGEXP "马航|飞机"

这里使用了正则表达式模糊查询数据，用法如下：

WHERE 字段名 REGEXP ‘操作符’

模式	匹配模式
^	字符串的开始
$	字符串的结尾
.	任何单个字符
[. . . ]	在方括号内的字符列表
[^ . . . ]	非列在方括号内的任何字符
p1 | p2 | p3	交替匹配任何模式p1，p2或p3
*	零个或多个前面的元素
+	前面的元素的一个或多个实例
{n}	前面的元素的n个实例
{m , n}	m到n个实例前面的元素

或者使用like语句：

SELECT *
FROM train_data
WHERE content LIKE '%存款%' AND content LIKE '%余额宝%'

SELECT *
FROM train_data
WHERE content LIKE '%中国%' AND content LIKE '%工行%'

SELECT *
FROM train_data
WHERE content LIKE '%马航%' AND content LIKE '%飞机%'

部分结果：

---

其他知识点：（做错的时候学到的）

SUBSTR(字符串,截取起始位置,截取长度)函数用于截取字符串，mysql里面字符串从1开始，且中文字符也代表1个长度

STR_TO_DATE(字符串,'字符串中日期的表现形式')用于将字符串转化为date格式

STR_TO_DATE()会自动把没有时分秒的数据添上00:00:00

DATE_FORMAT(date,date展示的形式)函数用于以不同的格式显示日期/时间数据

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

---

本次记录就到这~~这次出错真是太触目惊心了，害怕误导到别人，下次我一定好好检查再发出。。