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”来统计不及格人数,不需要先选出不及格的记录,再进行计数。这样就可以很方便的同时计算中位数和统计人数了。

Sales.csv

Employee.csv

Scores.csv