SQL计算困难的典型示例与点评

       SQL的批量结构化数据计算能力是完备的，也就是说找不出什么SQL无法计算的东西。但是其支持层面过低，会导致实际应用时十分繁琐。
      具体表现为如下四个方面：

• 计算不分步：SQL要求计算在一个语句内写出，必须采用存储过程才能实施分步计算。不分步不仅造成思维困难，而且难以利用中间结果；
• 集合无序：SQL不直接提供使用位置引用集合成员的机制，与次序和定位相关的计算都需要转换才能实施；
• 集合化不彻底：SQL的集合功能简单，只用于表示查询的结果集，而不能作为作为基本数据类型广泛应用；
• 缺乏对象引用：SQL不支持记录引用，数据表间的关联采用同值外键方案，多表联合计算时需要做连接，不仅理解困难而且效率低下；

      基于一种计算体系实现数据计算过程，事实上就是将业务问题翻译成形式化计算语法的过程（类似小学生解应用题，将题目翻译成形式化的四则运算）。而由于SQL的上述四个问题，其模型体系在处理复杂运算时不符合人们的自然思维习惯，造成问题翻译的极大障碍，导致将问题解法形式化成计算语法的难度要远远大于解决问题本身。
      下面分别举例说明这四个方面的问题。
      为了让例子中的SQL尽量简捷，这里大量使用了SQL2003标准的窗口函数，故而采用了对SQL2003标准的支持做得较好的ORACLE数据库语法，采用其它数据库的语法编写这些SQL一般将会更复杂。
一．计算不分步
      把复杂的计算分步可以在很大程度地降低问题的难度，反过来，把多步计算汇成一步完成则会提高问题的复杂度。
      任务1    销售部的人数，其中北京籍人数，再其中女员工人数？
            销售部的人数
            1    select count(*) from 员工表 where 部门=‘销售部’
           其中北京籍的人数
           1    select count(*) from 员工表 where 部门=‘销售部’and 籍贯=‘北京’
           在其中的女员工人数
            1    select countT(*) from 员工表
            2    where 部门=‘销售部’and 籍贯=‘北京’and 性别=‘女’
     常规的想法：选出销售部人员计数，再在其中找出其中北京籍人员计数，然后再递进地找出女员工计数。每次查询都基于上次已有的结果，不仅书写简单而且效率更高。
     但是，SQL的计算不能分步，回答下一个问题时无法引用前面的成果，只能把相应的查询条件再抄一遍。
    任务2    每个部门挑选一对男女员工组成游戏小组
          1       with A as
          2                      (select 姓名,部门,
          3                                    row_number() over(partition by 部门 order by 1) 序号
          4                      from 员工表 where 性别=‘男’),
          5               B as
          6                     (select 姓名,部门,
          7                                       row_number() over(partition by 部门 order by 1) 序号
          8                     from 员工表 where 性别=‘女’)
          9      select 姓名,部门 from A
         10     where 部门 in ( select distinct 部门 from B ) and 序号=1
         11     union all
         12     select 姓名,部门 from B
         13      where 部门 in ( select distinct 部门 from A ) and 序号=1
     计算不分步有时不仅造成书写麻烦和计算低效，甚至可能导致思路严重变形。
     这个任务的直观想法：针对每个部门循环，如果该部门有男女员工则各取一名添进结果集中。但SQL不支持这种逐步完成结果集的写法（要用存储过程才能实现此方案），这时必须转变思路为：从每个部门中选出男员工，从每个部门选出女员工，对两个结果集分别选出部门出现在另一个结果集的成员，最后再做并集。
     好在还有with子句和窗口函数over（SQL2003标准开始支持），否则这个SQL语句简直无法看了。
二．集合无序
     有序计算在批量数据计算中非常普遍（取前3名/第3名、比上期等），但SQL延用了数学上的无序集合概念，有序计算无法直接进行，只能调整思路变换方法。
     任务3    公司中年龄居中的员工
         1         select 姓名,出生日期
         2         from (select 姓名,出生日期,row_number() over (order by 出生日期) 排名 
         3         from 员工表 )
         4          where 排名=(select floor((count(*)+1)/2) from 员工表）
     中位数是个常见的计算，本来只要很简单地在排序后的集合中取出位置居中的成员。但SQL的无序集合机制不提供直接用位置访问成员的机制，必须人为造出一个序号字段，再用条件查询方法将其选出，导致必须采用子查询才能完成。
     任务4    某支股票最长连续涨了多少交易日
          1        select max(连续日数)
          2        from (select count(*) 连续日数
          3                    from (select sum(涨跌标志) over(order by 交易日) 不涨日数
          4                                 from (select 交易日,
          5                                                         case when
          6                                                                    收盘价>lag(收盘价) over(order by 交易日)
          7                                                         then 0 else 1 END 涨跌标志 8 from 股价表) )
          8                                              from 股价表) )
          9                     group by 不涨日数)
         无序的集合也会导致思路变形。
         常规的计算连涨日数思路：设定一初始为0的临时变量记录连涨日期，然后和上一日比较，如果未涨则将其清0，涨了再加1，循环结束看该值出现的最大值。
         使用SQL时无法描述此过程，需要转换思路，计算从初始日期到当日的累计不涨日数，不涨日数相同者即是连续上涨的交易日，针对其分组即可拆出连续上涨的区间，再求其最大计数。这句SQL读懂已经不易，写出来则更困难了。
