分组与汇总

在分析和统计数据时，将表中的记录按需要分组，并对各组中的数据进行汇总，是很常见的任务。如计算总和、平均值等汇总数据，或者列出前n个数据等。在这里，我们将研究集算器中如何处理序表中数据的分组与汇总。

计算唯一值

在序表的所有记录中，有一些字段的值是各不相同的，如编号等。而更多的字段，有可能出现重复值，有时会需要列出所有不同的值，可以使用id函数，如：

	A
1	=file("Order_Books.txt").import@t()
2	=A1.id(SalesID)
3	=A1.id(PName)

其中，A1中序表如下，窗口中只显示了部分数据：

在A2中，取出所有不同的销售员编号SalesID，A3中取出所有不同的书籍名称PName，A2和A3中的结果如下：

 

从结果中可以发现，用id函数计算唯一值时，会将结果升序排序。

 

实际上，id函数返回的结果，类似于SQL中的distinct语句，如：

	A
1	=demo.query("select * from EMPLOYEE")
2	=A1.id(STATE)
3	=demo.query("select distinct STATE from EMPLOYEE")

A1中从数据库中选出了员工资料表如下：

A2中用id函数从A1的序表中选出所有不同的州，而A3中用distinct语句从数据库中获取结果。A2和A3中的结果如下：

 

可以看到，两种方法返回的数据相同。但是id函数返回的是序列，而用SQL语句从数据库返回的是序表。

 

有些时候，我们不希望将原始顺序打乱，此时可以添加@o选项，如：

	A
1	=demo.query("select * from EMPLOYEE")
2	=A1.id@o(STATE)

此时，A2中的结果如下：

从结果中可以发现，id@o函数在执行时，不排序，但只是直接去除相邻的同值数据，结果中有可能出现重复的值，如上面结果中的Texas和California等。

如果id函数中的字段只有1个，且字段是自然数，可以添加@n选项或者@b选项，增加比较时的效率。

使用id函数时，也可以不选用字段，而是列出某个表达式的所有不同结果：

	A
1	=demo.query("select * from EMPLOYEE")
2	=A1.id@u(age(BIRTHDAY))

A2中列出了员工的年龄，这里使用了@u选项，结果中不会出现重复值，但会按照出现的先后顺序排列：

等值分组

我们经常需要根据某个值，将数据分组，如按部门将员工分组，按性别将人员分组等。在集算器中，可以用group函数将序表或排列中的数据分组，并返回各组构成的序列。如：

	A
1	=demo.query("select * from EMPLOYEE")
2	=A1.group(DEPT)
3	=A1.group@o(DEPT)
4	=A1.group(year(BIRTHDAY))

A2将员工按部门DEPT分组，结果如下：

可以发现，结果是由分组构成的序列，而每个分组都是同一个部门的员工数据组成的排列，而各个组是按照部门升序排序的。

在使用group函数时，同样可以用@o选项，表示不打乱顺序，只将相邻的同值数据合并为一组。如A3中按部门分组时，只将相邻的同部门员工分在一组，结果如下：

分组时，同样可以根据某个表达式，将结果相同的数据分在一组中。如A4中按出生年份将员工分组，结果如下：

group函数类似SQL中的group by运算。它的返回值是由各个分组构成的序列，最后结果是个由序列构成的序列。分组汇总值可基于分组结果继续多次计算。这与SQL不同，SQL中没有显式的集合数据类型，不能保存分组结果，必须在group by后立即计算出分组汇总值，运算后分组结果将被丢弃而不能重复利用。

在集算器中，分组后可以对分组的结果进行汇总，如：

	A
1	=demo.query("select * from EMPLOYEE")
2	=A1.group(DEPT)
3	=A2.new(DEPT,count(~):Count,~.sum(SALARY):TotalSalary)
4	=A2.new(DEPT,~.count(GENDER=="M"):Male, ~.count(GENDER=="F"):Female)

利用分组后的结果，A3中计算出各个部门的总人数和总薪水，A4计算出各个部门中男员工和女员工的总数。分组汇总的结果将会返回为序表，A3和A4中的结果如下：

 

可以看到，在A3与A4中，对A2中的同一分组进行了不同的汇总计算。分组结果可以重复利用，这是集算器的主要特点之一。

