上节给大家安利了Excel自带的SQL查询引擎，数据查询插件EFunction之中，万能查询函数ETSQL函数。本节继续上节介绍，10大典型案例模板，学好了，后续按照模板套路来，99%的复杂数据查询及统计工作都能应付。也希望之前没有接触过SQL脚本知识的同学，有个入门，对于后续学习SQL数据库有个入门，。

练习的样例数据为tableau学习资料数据集，全球超市数据。包括销量数据和退货数据。

说明：以下演示素材为tableau学习演示素材-全球超市订单数据。数据如下图所示，下文所有演示案例都基于本素材展开。

趁着年底各种汇报要做，提前整理出来本文，年底总结就直接可以派上用场了。文章较长，找好板凳，认真看，都是干货。

演示素材数据

模板1：复杂条件查询

任务：将姓名为“Aaron Bergman”的人所有订单都查询出来。并且按照订单时间从前到后排序。

SELECT * FROM [订单 $ ] AS a WHERE a.客户名称 = 'Aaron Bergman' ORDER BY a.订购日期 ASC

按照以上查询语句，可以将Aaron Bergman名下所有订单数据都给查询出来，并且能够按照订购日期将所有数据排序

Aaron Bergman 所有订单数据，并且按照降序排序

如果想进一步定位数据，想将Aaron Bergman最新三笔订单数据查询出来，则上述SQL查询语句，又得改写为：

SELECT top 3 * FROM [订单$] AS a WHERE a.客户名称 = 'Aaron Bergman' ORDER BY a.订购日期 desc

可以发现Aaron Bergman最新的三条订单有4条，这是因为top 3 函数执行这样的策略，如果参加排序的字段（列）有相同排名，则将相同排名数据一同提取出来。

Aaron Bergman 最新的三条订单

模板小结：

order by为排序关键词，后面跟着排序字段（列），可以类推Excel高级排序功能理解；
TOPN问题，可以使用top + number 关键词，提取前n个数据，具体提取数据多少，受排序字段影响；
数据过滤，可以使用where + conditions命令，多个条件之间使用and 或者or表示，表示条件集之间的交集或者并集。

模板2：求和统计

任务：统计每个细分市场的销售额，展示结果时按照降序排序

SELECT  a.细分市场,  sum( 销售额 ) AS 销售总额 FROM  [订单 $ ] AS a GROUP BY  a.细分市场 ORDER BY  sum( 销售额 ) DESC

三大细分领域总体销售额

将上述任务进一步深度统计，想统计每个细分领域历年，每年的总体销售额，则统计SQL脚本写法为：

SELECT  a.细分市场,  year(a.订购日期) AS 年份,  sum(销售额) AS 销售总额 FROM  [订单$] AS a GROUP BY  a.细分市场,  year(a.订购日期)ORDER BY  a.细分市场 asc,  year(a.订购日期) asc

统计出来，每个细分领域，历年销售额，通过数据可知道，不管哪个领域，历年销售额数据都是呈现递增趋势。

每份细分领域历年销售额

如果数据直接这样展示，不便于各个细分领域变化趋势对比分析。想将上述数据转化一下。利用透视表语法，就可以对数据进行透视分析了。则数据写法为：

Transform   sum(a.销售额) SELECT  CSTR(year(a.订购日期)) AS 年份FROM  [订单$] AS a GROUP BY  year(a.订购日期)pivot   a.细分市场

历年销售额变化趋势分析

是不是很强大，利用这个模板，就可以将数据透视表的功能，应用到函数了。这就可以规避数据透视表，需要手动刷新数据的缺点。

注意透视表语法的特点：

Transform语法，后面跟的就是统计的数据字段，求和、计数及取平均，均可；
pivot后面跟着的就是透视指标。这里跟着的是各个细分市场，例如将pivot后面字段改为“市场”。则透视结果立马调整。

Transform sum(a.销售额)SELECT  CSTR(year(a.订购日期)) AS 年份FROM  [订单$] AS a GROUP BY  year(a.订购日期)pivot   a.市场

根据市场调整透视结果

模板小结：

聚合运算，是数据分析领域一个重要课题，聚合运算有多种，最典型的统计运算有计数、求和、取平均值。在SQL里面，这些数据运算是有函数来完成的。上述三种工作对应点函数分别为count、sum和avg等函数。
聚合运算通常和分组同时出现；
Transform和pivot同时出现。实现对数据透视分析。

模板3：排名TOPN

任务：找出利润贡献最高的客户——钻石王老虎，并且将他购买的商品，按照利润率从高到低排序，显示出来：

该任务要先分解两个步骤：

查找出来利润最高的客户，王老五；
根据王老五，查找出来，王老五名义下所有订单。

先查询出来所有的人利润，并且按照降序排序，SQL脚本为：

SELECT a.[客户 ID]， sum(a.利润)FROM [订单$] AS a GROUP BY a.[客户 ID]order by sum(a.利润) desc

对利润数据进行排名找出最高客户

可以知道利润最高的是ID号为“TC-209801402”的客户，结合top 1命令，提取该ID号作为外层查询的条件，就可以提取该ID号下所有数据了，完整SQL脚本为：

select * from [订单$] where [客户 ID]=(  SELECT top 1    a.[客户 ID]  FROM    [订单$] AS a   GROUP BY    a.[客户 ID]  order by     sum(a.利润) desc) order by 利润 desc

利润最高的客户所有订单信息

通过以上模板就能够动态查询出来，利润最高的客户所有订单了。当原始数据有新增或者改变时，该模板脚本就能够动态统计出来结果。