三．集合化不彻底
         毫无疑问，集合是批量数据计算的基础。SQL虽然有集合概念，但只限于描述简单的结果集，没有将集合作为一种基本的数据类型以扩大其应用范围。
        任务5    公司中与其他人生日相同的员工
            1        select * from 员工表
            2        where to_char(出生日期,‘MMDD’) in
            3                   ( select to_char(出生日期,‘MMDD’) from 员工表
            4                        group by to_char(出生日期,‘MMDD’)
            5                         having count(*)>1 )
         分组的本意是将源集合分拆成的多个子集合，其返回值也应当是这些子集。但SQL无法表示这种“由集合构成的集合”，因而强迫进行下一步针对这些子集的汇总计算而形成常规的结果集。
        但有时我们想得到的并非针对子集的汇总值而是子集本身。这时就必须从源集合中使用分组得到的条件再次查询，子查询又不可避免地出现。
        任务6    找出各科成绩都在前10名的学生
            1         select 姓名
            2         from (select 姓名
            3                       from (select 姓名,
            4                                               rank() over(partition by 科目 order by 成绩 DESC) 名次
            5                                   from 成绩表)
            6                        where 名次<=10)
            7         group by 姓名
            8         having count(*)=(select count(distinct 科目) from 成绩表)
         用集合化的思路，针对科目分组后的子集进行排序和过滤选出各个科目的前10名，然后再将这些子集做交集即可完成任务。但SQL无法表达“集合的集合”，也没有针对不定数量集合的交运算，这时需要改变思路，利用窗口函数找出各科目前10名后再按学生分组找出出现次数等于科目数量的学生，造成理解困难。
四． 缺乏对象引用
        在SQL中，数据表之间的引用关系依靠同值外键来维系，无法将外键指向的记录直接用作本记录的属性，在查询时需要借助多表连接或子查询才能完成，不仅书写繁琐而且运算效率低下。
        任务7    女经理的男员工们
         用多表连接
              1       select A.*
              2       from 员工表 A,部门表 B,员工表 C
              3       where A.部门=B.部门 and B.经理=C.姓名 and
              4                         A.性别=‘男’ AND C.性别=‘女’
        用子查询
             1       select * from 员工表
             2       where 部门 in
             3                   (select 部门 from 部门表
             4                     where 经理 in
             5                                            (select 姓名 from 员工表 where 性别=‘女’))
        如果员工表中的部门字段是指向部门表中的记录，而部门表中的经理字段是指向员工表的记录，那么这个查询条件只要简单地写成这种直观高效的形式：
                where 部门.经理.性别='女' and 性别='男'
         但在SQL中则只能使用多表连接或子查询，写出上面那两种明显晦涩的语句。
         任务8    员工的首份工作公司
         用多表连接
              1        select 姓名,公司 首份工作公司
              2        from (select 员工.姓名 姓名,履历表.公司 公司,
              3                                row_number() over(partition by 履历表.姓名 
              4                                                                           order by 履历表.开始日期) 工作序号
              5                    from 员工表,履历表 where 员工表.姓名=履历表.姓名)
              6        where 工作序号=1
        用子查询
              1       select 姓名,
              2                     (select 公司 from 履历表
              3                       where 姓名=A.姓名 and
              4                                    开始日期=(select min(开始日期) from 履历表
              5                                                           where 姓名=A.姓名)) 首份工作公司
              6       from 员工表 A
        没有对象引用机制和彻底集合化的SQL，自然也不能将子表作主表的属性（字段值）处理。针对子表的查询要么使用多表连接，增加语句的复杂度，还要将结果集用过滤或分组转成与主表记录一一对应的情况（连接后的记录与子表一一对应）；要么采用子查询，每次临时计算出与主表记录相关的子表记录子集，增加整体计算量（子查询不能用with子句了）和书写繁琐度。