结构化数据中的存在判断问题
【摘要】
从数据表中选出数据时，有时候需要判断成员在某些条件下是否存在，这些条件可能是从其他的数据表中查询的。例如分数是成绩表的字段，怎样从学生表中选出各科分数都高于 80 分的学生？如何简便快捷的处理结构化数据中的存在判断问题，这里为你全程解析，并提供 esProc 示例代码。结构化数据中的存在判断问题
1. 外键映射的存在性检测
在两个表中，根据外键映射的存在性查找记录。
【例 1】 统计一班男生的平均分。成绩表和学生表如下：
【解题思路】
从分数表选出数据时，判断是否存在班级的名称是一班且学生性别是男性的记录，如果存在则选出。
【SPL 脚本】
A |
B |
|
1 |
=connect("db") |
/连接数据库 |
2 |
=A1.query("select * from Score") |
/查询学生表 |
3 |
=A1.query("select * from Student") |
/查询学生成绩表 |
4 |
=A3.select(Class=="Class 1" && Gender=="Male") |
/选出一班男生 |
5 |
=A2.join@i(Class:StudentID, A4:Class:ID) |
/使用函数 A.join@i() 连接过滤 |
6 |
=A5.groups(StudentID; avg(Score):Score) |
/分组汇总每个学生的平均分 |
A6的执行结果如下：
StudentID |
Score |
1 |
76 |
3 |
74 |
… |
… |
当外键表数据量大时，可以使用游标的有序归并来解决。
【例 2】 查询 2014 年每月没有使用折扣的订单数量。订单表和订单明细表如下：
【解题思路】
从订单表选出数据时，判断是否存在折扣为 0 的订单，如果存在则选出。
【SPL 脚本】
A |
B |
|
1 |
=connect("db") |
/连接数据库 |
2 |
=A1.cursor("select * from Order where year(Date)=2014 order by ID") |
/创建订单表游标，选出 2014 年记录 |
3 |
=A1.cursor("select * from Detail order by ID") |
/创建订单明细表游标 |
4 |
=A3.select(Discount==0) |
/选出没有使用折扣的记录 |
5 |
=joinx(A2:Order,ID;A4:Detail,ID) |
/使用函数 joinx 对订单表和订单明细表的游标进行有序归并 |
6 |
=A5.groups(month(Order.Date):Month; icount(Order.ID):OrderCount) |
/分组汇总每个月的订单数量 |
A6的执行结果如下：
Month |
OrderCount |
1 |
16 |
2 |
25 |
… |
… |
2. 非等值连接的存在性检测
在一个表中，通过非等值连接的存在性检测查找数据。
【例 3】 查询同一订单跨度超过一年的订单的销售额。订单表部分数据如下：
ID |
NUMBER |
AMOUNT |
DELIVERDATE |
ARRIVALDATE |
10814 |
1 |
408.0 |
2014/01/05 |
2014/04/18 |
10814 |
2 |
204.0 |
2014/02/21 |
2014/04/05 |
10814 |
3 |
102.0 |
2014/03/14 |
2014/04/06 |
10814 |
4 |
102.0 |
2014/04/09 |
2014/04/27 |
10814 |
5 |
102.0 |
2014/05/04 |
2014/07/04 |
10848 |
1 |
873.0 |
2014/01/06 |
2014/04/21 |
… |
… |
… |
… |
… |
【解题思路】
从订单表中选出数据时，判断订单跨度超过一年的记录是否存在，如果存在则选出。
【SPL 脚本】
A |
B |
|
1 |
=connect("db") |
/连接数据库 |
2 |
=A1.query("select * from Detail") |
/查询订单明细表 |
3 |
=A2.group(ID) |
/按订单日期分组 |
4 |
=A3.select(interval(~.min(DELIVERDATE), ~.max(ARRIVALDATE)) > 365) |
/选出同一订单的时间间隔超过 365 天的记录 |
5 |
=A4.new(ID, ~.sum(AMOUNT):Amount) |
/创建数据表，统计每个订单的销售额 |
A5的执行结果如下：
ID |
Amount |
10998 |
6800.0 |
11013 |
4560.0 |
11032 |
20615.0 |
… |
… |
3. 外键映射的不存在性检测
在两个表中，根据外键映射的不存在性检测查找记录。
【例 4】查询所有科目均高于 80 分的学生。成绩表和学生表如下：
【解题思路】
从学生表选出数据时，判断学生是否存在任意科目低于 80 分的成绩，如果不存在则选出。
【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 去重 |
6 |
=A2.join@d(ID, A5) |
/使用函数 A.join@d() 选出不匹配的记录 |
A6的执行结果如下：
ID |
Class |
Name |
2 |
Class 1 |
Ashley |
16 |
Class 2 |
Alexis |
4. 双重否定的存在性检测
通过双重否定，查询能够匹配的记录。
【例 5】 查询选修了所有课程的学生。选课表、课程表和学生表如下：
【解题思路】
从学生表选出数据时，判断学生是否存在某一科目课程没有选出的记录，如果不存在则选出。处理双重否定的存在性检测时，我们也可以正向思考，只要选出选修科目数量与所有科目数量相同的记录即可。
【SPL 脚本】
A |
B |
|
1 |
=connect("db") |
/连接数据库 |
2 |
=A1.query("select * from Student") |
/查询学生表 |
3 |
=A1.query("select * from Course") |
/查询课程表 |
4 |
=A1.query("select * from SelectCourse") |
/查询选课表 |
5 |
=A4.groups(StudentID; icount(CourseID):CourseCount) |
/选课表按照学生 ID 分组汇总每个学生的选课数量 |
6 |
=A5.select(CourseCount==A3.len()) |
/选出选择了所有课程的学生 ID |
7 |
=A2.join@i(ID, A6:StudentID) |
/使用函数 A.join@i() 连接过滤 |
A7的执行结果如下：
ID |
Name |
Class |
4 |
Emily Smith |
Class 1 |
5. 任意条件的存在性检测
在两个表中，根据任意条件的存在性检测查找记录。
【例 6】 查询两科分数差超过 30 分的学生。成绩表和学生表如下：
【SQL 语句】
从学生表选出数据时，判断是否存在有任意两个科目成绩相差 30 分的记录，存在则选出。
【SPL 脚本】
A |
B |
|
1 |
=connect("db") |
/连接数据库 |
2 |
=A1.query("select * from Student") |
/查询学生表 |
3 |
=A1.query("select * from Score") |
/查询成绩表 |
4 |
=A3.group(StudentID) |
/成绩表按学生 ID 分组 |
5 |
=A4.select(~.max(Score)-~.min(Score)>30) |
/选出最高分和最低分相差超过 30 分的学生 |
6 |
=A5.id(StudentID) |
/按学生 ID 去重 |
7 |
=A2.join@i(ID,A6) |
/使用函数 A.join@i() 连接过滤 |
A7的执行结果如下：
ID |
Name |
Class |
4 |
Emily Smith |
Class 1 |
8 |
Megan |
Class 1 |
… |
… |
… |
6. 全部条件的存在性检测
根据一个表中数据，筛选出满足所有条件的记录。
【例 7】查询哪些员工的工资比所有销售部员工都要高。员工表部分数据如下：
ID |
NAME |
DEPT |
SALARY |
1 |
Rebecca |
R&D |
7000 |
2 |
Ashley |
Finance |
11000 |
3 |
Rachel |
Sales |
9000 |
4 |
Emily |
HR |
7000 |
5 |
Ashley |
R&D |
16000 |
… |
… |
… |
… |
【SQL 语句】
从员工表选出数据时，判断员工工资大于所有销售部员工工资的记录是否存在，存在则选出。
【SPL 脚本】
A |
B |
|
1 |
=connect("db") |
/连接数据库 |
2 |
=A1.query("select * from Employee") |
/查询员工表 |
3 |
=A2.select(DEPT:"Sales").max(SALARY) |
/选出所在城市包含在一线城市中的记录 |
4 |
=A2.select(SALARY>A3) |
/分组汇总各部门的平均工资 |
A4的执行结果如下：
ID |
NAME |
DEPT |
SALARY |
5 |
Ashley |
R&D |
16000 |
20 |
Alexis |
Administration |
16000 |
22 |
Jacob |
R&D |
18000 |
47 |
Elizabeth |
Marketing |
17000 |
《SPL CookBook》中还有更多相关计算示例。
