像Excel一样使用SQL进行数据分析(2).docx
《像Excel一样使用SQL进行数据分析(2).docx》由会员分享，可在线阅读，更多相关《像Excel一样使用SQL进行数据分析(2).docx（15页珍藏版）》请在冰豆网上搜索。
像Excel一样使用SQL进行数据分析
（2）
Excel是数据分析中最常用的工具，本篇文章通过mysql与excel的功能对比介绍如何使用mysql完成excel中的数据生成，数据清洗，预处理，以及最常见的数据分类，数据筛选，分类汇总，以及数据透视等操作。
本篇文章我们介绍第3,4部分内容，数据表清洗及数据预处理。
3，数据表清洗
第三部分是对数据表中的问题进行清洗。
主要内容包括对空值，大小写问题。
这里不包含对数据间的逻辑验证。
处理缺失值（填充）
我们在创建数据表的时候在price字段中故意设置了几个0值。
对于空值的处理方式有很多种，可以直接删除包含空值的数据，也可以对空值进行填充，比如使用均值填充。
还可以根据不同字段间的逻辑对空值进行推算，例如通过回归计算填充值。
Excel中可以通过“查找和替换”功能对空值进行处理，将空值统一替换为0或均值。
也可以通过“定位”空值来实现。
我们选择填充的方式来处理空值，使用price列的均值来填充0值字段。
具体分为两个步骤，第一步计算data1数据表中price列的均值，并保留两位小数。
第二步使用price列的均值更新price列中0值的字段。
#计算price列的均值
SELECTROUND（AVG（price）,2）ASavg_priceFROMdata1;
#使用均值填充0值
UPDATEdata1SETprice=2199.67WHEREprice=0;
清理空格
除了空值，字符中的空格也是数据清洗中一个常见的问题，mysql中使用TRIM函数清洗数据两侧的空格，下面是清除字符中空格的代码。
#清理字符中的空格
UPDATEdata1SETcity=TRIM（city）;
selectcityfromdata1;
数值修改及替换
数据清洗中最后一个问题是数值修改或替换，Excel中使用“查找和替换”功能就可以实现数值的替换。
Mysql中使用REPLACE函数完成数值修改和替换的操作。
#修改和替换
UPDATEdata1SETcity=REPLACE（city,'SH','shanghai'）;
SELECTcityFROMdata1;
大小写转换
在英文字段中，字母的大小写不统一也是一个常见的问题。
Excel中有UPPER，LOWER等函数，mysql中也有同名函数用来解决大小写的问题。
在数据表的city列中就存在这样的问题。
我们将city列的所有字母转换为小写。
下面是具体的代码和结果。
#大小写转换
UPDATEdata1SETcity=LOWER（city）;
selectcityfromdata1;
4，数据预处理
第四部分是数据的预处理，对清洗完的数据进行整理以便后期的统计和分析工作。
主要包括数据表的匹配合并，排序，数值分列，数据分组及标记等工作。
数据表匹配合并
首先是对不同的数据表进行合并，我们这里有两个数据表data1和data1，将data1和data2两个数据表进行合并。
在Excel中没有直接完成数据表合并的功能，可以通过VLOOKUP函数分步实现。
在mysql中可以通过JOIN函数实现。
JOIN匹配常用的模式有三种，分布为INNERJOIN，LEFTJOIN，和RIGHTJOIN。
下面分别给出三种匹配模型的代码和结果。
#INNERJOIN匹配查询
SELECTrecord_date,city,age,category,price,gender,pay,mpFROMdata1INNERJOINdata2ONdata1.id=data2.id;
#LEFTJOIN匹配查询
SELECTrecord_date,city,age,category,price,gender,pay,mpFROMdata1LEFTOUTERJOINdata2ONdata1.id=data2.id;
#RIGHTJOIN匹配查询
SELECTrecord_date,city,age,category,price,gender,pay,mpFROMdata1RIGHTOUTERJOINdata2ONdata1.id=data2.id;
数据排序
Excel中可以通过数据目录下的排序按钮直接对数据表进行排序，比较简单。
mysql中需要使用ORDERBY完成排序。
#数据升序排序
SELECT*FROMdata1ORDERBYage;
#数据降序排序
SELECT*FROMdata1ORDERBYageDESC;
#多列数据进行排序
SELECT*FROMdata1ORDERBYage,priceDESC;
数据分组
Excel中可以通过VLOOKUP函数进行近似匹配来完成对数值的分组，或者使用“数据透视表”来完成分组。
相应的mysql中可以使用CASEWHEN函数完成数据分组。
CASEWHEN函数用来对数据进行判断和分组，下面的代码中我们对age列的值进行判断，age小于30岁记录为A组，age大于等于30岁，小于50岁记录为B组,age大于等于50岁记录为C组,其他的年龄记录为D组。
结果使用age_type字段进行标记。
#age字段分组
SELECTage,
CASE
WHENage<30THEN'A'
WHENage>=30ANDage<50THEN'B'
WHENage>=50THEN'C'
ELSE'D'ENDASage_type
FROMdata1;
#直接分组查询并汇总
SELECTCOUNT（id）ASid_count,SUM（price）AStotal_price,
CASE
WHENage<30THEN'A'
WHENage>=30ANDage<50THEN'B'
WHENage>=50THEN'C'
ELSE'D'ENDASage_type
FROMdata1GROUPBYage_typeORDERBYid_count;
数据分列
Excel中的数据目录下提供“分列”功能。
在mysql中使用SUBSTRING_INDEX函数实现分列。
#数据分列
SELECTSUBSTRING_INDEX（category,'-',1）ASsize,SUBSTRING_INDEX（category,'-',-1）AScolourFROMdata1;
SELECTid,Record_date,city,age,category,price,SUBSTRING_INDEX（category,'-',1）ASsize,SUBSTRING_INDEX（category,'-',-1）AScolourFROMdata1;
#按分列后的结果进行单列数据汇总
SELECTSUBSTRING_INDEX（category,'-',1）ASsize,COUNT（id）FROMdata1GROUPBYsize;
#按分列后的结果进行多列数据汇总
SELECTSUBSTRING_INDEX（category,'-',1）ASsize,COUNT（id）ASid_count,ROUND（SUM（price）,2）AStotal_priceFROMdata1GROUPBYsize;
分列后的数据可以通过更新增加在原数据表中，下面是具体的代码。
#数据分列（改表）
#添加两个空字段
ALTERTABLEdata1ADD（sizeVARCHAR（255）,colourVARCHAR（255））;
SELECT*FROMdata1;
#更新分列后的字段内容
UPDATEdata1SETsize=SUBSTRING_INDEX（category,'-',1）,colour= SUBSTRING_INDEX（category,'-',-1）;
SELECT*FROMdata1;
下一篇文章，也就是本系列的最后一篇我们将介绍5-7最后三部分的内容，分别为数据提取，数据筛选以及数据汇总及透视。
。
请朋友们继续关注。
END
升级会员 