分组？原来你是玩儿真的！

数据计算中，分组绝对是最常用的计算方式之一，相应的，在SQL语言中，自然会用到group by了。但是，不知道你想过没有，SQL中的group by并不能算是“纯粹”的分组，它实际上并不是针对原始数据分组，而是将结果集分组，最终是为了实现5类聚合计算：min/max/avg/count/sum，而单独使用group by没有任何意义，只是相当于按照group by的条件进行了排序而已。

但实际的数据计算中，针对分组数据的统计要求有可能远远超过5类聚合计算的能力。换句话说，对原始记录执行“真实”的分组操作，将记录分为多个子集，这样的分组方式不仅更为自然，而且也便于执行一些更为复杂的分组统计。这种自然而纯粹的思路，在集算器中得到了真正的体现。下面我们会通过分组汇总、分组子集，以及枚举分组三类问题来进一步说明这种思路的真正优势。

1、分组汇总

先来看看用SQL的分组计算吧，其实就是各类分组汇总计算。查看SQL执行结果的方法和工具很多，我们在这里使用集算器直接调用SQL通过JDBC执行查询：

CITIES表中存储了一些城市的人口数据，A1中查询出CITIES的所有内容，结果如下：

SQL的group by从句可以和各类聚合函数配合完成计算，这里A2中的SQL，就是根据这些城市所在州编号STATEID分组，并分别用count和sum函数统计出每个州中城市的总数和总人口，并按州序号排序。查询结果如下：

而A3中的SQL根据城市名称的首字母分组，并计算出首字母相同的城市的平均人口，以及最大和最小人口数，同时将结果按首字母执行了排序，结果如下：

从这两个例子可以直观的看出，在SQL中进行“分组”时，得到的并不是“组”，只能得到 “组的计算结果”，更别说能得到分组中的各条记录了。

在集算器中，我们可以利用groups函数，实现和上面SQL等效的统计计算：

A1中取出CITIES表中全部数据：

A2和A3中计算分组统计，统计时可以直接使用A1中已获得的数据，不必再访问数据库。A2中分组汇总的结果和前面用SQL时的情况是相同的：

A3中分组汇总的结果和前面有一些区别，由于集算器中计算平均值时会默认使用双精度处理，而SQL在计算整数均值时会保留整数部分：

除了groups和SQL一样能够直接分组汇总之外，在集算器中还可以利用group函数“仅仅”将记录分组，然后在分组结果的基础上，进一步进行汇总计算，如：

A2中将数据根据STATEID分为多组，结果如下：

展示数据时，每个分组都可以双击查看，可以发现每个分组都是由1条或多条记录构成的。比如STATEID为1的城市有两个：Birmingham和Montgomery。

在A3中，根据这个分组结果来进一步查询，统计出每个州中城市的总个数和总人口，这里的语法和SQL有所不同，如统计某个组内记录总数写为~.count()。A3中的计算结果如下：

这里的结果和前面用SQL和groups函数的情况是相同的。两者相比，用先分组再聚合计算的方式，过程比较直观且容易控制，而直接聚合获得结果效率较高。

与A2类似，A4中根据城市名称的首字母将数据分组，结果如下：

在A5中，根据分组结果来计算，统计出每个首字母编组中，人口的平均值和最大最小值。

使用先分组再聚合计算的方法时，统计方法比较自由，并不局限于各类聚合函数，比如这里在计算人口平均值时，就可以用int函数保留平均值的整数部分，获得的汇总结果与SQL相同：

如果不需要查看中间的分组结果，也可以用group函数另一种写法直接计算出结果：

这里，A2执行的计算其实就相对于前一个例子中A2和B2中计算，结果也和B2中相同。A3相当于前例中A3和B3的计算，结果和B3相同。这种方式得到的结果和前面使用groups时是相同的，不过语法略有不同，能够完成的统计计算更为自由，但计算效率会比groups低一些。

2、分组子集

SQL的分组除了只能得到分组汇总的结果，查询时也只能查询分组时使用的字段和聚合结果，这其实和分组时每组的数据有关，比如下面是按城市首字母分组时，B开头的城市：

在这样的分组中，除了分组时使用的NAME首字母，各条城市记录的其它字段全都来源于多条记录，是不一定相同的。所以用SQL查询时结果集中也就仅允许使用两类字段：一类是分组条件中包含的字段，另一类是计数、求和、均值、最大值、最小值这样的聚合结果字段。正是由于这样的模式，如果想进一步获得分组中的某一条记录，或者其中某些记录构成的子集，就会变得非常复杂，如“组内人口最多的城市名称”、“组内人口低于平均值的城市名称”，如果仍然使用SQL，那么只能用子查询来处理了：

