文章目录

  • 前言
  • 一、SQL困难的分析探讨
  • 1、引例
  • 2、解决方案
  • 3、SQL的第一个重要缺点
  • 1）不支持步骤化
  • 2）尝试优化
  • 4、SQL的第二个重要缺点
  • 1）集合化不彻底
  • 5、SQL的第三个重要缺点
  • 1）缺乏有序支持
  • 二、SQL的更多例子
  • 1、计算不分步
  • 2、集合无序
  • 3、集合化不彻底
  • 三、SPL的引入
  • 四、SPL资料


前言

  发明SQL的初衷之一显然是为了降低人们实施数据查询计算的难度。SQL中用了不少类英语的词汇和语法，这是希望非技术人员也能掌握。确实，简单的SQL可以当作英语阅读，即使没有程序设计经验的人也能运用。
  然而，面对稍稍复杂的查询计算需求，SQL就会显得力不从心，经常写出几百行有多层嵌套的语句。这种SQL，不要说非技术人员难以完成，即使对于专业程序员也不是件容易的事，常常成为很多软件企业应聘考试的重头戏。三行五行的SQL仅存在教科书和培训班，现实中用于报表查询的SQL通常是以“K”计的。

一、SQL困难的分析探讨

1、引例

  这是为什么呢？我们通过一个很简单的例子来考察SQL在计算方面的缺点。设有一个由三个字段构成的销售业绩表：

字段

名称

sales_amount

销售业绩表

sales

销售员姓名，假定无重名

product

销售的产品

amount

该销售员在该产品上的销售额

  现在我们想知道出空调和电视销售额都在前10名的销售员名单。

sql server 第几行至第几行_数据库

2、解决方案

  这个问题并不难，人们会很自然地设计出如下计算过程：
  1． 按空调销售额排序，找出前10名；
  2． 按电视销售额排序，找出前10名；
  3． 对1、2的结果取交集，得到答案；
  我们现在来用SQL做。
    1． 找出空调销售额前10名，还算简单：

select top 10 sales from sales_amount where product='AC' order by amount desc

    2． 找出电视销售额前10名。动作一样：

select top 10 sales from sales_amount where product='TV' order by amount desc

    3． 求1、2的交集。这有点麻烦，SQL不支持步骤化，上两步的计算结果无法保存，只能再重抄一遍了:

select * from
    ( select top 10 sales from sales_amount where product='AC' order by amount desc )
intersect
    ( select top 10 sales from sales_amount where product='TV' order by amount desc )

  一个只三步的简单计算用SQL要写成这样，而日常计算中多达十几步的比比皆是，这显然超出来许多人的可接受能力。

sql server 第几行至第几行_数据库_02

3、SQL的第一个重要缺点

1）不支持步骤化

  我们知道了SQL的第一个重要缺点：不支持步骤化。把复杂的计算分步可以在很大程度地降低问题的难度，反过来，把多步计算汇成一步则很大程度地提高了问题的难度。
  可以想象，如果老师要求小学生做应用题时只能列一个算式完成，小朋友们会多么苦恼。SQL查询不能分步，但用SQL写出的存储过程可以分步，那么用存储过程是否可以方便地解决这个问题呢？
  暂先不管使用存储过程的技术环境有多麻烦和数据库的差异性造成的不兼容，我们只从理论上来看用分步SQL是否能让这个计算更简单捷些。

2）尝试优化

  1． 计算空调销售额前10名。语句还是那样，但我们需要把结果存起来供第3步用，而SQL中只能用表存储集合数据，这样我们要建一个临时表：

create temporary table x1 as
    select top 10 sales from sales_amount where product='AC' order by amount desc

  2． 计算电视销售额前10名。类似地

create temporary table x2 as
    select top 10 sales from sales_amount where product='TV' order by amount desc

  3． 求交集，前面麻烦了，这步就简单些

select * from x1 intersect x2

  分步后思路变清晰了，但临时表的使用仍然繁琐。在批量结构化数据计算中，作为中间结果的临时集合是相当普遍的，如果都建立临时表来存储，运算效率低，代码也不直观。
  而且，SQL不允许某个字段取值是集合（即临时表），这样，有些计算即使容忍了繁琐也做不到。

4、SQL的第二个重要缺点

1）集合化不彻底

  现在，我们知道了SQL的第二个重要缺点：集合化不彻底。虽然SQL有集合概念，但并未把集合作为一种基础数据类型提供，这使得大量集合运算在思维和书写时都需要绕路。
  我们在上面的计算中使用了关键字 top，事实上关系代数理论中没有这个东西（它可以被别的计算组合出来），这不是SQL的标准写法。
  我们来看一下没有top时找前10名会有多困难？
  大体思路是这样：找出比自己大的成员个数作为是名次，然后取出名次不超过10的成员，写出的SQL如下：

select sales
from ( select A.sales sales, A.product product,
             (select count(*)+1 from sales_amount
              where A.product=product AND A.amount<=amount) ranking
       from sales_amount A )
where product='AC' AND ranking<=10

或

