SPL:集合运算

   集合运算是指集合间的和、交、并、差、异或、包含等基本运算。结构化数据经常是以集合形式出现的,为了方便地计算这类数据,SPL 提供了丰富的集合运算方法和函数。接下来我们通过一些例子来看一下,SPL 是如何解决集合运算问题的。

 

1. 合集

   集合 A 和 B 的合集是由所有属于集合 A 或属于集合 B 的成员所组成的集合,包括重复成员。比如集合 [1,2] 与[1,3]的合集,是集合[1,2,1,3],重复成员 1 在合集中出现了 2 次。

 

【例 1】 某商家有网店和实体店铺两种销售渠道,其销售数据分别存储在 SALES_ONLINE 和 SALES_STORE 两个表中。现在要查询 2014 年每种产品的总销售额。部分数据如下:

SALES_ONLINE

ID

CUSTOMERID

ORDERDATE

PRODUCTID

AMOUNT

1

HANAR

2014/01/01

17

3868.6

2

ERNSH

2014/01/03

42

1005.9

3

LINOD

2014/01/06

64

400.0

4

OTTIK

2014/01/07

10

1194.0

5

OCEAN

2014/01/09

41

319.2

SALES_STORE

ID

CUSTOMERID

ORDERDATE

SELLERID

PRODUCTID

AMOUNT

1

EASTC

2014/01/01

1

27

3063.0

2

ERNSH

2014/01/02

8

70

2713.5

3

MAGAA

2014/01/03

2

74

1675.0

4

SAVEA

2014/01/07

7

45

2018.2

5

FOLIG

2014/01/08

8

30

1622.4

 

    两个表的数据结构并不完全相同,比如 SALES_STORE 有字段 SELLERID 用于存储销售人员。但是都有相同的订单日期、产品 ID、销售额等字段。

 

   SPL中可以使用符号“|”来计算集合的合集。比如 A|B 就是集合 A 与集合 B 的合集。

   SPL脚本如下:


A

1

=T("SalesOnline.csv").select(year(ORDERDATE)==2014)

2

=T("SalesStore.csv").select(year(ORDERDATE)==2014)

3

=A1|A2

4

=A3.groups(PRODUCTID; sum(AMOUNT):AMOUNT)

A1:从文件中导入线上销售表,并选出 2014 年的销售记录。

A2:从文件中导入实体店销售表,并选出 2014 年的销售记录。

A3:使用符号“|”计算两个集合的合集。

A4:分组汇总每种商品的总销售额。

 

   值得注意的是,SPL 计算合集时并不要求集合有相同的数据结构,这是因为 SPL 的序列支持异构的成员。当我们想访问序列成员的共有字段产品 ID 和销售额时,又可以像普通数据表一样直接访问,对于使用者来说非常方便。

 

【例 2】 根据成绩表,查询数学前 4 名、英语前 3 名和体育前 2 名的成绩。部分数据如下:

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.conj(),当 A 是集合的集合时,用于计算所有成员集合的合集。

   SPL脚本如下:


A

1

=T("Scores.csv")

2

[Math,English,PE]

3

[4,3,2]

4

