SQL数据清洗

大家好,我是jacky,很高兴继续跟大家分享《MySQL数据分析实战》,从本节课程开始,我们的课程就会变得越来越实战,也会越来越有意思了;
我们课程的主体叫MySQL数据分析实战,那我们用MySQL来进行数据分析的时候,也一定会有其逻辑在的,大家想想我们无论从事任何的工作岗位,我们工作都是有我们的逻辑在的,可能有人会说,我的工作有什么工作逻辑?那jacky说,那是因为我们的工作都太熟悉了,我们也没有进行过总结,最简单的来说,我们工作总会有先后顺序吧,就是先做什么,后做什么,这也是一种逻辑;好,前面铺垫了这么多,jacky就是说,在实际工作中,用MySQL来做数据分析也是有它的逻辑在的,jacky给大家总结的单表操作的八句箴言,也是完成按照我们实际工作中的逻辑来编写的;
前面jacky说过单表查询的八句箴言第二句是:数据清洗两条路,唯一where走一走;第三句:清洗数据有空值,is not 来去除;那jacky说第二句箴言,和第三句箴言我们结合着来讲,这都属于数据清洗的范畴;
本节课程,jacky将结合数据数据清洗的六大步骤,给大家解析八句箴言的第二、三、四句;

(一)本小节逻辑线(开篇铺垫)-数据清洗的六大步骤

  • (1)数据预处理;
  • (2)去除(补全)有缺失的数据
  • (3)去除(修改)格式和内容错误的数据
  • (4)去除(修改)逻辑错误的数据
  • (5)去除不需要的数据
  • (6)关联性验证

第一步:数据预处理

  • 八句箴言第一句:不管三七二十一,先把数据show来看

  • 数据预处理说白了就是看两件事:

    • (1)看描述数据的信息:就是我们上次课讲的看表结构,SQL语句1;

    • (2)二是抽取一部分数据,使用人工查看方式,对数据本身有一个直观的了解,并且初步发现一些问题,为之后的处理做准备:这部分呢,jacky上次课也将了就是SQL语句2和SQL语句3;

第二步:去除(补全)有缺失的数据

SQL语句5:去除缺失数据-空值

select * from user where name is null; 
select * from user where name is not null;
  • 为了不打断逻辑的完整性,关于空值和NULL值的相关内容,在系列课程的最后一个章节,请观看《这些年踩过的坑-空值与NULL》,这里jacky就不给大家过空值和NULL值的一些知识点,包括优化的一些内容,大家对应最后的一章查看吧;

  • 我们打开MySQL软件,看一下message表,

select * from message
  • 我们在向message表中插入一些空值和NULL的数据
insert into mess(name,content) values('','vvvvvvvvvv');
insert into mess(name,content) values('user7',null);

±—±------±-----------------------+
| id | name | content |
±—±------±-----------------------+
| 1 | user1 | dfdfdfdfd |
| 2 | user1 | aaaaaaaaa |
| 3 | user1 | ccccccccc |
| 4 | user2 | jjjjjjjjjj |
| 5 | user5 | hello world |
| 6 | user6 | weixin:run2lucky |
| 7 | user6 | gongzhognhao:shujudata |
| 8 | | vvvvvvvvvv |
| 9 | use7 | NULL |
±—±------±-----------------------+

SQL语句4:去除缺失数据- 用is not 去除null值

select * from mess where content is not null
select * from mess where content <>''
select * from mess where name <>'' and content is not null;
  • SQL语句5:用比较运算符去除空值

    • 下面jacky总结比较运算符的用法
    • 比较运算符:
运算符 含义
= 和~相等
<> 和~不相等
>= 大于等于~
> 大于~
<= 小于等于~
< 小于
  • 运算符很简单,没有必要每条来说,但有几个主要注意的点:

    • 第一点:比较运算符不仅可以对数据这个数据类型的列进行比较,还能对包括字符、日期等所有数据类型和列进行比较;
insert into mess(name,content) values('user11','llllllllll'),('user32','kkkkkkkk');

对于字符串来说,由于时间关系,jacky就不用MySQL举例了,jacky把规则说一下:字符串的比较是按字典顺序进行排序的,我们一定要与数字的大小顺序区分,什么意思:

1<10<11<2<222<3

  • 注意第二点:

在MySQL中!= 与<>都是与~不相等的意思,但jacky这里更倾向于用<>,因为这样显得更规范,也更兼容其他的除MySQL外的数据库软件;

  • 关于如何填充缺失的数据,SQL能做,我们可以用SQL的AVG函数计算出平均值,在进行填充,但工作中有这么用SQL清洗数据的吗?没有,我们都是用编程语言,这里就略过了

第三步:去除(修改)格式和内容错误的数据

下面jacky来说下数据清洗的第三步,去除或修改格式和内容错误的数据,前面 jacky说数据清洗,是整个数据分析过程中不可缺少的一个环节,在实际操作中,数据清洗通常会占用数据分析过程的80%的时间,如果说我们的数据是由我们企业内部系统日志而来,那我们的数据源,在格式和内容方面,一般来说,格式和内容会是比较结构化的,或者说是规范的,我们不用花太多的时间进行数据清洗,但如果说,我们的数据是由人工收集来的,或是多个数据源整合而来,那么我们就要花时间对数据源进行清洗;

  • 那清洗格式和内容错误的原则思想就是数据过滤,那什么是数据过滤或者说当我们在工作中遇到需要用SQL清洗格式和内容有错误的数据时,我们首要要想到的是什么?

    • 就是数据过滤的两种方法:

      • (1)where过滤

        看一个人SQL水平高不高,你看他使用IN还是使用OR

      • (2)通配符过滤

3.1 先说where过滤

有朋友说,这where过滤不就是where 字句吗?这有什么好说的,但jacky看过太多工作中经常写SQL的人,还真就是连where都写不好,下面jacky给大家好好总结一下where字句,告诉大家看一个人SQL 用的水平高不高,其实看where 字句就能看出来:

  • where过滤的第一层次:单一查询条件用where :真的没啥好说的,太简单了select * from message where age>30;

  • where 过滤的第二层次:多个查询条件组合:

    • 关于多个查询条件的whereSQL语句,玩的就是逻辑运算符,那逻辑运算符有哪几个呢:AND、OR、IN、NOT

@数据分析-jacky

3.1.1 逻辑运算符之AND和OR

  • SQL语句7:AND先运算,OR后运算(AND运算符的优先级高于OR运算符)想要优先执行OR运算符时就要使用括号。

3.1.2 逻辑运算符之OR和IN

  • SQL语句8:当在SQL语句中,用OR也行,用IN也行,那我们用IN就牛*,就专业,用OR就**了,就不专业

  • 为什么IN要远远好过用OR呢?主要有四点理由:

    • (1)IN语法更清楚,更直观

    • (2)在与其他AND和OR组合使用IN时,求值顺序更容易管理

    • (3)这点是最最重要的就是,IN的性能更优,执行速度更快

    • (4)IN可以包含其他SELECT语句,OR不行

去重:

  • 合并去重:分组

  • “暴力去重”:distinct

posted on 2018-09-25 21:02  朱元禄  阅读(3632)  评论(0编辑  收藏  举报