再解说一下A3中的表达式：=A2.new(DEPT,count(~):Count,~.sum(SALARY):TotalSalary)，由于A2是序表A1的分组结果，其每个成员都是一个记录的集合，即排列，因此new函数对A2进行循环时，其内部表达式都是对排列进行操作的。比如DEPT表示取当前成员的第一条记录的部门字段的值；~.sum(SALARY)表示对当前排列的SALARY字段求和。

和SQL中的group by类似，group函数也支持同时对多个字段（表达式）分组，此时只有多个字段的值均相等才会分到同一组。

 

有时候，需要把数据按指定数量分为多组，此时可以使用ntile (i,n) 函数。如：

	A
1	=ntile(123,40)
2	=demo.query("select * from EMPLOYEE")
3	=A2.group(ntile(#,60))
4	=A3.new(#:GID,~.count():Count,~:Emps)

其中，ntile(i,n) 函数用来计算如果每n个分为一组，那么第i个成员分在第几组。A1中表达式即用来计算，如果40个一组的话，第123个成员分在第几组，结果如下：

在A3中，则利用ntile函数，将员工数据按照每60个一组来分组，在A4中统计出了每一组的员工总数，计算后，A4中结果如下：

 

除了上面的分组情况，有时需要将记录平均分为k组，此时可以使用ntile(i,k,n) 函数，如：

	A
1	=demo.query("select * from EMPLOYEE")
2	=A1.len()
3	=A1.group(ntile(#,6,A2))
4	=A3.new(#:GID,~.count():Count,~:Emps)

其中，ntile(i,k,n) 函数用来计算将总共n个成员平均分为k组，那么第i个成员分在第几组。在A3中，则利用ntile函数，将员工数据平均分为6组，在A4中统计出了每一组的员工总数，计算后，A4中结果如下：

可以看到，当数据并不能完全平分时，多余的数据会优先分配在前面的组中，ntile(i,k,n) 函数的效果类似于SQL中的NTILE函数。

分组汇总

在上一节，我们看到了将序表中的记录分组后，再利用分组后的结果汇总计算的例子。使用group函数，也可以直接获得分组后的计算结果，而无需分步，如：

	A
1	=demo.query("select * from EMPLOYEE")
2	=A1.group(DEPT;~.count():Count,~.sum(SALARY):TotalSalary)
3	=A1.group(DEPT;~.count(GENDER=="M"):Male, ~.count(GENDER=="F"):Female)
4	=A1.group(DEPT;round(~.avg(age(BIRTHDAY)),2):AverageAge)

A2和A3中直接根据DEPT字段分组并汇总，结果和分步处理时是相同的：

 

A4中则计算出了各部门员工的平均年龄：

集算器也提供了用累计方法来计算分组汇总值的函数groups，如：

	A
1	=demo.query("select * from EMPLOYEE")
2	=A1.groups(DEPT;count(~):Count,sum(SALARY):TotalSalary)
3	=A1.groups(DEPT;count(GENDER=="M"):Male, count(GENDER=="F"):Female)

A2和A3中直接根据DEPT字段分组汇总，注意写法和前面的例子略有不同，"~.”可以省略，结果和先分组再汇总时是相同的：

 

直接用groups函数分组汇总时，返回的结果同样是序表。用groups函数计算分组汇总时，并不会记录每个分组中的数据，仅根据分组表达式计算累加。不过和前面用group函数计算分组汇总相比，用groups函数效率更高。

需要注意的是，使用groups直接累计分组汇总时，只能用sum/count/max/min/top/avg/iterate/icount/median等简单聚合函数。更复杂的分组汇总，只能用group函数，而不能用groups。

特别的，在汇总函数中可以用top来求出最小的n个结果，如：

	A
1	=demo.query("select * from EMPLOYEE")
2	=A1.groups(DEPT;top(3,age(BIRTHDAY)))

A2中分组统计出每个部门中，最年轻的3个人的年龄：

在用top汇总时，只能获得最小的n个结果。如果需要统计最年长的3个人的年龄，可以用下面的方法：

	A
1	=demo.query("select * from EMPLOYEE")
2	=A1.groups(DEPT;top(3,BIRTHDAY))
3	=A2.new(DEPT,#2.(age(~)))
4	=A1.groups(DEPT;top(-3,age(BIRTHDAY)))

A2中先统计出每个部门中最小的3个生日，再在A3中进一步计算出年龄。A2和A3中结果如下：

 

更方便的方法是如A4中的表达式，用top(-n)直接计算取出年龄最大的3位，结果如下：

A4和A3中计算出的数据是相同的。