SQL还有多少“理所应当”？

转贴存档，原帖地址：http://blog.chinaunix.net/uid-29242841-id-3968998.html、http://blog.chinaunix.net/uid-29242841-id-3971046.html！

------------------------------------华丽的分割线--------------------------------

发明SQL的主要目的是为结构化数据提供一种屏弊数据物理存储方案的访问方法，因此SQL中大量使用了类英语的词汇和语法以降低理解和书写困难。作为SQL基础理论的关系代数是个完备的计算体系，原则上可以计算一切。这样看来，我们理所应当地用SQL完成各种数据计算需求。
但是，尽管关系数据库取得了巨大的成功，SQL却显然没有达到其发明初衷，除了极少数简单的查询可由终端用户采用SQL完成外，绝大多数的SQL使用者仍是技术人员，甚至许多复杂的查询对技术人员也不是件容易的事。

通过一个很简单的例子来考察SQL在计算方面的缺点。
假设有一个由三个字段构成的销售业绩表sales_amount（为了简化问题，省去日期信息）：
Sales         销售员姓名，假定无重名
Product    销售的产品
Amount    该销售员在该产品上的销售额
       
现在我们想知道空调和电视销售额都在前10名的销售员名单。这个问题很简单，人们会很自然地设计出如下计算过程：a、先按空调销售额排序，找出前10名；b、再按电视销售额排序，找出前10名；c、对a、b的结果取交集。

用SQL做：
a、找出空调销售额前10名，这很简单：
select top 10 sales from sales_amount where product='AC' order by amount desc
b、找出电视销售额前10名，动作一样：
select top 10 sales from sales_amount where product='TV' order by amount desc
c、求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 )
一个只3步的简单计算得用SQL写成这样，而日常计算多达10几步的比比皆是，这显然超出来许多人的可接受能力。
这样，我们知道SQL的第一个重要缺点：不支持步骤化。把复杂的计算分步可以在很大程度地降低问题的难度，反过来，把多步计算汇成一步则很大程度地提高了问题的难度。
如果老师要求小学生做应用题时只能列一个算式完成，小朋友们会多么苦恼（当然，不乏一些聪明孩子搞得定）。

SQL查询不能分步，但用SQL写出的存储过程可以分步，那么用存储过程是否可以方便地解决这个问题呢？
不提使用存储过程的技术环境有多复杂（这足以令大多数人却步了）和数据库的差异性造成的不兼容，我们只从理论上来看用分步SQL是否能让这个计算更简捷些。
a、计算空调销售额前10名。语句还是那样，但我们需要把结果存起来供第3步用，而SQL中只能用表存储集合数据，这样我们要建一个临时表：
create temporary table x1 as
select top 10 sales from sales_amount where product='AC' order by amount desc
b、计算电视销售额前10名。类似地：
create temporary table x2 as
select top 10 sales from sales_amount where product='TV' order by amount desc
c、求交集，前面麻烦了，这步就简单些：
select * from x1 intersect x2
分步后思路变清晰了，但临时表的使用仍显繁琐。在以批量结构化数据计算中，作为中间结果的临时集合是相当普遍的，如果都建立临时表来存储，不仅运算效率低，同时也不直观。

而且，SQL不允许某个字段取值是集合（即临时表），这样，有些计算即使容忍了繁琐也做不到。
如果我们把问题改为计算所有产品销售额都在前10名的销售员，试想一下应当如何计算，继续延用上述的思路很容易想到：
1．  将数据按产品分组，将每组排序，取出前10名；
2．  将所有的前10名取交集；
由于我们事先不知道会有多个产品，这样需要把分组结果也存储在一个临时表中，而这个表有个字段要存储对应的分组成员，这是SQL不支持的，办法就行不通了。
如果有窗口函数（SQL2003标准）的支持，可以转换思路，按产品分组后，计算每个销售员在所有分组的前10名中出现的次数，若与产品总数相同，则表示该销售员在所有产品销售额中均前在前10名内。
select sales
from ( select sales,
           from ( select sales,
                                     rank() over (partition by product order by amount desc ) ranking
                            from sales_amount)
           where ranking <=10 )
group by sales
having count(*)=(select count(distinct product) from sales_amount)
这样的SQL，有多少人会写呢？
况且，窗口函数在许多数据库中还不支持。那么，就只能用存储过程写循环依次计算每个产品的前10名，与上一次结果做交集。这个过程比用高级语言编写程序并不简单多少，而且仍然要面向临时表的繁琐。
现在，我们知道了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%的销售员，困难仍然存在。
造成这个现象的原因就是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的第四个重要困难：缺乏对象引用机制，关系代数中对象之间的关系完全靠相同的外键值来维持，这不仅在寻找时效率很低，而且无法将外键指向的记录成员直接当作本记录的属性对待，试想，上面的句子可否被写成这样：
select sales.gender,count(*)
from (…)                                       // …是前面计算“好”销售员的SQL
group by sales.gender
显然，这个句子不仅更清晰，同时计算效率也会更高（没有连接计算）。

通过一个简单的例子分析了SQL的四个重要困难，我认为这就是SQL没有达到其发明实衷的主要原因。基于一种计算体系解决业务问题的过程，事实上就是将业务问题翻译成形式化计算语法的过程（类似小学生解应用题，将题目翻译成形式化的四则运算）。而在克服这些困难之前，SQL的模型体系很不符合人们的自然思维习惯，造成问题翻译的极大障碍，使得 SQL很难大规模地应用于针对业务问题的数据计算中。
打个程序员易于理解的比方，用SQL做数据计算，类似于用汇编语言完成四则运算。我们很容易写出3+5*7这样的算式，但如果用汇编语言（以X86为例），就要写成：
mov ax,3
mov bx,5
mul bx,7
add ax,bx
这样的代码无论书写还是阅读都远不如3+5*7了（要是碰到小数就更要命了）。虽然对于熟练的程序员也算不了太大的麻烦，但对于大多数人而言，这种写法还是过于晦涩难懂了，从这个意义上讲，FORTRAN确实是个伟大的发明。

评论

1 楼 icefishc 2013-11-19  

这明显是你ｓｑｌ不扎实造成的。。。　和ｓｑｌ本身无关。