下载掌阅APP，畅读海量书库
立即打开
汇总分析在工作里经常要用到，比如，分析每个部门的销售业绩。这个类型的题目主要考查如何用SQL进行汇总分析。
考查知识点：
· 汇总函数、分组汇总、SQL书写顺序和运行顺序。
说到汇总分析，必须要提到汇总函数，那什么是汇总函数呢？
汇总函数也被称为聚合函数。简单来说，汇总函数就是在一堆繁杂数据里进行类似求和、求平均值、求最大值、求最小值等运算后聚合成一个结果的数据。如图3.2所示，我们整理天气数据时需要统计一周内多云和闪电总共出现多少次？数据量不大时，我们通过加法运算即可得到：3+2=5。
图3.2 不同天气的简单汇总
那么，如果对应到SQL中，如何实现呢？当数据量大时，我们就无法用上述手工计算的方式了，要用到SQL的汇总函数。
上述的天气案例，假设天气数据放在数据库的“天气表”中，可以使用汇总函数中的求和函数（sum()函数）快速统计出“天气”列的总数，SQL语句的书写方法如下：
返回结果如图3.3所示。
图3.3 使用求和函数sum()的计算结果
SQL中常用的汇总函数如表3.9所示。
表3.9 常用的汇总函数
使用汇总函数时，要根据业务情况，灵活判断是否要先去掉重复数据，再统计数据，例如下面的面试题。
面试题4：游戏App用户分析
某游戏公司为了监测新上市的游戏App的受欢迎程度，通过数据来分析用户的总数、用户的平均年龄。表3.10所示为“用户登录信息表”。
表3.10 用户登录信息表
· 登录日期：记录用户登录游戏App的时间。
· 用户ID：用户的唯一标识。
· 用户年龄：用户在App中记录的年龄。
现需要分析出用户的总数、用户的平均年龄。
求用户的总数，可以用汇总函数（对“用户ID”列求和：sum()函数）。
求用户的平均年龄，可以用汇总函数（对“用户年龄”列求平均值：avg()函数）。
但是，如果直接进行汇总计算，则会有问题，是什么问题呢？
观察表3.10中的数据，可以看到同一用户同一天有多条登录记录，也就是表中存在重复数据。如果直接用汇总函数进行计算，则会把重复数据计算在内，所以，应该先按照“用户ID”对重复数据进行去重（用distinct关键字），再分析用户的总数及平均年龄。
SQL语句的书写方法如下：
查询结果如表3.11所示。
表3.11 用户的总数和平均年龄
汇总函数也经常用于计算常用的指标，例如下面的面试题。
面试题5：公司经营指标
表3.12所示的“销售订单表”记录了公司的销售情况，每一行数据表示哪位顾客、哪一天、在哪个交易网点购买了什么产品，购买的数量是多少，以及对应产品的零售价。
表3.12 销售订单表
分析购买人数、总销售金额、客单价、客单件、人均购买频次。
其中：
销售金额=销售数量×零售价
客单价=总销售金额/购买人数
客单件=总销售数量/购买人数
人均购买频次=总订单数/购买人数
（1）购买人数。
我们可以用表3.12“销售订单表”中的“顾客ID”来汇总出购买人数。
在实际销售中，一个顾客可以在一个交易网点购买多次，或者在多个交易网点购买多次。因此在计算“购买人数”时，需要用关键字distinct去重后，再用count()函数计数，得到购买人数。
SQL语句的书写方法如下：
（2）总销售金额。
销售金额=销售数量×零售价，总销售金额为各个产品销售金额之和。销售数量、零售价都在表3.12所示的“销售订单表”中。
SQL语句的书写方法如下：
（3）客单价。
客单价指的是平均每个顾客购买产品的金额。总销售金额、购买人数在前面我们已经分析出来了，现在计算客单价就简单了，计算逻辑为：客单价=总销售金额/购买人数。
SQL语句的书写方法如下：
（4）客单件。
客单件是平均每个顾客购买的件数，计算逻辑为：客单件=总销售数量/购买人数。
SQL语句的书写方法如下：
（5）人均购买频次。
人均购买频次是平均每个顾客购买几次，人均购买频次=总订单数/购买人数。
总订单数可以通过对“订单号”列计数得到。
SQL语句的书写方法如下：
汇总以上5个指标的SQL语句，最终如下：
在实际工作中，汇总函数经常和分组（group by子句）结合在一起来分析数据，所以，面试中会经常会考查分组汇总（汇总函数+分组）的知识。
当面试题中涉及“查找重复数据”或者类似“每个”“每天”这样的词汇时，要马上想到可以用分组汇总来实现。
我们通过一个面试题来看一下如何用SQL进行分组汇总。
面试题6：查找重复数据
查找表3.13所示“学生表”中所有重复的学生姓名。
表3.13 学生表
（1）分组汇总：按“姓名”分组（group by子句），再用聚合函数中的计数函数count()对“姓名”列计数。
（2）筛选出计数大于1的姓名，就是重复的姓名。
为了整理思路，我们先手动找出重复数据，然后对应看如何用SQL来实现。直观来看，可以了解到“姓名”列有“猴子”和“小王”两个重复的姓名值，如图3.4所示。
方法一
（1）通过创建一个辅助表，将“姓名”列进行分组和汇总。
SQL语句的书写方法如下：
查询结果如图3.5所示。
图3.4“学生表”中重复姓名的学生
图3.5 对“姓名”列进行分组和汇总
（2）通过辅助表数据，查询重复计数大于1的姓名。
SQL语句的书写方法如下：
查询结果如图3.6所示。
图3.6 重复计数大于1的姓名
（3）结合前两步，将第（1）步中创建的辅助表作为子查询，进行重复姓名计数大于1的SQL查询。
SQL语句的书写方法如下：
查询结果如图3.7所示。
图3.7 查询所有重复的学生姓名
方法二
这时有的读者可能会想，为什么要这么麻烦创建一个子查询，不能用下面的语句（将count()函数放到where子句中）直接得出答案吗？
如果我们运行上面的SQL语句，则会出现如图3.8所示的错误，问题出在哪里呢？
图3.8 SQL语句错误提示
前面提到的聚合函数（count()），where子句无法与聚合函数一起使用。因为where子句的运行顺序排在第二，运行到where子句时，表还没有被分组。
如果要对分组后的结果按条件筛选，则需要用到having子句。所以，这道题的最优解法如下：
（1）考查解题思路，比较两种解题方法的差别。
（2）考查对having子句的掌握，having子句表示对分组后的结果按条件筛选。
（3）熟记SQL语句的书写顺序和运行顺序。
（4）考查分组汇总的应用，切莫把聚合函数写到where子句中。
通过面试题6，我们可以总结出查找“重复数据”的SQL万能模板。
万能模板 查找重复数据
查找重复数据的模板如下。模板中所示的“列名”，表示在该列中查找重复的数据。
进一步，我们还可以衍生出，查找重复出现N次数据的SQL万能模板，只需要把having里的条件设置为=N：
group by作为条件分组子句，并不是通过某个列名条件分组就万事大吉了，在SQL语句中使用它还需要注意：当group by后面跟多个列时，这几个列的值全部相同才能算为一组。
例如，下面SQL语句中的“group by姓名，性别”表示只有姓名、性别都相同的学生才能算一组。
面试题7：城市人口流动分析
表3.14所示为统计每天各个城市之间的人口流入、流出的“各城市人口流动表”。
表3.14 各城市人口流动表
＊交通工具（1表示汽车，2表示火车，3表示飞机）
我们通过表中第一行理解表中各字段的含义。例如，猴子老家是长春，乘坐汽车，到合肥工作，那么对应这个表中的字段就是，“流出城市”是“长春”，“流入城市”是“合肥”，交通工具是1（表示汽车）。表中的字段“数量”表示从“流出城市”到“流入城市”的人口数量。
面试官问应聘者：如何分析每个城市的总流入人口数量？
这里涉及“每个”，所以用汇总分析来实现，问题拆解为下面两步。
（1）分组：按“流入城市”分组（group by子句），得到“每个城市”。
（2）汇总：题中提到“总流入人口数量”，对应计算为求和，用到汇总函数里的求和函数sum()。
SQL语句的书写方法如下：
查询结果如表3.15所示。
表3.15 各个城市的总流入人口数量
考查分组汇总的应用，当遇到的面试题中涉及类似“每个”“每天”这样的词汇时，要想到用分组汇总或者窗口函数来实现。