=A3.conj(A1.select(SUBJECT==A2(A3.#)).top(-~;SCORE))

A1:导入学生成绩。

A2:定义学科集合。

A3:定义与学科对应的名次集合。

A4:分别选出各学科的前 N 名,并使用函数 A.conj() 计算成员集合的合集。

 

2. 交集

   集合 A 和 B 的交集是指由所有属于集合 A 且属于集合 B 的成员所组成的集合。比如集合 [1,2,3] 与[1,3,5]的交集,是集合[1,3]。

 

【例 3】 某商家有网店和实体店铺两种销售渠道,其销售数据分别存储在 SALES_ONLINE 和 SALES_STORE 两个表中。现在要查询 2014 年在网店和实体店都有消费的客户有哪些。部分数据如下:

SALES_ONLINE

ID

CUSTOMERID

ORDERDATE

PRODUCTID

AMOUNT

1

HANAR

2014/01/01

17

3868.6

2

ERNSH

2014/01/03

42

1005.9

3

LINOD

2014/01/06

64

400.0

4

OTTIK

2014/01/07

10

1194.0

5

OCEAN

2014/01/09

41

319.2

SALES_STORE

ID

CUSTOMERID

ORDERDATE

SELLERID

PRODUCTID

AMOUNT

1

EASTC

2014/01/01

1

27

3063.0

2

ERNSH

2014/01/02

8

70

2713.5

3

MAGAA

2014/01/03

2

74

1675.0

4

SAVEA

2014/01/07

7

45

2018.2

5

FOLIG

2014/01/08

8

30

1622.4

 

   SPL中可以使用符号“^”来计算集合的交集。比如 A^B 就是集合 A 与集合 B 的交集。

   SPL脚本如下:


A

1

=T("SalesOnline.csv").select(year(ORDERDATE)==2014)

2

=T("SalesStore.csv").select(year(ORDERDATE)==2014)

3

=A1.id(CUSTOMERID)^A2.id(CUSTOMERID)

A1:从文件中导入线上销售表,并选出 2014 年的销售记录。

A2:从文件中导入实体店销售表,并选出 2014 年的销售记录。

A3:使用函数 A.id() 计算客户 ID 的唯一值集合。再使用符号“^”计算两个集合的交集,即在网店和实体店都有消费的客户。

 

【例 4】根据销售数据表,统计 2014 年每个月销售额都排前 10 名的客户名称。部分数据如下:

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

 

   当 A 是由集合组成的集合时,SPL 提供了函数 A.isect() 计算所有成员集合的交集。

   SPL脚本如下:


A

1

=T("Sales.csv").select(year(ORDERDATE)==2014)

2

=A1.group(month(ORDERDATE))

3

=A2.(~.groups(CUSTOMERID;sum(AMOUNT):AMOUNT))

4

=A3.(~.top(-10;AMOUNT).(CUSTOMERID))

5

=A4.isect()

A1:从文件中导入销售表,并选出 2014 年的销售记录。

A2:销售表按月份分组。

A3:每月的销售记录再按客户分组。

A4:统计每月总销售额前十名的客户。

A5:使用函数 A.isect() 计算所有月份选出的客户的交集。

 

3. 并集

   集合 A 和 B 的并集是由所有属于集合 A 或属于集合 B 的成员所组成的集合,但是不包括重复成员。比如集合 [1,2] 与[1,3]的并集,是集合[1,2,3]。值得注意的是,重复成员 1 在并集中只会出现一次。

 

【例 5】 某商家有网店和实体店铺两种销售渠道,其销售数据分别存储在 SALES_ONLINE 和 SALES_STORE 两个表中。现在要查询 2014 年哪些产品在网店累计销售额超过 10000,或者在实体店销售次数大于 5 次。部分数据如下:

SALES_ONLINE

ID

CUSTOMERID

ORDERDATE

PRODUCTID

AMOUNT

1

HANAR

2014/01/01

17

3868.6

2

ERNSH

2014/01/03

42

1005.9

3

LINOD

2014/01/06

64

400.0

4

OTTIK

2014/01/07

10

1194.0

5

OCEAN

2014/01/09

41

319.2

SALES_STORE

ID

CUSTOMERID

ORDERDATE

SELLERID

PRODUCTID

AMOUNT

1

EASTC

2014/01/01

1

27

3063.0

2

ERNSH

2014/01/02

8

70

2713.5

3

MAGAA

2014/01/03

2

74

1675.0

4

SAVEA

2014/01/07

7

45

2018.2

5

FOLIG

2014/01/08

8

30

1622.4

 

   SPL中可以使用符号“&”来计算集合的并集。比如 A&B 就是集合 A 与集合 B 的并集。

   SPL脚本如下:


A

1

=T("SalesOnline.csv").select(year(ORDERDATE)==2014)

2

=T("SalesStore.csv").select(year(ORDERDATE)==2014)

3

=A1.groups(PRODUCTID;   sum(AMOUNT):AMOUNT).select(AMOUNT>10000)

4

=A2.groups(PRODUCTID; count(~):COUNT).select(COUNT>5)

5

=A3.(PRODUCTID)&A4.(PRODUCTID)

A1:从文件中导入线上销售表,并选出 2014 年的销售记录。

A2:从文件中导入实体店销售表,并选出 2014 年的销售记录。

A3:分组汇总线上每种商品的总销售额,并选出大于 10000 的记录。

A4:分组汇总实体店每种商品的总销售次数,并选出超过 5 次的记录。

A5:使用符号“&”计算线上和实体店的产品集合的并集。

 

【例 6】根据销售数据表,查询总销售次数排进过年度前 10 名的产品有哪些。部分数据如下:

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

 

   当 A 是由集合组成的集合时,SPL 提供了函数 A.union() 计算所有成员集合的并集。

   SPL脚本如下:


A

1

=T("Sales.csv")

2

=A1.group(year(ORDERDATE))

3

=A2.(~.groups(PRODUCTID;count(~):COUNT))

4

=A3.(~.top(-5;COUNT).(PRODUCTID))

5

=A4.union()

A1:从文件中导入销售表。

A2:销售表按年份分组。

A3:每年的记录再按产品分组统计销售数量。

A4:选出每年销售数量前 10 名的产品 ID。

A5:使用函数 A.union() 计算所有年份选出的产品的并集。

 

4. 差集

   集合 A 和 B 的差集是指由所有属于集合 A 且不属于集合 B 的成员所组成的集合。比如集合 [1,2,3] 与[1,3,5]的交集,是集合[2]。

 

【例 7】 某商家有网店和实体店铺两种销售渠道,其销售数据分别存储在 SALES_ONLINE 和 SALES_STORE 两个表中。现在要查询在实体店消费总金额超过 1000,在网店没有消费过的客户有哪些。部分数据如下:

SALES_ONLINE

ID

CUSTOMERID

ORDERDATE

PRODUCTID

AMOUNT

1

HANAR

2014/01/01

17

3868.6

2

ERNSH

2014/01/03

42

1005.9

3

LINOD

2014/01/06

64

400.0

4

OTTIK

2014/01/07

10

1194.0

5

OCEAN

2014/01/09

41

319.2

SALES_STORE

ID

CUSTOMERID

ORDERDATE

SELLERID

PRODUCTID

AMOUNT

1

EASTC

2014/01/01

1

27

3063.0

2

ERNSH

2014/01/02

8

70

2713.5

3

MAGAA

2014/01/03

2

74

1675.0

4

SAVEA

2014/01/07

7

45

2018.2

5

FOLIG

2014/01/08

8

30

1622.4

 

   SPL中可以使用符号“\”来计算集合的差集。比如 A\B 就是集合 A 与集合 B 的差集。

   SPL脚本如下:


A

1

=T("SalesOnline.csv").select(year(ORDERDATE)==2014)

2

=T("SalesStore.csv").select(year(ORDERDATE)==2014)

3

=A2.groups(CUSTOMERID; count(~):COUNT).select(COUNT>3)

4

=A3.id(CUSTOMERID)\A1.id(CUSTOMERID)

A1:从文件中导入线上销售表,并选出 2014 年的销售记录。

A2:从文件中导入实体店销售表,并选出 2014 年的销售记录。

A3:分组汇总实体店每个客户的总销售次数,并选出超过 3 次的记录。

A4:使用符号“\”计算实体店和线上客户的差集。

 

【例 8】根据销售数据表,查询 2014 年只在一月份排进总销售额前 10 名的客户有哪些。部分数据如下:

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

 

   当 A 是由集合组成的集合时,SPL 提供了函数 A.diff() 计算第一个成员集合与其他成员集合的差集。

   SPL脚本如下:


A

1

=T("Sales.csv").select(year(ORDERDATE)==2014)

2

=A1.group(month(ORDERDATE))

3

=A2.(~.groups(CUSTOMERID;sum(AMOUNT):AMOUNT))

4

=A3.(~.top(-10;AMOUNT).(CUSTOMERID))

5

=A4.diff()

A1:从文件中导入销售表,并选出 2014 年的销售记录。

A2:销售表按月份分组。

A3:每月的销售记录再按客户分组汇总。

A4:统计每月总销售额前十名的客户。

A5:使用函数 A.diff() 计算一月份与其他月份的客户的差集。

 

5. 异或集

   有集合 A 和 B,属于 A 或 B,但不同时属于 A 和 B 的成员的集合称为 A 和 B 的异或集。比如集合 [1,2,3] 与[1,3,5]的异或集,是集合[2,5]。

 

【例 9】 两个学期的成绩分别保存在不同的表中,查询一班上下学期只有一次进入总分前 10 名的学生。部分数据如下:

上学期 SCORES1:

CLASS

STUDENTID

SUBJECT

SCORE

1

1

English

84

           1

1

Math

77

1

1

PE

69

1

2

English

81

1

2

Math

80

下学期SCORES2

CLASS

STUDENTID

SUBJECT

SCORE

1

1

English

97

           1

1

Math

64

1

1

PE

97

1

2

English

56

1

2

Math

82

 

   SPL中可以使用符号“%”来计算集合的异或集。比如 A%B 就是集合 A 与集合 B 的异或集。

   SPL脚本如下:


A

1

=T("Scores1.csv")

2

=T("Scores2.csv")

3

=A1.groups(STUDENTID; sum(SCORE):SCORE)

4

=A2.groups(STUDENTID; sum(SCORE):SCORE)

5

=A3.top(-10;SCORE).(STUDENTID)

6

=A4.top(-10;SCORE).(STUDENTID)

7

=A5%A6

A1:从文件中导入上学期成绩表。

A2:从文件中导入下学期成绩表。

A3:上学期成绩表按学生 ID 分组汇总每个学生的总分数。

A4:下学期成绩表按学生 ID 分组汇总每个学生的总分数。

A5:选出上学期总分前 10 名的学生 ID。

A6:选出下学期总分前 10 名的学生 ID。

A7:使用符号“%”计算上下两个学期的异或集。

 

【例 10】查询客户 RATTC,在 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

 

   当 A 是由集合组成的集合时,SPL 提供了函数 A.cor() 计算多个成员集合的异或集。

   SPL脚本如下:


A

1

=T("Sales.csv").select(year(ORDERDATE)==2014)

2

=A1.group(month(ORDERDATE))

3

=A2.(~.groups(CUSTOMERID; sum(AMOUNT):AMOUNT))

4

=A3.new(~.top(-3; AMOUNT):TOP3)

5

=A4.(TOP3.(CUSTOMERID).pos("RATTC")>0)

6

=A5.cor()

A1:从文件中导入销售表,并选出 2014 年的销售记录。

A2:销售表按月份分组。

A3:每月的销售记录再按客户分组汇总。

A4:统计每月总销售额前三名的客户。

A5:计算每组的客户是否包含 "RATTC"。

A6:使用函数 A.cor() 计算每个月是否包含客户 "RATTC" 的异或集。结果为 true 表示存在,false 表示不存在。

 

6. 从属和包含

   对于两个集合 A 和 B,若集合 A 的任何一个成员都是集合 B 的成员,则称 B 包含 A。从属关系是元素与集合之间的关系,当集合 A 中存在成员 x 时,我们称 x 从属于 A。比如集合 [1,2,3] 包含集合[1,2],成员 1 从属于集合[1,2,3]。

 

【例 11】 在员工表中,统计 California, New York, Texas, Washington 各部门的平均工资。部分数据如下:

ID

NAME

SURNAME

STATE

DEPT

SALARY

1

Rebecca

Moore

California

R&D

7000

2

Ashley

Wilson

New   York

Finance

11000

3

Rachel

Johnson

New   Mexico

Sales

9000

4

Emily

Smith

Texas

HR

7000

5

Ashley

Smith

Texas

R&D

16000

 

   SPL中可以使用函数 A.contain(x) 判断 x 是否从属于集合 A。

   SPL脚本如下:


A

1

=T("Employee.csv")

2

[California,New York,Texas,Washington]

3

=A1.select(A2.contain(STATE))

4

=A3.groups(DEPT; avg(SALARY):SALARY)

A1:从文件中导入员工表。

A2:定义几个州的常量集合。

A3:从员工表中选出州名从属于 A2 集合的记录。

A4:对 A3 选出的记录进行分组汇总每个部门的平均工资。

 

【例 12】根据课程表和选课表,查询同时选修了 Matlab 和 Modern wireless communication system 课程的学生。部分数据如下:

COURSE

ID

NAME

TEACHERID

1

Environmental   protection and sustainable development

5

2

Mental   health of College Students

1

3

Matlab

8

4

Electromechanical   basic practice

7

5

Introduction to modern life science

3

6

Modern wireless communication system

14

SELECT_COURSE

ID

STUDENT_NAME

COURSE

1

Rebecca   Moore

2,7

2

Ashley   Wilson

1,8

3

Rachel   Johnson

2,7,10

4

Emily   Smith

1,10

5

Ashley   Smith

5,6

 

   这个题目并不复杂,就是判断选课表的课程字段中,是否包含课程集合 [3,6](Matlab 和 Modern wireless communication system 的课程 ID)。

   SPL中可以使用函数 A.pos(B) 获取集合 B 中的成员在集合 A 中的位置,不存在时返回 null。

   SPL脚本如下:


A

1

=T("Course.txt")

2

=T("SelectCourse.txt")

3

=A1.select(NAME=="Matlab" || NAME=="Modern wireless   communication system").(ID)

4

=A2.run(COURSE=COURSE.split@cp())

5

=A4.select(COURSE.pos(A3)!=null)

A1:从文件中导入课程表。

A2:从文件中导入选课表。

A3:选出两门课程的 ID 集合。

A4:将选修的课程按逗号分割后,解析数字组成集合。

A5:使用函数 A.pos() 在选课中定位两门课程的 ID,不为空的即为同时选择了两门课程的记录。