SPL：分组汇总
把集合中具有相同属性的成员分配到同一个组，这就是分组运算。比如员工表根据部门分组，每组的员工都具有相同的部门；销售表可以根据销售年份分组，每组都是同一个年份的销售记录等等。
有时候我们需要将数据按不同类型进行统计，这就要用到分组汇总。分组汇总是指根据一定的规则将数据进行分组，然后针对每个分组进行聚合运算。
【例 1】 根据销售表，查询 2014 年每个月的总销售额。部分数据如下：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
10400 |
EASTC |
2014/01/01 |
1 |
27 |
3063.0 |
10401 |
HANAR |
2014/01/01 |
1 |
17 |
3868.6 |
10402 |
ERNSH |
2014/01/02 |
8 |
70 |
2713.5 |
10403 |
ERNSH |
2014/01/03 |
4 |
42 |
1005.9 |
10404 |
MAGAA |
2014/01/03 |
2 |
74 |
1675.0 |
… |
… |
… |
… |
… |
… |
在 SPL 中提供了函数 A.groups() 用于分组汇总。
SPL脚本如下：
A |
|
1 |
=T("Sales.csv").select(year(ORDERDATE)==2014) |
2 |
=A1.groups(month(ORDERDATE):MONTH; sum(AMOUNT):AMOUNT) |
A1：导入销售表数据，并选出 2014 年的记录。
A2：使用函数 A.groups() 按月份分组汇总每个月的总销售额。
【例 2】 根据员工表，查询在纽约州平均年龄低于 45 岁的部门。部分数据如下：
ID |
NAME |
BIRTHDAY |
STATE |
DEPT |
SALARY |
1 |
Rebecca |
1974/11/20 |
California |
R&D |
7000 |
2 |
Ashley |
1980/07/19 |
New York |
Finance |
11000 |
3 |
Rachel |
1970/12/17 |
New Mexico |
Sales |
9000 |
4 |
Emily |
1985/03/07 |
Texas |
HR |
7000 |
5 |
Ashley |
1975/05/13 |
Texas |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
SPL脚本如下：
A |
|
1 |
=T("Employee.csv").select(STATE=="New York") |
2 |
=A1.groups(DEPT; avg(age(BIRTHDAY)):AVGAGE) |
3 |
=A2.select(AVGAGE<45) |
A1：导入员工表，并选出纽约州的记录。
A2：使用函数 A.groups() 按部门分组统计平均年龄。
A3：在分组汇总后的结果集中选出平均年龄小于 45 的记录。在 SPL 中无论在分组前后，都可以使用函数 A.select() 进行选出。
在 SPL 中，函数 A.groups() 不仅支持 sum,count,avg,max,min 等 SQL 支持的聚合函数，还支持一些比较常用，但是 SQL 的 GROUP BY 语句并不支持的聚合函数：top（取前 N 名 / 后 N 名）、iterate（迭代函数）、icount（唯一值计数）、median（中位数，部分数据库支持）等等。
【例 3】 根据销售表，统计 2014 年客户排进每月单笔销售额前三名的次数。部分数据如下：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
10400 |
EASTC |
2014/01/01 |
1 |
27 |
3063.0 |
10401 |
HANAR |
2014/01/01 |
1 |
17 |
3868.6 |
10402 |
ERNSH |
2014/01/02 |
8 |
70 |
2713.5 |
10403 |
ERNSH |
2014/01/03 |
4 |
42 |
1005.9 |
10404 |
MAGAA |
2014/01/03 |
2 |
74 |
1675.0 |
… |
… |
… |
… |
… |
… |
SPL脚本如下：
A |
|
1 |
=T("Sales.csv").select(year(ORDERDATE)==2014) |
2 |
=A1.groups(month(ORDERDATE):MONTH; top(-3;AMOUNT):TOP3) |
3 |
=A2.conj(TOP3).groups(CUSTOMERID; count(~):COUNT) |
A1：导入销售表，并选出 2014 年的记录。
A2：使用函数 A.groups() 按月份分组汇总前三名。
A3：将每月前三名的记录合并后，使用函数 A.groups() 按客户分组统计次数。
【例 4】 统计每个班级各科的得分中位数和不及格（低于 60 分）人数。部分数据如下：
CLASS |
STUDENTID |
SUBJECT |
SCORE |
1 |
1 |
English |
84 |
1 |
1 |
Math |
77 |
1 |
1 |
PE |
69 |
1 |
2 |
English |
81 |
1 |
2 |
Math |
80 |
… |
… |
… |
… |
SPL的解决方案：
A |
|
1 |
=T("Scores.csv") |
2 |
=A1.groups(CLASS,SUBJECT; median(,SCORE):MEDIAN_SCORE, count(SCORE<60):FAIL_COUNT) |
A1：导入成绩表。
A2：使用函数 A.groups() 按班级和科目分组汇总中位数和不及格人数。
在 SPL 中，函数 count() 可以直接使用参数“SCORE<60”来统计不及格人数，不需要先选出不及格的记录，再进行计数。这样就可以很方便的同时计算中位数和统计人数了。
