结构化数据中的从属判断问题

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》中还有更多相关计算示例。


上一篇:那些Python集合中神秘的招式


下一篇:SQLserver 2014自定义备份数据库