SQL 和 SPL 的选出运算对比
【摘要】
选出运算是指在集合中，根据指定条件获取成员。SQL 和 SPL 是大家比较熟悉的程序语言，本文将探讨对于选出运算问题，这两种语言的解决方案和基本原理。如何简便快捷的处理选出运算，这里为你全程解析，并提供 SQL 和 SPL 示例代码。SQL 和 SPL 的选出运算对比
使用过 SQL 或 SPL 的朋友对选出运算都不会陌生。选出与定位运算很相似，不过定位关心的是成员在集合中的位置，而选出关心的是成员记录的详细信息。比如选出所有销售额超过一万的客户名称，查询总成绩最高的学生姓名等等。
1. 选出符合条件的成员
选出符合条件的成员，是很常见的选出运算，SQL 和 SPL 都可以很好的解决。SQL 语句中可以使用 WHERE 子句来实现条件过滤，SPL 则是提供了函数 A.select()。我们通过几个例子来看一下具体实现：
【例 1】 查询纽约研发部门员工的全名和收入。部分数据如下：
ID |
NAME |
SURNAME |
STATE |
DEPT |
SALARY |
1 |
Rebecca |
Moore |
California |
R&D |
7000 |
2 |
Ashley |
Wilson |
New York |
Finance |
11000 |
3 |
Rachel |
Johnson |
New Mexico |
Sales |
9000 |
4 |
Emily |
Smith |
Texas |
HR |
7000 |
5 |
Ashley |
Smith |
Texas |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
select
Concat(Concat(NAME,' '),SURNAME) FULLNAME, SALARY
from
EMPLOYEE
where
STATE='New York' and DEPT='R&D'
在 SQL 语句的 WHERE 子句中，通过州名是纽约并且部门名称是研发部进行条件过滤。需要同时满足所有条件时，可以使用关键字 "and"。这里以 ORACLE 为例，返回全名时使用了函数 Concat 进行字符串连接。
SPL的解决方案：
A |
|
1 |
=T("Employee.csv") |
2 |
=A1.select(STATE=="New York"&&DEPT=="R&D") |
3 |
=A2.new(NAME+" "+SURNAME:FULLNAME, SALARY) |
A1：从文件中导入员工表。
A2：使用函数 A.select() 选出纽约研发部门员工的记录。
A3：使用符号 + 连接字符串，组成全名。
SPL的关系运算符和逻辑运算符与 JAVA、C 等高级语言类似。使用两个等号"=="来比较是否相等。需要同时满足所有条件时，可以使用符号 "&&"。
SPL同样也支持从数据库中读取数据表，比如数据来源于数据库"db"中 "tbl" 表时， A1可以改为：
A |
|
1 |
=connect("db").query("select * from tbl") |
【例 2】 查询各部门 30 岁以下和 50 岁以上的员工的人数。部分数据如下：
ID |
NAME |
BIRTHDAY |
STATE |
DEPT |
SALARY |
1 |
Rebecca |
1974/11/20 |
California |
R&D |
7000 |
2 |
Ashley |
1980/07/19 |
New York |
Finance |
11000 |
3 |
Rachel |
1970/12/17 |
New Mexico |
Sales |
9000 |
4 |
Emily |
1985/03/07 |
Texas |
HR |
7000 |
5 |
Ashley |
1975/05/13 |
Texas |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
select
DEPT, COUNT(*) COUNT
from
EMPLOYEE
where
TRUNC(months_between(sysdate, BIRTHDAY)/12)<30 or TRUNC(months_between(sysdate, BIRTHDAY)/12)>50
group by DEPT
在 SQL 语句的 WHERE 子句中，通过年龄大于 50 岁或者小于 30 岁进行条件过滤。需要满足多个条件中任意一个时，可以使用关键字 "or"。计算员工年龄时，使用函数 months_between 计算出员工的出生日期与系统时间相差的月份数，再除以 12 就是年份数，最后使用函数 TRUNC 取整。SQL 语句无法在 WHERE 子句中定义临时变量，我们只能将计算式写了两次。如果想要重复使用计算结果，就要使用子查询了。
SPL的解决方案：
A |
|
1 |
=T("Employee.csv") |
2 |
=A1.select((age=age(BIRTHDAY), age<30 || age>50)) |
3 |
=A2.groups(DEPT; count(~):COUNT) |
A1：导入员工表。
A2：使用函数 A.select() 选出年龄小于 30 或者年龄大于 50 的员工。
A3：分组汇总各部门满足条件的人数。
SPL提供了函数 age()，可以直接根据员工出生日期计算出年龄。需要满足多个条件中任意一个时，可以使用符号 "||"。SPL 允许查找条件中使用临时变量，可以一步完成过滤。
【例 3】 计算员工 Emma Taylor 与 Alexis Jones 的工资差和年龄差。员工表部分数据如下：
ID |
NAME |
SURNAME |
STATE |
DEPT |
SALARY |
1 |
Rebecca |
Moore |
California |
R&D |
7000 |
2 |
Ashley |
Wilson |
New York |
Finance |
11000 |
3 |
Rachel |
Johnson |
New Mexico |
Sales |
9000 |
4 |
Emily |
Smith |
Texas |
HR |
7000 |
5 |
Ashley |
Smith |
Texas |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
select
(select SALARY
from EMPLOYEE
where NAME='Emma' and SURNAME='Taylor')
- (select SALARY
from EMPLOYEE
where NAME='Alexis' and SURNAME='Jones') as SALARY_GAP,
(select TRUNC(months_between(sysdate, BIRTHDAY)/12) as AGE
from EMPLOYEE
where NAME='Emma' and SURNAME='Taylor')
-(select TRUNC(months_between(sysdate, BIRTHDAY)/12) as AGE
from EMPLOYEE
where NAME='Alexis' and SURNAME='Jones') as AGE_GAP
from EMPLOYEE
where rownum=1
这种写法不仅书写麻烦，还要多次查询。我们使用公用表来简化这个 SQL：
with cte1 as
(select SALARY,TRUNC(months_between(sysdate, BIRTHDAY)/12) as AGE
from EMPLOYEE
where NAME='Emma' and SURNAME='Taylor'),
cte2 as
select SALARY,TRUNC(months_between(sysdate, BIRTHDAY)/12) as AGE
from EMPLOYEE
where NAME='Alexis' and SURNAME='Jones')
select
cte1.SALARY-cte2.SALARY as SALARY_GAP,
cte1.AGE-cte2.AGE as AGE_GAP
from cte1,cte2
使用公用表以后，比之前的 SQL 效率提高了一些，查询次数明显降低了。但是选出的明明是两条员工记录，却要当作两个表来使用。其原因是 SQL 体系中虽然有记录的概念，但并没有显式的记录数据类型。单条记录被 SQL 作为只有一条记录的临时表处理，也就是个单成员的集合。
SPL的解决方案：
A |
|
1 |
=T("Employee.csv") |
2 |
=A1.select@1(NAME=="Emma"&&SURNAME=="Taylor") |
3 |
=A1.select@1(NAME=="Alexis"&&SURNAME=="Jones") |
4 |
=A2.SALARY-A3.SALARY |
5 |
=age(A2.BIRTHDAY)-age(A3.BIRTHDAY) |
A1：导入员工表。
A2：使用函数 A.select() 的 @1 选项，选出员工 Emma Taylor 的记录。
A3：使用函数 A.select() 的 @1 选项，选出员工 Alexis Jones 的记录。
A4：计算两人的工资差。
A5：计算两人的年龄差。
A5还可以使用函数 interval()，计算两个日期时间的间隔：
A |
|
5 |
=interval@y(A2.BIRTHDAY,A3.BIRTHDAY) |
其中函数 interval() 的 @y 选项，表示取间隔的年份。
SPL有真正的记录数据类型，本例中 A2 和 A3 的计算结果就是两名员工的记录。在后续的计算中，我们可以直接从记录中获取成员值，不再需要将它当作一个表来使用。
通过上面几个例子我们可以看到，简单的选出符合条件的成员时，SQL 和 SPL 的差别并不是很大。但是当选出变得复杂时，SPL 只是按照逻辑增加了一两行脚本，而 SQL 语句的复杂程度则是成倍增加。另外 SPL 可以在选出时定义临时变量，还提供了丰富的函数，使得语句更加简洁。
2. 查找最大值 / 最小值对应记录
接下来我们看一下如何查找最大值 / 最小值对应记录。
【例 4】 根据成绩表，求一班数学成绩最低的学生 ID。部分数据如下：
CLASS |
STUDENTID |
SUBJECT |
SCORE |
1 |
1 |
English |
84 |
1 |
1 |
Math |
77 |
1 |
1 |
PE |
69 |
1 |
2 |
English |
81 |
1 |
2 |
Math |
80 |
… |
… |
… |
… |
SQL的解决方案：
熟悉 SQL 的朋友会知道，SQL 很难选出最小值 / 最大值所在记录。在不使用窗口函数的情况下，我们需要先计算出最小值 / 最大值，再将取到的极值作为条件嵌套查询。
SQL语句如下：
select
STUDENTID
from
SCORES
where
SUBJECT='Math' and and
SCORE=(
select MIN(SCORE)
from SCORES
where SUBJECT='Math' and
)
本来可以在一次循环中找到最小值对应记录，却因为 SQL 本身的原因只能采取其他效率低下的方法来实现，在实际应用中并不可取。
针对这类问题，有的数据库提供了解决方案。比如 ORACLE 的分析函数 KEEP 可以实现这个需求，比较普通 SQL 语句要简便一些，效率也不差：
select
MIN(STUDENTID) keep (dense_rank first order by SCORE asc) STUDENTID
from
SCORES
where
SUBJECT='Math' and
但是 KEEP 函数也有其局限性，只能与聚合函数同时使用，如果最低分有多个就不容易实现了。
SPL的解决方案：
SPL提供了函数 A.minp() 用于选出最小值对应记录。
A |
|
1 |
=T("Scores.csv").select(SUBJECT=="Math"&&CLASS==1) |
2 |
=A1.minp(SCORE).STUDENTID |
A1：导入成绩表，并查询一班的数学成绩。
A2：使用了函数 A.minp() 选出最低分所在的记录，再从记录中取学生 ID。
最小值所在记录不一定是唯一的，如果想返回所有记录，可以使用函数A.minp()的选项@a ：
A |
|
2 |
=A1.minp@a(SCORE).(STUDENTID) |
A2：使用 A.minp() 函数的 @a 选项，选出所有最高分的记录，然后取出所有学生 ID。
其中A1.minp@a(SCORE)的执行结果如下：
CLASS |
STUDENTID |
SUBJECT |
SCORE |
1 |
5 |
Math |
60 |
1 |
14 |
Math |
60 |
与 SQL 相比，SPL 的脚本十分简洁。这是因为 SPL 的选出，是真正的选出了最大值 / 最小值对应记录。而 SQL 无法做到这一点，即便是 ORACLE 的 KEEP 函数也只是根据排序结果计算出聚合值。
【例 5】 根据奥运会奖牌榜统计表，求总成绩蝉联第一名届数最长的国家及其奖牌信息。部分数据如下：
Game |
Nation |
Gold |
Silver |
Copper |
30 |
USA |
46 |
29 |
29 |
30 |
China |
38 |
27 |
23 |
30 |
UK |
29 |
17 |
19 |
30 |
Russia |
24 |
26 |
32 |
30 |
Korea |
13 |
8 |
7 |
… |
… |
… |
… |
… |
SQL的解决方案：
with cte1 as
(select GAME, max(NATION) keep (dense_rank first order by 1000000*GOLD+1000*SILVER+COPPER desc) NATION
from OLYMPIC
group by GAME),
cte2 as
(select min(NATION) NATION, min(GAME) GAMESTART, count(*) GAMECOUNT
from
(select GAME, NATION, row_number() over(ORDER by GAME) rowno, ROW_NUMBER()OVER(PARTITION BY NATION ORDER BY GAME) groupno
from cte1)
group by NATION, groupno-rowno),
cte3 as
(select min(NATION) keep (dense_rank first order by GAMECOUNT desc) NATION, min(GAMESTART) keep (dense_rank first order by GAMECOUNT desc) GAMESTART, max(GAMECOUNT) GAMECOUNT
from cte2)
select t1.GAME,t1.NATION,t1.GOLD,t1.SILVER,t1.COPPER
from OLYMPIC t1
right join cte3
on t1.nation=cte3.nation and t1.game>=cte3.GAMESTART and t1.game<(cte3.GAMESTART+cte3.GAMECOUNT)
这个例子 SQL 实现起来非常复杂。首先根据总成绩选出每一届的冠军，然后再按照连续的国家名称分组，选出每组成员最多的国家，并选出从第几届开始、连续几届等信息。最后根据这些信息在奥运会奖牌榜中选出这些记录。
SPL的解决方案：
SPL提供了函数 A.maxp() 用于选出最大值对应记录。
A |
|
1 |
=T("Olympic.txt") |
2 |
=A1.sort@z(GAME, 1000000*GOLD+1000*SILVER+COPPER) |
3 |
=A2.group@o1(GAME) |
4 |
=A3.group@o(NATION) |
5 |
=A4.maxp(~.len()) |
A1：导入奥运会奖牌榜。
A2：按第几届和总成绩降序排列。
A3：每届取一名，因为有序也就是每届的第一名。
A4：当国家改变时重新分组。
A5：使用了函数 A.maxp() 选出成员数量最多的一组，也就是蝉联次数最多的。
与让人眼晕的 SQL 语句相比，SPL 语句并没有变得复杂。一方面原因前面已经提到了， SQL 语句很难选出最大值 / 最小值对应记录。另外一方面原因，是由于 SQL 的分组子集无法保留，不能使用上次的分组结果继续进行分组等运算。分组这一部分暂时先放在一边，将来会单独整理出来讲解。
3. 选出前 N 名 / 后 N 名
【例 7】 根据成绩表，求各班各科前两名的学生成绩。部分数据如下：
CLASS |
STUDENTID |
SUBJECT |
SCORE |
1 |
1 |
English |
84 |
1 |
1 |
Math |
77 |
1 |
1 |
PE |
69 |
1 |
2 |
English |
81 |
1 |
2 |
Math |
80 |
… |
… |
… |
… |
SQL的解决方案：
SQL选出前 N 名 / 后 N 名的方法并不统一。有些数据库支持 SELECT TOP 语句，ORACLE 并不支持这种语法。我们还是以 ORACLE 为例，使用 ORDER BY 配合行号来实现：
select CLASS, STUDENTID, SUBJECT, SCORE
from (
select CLASS, STUDENTID, SUBJECT, SCORE,
ROW_NUMBER() OVER(PARTITION BY CLASS,SUBJECT
ORDER BY SCORE DESC) grouprank
from SCORES
)
where grouprank <= 2
SPL的解决方案：
SPL提供了 A.top() 函数，用于选出前 N 名 / 后 N 名。
A |
|
1 |
=T("Scores.csv") |
2 |
=A1.group(CLASS,SUBJECT;~.top(-2;SCORE):TOP2) |
3 |
=A2.conj(TOP2) |
A1：导入学生成绩。
A2：使用函数 A.top() 获取每个班级各科目分数的前两名。其中 -2 表示从大到小取两名。
A3：将所有班级各科的前两名记录合并到一起。
4. 分区段查找数据
有时候我们需要通过计算数值在区间中的序号，来获取在集合中的对应成员。比如根据考试成绩返回评价（优、良、中、差）；根据家庭年收入返回家庭所处的资产等级（贫困、小康、中产、富裕等）。
【例 6】 根据成绩表，统计英语科目卓越（分数 >=90）、优秀（80=< 分数 <90）、良好（70<= 分数 <80）、及格（60<= 分数 <70）和不及格（分数 <60）的人数。部分数据如下：
CLASS |
STUDENTID |
SUBJECT |
SCORE |
1 |
1 |
English |
84 |
1 |
1 |
Math |
77 |
1 |
1 |
PE |
69 |
1 |
2 |
English |
81 |
1 |
2 |
Math |
80 |
… |
… |
… |
… |
期望结果如下：
ASSESSMENT |
COUNT |
EXCELLENT |
7 |
GOOD |
3 |
OK |
8 |
PASS |
6 |
FAIL |
4 |
SQL的解决方案：
select
ASSESSMENT, count(*) COUNT
from
(select
case when score<60 then 'FAIL'
when score>=60 and score<70 then 'PASS'
when score>=70 and score<80 then 'OK'
when score>=80 and score<90 then 'GOOD'
when score>=90 then 'EXCELLENT'
else 'OTHER' end ASSESSMENT
from SCORES
where subject='English')
group by ASSESSMENT
SQL可以使用 CASE WHEN 语句来分区段，根据学生成绩计算出评价等级。再按照评价进行分组，统计每组的人数。
SPL的解决方案：
SPL提供了分段函数 A.segp()，根据区段序号返回序列中对应成员。
A |
|
1 |
=T("Scores.csv").select(SUBJECT:"English") |
2 |
=create(ASSESSMENT,SCORE).record(["FAIL",0,"PASS",60,"OK",70,"GOOD",80,"EXCELLENT",90]) |
3 |
=A1.groups(A2.segp(SCORE,SCORE).ASSESSMENT;count(1):COUNT) |
A1：导入成绩表，并选出英语成绩。
A2：创建分数与评价对照表。
A3：使用分段函数 A.segp()，根据分数在对照表中的区间序号，获取相应的评价，并按评价分组统计人数。
SQL和 SPL 都能实现分区段查找数据的需求。SQL 语句对于每个区段，都要增加一段 CASE WHEN … THEN … 语句。所以当分段数很多时，SQL 语句有些繁琐，在每个区段内都要定义分段条件。而 SPL 则是定义一个对照表，统一定义每个区段的名称和分段值，维护起来很方便。
总结
从上面的讨论可以看出，SQL很难选出最大值 / 最小值对应记录。即便 ORACLE 提供了 KEEP 函数，也只能对排序后的结果进行聚合运算，并不能真正的选出最大值 / 最小值对应记录。而 SPL 提供了函数 A.maxp()和 A.minp()，一步到位直接解决问题。
另外，当查询比较复杂时，SQL 语句的复杂程度会成倍增加。比如经常要用到临时表、嵌套查询等等，使得 SQL 语句的编写和维护难度提升。而 SPL 只要按自然思维去组织计算逻辑，逐行书写出简洁的代码。
esProc 是专业的数据计算引擎，基于有序集合设计，同时提供了完善的集合运算，相当于 Java 和 SQL 优势的结合。在 SPL 的支持下，选出运算会非常容易。
SQL 与 SPL 对比系列：
SQL 和 SPL 的集合运算对比
SQL 和 SPL 的选出运算对比
SQL 和 SPL 的有序运算对比
SQL 和 SPL 的等值分组对比
SQL 和 SPL 的非等值分组对比
SQL 和 SPL 的有序分组对比
SQL 和 SPL 的一对一和一对多连接对比
SQL 和 SPL 的多对一连接对比
SQL 和 SPL 的多对多连接对比
SQL 和 SPL 的基本静态转置对比
SQL 和 SPL 的复杂静态转置对比
SQL 和 SPL 的动态转置对比
SQL 和 SPL 的递归对比
对润乾产品感兴趣的小伙伴，一定要知道软件还能这样卖哟性价比还不过瘾？ 欢迎加入好多乾计划。
这里可以低价购买软件产品，让已经亲民的价格更加便宜！
这里可以销售产品获取佣金，赚满钱包成为土豪不再是梦！
这里还可以推荐分享抢红包，每次都是好几块钱的巨款哟！
来吧，现在就加入，拿起手机扫码，开始乾包之旅
嗯，还不太了解好多乾？
英文版