1. 集合包含性检测
在一个表中,根据集合的包含性判断查找记录。
【例 1】 在员工表中,统计一线城市各部门的平均工资。部分数据如下:
IDNAMECITYSALARY
1RebeccaTianjin7000
2AshleyTianjin11000
3RachelShijiazhuang9000
4EmilyShenzhen7000
5AshleyNanjing16000
…………
【解题思路】
从员工表选出数据时,需要判断员工的所在城市是否从属于由北京、上海、广州、深圳组成的常数集合。当集合的成员数小于 10 个时,可以使用函数 A.contain() 进行过滤。
【SPL 脚本】
AB
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的执行结果如下:
DEPTSALARY
Finance7833.33
HR7187.5
Marketing7977.27
……
在一个表中,根据较大集合的包含性判断来查找记录。
【例 2】 在销售表中,统计 2014 年大客户的每月销售额。部分数据如下:
IDCustomerSellerIdDateAmount
10400EASTC12014/01/013063.0
10401HANAR12014/01/013868.6
10402ERNSH82014/01/022713.5
10403ERNSH42014/01/031005.9
10404MAGAA22014/01/031675.0
……………
【解题思路】
本题与【例 1】类似,从销售表选出数据时,需要判断销售客户是否从属于大客户的常数集合。当集合的成员较多时(超过 10 个),可以先对常数集合排序,再使用函数 A.contain() 的 @b 选项,进行二分法查找。
【SPL 脚本】
AB
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的执行结果如下:
STATESALARY
California7700.0
Texas7592.59
New York7677.77
Florida7145.16
Other7308.1
2. 外键映射的包含性检测
在两个表中,根据外键映射的包含性检测查找记录。
【例 3】 查询各班有多少学生选修了“Matlab”课程。选课表和课程表如下:
【解题思路】
从选课表选出数据时,需要判断课程的名称是否等于“Matlab”。可以先在课程表中筛选出课程名称是“Matlab”的课程集合,再选出选课表的课程 ID 从属于这个集合的记录。
【SPL 脚本】
AB
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的执行结果如下:
ClassSelectCount
Class 13
Class 25
……
3. 非外键的包含性检测
在两个表中,根据非外键的包含性检测查找记录。
【例 4】 查询各班所有某科成绩超过 80 分的学生数量。成绩表和学生表如下:
【SQL 语句】
从学生表选出数据时,需要判断学生是否有单科成绩超过 80 分的。可以先在成绩表中选出所有大于 80 分的记录,再按学生 ID 去重,得到某科成绩高于 80 分的学生 ID 的集合。接下来只要选出学生的 ID 从属于这个集合的记录。
【SPL 脚本】
AB
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的执行结果如下:
ClassStudentCount
Class 19
Class 211
……
在两个表中,根据非外键的匹配性检测查找记录,优化提速。
【例 5】查询 2014 年各城市有销售记录的客户数量。销售表和客户表如下:
【解题思路】
从客户表选出数据时,需要判断客户在 2014 年是否有销售记录。可以先在销售表中选出 2014 年的记录,再按客户 ID 去重,得到 2014 年有销售记录的客户 ID 的集合。接下来只要选出客户表的 ID 从属于这个集合的记录。
【SPL 脚本】
AB
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的执行结果如下:
CityCustomerCount
Dongying6
Tangshan7
……
4. 外键映射的不包含性检测
在两个表中,根据外键映射的不包含性检测查找记录。
【例 6】 查询 2014 年每个新增客户的销售总额。销售表和客户表如下:
【解题思路】
从客户表选出数据时,需要判断该客户在 2014 年没有销售记录。可以先在销售表中筛选出 2014 年有销售记录的集合,再选出客户表的 ID 不从属于这个集合的记录。
【SPL 脚本】
AB
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的执行结果如下:
CustomerIDAmount
DOS11830.1
HUN57317.39
……
《
SPL CookBook》中还有更多相关计算示例。