select sales
from ( select A.sales sales, A.product product, count(*)+1 ranking
       from sales_amount A, sales_amount B
       where A.sales=B.sales and A.product=B.product AND A.amount<=B.amount
       group by A.sales,A.product )
where product='AC' AND ranking<=10

  这样的SQL语句，专业程序员写出来也未必容易吧！而仅仅是计算了一个前10名。
  退一步讲，即使有top，那也只是使取出前一部分轻松了。如果我们把问题改成取第6至10名，或者找比下一名销售额超过10%的销售员，困难仍然存在。

5、SQL的第三个重要缺点

1）缺乏有序支持

  造成这个现象的原因就是SQL的第三个重要缺点：缺乏有序支持。SQL继承了数学上的无序集合，这直接导致与次序有关的计算相当困难，而可想而知，与次序有关的计算会有多么普遍（诸如比上月、比去年同期、前20%、排名等）。

  SQL2003标准中增加的窗口函数提供了一些与次序有关的计算能力，这使得上述某些问题可以有较简单的解法，在一定程度上缓解SQL的这个问题。但窗口函数的使用经常伴随着子查询，而不能让用户直接使用次序访问集合成员，还是会有许多有序运算难以解决。

  我们现在想关注一下上面计算出来的“好”销售员的性别比例，即男女各有多少。一般情况下，销售员的性别信息会记在花名册上而不是业绩表上，简化如下：

employee

员工表

name

员工姓名，假定无重名

gender

员工性别

  我们已经计算出“好”销售员的名单，比较自然的想法，是用名单到花名册时找出其性别，再计一下数。但在SQL中要跨表获得信息需要用表间连接，这样，接着最初的结果，SQL就会写成：

select employee.gender,count(*)
from employee,
    ( ( select top 10 sales from sales_amount where product='AC' order by amount desc )
    intersect
    ( select top 10 sales from sales_amount where product='TV' order by amount desc ) ) A
where A.sales=employee.name
group by employee.gender

  仅仅多了一个关联表就会导致如此繁琐，而现实中信息跨表存储的情况相当多，且经常有多层。比如销售员有所在部门，部门有经理，现在我们想知道“好”销售员归哪些经理管，那就要有三个表连接了，想把这个计算中的where和group写清楚实在不是个轻松的活儿了。

sql server 第几行至第几行_字段_03

二、SQL的更多例子

  我们再举几个例子来分别说明这几个方面的问题。
  为了让例子中的SQL尽量简捷，这里大量使用了窗口函数，故而采用了对窗口函数支持较好的ORACLE数据库语法，采用其它数据库的语法编写这些SQL一般将会更复杂。
这些问题本身应该也算不上很复杂，都是在日常数据分析中经常会出现的，但已经很难为SQL了。

1、计算不分步

  把复杂的计算分步可以在很大程度地降低问题的难度，反过来，把多步计算汇成一步完成则会提高问题的复杂度。

任务1 销售部的人数，其中北京籍人数，再其中女员工人数？

销售部的人数

select count(*) from employee where department='sales'

其中北京籍的人数

select count(*) from employee where department='sales' and native_place='Beijing'

再其中的女员工人数

select count (*) from employee
where department='sales' and native_place='Beijing' and gender='female'

  常规想法：选出销售部人员计数，再在其中找出其中北京籍人员计数，然后再递进地找出女员工计数。每次查询都基于上次已有的结果，不仅书写简单而且效率更高。
  但是，SQL的计算不分步，回答下一个问题时无法引用前面的成果，只能把相应的查询条件再抄一遍。

任务2 每个部门挑选一对男女员工组成游戏小组

with A as
           (select name, department,
                  row_number() over (partition by department order by 1) seq
            from employee where gender=‘male’)
         B as
            (select name, department,
                  row_number() over(partition by department order by 1) seq
            from employee where gender=‘female’)
    select name, department from A
    where department in ( select distinct department from B ) and seq=1
    union all
    select name, department from B
    where department in (select distinct department from A ) and seq=1

  计算不分步有时不仅造成书写麻烦和计算低效，甚至可能导致思路严重变形。
  这个任务的直观想法：针对每个部门循环，如果该部门有男女员工则各取一名添进结果集中。但SQL不支持这种逐步完成结果集的写法（要用存储过程才能实现此方案），这时必须转变思路为：从每个部门中选出男员工，从每个部门选出女员工，对两个结果集分别选出部门出现在另一个结果集的成员，最后再做并集。
  好在还有with子句和窗口函数，否则这个SQL语句简直无法看了。

2、集合无序

  有序计算在批量数据计算中非常普遍（取前3名/第3名、比上期等），但SQL延用了数学上的无序集合概念，有序计算无法直接进行，只能调整思路变换方法。

任务3 公司中年龄居中的员工

select name, birthday
from (select name, birthday, row_number() over (order by birthday) ranking
      from employee )
where ranking=(select floor((count(*)+1)/2) from employee)

  中位数是个常见的计算，本来只要很简单地在排序后的集合中取出位置居中的成员。但SQL的无序集合机制不提供直接用位置访问成员的机制，必须人为造出一个序号字段，再用条件查询方法将其选出，导致必须采用子查询才能完成。

任务4 某支股票最长连续涨了多少交易日

