像Excel一样使用SQL进行数据分析(2)Word文档下载推荐.docx
- 文档编号:12982606
- 上传时间:2022-10-01
- 格式:DOCX
- 页数:15
- 大小:683.33KB
像Excel一样使用SQL进行数据分析(2)Word文档下载推荐.docx
《像Excel一样使用SQL进行数据分析(2)Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《像Excel一样使用SQL进行数据分析(2)Word文档下载推荐.docx(15页珍藏版)》请在冰豆网上搜索。
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);
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'
=50THEN'
C'
ELSE'
D'
ENDASage_type
FROMdata1;
#直接分组查询并汇总
SELECTCOUNT(id)ASid_count,SUM(price)AStotal_price,
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,COUNT(id)FROMdata1GROUPBYsize;
#按分列后的结果进行多列数据汇总
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);
下一篇文章,也就是本系列的最后一篇我们将介绍5-7最后三部分的内容,分别为数据提取,数据筛选以及数据汇总及透视。
。
请朋友们继续关注。
END
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 一样 使用 SQL 进行 数据 分析