分组与汇总

阅读(276) 标签: 分组, 汇总,

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

计算唯一值

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

 

A

1

=file("Order_Books.txt").import@t()

2

=A1.id(SalesID)

3

=A1.id(PName)

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

A2中,取出所有不同的销售员编号SalesIDA3中取出所有不同的书籍名称PNameA2A3中的结果如下:

 

从结果中可以发现,用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语句从数据库中获取结果。A2A3中的结果如下:

 

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

 

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

 

A

1

=demo.query("select * from EMPLOYEE")

2

=A1.id@o(STATE)

此时,A2中的结果如下:

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

 

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

 

A

1

=demo.query("select * from EMPLOYEE")

2

=A1.id@o(age(BIRTHDAY))

A2中列出了员工的年龄:

等值分组

我们经常需要根据某个值,将数据分组,如按部门将员工分组,按性别将人员分组等。在集算器中,可以用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计算出各个部门中男员工和女员工的总数。分组汇总的结果将会返回为序表,A3A4中的结果如下:

 

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

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

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

 

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

 

A

1

=z(123,40)

2

=demo.query("select * from EMPLOYEE")

3

=A2.group(z(#,60))

4

=A3.new(#:GID,~.count():Count,~:Emps)

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

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

 

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

 

A

1

=demo.query("select * from EMPLOYEE")

2

=A1.len()

3

=A1.group(z(#,6,A2))

4

=A3.new(#:GID,~.count():Count,~:Emps)

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

可以看到,当数据并不能完全平分时,多余的数据会优先分配在前面的组中,z(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)

A2A3中直接根据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)

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

 

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

需要注意的是,使用groups直接累计分组汇总时,只能用sum/count/max/min/top/avg/iterate等简单聚合函数。更复杂的分组汇总,只能用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中进一步计算出年龄。A2A3中结果如下:

 

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

A4A3中计算出的数据是相同的。