select max (consecutive_day)
from (select count(*) (consecutive_day
      from (select sum(rise_mark) over(order by trade_date) days_no_gain
            from (select trade_date,
                         case when
                              closing_price>lag(closing_price) over(order by trade_date)
                         then 0 else 1 END rise_mark
                from stock_price) )
     group by days_no_gain)

  无序的集合也会导致思路变形。
  常规的计算连涨日数思路：设定一初始为0的临时变量记录连涨日期，然后和上一日比较，如果未涨则将其清0，涨了再加1，循环结束看该值出现的最大值。
  使用SQL时无法描述此过程，需要转换思路，计算从初始日期到当日的累计不涨日数，不涨日数相同者即是连续上涨的交易日，针对其分组即可拆出连续上涨的区间，再求其最大计数。这句SQL读懂已经不易，写出来则更困难了。

3、集合化不彻底

  毫无疑问，集合是批量数据计算的基础。SQL虽然有集合概念，但只限于描述简单的结果集，没有将集合作为一种基本的数据类型以扩大其应用范围。

任务5 公司中与其他人生日相同的员工

select * from employee
where to_char (birthday, ‘MMDD’) in
    ( select to_char(birthday, 'MMDD') from employee
      group by to_char(birthday, 'MMDD')
      having count(*)>1 )

  分组的本意是将源集合分拆成的多个子集合，其返回值也应当是这些子集。但SQL无法表示这种“由集合构成的集合”，因而强迫进行下一步针对这些子集的汇总计算而形成常规的结果集。

  但有时我们想得到的并非针对子集的汇总值而是子集本身。这时就必须从源集合中使用分组得到的条件再次查询，子查询又不可避免地出现。

任务6 找出各科成绩都在前10名的学生

select name
from (select name
      from (select name,
                   rank() over(partition by subject order by score DESC) ranking
            from score_table)
      where ranking<=10)
group by name
having count(*)=(select count(distinct subject) from score_table)

  用集合化的思路，针对科目分组后的子集进行排序和过滤选出各个科目的前10名，然后再将这些子集做交集即可完成任务。但SQL无法表达“集合的集合”，也没有针对不定数量集合的交运算，这时需要改变思路，利用窗口函数找出各科目前10名后再按学生分组找出出现次数等于科目数量的学生，造成理解困难。

三、SPL的引入

  问题说完，该说解决方案了。
  其实在分析问题时也就一定程度地指明了解决方案，重新设计计算语言，克服掉SQL的这几个难点，问题也就解决了。
  这就是发明SPL的初衷！
  SPL是个开源的程序语言，其全名是Structured Process Language，和SQL只差一个词。目的在于更好的解决结构化数据的运算。SPL中强调了步骤化、支持有序集合和对象引用机制、从而得到彻底的集合化，这些都会大幅降低前面说的“解法翻译”难度。
  这里的篇幅不合适详细介绍SPL了，我们只把上一节中的8个例子的SPL代码罗列出来感受一下：

任务1

A

B

1

=employee.select(department==“sales”)

=A1.len()

2

=A1.select(native_place==“Beijing”)

=A2.len()

3

=A2.select(gender==“female”)

=A3.len()

SPL可以保持记录集合用作中间变量，可逐步执行递进查询。

任务2

A

B

C

1

for employee.group(department)

=A1.group@1(gender)

2

>if B1.len()>1

=@|B1

有步骤和程序逻辑支持的SPL能很自然地逐步完成结果。

任务3

A

1

=employee.sort(birthday)

2

=A1((A1.len()+1)/2)

对于以有序集合为基础的SPL来说，按位置取值是个很简单的任务。

任务4

A

1

=stock_price.sort(trade_date)

2

=0

3

=A1.max(A2=if(close_price>close_price[-1],A2+1,0))

SPL按自然的思路过程编写计算代码即可。

任务5

A

1

=employee.group(month(birthday),day(birthday))

2

=A1.select(~.len()>1).conj()

SPL可以保存分组结果集，继续处理就和常规集合一样。

任务6

A

1

=score_table.group(subject)

2

=A1.(~.rank(score).pselect@a(~<=10))

3

=A1.(~(A2(#)).(name)).isect()

使用SPL只要按思路过程写出计算代码即可。

任务7

A

1

=employee.select(gender==“male” && department.manager.gender==“female”)

支持对象引用的SPL可以简单地将外键指向记录的字段当作自己的属性访问。

任务8

A

1

=employee.new(name,resume.minp(start_date).company:first_company)

SPL支持将子表集合作为主表字段，就如同访问其它字段一样，子表无需重复计算。
SPL有直观的IDE，提供了方便的调试功能，可以单步跟踪代码，进一步降低代码的编写复杂度。

sql server 第几行至第几行_数据库_04

  对于应用程序中的计算，SPL提供了标准的JDBC驱动，可以像SQL一样集成到Java应用程序中：

…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
Statement st = connection.();
CallableStatement st = conn.prepareCall("{call xxxx(?,?)}");
st.setObject(1, 3000);
st.setObject(2, 5000);
ResultSet result=st.execute();
...

四、SPL资料