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

【摘要】
    从数据表中选出数据时，有时需要判断成员是否从属于某一个集合。例如从房价表中选出重要城市的房价信息，从销售表中选出 VIP 客户的销售记录等等。如何简便快捷的处理结构化数据中的从属判断问题，这里为你全程解析，并提供 esProc 示例代码。结构化数据中的从属判断问题

 

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