像Excel一样使用SQL进行数据分析(2)

Excel是数据分析中最常用的工具,本篇文章通过mysql与excel的功能对比介绍如何使用mysql完成excel中的数据生成,数据清洗,预处理,以及最常见的数据分类,数据筛选,分类汇总,以及数据透视等操作。本篇文章我们介绍第3,4部分内容,数据表清洗及数据预处理。

mysql-logo

3,数据表清洗

第三部分是对数据表中的问题进行清洗。主要内容包括对空值,大小写问题。这里不包含对数据间的逻辑验证。

处理缺失值(填充)

我们在创建数据表的时候在price字段中故意设置了几个0值。对于空值的处理方式有很多种,可以直接删除包含空值的数据,也可以对空值进行填充,比如使用均值填充。还可以根据不同字段间的逻辑对空值进行推算,例如通过回归计算填充值。

Excel中可以通过“查找和替换”功能对空值进行处理,将空值统一替换为0或均值。也可以通过“定位”空值来实现。

查找和替换空值

我们选择填充的方式来处理空值,使用price列的均值来填充0值字段。具体分为两个步骤,第一步计算data1数据表中price列的均值,并保留两位小数。第二步使用price列的均值更新price列中0值的字段。

#计算price列的均值
SELECT ROUND(AVG(price),2) AS avg_price FROM data1;

计算price均值

#使用均值填充0值
UPDATE data1 SET price=2199.67 WHERE price=0;

使用均值填充缺失字段

 

清理空格

除了空值,字符中的空格也是数据清洗中一个常见的问题,mysql中使用TRIM函数清洗数据两侧的空格,下面是清除字符中空格的代码。

#清理字符中的空格
UPDATE data1 SET city=TRIM(city);
select city from data1;

清理字符中的空格

数值修改及替换

数据清洗中最后一个问题是数值修改或替换,Excel中使用“查找和替换”功能就可以实现数值的替换。Mysql中使用REPLACE函数完成数值修改和替换的操作。

查找和替换空值

#修改和替换
UPDATE data1 SET city=REPLACE(city,'SH','shanghai'); 
SELECT city FROM data1;

 

修改和替换

大小写转换

在英文字段中,字母的大小写不统一也是一个常见的问题。Excel中有UPPER,LOWER等函数,mysql中也有同名函数用来解决大小写的问题。在数据表的city列中就存在这样的问题。我们将city列的所有字母转换为小写。下面是具体的代码和结果。

#大小写转换
UPDATE data1 SET city=LOWER(city);
select city from data1;

大小写转换

4,数据预处理

第四部分是数据的预处理,对清洗完的数据进行整理以便后期的统计和分析工作。主要包括数据表的匹配合并,排序,数值分列,数据分组及标记等工作。

数据表匹配合并

首先是对不同的数据表进行合并,我们这里有两个数据表data1和data1,将data1和data2两个数据表进行合并。在Excel中没有直接完成数据表合并的功能,可以通过VLOOKUP函数分步实现。在mysql中可以通过JOIN函数实现。
JOIN匹配常用的模式有三种,分布为INNER JOIN,LEFT JOIN,和RIGHT JOIN。下面分别给出三种匹配模型的代码和结果。

#INNER JOIN匹配查询
SELECT record_date, city, age, category, price,gender,pay,mp FROM data1 INNER JOIN data2 ON data1.id=data2.id;

inner匹配

# LEFT JOIN匹配查询
SELECT record_date, city, age, category, price,gender,pay,mp FROM data1 LEFT OUTER JOIN data2 ON data1.id=data2.id;

left匹配

# RIGHT JOIN匹配查询
SELECT record_date, city, age, category, price,gender,pay,mp FROM data1 RIGHT OUTER JOIN data2 ON data1.id=data2.id;

right匹配

 

数据排序

Excel中可以通过数据目录下的排序按钮直接对数据表进行排序,比较简单。mysql中需要使用ORDER BY完成排序。

排序

#数据升序排序
SELECT * FROM data1 ORDER BY age;

按年龄升序

#数据降序排序
SELECT * FROM data1 ORDER BY age DESC;

按年龄降序

#多列数据进行排序
SELECT * FROM data1 ORDER BY age,price DESC;

按年龄和价格排序
数据分组

Excel中可以通过VLOOKUP函数进行近似匹配来完成对数值的分组,或者使用“数据透视表”来完成分组。相应的mysql中可以使用CASE WHEN函数完成数据分组。

分类汇总

CASE WHEN函数用来对数据进行判断和分组,下面的代码中我们对age列的值进行判断,age小于30岁记录为A组,age大于等于30岁,小于50岁记录为B组,age大于等于50岁记录为C组,其他的年龄记录为D组。结果使用age_type字段进行标记。

#age字段分组
SELECT age,
CASE 
WHEN age<30 THEN 'A' 
WHEN age>=30 AND age<50 THEN 'B' 
WHEN age>=50 THEN 'C' 
ELSE 'D' END AS age_type
FROM data1;

年龄分组

#直接分组查询并汇总
SELECT COUNT(id) AS id_count, SUM(price)AS total_price,
CASE 
WHEN age<30 THEN 'A' 
WHEN age>=30 AND age<50 THEN 'B' 
WHEN age>=50 THEN 'C' 
ELSE 'D' END AS age_type
FROM data1 GROUP BY age_type ORDER BY id_count;

直接分组查询并汇总
数据分列

Excel中的数据目录下提供“分列”功能。在mysql中使用SUBSTRING_INDEX函数实现分列。

数据分列

#数据分列
SELECT SUBSTRING_INDEX(category,'-',1)AS size,SUBSTRING_INDEX(category,'-',-1)AS colour FROM data1;

数据分列结果

SELECT id,Record_date,city,age,category,price,SUBSTRING_INDEX(category,'-',1)AS size,SUBSTRING_INDEX(category,'-',-1)AS colour FROM data1;

数据分列结果查询

#按分列后的结果进行单列数据汇总
SELECT SUBSTRING_INDEX(category,'-',1) AS size,COUNT(id) FROM data1 GROUP BY size;

按分列结果进行计数

#按分列后的结果进行多列数据汇总
SELECT SUBSTRING_INDEX(category,'-',1) AS size,COUNT(id) AS id_count,ROUND(SUM(price),2) AS total_price FROM data1 GROUP BY size;

按分列结果进行计数和求和

分列后的数据可以通过更新增加在原数据表中,下面是具体的代码。

#数据分列(改表)
#添加两个空字段
ALTER TABLE data1 ADD (size VARCHAR(255),colour VARCHAR(255));
SELECT * FROM data1;

增加两个空字段

#更新分列后的字段内容
UPDATE data1 SET size = SUBSTRING_INDEX(category,'-',1),colour = SUBSTRING_INDEX(category,'-',-1);
SELECT * FROM data1;

数据分列并插入到表中
下一篇文章,也就是本系列的最后一篇我们将介绍5-7最后三部分的内容,分别为数据提取,数据筛选以及数据汇总及透视。请朋友们继续关注。

—【所有文章及图片版权归 蓝鲸(王彦平)所有。欢迎转载,但请注明转自“蓝鲸网站分析博客”。】—

Speak Your Mind

*