上述模板，利用top函数，将利润贡献排在第一位的客户挑选出来。作为一个子查询，供主查询调用，最终将绿润率贡献最高的客户所有订单给匹配出来。这里核心关键词“子查询”和“op +n”t关键词。

关于TOP函数，再深入分析。有这样一种统计，想统计出来，中国各个省份，销售额前三的城市，并且每个省份销售额按照降序排序。这是不是有些复杂了，很可惜Excel SQL查询引擎没有窗口函数的概念，如果有窗口函数，该问题很好解决，不过不用担心，下面介绍一个模板，大家按这个模板套路来。

select   a.[省/市/自治区 (State/Province)],  a.[城市 (City)],  sum(a.销售额)  as 总销售额from [订单$] as a where   a.[国家/地区 (Country)]='中国'group by a.[省/市/自治区 (State/Province)], a.[城市 (City)]order by   a.[省/市/自治区 (State/Province)],  a.[城市 (City)],  sum(a.销售额)  desc

全国各省各城市销售额降序排序

因为Excel SQL引擎不支持窗口函数，统计各个省TOP3高销售额数据比较困难。这里借用EFunction一个很重要函数ETSamePrevious，完整公式为：=ETSamePrevious(ETSQL(SQL脚本,TRUE),1,3)

利用ETSQL函数统计出来所有的销售额，利用ETSamePrevious，提取top3数据

上述公式完整逻辑为，先利用ETSQL函数，提取出来中国各个省份所有成熟的总体销售额，然后利用ETSamePrevious函数，根据省份提取前三行数据，最后得到TOP3统计结果。

模板小结：

子查询，是SQL查询只是体系一个很重要概念，子查询可以作为另外一个查询的数据集，也可以作为其他查询的条件过滤数据，在现实统计业务场景之中，很多工作都要子查询配合完成；
Excel SQL查询引擎，没有窗口函数概念，提取TOPN结果，可以直接提取出来分割分组统计结果，在利用ETSamePrevious函数，提取每个数据的前N项结果。

模板4：数据综合排序

任务：模板三，各个省份排名是按照文本顺讯排序。现在想深入挖掘，想将销售额高的省份排在前面，低的省份排在后面。同省份内，销售额高的城市排在前面，低的省份排在后面。

select t1.*,t2.[总销售额] as 省份销售总额,t1.[总销售额]/t2.[总销售额] as 城市销售额占比 from(select a.[省/市/自治区 (State/Province)],  a.[城市 (City)],  sum(a.销售额)  as 总销售额from [订单$] as a where   a.[国家/地区 (Country)]='中国'group by  a.[省/市/自治区 (State/Province)],  a.[城市 (City)]order by   a.[省/市/自治区 (State/Province)],  sum(a.销售额)  desc ) t1 left join (select a.[省/市/自治区 (State/Province)],  sum(a.销售额)  as 总销售额from [订单$] as a where   a.[国家/地区 (Country)]='中国'group by a.[省/市/自治区 (State/Province)]order by   a.[省/市/自治区 (State/Province)],sum(a.销售额)  desc ) t2 on t1.[省/市/自治区 (State/Province)] = t2.[省/市/自治区 (State/Province)]order by  t2.[总销售额]  desc,  t1.[省/市/自治区 (State/Province)] desc,  t1.[总销售额] desc

广东省排第一位

上述脚本，直接结果，可以知道广东省排在了第一位。全省销售额最高。山东省其次。并且列出了各个城市销售额及占全省比重。

上述模板有几大知识点需要掌握：

SQL查询结果，可以当作一个虚拟表，针对这个虚拟表，可以进一步查询和统计，上述案例，第一个子查询，统计出来全国各省各个城市销售额数据，第二个子查询，查询出来各个省销售额。
两个子查询可以利用left join连接查询，将两张子查询虚拟表，连接查询。

上述模板写好后，数据有更新后，ETSQL函数可以自动更新，返回最新的统计结果。

模板5:典型分析模板

不是每一单都挣钱，想分析折扣率和订单利润率之间关系。

select  '折扣率' +CSTR(a.折扣) AS 折扣,a.利润/a.销售额 as 利润率from [订单$] as a order by  '折扣率' +CSTR(a.折扣) asc

折扣率 vs 利润率

通过分析可知，折扣率越大，利润率越低。赔钱挣了个吆喝。在很多实际业务场景之中，可以通过折扣达到引流目的，或者处理库存积压产品。

selecta.[子类别],sum(a.销售额) as 子类别销售额,sum(a.利润)/sum(a.销售额) as 利润率from [订单$] as a group by a.[子类别]order bysum(a.销售额)  desc

子类别利润

桌子利润率最低，成为年终奖的后腿。电话和复印机等3C产品，销售额和利润率总体不错。纸张、便笺等高利润产品，有待进一步开发提升销量。

各个地区，利润贡献上。

Transform sum(a.利润)SELECT  CSTR(year(a.订购日期)) AS 年份FROM  [订单$] AS a GROUP BY  year(a.订购日期)pivot   a.市场

各例如利润贡献情况

欧洲市场和亚洲市场利润贡献最大。同时欧洲市场利润增长加速。

Transform count(1)SELECT  CSTR(year(a.订购日期)) AS 年份,month(a.订购日期) as 月份FROM  [订单$] AS a GROUP BY  year(a.订购日期),  month(a.订购日期)pivot   round(a.折扣,1)

折扣订单纵览分析

分析可知，高折扣订单毕竟还是少数，大多数订单均能按原价出售。

总结

以上总结了一些典型Excel SQL脚本知识，基本上能够满足日常数据统计需求。以上脚本均是可执行脚本，大家可以使用tableau数据集多加练习，熟练掌握。后续有时间研究下和power bi区别。

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。