【摘要】
    选出运算是指在集合中，根据指定条件获取成员。SQL 和 SPL 是大家比较熟悉的程序语言，本文将探讨对于选出运算问题，这两种语言的解决方案和基本原理。如何简便快捷的处理选出运算，这里为你全程解析，并提供 SQL 和 SPL 示例代码。SQL 和 SPL 的选出运算对比

使用过 SQL 或 SPL 的朋友对选出运算都不会陌生。选出与定位运算很相似，不过定位关心的是成员在集合中的位置，而选出关心的是成员记录的详细信息。比如选出所有销售额超过一万的客户名称，查询总成绩最高的学生姓名等等。

1. 选出符合条件的成员

选出符合条件的成员，是很常见的选出运算，SQL 和 SPL 都可以很好的解决。SQL 语句中可以使用 WHERE 子句来实现条件过滤，SPL 则是提供了函数 A.select()。我们通过几个例子来看一下具体实现：

【例 1】 查询纽约研发部门员工的全名和收入。部分数据如下：

 

SQL的解决方案：

   select

      Concat(Concat(NAME,' '),SURNAME) FULLNAME, SALARY

   from

      EMPLOYEE

   where

      STATE='New York' and DEPT='R&D'

 

    在 SQL 语句的 WHERE 子句中，通过州名是纽约并且部门名称是研发部进行条件过滤。需要同时满足所有条件时，可以使用关键字 "and"。这里以 ORACLE 为例，返回全名时使用了函数 Concat 进行字符串连接。

 

SPL的解决方案：

A1：从文件中导入员工表。

A2：使用函数 A.select() 选出纽约研发部门员工的记录。

A3：使用符号 + 连接字符串，组成全名。

    SPL的关系运算符和逻辑运算符与 JAVA、C 等高级语言类似。使用两个等号"=="来比较是否相等。需要同时满足所有条件时，可以使用符号 "&&"。

SPL同样也支持从数据库中读取数据表，比如数据来源于数据库"db"中 "tbl" 表时， A1可以改为：

   

 

【例 2】 查询各部门 30 岁以下和 50 岁以上的员工的人数。部分数据如下：

 

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的解决方案：

A1：导入员工表。

A2：使用函数 A.select() 选出年龄小于 30 或者年龄大于 50 的员工。

A3：分组汇总各部门满足条件的人数。

    SPL提供了函数 age()，可以直接根据员工出生日期计算出年龄。需要满足多个条件中任意一个时，可以使用符号 "||"。SPL 允许查找条件中使用临时变量，可以一步完成过滤。

 

【例 3】 计算员工 Emma Taylor 与 Alexis Jones 的工资差和年龄差。员工表部分数据如下：

 

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的解决方案：

A1：导入员工表。

A2：使用函数 A.select() 的 @1 选项，选出员工 Emma Taylor 的记录。

A3：使用函数 A.select() 的 @1 选项，选出员工 Alexis Jones 的记录。

A4：计算两人的工资差。

A5：计算两人的年龄差。

A5还可以使用函数 interval()，计算两个日期时间的间隔：

    其中函数 interval() 的 @y 选项，表示取间隔的年份。

    SPL有真正的记录数据类型，本例中 A2 和 A3 的计算结果就是两名员工的记录。在后续的计算中，我们可以直接从记录中获取成员值，不再需要将它当作一个表来使用。

 

通过上面几个例子我们可以看到，简单的选出符合条件的成员时，SQL 和 SPL 的差别并不是很大。但是当选出变得复杂时，SPL 只是按照逻辑增加了一两行脚本，而 SQL 语句的复杂程度则是成倍增加。另外 SPL 可以在选出时定义临时变量，还提供了丰富的函数，使得语句更加简洁。

 

2. 查找最大值 / 最小值对应记录

接下来我们看一下如何查找最大值 / 最小值对应记录。

 

【例 4】 根据成绩表，求一班数学成绩最低的学生 ID。部分数据如下：

 

 

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() 用于选出最小值对应记录。

A1：导入成绩表，并查询一班的数学成绩。

A2：使用了函数 A.minp() 选出最低分所在的记录，再从记录中取学生 ID。

最小值所在记录不一定是唯一的，如果想返回所有记录，可以使用函数A.minp()的选项@a ：

A2：使用 A.minp() 函数的 @a 选项，选出所有最高分的记录，然后取出所有学生 ID。

 

其中A1.minp@a(SCORE)的执行结果如下：

   

    与 SQL 相比，SPL 的脚本十分简洁。这是因为 SPL 的选出，是真正的选出了最大值 / 最小值对应记录。而 SQL 无法做到这一点，即便是 ORACLE 的 KEEP 函数也只是根据排序结果计算出聚合值。

 

【例 5】 根据奥运会奖牌榜统计表，求总成绩蝉联第一名届数最长的国家及其奖牌信息。部分数据如下：

 

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() 用于选出最大值对应记录。

A1：导入奥运会奖牌榜。

A2：按第几届和总成绩降序排列。

A3：每届取一名，因为有序也就是每届的第一名。

A4：当国家改变时重新分组。

A5：使用了函数 A.maxp() 选出成员数量最多的一组，也就是蝉联次数最多的。

 

    与让人眼晕的 SQL 语句相比，SPL 语句并没有变得复杂。一方面原因前面已经提到了， SQL 语句很难选出最大值 / 最小值对应记录。另外一方面原因，是由于 SQL 的分组子集无法保留，不能使用上次的分组结果继续进行分组等运算。分组这一部分暂时先放在一边，将来会单独整理出来讲解。

 

3. 选出前 N 名 / 后 N 名

【例 7】 根据成绩表，求各班各科前两名的学生成绩。部分数据如下：

 

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 名。

A1：导入学生成绩。

A2：使用函数 A.top() 获取每个班级各科目分数的前两名。其中 -2 表示从大到小取两名。

A3：将所有班级各科的前两名记录合并到一起。

 

4. 分区段查找数据

有时候我们需要通过计算数值在区间中的序号，来获取在集合中的对应成员。比如根据考试成绩返回评价（优、良、中、差）；根据家庭年收入返回家庭所处的资产等级（贫困、小康、中产、富裕等）。

 

【例 6】 根据成绩表，统计英语科目卓越（分数 >=90）、优秀（80=< 分数 <90）、良好（70<= 分数 <80）、及格（60<= 分数 <70）和不及格（分数 <60）的人数。部分数据如下：

期望结果如下：

 

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()，根据区段序号返回序列中对应成员。

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 的支持下，选出运算会非常容易。

Employee.csv

Scores.csv

Olympic.txt

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 的递归对比