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


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