SQL为什么支持聚合查询？这似乎是一个幼稚的问题，但让我们一步一步地思考它。数据以行为粒度存储。最简单的SQL语句是select*fromtest，获取整个二维表的详细信息，但这还远远不够。SQL出于以下两个目的，需要提供聚合功能：明细数据没有统计意义。比如我想知道今天的总营业额，不太关心某张桌子消费了多少。虽然可以先把数据查到内存里再聚合，但是当数据量很大的时候很容易爆内存。可能一张表一天有10TB的数据，即使10TB的数据可以读入内存，Aggregate计算也会慢的让人无法接受。另外，聚合本身具有一定的逻辑复杂性，而SQL提供了聚合函数和分组聚合能力，可以方便快捷地计算出具有业务价值的聚合数据，从而确立了SQL语言的分析价值，所以大部分分析软件直接使用SQL作为直接面向用户的表达。聚合函数常见的聚合函数有：COUNT：计数。SUM：求和。AVG：平均值。MAX：求最大值。MIN：求最小值。COUNTCOUNT用于计算有多少条数据。比如我们看看id列有多少条数据：SELECTCOUNT(id)FROMtest但是我们发现任意一列的COUNT其实都是一样的，那么传入id有什么意义呢？不需要找具体的列来引用，所以也可以写成：SELECTCOUNT(*)FROMtest，但两者有细微的差别。SQL中有一个非常特殊的值类型NULL。如果COUNT指定了特定的列，统计时将跳过该列中值为NULL的行。但是，由于COUNT(*)没有指定具体的列，所以即使包含NULL，即使一行中的所有列都是NULL，也会被包含在内。因此，COUNT(*)的结果必须大于或等于COUNT(c1)。当然任何聚合函数都可以跟在查询条件WHERE之后，例如：SELECTCOUNT(*)FROMtestWHEREis_gray=1SUMSUM对所有项求和，所以必须作用于数字字段，而不是字符串。SELECTSUM(cost)FROMtestSUM将NULL值视为0，因为这相当于忽略。AVGAVG计算所有项的平均值，因此它必须用于数字字段，而不是字符串。SELECTAVG(cost)FROMtestAVG采用了最彻底的忽略NULL值的方式，即NULL根本不参与分子和分母的计算，就好像这一行数据不存在一样。MAX、MINMAX、MIN分别求最大值和最小值。以上不同时，也可以应用于字符串，所以可以根据字母来判断大小，对应a-z从大到小，但是即使可以计算，也没有实际意义，是不容易理解，所以不建议求字符串的极值。SELECTMAX(cost)FROMtest的多个聚合字段虽然是聚合函数，但是MAX和MIN并不是严格意义上的聚合函数，因为它们只查找满足条件的行。可以看下面两个查询结果的对比：SELECTMAX(cost),idFROMtest--id:100SELECTSUM(cost),idFROMtest--id:1第一个查询可以找到该行的id有最大值，而第二次查询的id是没有意义的，因为我们不知道它属于哪一行，所以只返回第一个数据的id。当然，如果同时计算MAX和MIN，那么此时id只会返回第一条数据的值，因为查询结果对应复数行：SELECTMAX(cost),MIN(cost),idFROMtest--id:1基于这些特点，聚合和非聚合最好不要混用，即一旦查询有一个字段是聚合的，那么所有的字段都必须聚合。现在很多BI引擎的自定义字段都有这个限制，因为在自定义内存计算中混用聚合和非聚合的时候有很多borderlinecase。SQL虽然可以支持，但是业务定义的函数可能不支持。分组聚合分组聚合就是GROUPBY，可以看成是一种高级条件语句。例如查询每个国家的GDP总和：SELECTCOUNT(GDP)FROMamazing_tableGROUPBYcountry返回的结果会按国家分组。这时聚合函数就变成了组内聚合。其实如果我们只想看中美两国的GDP，也可以使用不分组的方式来查找，但是需要分成两条SQL：SELECTCOUNT(GDP)FROMamazing_tableWHEREcountry='China'SELECTCOUNT(GDP)FROMamazing_tableWHEREcountry='UnitedStates'所以GROUPBY也可以理解为查出某个字段的所有可枚举条件，并整合到一张表中。每行代表一个枚举条件，不需要分解成单独的WHERE查询。多字段分组聚合GROUPBY可以用于多个维度，相当于查询表时将行/列拖入多个维度。以上是BI查询工具的视角。如果没有上下文，可以看到下面的递进说明：按多个字段分组聚合。多个字段组合成唯一键，即GROUPBYa,b表示a和b共同描述一个组。GROUPBYa,b,c查询结果的第一列可能会看到很多重复的a行，第二列可能会看到重复的b行，但是在a的相同值内不会有重复。b行中的c也是如此。下面是一个例子：SELECTSUM(GDP)FROMamazing_tableGROUPBYprovince,city,area查询结果为：浙江杭州余杭区浙江杭州西湖区浙江宁波海曙区浙江宁波江北区北京.........GROUPBY+WHEREWHERE基于行的条件过滤。因此，GROUPBY+WHERE不是组内过滤，而是整体过滤。但是由于按行过滤，在组内和不在组内的结果是完全一样的，所以我们几乎察觉不到这个区别：SELECTSUM(GDP)FROMamazing_tableGROUPBYprovince,city,areaWHEREindustry='internet'但是，忽略这个差异会导致我们在聚合过滤的时候碰壁。比如要过滤掉平均分大于60的学生的成绩总和，如果不使用子查询，那么在普通查询的WHERE中是不能加聚合函数的。例如下面是一个语法错误的例子：SELECTSUM(score)FROMamazing_tableWHEREAVG(score)>60不要幻想上面的SQL可以执行成功，也不要在WHERE中使用聚合函数。GROUPBY+HAVINGHAVING是根据分组条件进行过滤。因此，可以在HAVING中使用聚合函数：SELECTSUM(score)FROMamazing_tableGROUPBYclass_nameHAVINGAVG(score)>60上面的例子中可以正常查询，也就是按照类分组看总分，并且只过滤掉平均分大于60的类。那么为什么HAVING可以使用聚合条件呢？因为HAVING过滤组，聚合组并过滤掉不符合条件的组是有意义的。而WHERE用于行粒度。聚合后全表只有一条数据，过滤不过滤都没意义。但需要注意的是，GROUPBY生成的派生表不能使用索引过滤，所以WHERE可以使用索引到字段来优化性能，而HAVING对索引字段不起作用。总结聚合函数+分组可以满足大部分简单的SQL需求。在编写SQL表达式时，需要考虑这些表达式是如何计算的。比如MAX(c1),c2是合理的，而SUM(c1),c2是合理的。c2没有意义。最后记住WHERE是在GROUPBY之前执行的，HAVING是针对组的过滤器。讨论地址为：Jingdu《SQL 聚合查询》·Issue#401·ascoders/weekly想参与讨论的请点这里，每周都有新话题，周末或周一发布。前端精读——帮你过滤靠谱的内容。关注前端精读微信公众号
