在分析和统计数据时,将表中的记录按需要分组,并对各组中的数据进行汇总,是很常见的任务。如计算总和、平均值等汇总数据,或者列出前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中计算出的数据是相同的。