|
|
|
|
公众号矩阵

Excel 执行SQL查询函数

之前给大家介绍过,利用Excel内置的SQL查询引擎,来执行查询操作。通过编写SQL查询语句,可以完成Excel内置函数不能够完成的工作。带好笔纸做笔记吧,下面通过几个案例,介绍SQL查询语句的基本用法,如果有类似的工作,只需要更改几个地方就可以了。

作者:Excel数据分析阶梯来源:今日头条|2020-11-11 14:36

之前给大家介绍过,利用Excel内置的SQL查询引擎,来执行查询操作。通过编写SQL查询语句,可以完成Excel内置函数不能够完成的工作。带好笔纸做笔记吧,下面通过几个案例,介绍SQL查询语句的基本用法,如果有类似的工作,只需要更改几个地方就可以了。当然如果条件允许,还是建议你系统学习一下SQL语句,提升下综合技能。因为Excel内置引擎网络上资料很少,大家可以以SQLSERVER (都是微软的产品,虽有区别,但还是有很大的相通的地方的)教程为模板学习,网络上资料很多。

使用Excel进行数据处理,不外乎两种应用场景:

  • 数据匹配(数据查找)
  • 数据聚类(求和,平均,总数)

数据素材

数据素材为从GitHub上获取关于COVID-19数据集。如果大家需要素材,请留言回复“ETSQL”获取练习素材。

这里面要用到一个函数ETSQL,它是EFunction内置的函数。

Excel 执行SQL查询函数
数据素材包括全球和国内的数据

案例1:数据匹配

“国家”Sheet表格内对应的是,各个省份每天累计数据和新增数据。假如需要提取出来上海每天的数据信息。则SQL语句为:

  1. select * from [国家$] as a where a.省份='上海市' 

对于学习过SQL语句的朋友来说,Excel内置引擎,SQL语句和其他关系数据库的查询语法基本相同。对于未接触SQL语句的朋友来说,可以这样简单理解。

  • select 关键词是必须的,表示要查询一个信息关键词,每个语句必须有的。
  • from 关键词也是必须的,表示从什么地方查询,Excel表格表示从“国家”这个Sheet之中查询。
  • where 关键词也是必须的,表示是查询的筛选条件,它后面跟着的就是筛选条件,案例之中,要求“国家”Sheet表之中,省份等于“上海市”,如果有多个条件使用关键词and 或者or进行连接。

通过以上语句,就能够将上海每天的数据全部提取出来。

Excel 执行SQL查询函数
从“国家”表格之中提取出上海所有信息

如果要提取出来上海市,2020-40-20日之后所有的数据,则上述SQL语句只需要变为

  • select * from [国家$] as a where a.省份='上海市' and a.日期>43941。

对于熟悉MySQL或者MsSQL的朋友来说,Excel之中没有“时间”类型数据,Excel之中只有数值类型数据,Excel之中是以数字表示时间的,43941就对应2020-40-20这一天。当然在实际应用过程之中,可以使用公式进行拼接SQL语句,Excel会自动将日期转化为数字类型数据处理的。

案例之中,就是将B1和F1单元格内的数据,进行拼接到A1单元格之中,Excel自动生成SQL完整语句。

Excel 执行SQL查询函数
SQL拼接

如果学习好了SQL查询语句后,再配合ETSQL函数,Excel之中所有匹配函数就可以说拜拜了。当然SQL语句编写起来没有专用的函数方便。但SQL很适合用来进行复杂数据匹配工作,这点VLOOKUP,match等函数,是万万没有这个技能的。所以说没有最好的工具,只有适合的工具。

案例2:数据聚合(求和,平均、计数)

如果要统计上海市,每天新增人数的累加和-累计确诊人数时,这个时候,就可以应用到SQL另外一个强大的功能,数据聚合,这个功能很像Excel自带的透视表功能,这相当于透视表函数化了,我们把Excel语句写好了,只需要F9刷新数据就OK了。基本语句为

  • select sum(当日新增) from [国家$] as a where a.省份='上海市'

上述语句通过SQL引擎的sum函数(需要注意这个sum和Excel函数SUM的区别),统计“当日新增”这个字段数据的和,条件是省份为上海市。

如果说我要统计上海市每天的平均新增人数,该怎么写:

  • select avg(当日新增) from [国家$] as a where a.省份='上海市'

只需要把sum替换为avg求平均函数就OK了。如果要统计上海市有多少天有新增确诊记录时,则条件SQL语句为:

  • select count(当日新增) from [国家$] as a where a.省份='上海市' and a.当日新增>0

count函数为计数统计函数,同时添加了另外一个条件,就是添加了当日新增人数大于“0”值的数据。

以上介绍的聚合,是通过where条件来了,如果说我要统计全国所有省份总确诊人数,总不能连续写30多个SQL语句吧!

  • 正常的SQL语句为:select a.省份,sum(a.当日新增) from [国家$] as a group by a.省份

这个使用到的是group by 关键词。这个特点是不是很像Excel的透视表。group by后面相当于透视表“行”条件,select 后面相当于透视表的“列”条件,sum或者count或者avg相当于透视表的“值”

Excel 执行SQL查询函数
统计累计确诊人数

以上统计出来的结果是杂乱无章的,这个时候稍加改动就可以排序了

select a.省份,sum(a.当日新增) from [国家$] as a group by a.省份 order by sum(a.当日新增) desc

Excel 执行SQL查询函数
排好序的统计结果

order by 关键词后就是排序条件,将统计出来的 sum(a.当日新增) 进行排序,desc表示降序。如果要升序的话,可以省略或者写上asc。为了增加SQL语句可读性,建议你写上。

小结

通过以上介绍,可以知道利用ETSQL一个函数,通过不同的SQL语句,就可以完成Excel自带的函数几乎所有功能,是不是很强大。这里并不是说不用学习其他函数了,SQL语句适合用来解决复杂的问题,以下列出的案例需求,Excel单独一个函数是不能够完成的。

  • 截止到某天总确诊人数的TOP3省份
  • 匹配出来每个省份新增确认记录是哪天
  • 匹配出来大于平均值所有省份
Excel 执行SQL查询函数
ETSQL复杂统计

因为SQL是系统性知识,在这里码字确实困难。不知有无必要录制视频,来系统介绍Excel内置SQL引擎基本知识。大家如果有需要,请留言“SQL视频关键词”,告知下,同时添加关注,以免视频更新时,错过了。

【编辑推荐】

  1. 我是Redis,MySQL大哥被我害惨了!
  2. 从零到千万用户,我是如何一步步优化MySQL数据库的?
  3. 为啥不建议MySQL使用Text类型?
  4. MySQL逻辑数据恢复体系的设计
  5. MySQL 启动失败的常见原因
【责任编辑:未丽燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

28人订阅学习

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

193人订阅学习

搭建数据中心实验Lab

搭建数据中心实验Lab

实验平台Datacenter
共5章 | ITGO(老曾)

119人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微