A1和A2中的SQL语句，都在where条件中使用了子查询。A1中计算各个首字母的城市中，人口最多的城市名称及人口；A2中计算首字母相同的各个城市分组中，人口低于组内平均人口的城市名称及人口。A1和A2中的结果如下：

A1和A2中的SQL语句相当冗长难读，更麻烦的是，这样的处理方式，需要让数据库在查询每一条记录时都去执行一次类似分组汇总的计算，计算效率非常低，一旦数据量比较大的话就是灾难了。另外，SQL返回的结果集实际上也看不出分组，只是把满足条件的城市数据依次列出而已。

在集算器中就不一样了，通过使用直观的记录分组，可以让问题解决起来就变得非常简单：

A2中根据城市名称的首字母将数据分组，上面的两个问题都可以利用A2的分组结果来计算。A3中可以很简单地获得每个首字母城市组中，人口最多的城市记录，结果如下：

这里获得的是原始表中的记录，如果只需要NAME和POPULATION，可以使用A4中的方法计算结果：

这个结果和前面用SQL获得的是相同的，但计算过程就简单得多了。

A5中的处理也类似，计算出各个同首字母的城市组中，人口低于平均值的记录，为了避免重复计算，在每个分组中先计算出人口均值记录为avg。A5中结果如下：

如果需要获得和SQL查询相同的结果，可以将各个分组中的记录合并在一起再取出所需字段，如A6中的结果：

3、枚举分组

以上提到的分组，其实都是“等值分组”，就是按照相同的字段值或计算结果将记录分组。还有一种更麻烦的情况，“按条件分组”。

这里，我们以一个“按枚举条件分组”的例子来看一下，仍然使用前面城市信息数据，现在需要计算分别以G,R,O,U,P这5个字母作为首字母的城市各有多少个，以及这些城市的平均人口。如果仍然使用SQL执行查询，通常的方法就只能用union去写出一个超长的语句去处理了，如下：

这里调用的SQL，需要将5个类似的select语句union到一起，同时为了得到所需顺序，还需要添加一列用来排序，执行后结果如下：

这种情况在用SQL处理一些不那么常规的查询时经常会发生，每次都不得不去写长长的一串SQL，万一在复制粘贴时出点什么差错，也是相当难以纠正的。

由于集算器和SQL不同，支持真正的分组操作，所以这类个性化的问题，反而正是集算器所擅长的：

A3中用函数align将城市记录按照所需的首字母对齐分组，结果如下：

A4中统计出每个分组中的城市总数和平均人口，考虑到其中可能存在的空组，首字母直接从A2中按位置获取：

如果不需要其中不包含任何城市的“U”分组，可以把A4中的表达式修改为：=A3.select(~.count() > 0).new(left(NAME, 1):INITIAL, ~.count():CITY_COUNT, int(~.avg(POPULATION)):AVG_POPU)。

在上面的问题中，根据不同的首字母分组，也可以写为用表达式表示的条件，在集算器中可以enum函数来分组计算：

A2中的分组条件用更直观的方式表示，如left(?,1)==”G”表示首字母为G，等等。这种情况下使用枚举分组函数enum就能根据NAME的首字母按条件分组。A3中的分组结果，以及A4中的汇总结果和用align的情况是相同的。

实际上，用align对齐分组时，解决的仍然是“等值分组”的情况，而用枚举分组时就很自由了，只要能用表达式表示的条件都可以作为分组依据。如下面按人口数将城市分组：

A3中按照人口将各个城市分为500万以上，200万以上和100万以上三组，结果如下：

A4中根据分组结果统计计算：

使用enum函数执行枚举分组时，还支持多种分组模式，如：

A3中枚举分组时，添加了@r选项，此时分组时允许记录重复出现在不同的分组中，分组结果如下：

从结果中可以看到，由于人口500万以上的城市当然也能满足人口在200万以上，所以在这种分组方式下，第2组中也包含了第1组中的New York。

A4中使用了@n选项，分组结果如下：

可以发现，这时分组时在最后多了一组，不满足A2中任何一个分组条件的城市记录，都被分在了第4组中。

在集算器中使用枚举分组时，可以处理各类灵活的分组计算问题，而这种问题往往是用SQL时所面对的最大的烦恼。所以，是时候摆脱又长又烦的SQL语句了，就让集算器来帮你放松一下吧。