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，不为空的即为同时选择了两门课程的记录。
英文版