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

上传人:b****9 文档编号:52959 上传时间:2022-10-01 格式:DOCX 页数:15 大小:683.33KB
下载 相关 举报
像Excel一样使用SQL进行数据分析(2).docx_第1页
第1页 / 共15页
像Excel一样使用SQL进行数据分析(2).docx_第2页
第2页 / 共15页
像Excel一样使用SQL进行数据分析(2).docx_第3页
第3页 / 共15页
像Excel一样使用SQL进行数据分析(2).docx_第4页
第4页 / 共15页
像Excel一样使用SQL进行数据分析(2).docx_第5页
第5页 / 共15页
点击查看更多>>
下载资源
资源描述

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

《像Excel一样使用SQL进行数据分析(2).docx》由会员分享,可在线阅读,更多相关《像Excel一样使用SQL进行数据分析(2).docx(15页珍藏版)》请在冰豆网上搜索。

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

像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 

展开阅读全文
相关资源
猜你喜欢
相关搜索
资源标签

当前位置:首页 > 表格模板 > 合同协议

copyright@ 2008-2022 冰豆网网站版权所有

经营许可证编号:鄂ICP备2022015515号-1