最近在学陈旸老师的专栏SQL必知必会里面学到如何用SQL对数据进行清洗这一节时,在实操的时候遇到了一些问题,这里记录一下其中对各种问题的解决办法,希望对后面学习的朋友也有帮助!(PS: 我这里是WIN10系统下使用的MySQL-8.0.15版本!!!

  • 第一步,创建数据表titanic_train(陈老师是直接用的Navicat工具创建的,而小白我为了锻炼自己的能力,决定直接用MySQL操作):

由于我的MySQL数据库并没有这个表格,所以需要我先创建这个表格,下面是创建titanic_train表格的MySQL语句:

DROP TABLE IF EXISTS `titanic_train`;
CREATE TABLE `titanic_train`  (
  `PassengerId` varchar(255),
  `Survived` varchar(255),
  `Pclass` varchar(255),
  `Name` varchar(255),
  `Sex` varchar(255),
  `Age` varchar(255),
  `SibSp` varchar(255),
  `Parch` varchar(255),
  `Ticket` varchar(255),
  `Fare` varchar(255),
  `Cabin` varchar(255),
  `Embarked` varchar(255)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

OK,数据表已经成功创建好了,下面就是导入外部数据。

  • 第二步,导入外部数据集。于是小白成功的迎来了第一个问题

  • The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
    这个问题的具体解决办法已经写在了前面那篇博客,请自行前往了解,谢谢:
    如何解决MySQL8.0外部文件导入的权限问题?

  • 好不容易把第一个外部文件导入的权限问题解决了,数据成功导入了,接下来第三步:
    我们就要用MySQL对数据进行清洗
    首先对数据集进行一个概览:

    可以看到正如陈老师文中所写,数据存在缺失。根据陈老师的清洗四字准则---->“完全合一”,我们先检查完整性(即检查字段是否存在空值):
    陈老师用的是这个命令:

SELECT COUNT(*) as num FROM titanic_train WHERE Age IS NULL;

但是我这里却没有效。。。这就是第二个问题:查询空值用IS NULL无效!

于是我去翻看MySQL8.0的官方文档,发现还有一种查看空值的写法:

于是我也改成这样的命令来查询空值数量,结果就生效了:

SELECT COUNT(*) as num FROM titanic_train WHERE Age = '';


同样对多字段的空值进行统计的命令如下:

  1. 对缺失值进行处理,这里采用的是均值填充(缺失值还有其他处理方法,改天专门写一篇来讲):
    按照陈老师的方法是先建立一张临时表titanic_train2,再对原表titanic_train进行数据填充的修改。所以这里我们要先创建一张临时表,使用如下命令:
CREATE TEMPORARY TABLE titanic_train2 SELECT * FROM titanic_train;

然后对titanic_train进行修改并填充:

UPDATE titanic_train SET age = (SELECT ROUND(AVG(age),1) FROM titanic_train2) WHERE age = '';

修改成功你会看的下面的change为177:

(PS:如果你的UPDATE命令虽然成功执行了,但是change是0,像下面这种情况:

那么恭喜你,遇到了跟我一样的第三个问题:UPDATE命令无效?
一般出现这种情况的原因有两种:
1. 要更新的数据跟原始数据一样;
2. 要更新的数据并不存在;

而小白我的情况后来通过排查发现是第二种,因为我创建临时表的时候并没有把数据全部复制过去,于是导致了临时表虽然存在,但是里面没有数据,这就尴尬了😂。所以解决办法也很简单,就是重新创建临时表,然后再次执行上面的UPDATE命令即可)

然后我们可以对数据集进行查看来确认我们的数据填充是成功的:

OK,接下来我们继续对其他字段进行清洗,跟着陈老师的步骤是Cabin和Embarked这两个字段,这里没有问题,直接上图:

  • 好了,直到上面我们对缺失值的处理已经结束,完整性已经ok,下面是检查全面性
    这里就是观察数值情况并检查字段类型,显然一开始我们设置的表格字段类型是不太全面规范的,需要进行修改。陈老师这里也给出了解决的办法,一般创建数据表应该是这样的才对,所以我们也对字段进行规范修改。
  • 到这里全面性也检查完了,然后是合法性和唯一性的检查。经过检查,这个数据集不存在这两个问题,所以就不需要操作。
  • 最后就是对清洗完的数据进行可视化,这里用的MySQL For Excel 插件工具,除了安装这里要注意一下:需要先安装 一个微软VS Tools 的工具 才可以:

    这里是微软工具的下载链接:
    https://www.microsoft.com/en-us/download/details.aspx?id=48217
    你可选择下载中文版,方便你安装。

接着再安装陈老师说的那两个工具,然后去Excel里面设置一下登录账号和密码,就可以把刚刚清洗完的MySQL数据导入Excel:

最后就是成功的了解到用户生存情况(Survived)与 Embarked 字段的关系:

总的操作到这里就结束了,这次实操遇到了三个问题+一个注意事项

1. MySQL8.0外部文件导入的权限问题;
2. 第二个问题:查询空值用IS NULL无效;
3. UPDATE命令执行成功却Change为0,无效更新;
4. 注意事项:需要先安装 一个微软VS Tools 的工具才可以用MySQL For Excel插件。

总的来说,个人觉得MySQL进行数据清洗没有直接用Python操作更方便,虽然陈老师也建议可以用存储过程进行处理,但是个人还是更喜欢用Python来进行数据清洗,下一篇写写Python来进行数据清洗的方法。

谢谢阅读,如有错误,请指正。

以上数据来源 Kaggle: https://www.kaggle.com/c/titanic/data
文中课程资料来自陈旸老师的:SQL必知必会之SQL项目实战3讲的第一讲:如何用SQL对数据进行清洗