像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