1. 集合包含性检测
在一个表中，根据集合的包含性判断查找记录。
【例 1】 在员工表中，统计一线城市各部门的平均工资。部分数据如下：
| ID | NAME | CITY | SALARY |
| 1 | Rebecca | Tianjin | 7000 |
| 2 | Ashley | Tianjin | 11000 |
| 3 | Rachel | Shijiazhuang | 9000 |
| 4 | Emily | Shenzhen | 7000 |
| 5 | Ashley | Nanjing | 16000 |
| … | … | … | … |
【解题思路】
从员工表选出数据时，需要判断员工的所在城市是否从属于由北京、上海、广州、深圳组成的常数集合。当集合的成员数小于 10 个时，可以使用函数 A.contain() 进行过滤。
【SPL 脚本】
| A | B | |
| 1 | =connect("db").query("select * from Employee") | /连接数据库并查询员工表 |
| 2 | [Beijing, Shanghai, Guangzhou, Shenzhen] | /创建一线城市的常数集合 |
| 3 | =A1.select(A2.contain(CITY)) | /使用函数 A.contain() 选出所在城市包含在一线城市中的记录。 |
| 4 | =A3.groups(DEPT; avg(SALARY):SALARY) | /分组汇总各部门的平均工资 |
A4的执行结果如下：
| DEPT | SALARY |
| Finance | 7833.33 |
| HR | 7187.5 |
| Marketing | 7977.27 |
| … | … |
在一个表中，根据较大集合的包含性判断来查找记录。
【例 2】 在销售表中，统计 2014 年大客户的每月销售额。部分数据如下：
| ID | Customer | SellerId | Date | Amount |
| 10400 | EASTC | 1 | 2014/01/01 | 3063.0 |
| 10401 | HANAR | 1 | 2014/01/01 | 3868.6 |
| 10402 | ERNSH | 8 | 2014/01/02 | 2713.5 |
| 10403 | ERNSH | 4 | 2014/01/03 | 1005.9 |
| 10404 | MAGAA | 2 | 2014/01/03 | 1675.0 |
| … | … | … | … | … |
【解题思路】
本题与【例 1】类似，从销售表选出数据时，需要判断销售客户是否从属于大客户的常数集合。当集合的成员较多时（超过 10 个），可以先对常数集合排序，再使用函数 A.contain() 的 @b 选项，进行二分法查找。
【SPL 脚本】
| A | B | |
| 1 | =connect("db").query("select * from Sales") | /连接数据库并查询销售表 |
| 2 | =["SAVEA","QUICK","ERNSH","HUN","RATTC","HANAR","FOLKO","QUEEN,MEREP","WHITC","FRANK","KOENE"].sort() | /创建大客户的常数集合并排序 |
| 3 | =A1.select(year(Date)==2014 && A2.contain@b(Customer)) | /选出 2014 年的大客户记录。当集合 A 有序时，使用函数 A.contain() 的 @b 选项，进行二分法查找。 |
| 4 | =A3.groups(month(Date):Month; sum(Amount):Amount) | /分组汇总每月的销售额 |
A4的执行结果如下：
| STATE | SALARY |
| California | 7700.0 |
| Texas | 7592.59 |
| New York | 7677.77 |
| Florida | 7145.16 |
| Other | 7308.1 |
2. 外键映射的包含性检测
在两个表中，根据外键映射的包含性检测查找记录。
【例 3】 查询各班有多少学生选修了“Matlab”课程。选课表和课程表如下：
【解题思路】
从选课表选出数据时，需要判断课程的名称是否等于“Matlab”。可以先在课程表中筛选出课程名称是“Matlab”的课程集合，再选出选课表的课程 ID 从属于这个集合的记录。
【SPL 脚本】
| A | B | |
| 1 | =connect("db") | /连接数据库 |
| 2 | =A1.query("select * from Course") | /查询课程表 |
| 3 | =A1.query("select * from SelectCourse") | /查询选课表 |
| 4 | =A2.select(Name=="Matlab") | /从课程表中选出指定课程 |
| 5 | =A3.join@i(CourseID, A4:ID) | /使用函数 A.join() 的 @i 选项进行连接过滤 |
| 6 | =A5.groups(Class; count(1):SelectCount) | /分组汇总各班报名的人数 |
A6的执行结果如下：
| Class | SelectCount |
| Class 1 | 3 |
| Class 2 | 5 |
| … | … |
3. 非外键的包含性检测
在两个表中，根据非外键的包含性检测查找记录。
【例 4】 查询各班所有某科成绩超过 80 分的学生数量。成绩表和学生表如下：
【SQL 语句】
从学生表选出数据时，需要判断学生是否有单科成绩超过 80 分的。可以先在成绩表中选出所有大于 80 分的记录，再按学生 ID 去重，得到某科成绩高于 80 分的学生 ID 的集合。接下来只要选出学生的 ID 从属于这个集合的记录。
【SPL 脚本】
| A | B | |
| 1 | =connect("db") | /连接数据库 |
| 2 | =A1.query("select * from Student") | /查询学生表 |
| 3 | =A1.query("select * from Score") | /查询学生成绩表 |
| 4 | =A3.select(Score>80) | /选出有成绩超过 80 分的记录 |
| 5 | =A4.id(StudentID) | /使用 id 函数按学生 ID 去重 |
| 6 | =A2.join@i(ID, A5) | /使用函数 A.join@i() 连接过滤 |
| 7 | =A6.groups(Class; count(1):StudentCount) | /分组汇总每个班级的学生数量 |
A7的执行结果如下：
| Class | StudentCount |
| Class 1 | 9 |
| Class 2 | 11 |
| … | … |
在两个表中，根据非外键的匹配性检测查找记录，优化提速。
【例 5】查询 2014 年各城市有销售记录的客户数量。销售表和客户表如下：
【解题思路】
从客户表选出数据时，需要判断客户在 2014 年是否有销售记录。可以先在销售表中选出 2014 年的记录，再按客户 ID 去重，得到 2014 年有销售记录的客户 ID 的集合。接下来只要选出客户表的 ID 从属于这个集合的记录。
【SPL 脚本】
| A | B | |
| 1 | =connect("db") | /连接数据库 |
| 2 | =A1.query("select * from Customer") | /查询客户表 |
| 3 | =A1.query("select * from Sales where year(Date)=2014 order by CustomerID") | /查询 2014 年的销售记录，并按客户 ID 排序 |
| 4 | =A3.groups@o(ID) | /使用 groups 函数按客户 ID 去重，有序时使用 @o 选项 |
| 5 | =A2.join@i(ID, A4:CustomerID) | /使用函数 A.join@i() 连接过滤 |
| 6 | =A5.groups(City; count(1):CustomerCount) | /分组汇总每个城市的客户数量 |
A6的执行结果如下：
| City | CustomerCount |
| Dongying | 6 |
| Tangshan | 7 |
| … | … |
4. 外键映射的不包含性检测
在两个表中，根据外键映射的不包含性检测查找记录。
【例 6】 查询 2014 年每个新增客户的销售总额。销售表和客户表如下：
【解题思路】
从客户表选出数据时，需要判断该客户在 2014 年没有销售记录。可以先在销售表中筛选出 2014 年有销售记录的集合，再选出客户表的 ID 不从属于这个集合的记录。
【SPL 脚本】
| A | B | |
| 1 | =connect("db") | /连接数据库 |
| 2 | =A1.query("select * from Sales where year(OrderDate)=2014") | /查询 2014 年的销售记录 |
| 3 | =A1.query("select * from Customer") | /查询客户表 |
| 4 | =A2.join@d(CustomerID ,A3:ID) | /使用函数 A.join@d() 从销售表中选出客户 ID 在客户表中不存在的记录 |
| 5 | =A4.groups(CustomerID; sum(Amount):Amount) | /分组汇总每个客户的销售额 |
A5的执行结果如下：
| CustomerID | Amount |
| DOS | 11830.1 |
| HUN | 57317.39 |
| … | … |
《SPL CookBook》中还有更多相关计算示例。