SQL 为什么动不动就 N 百行以 K 计

2021-12-15 00:00:00 查询集合计算分组销售员

发明 SQL 的初衷之一显然是为了降低人们实施数据查询计算的难度。SQL 中用了不少类英语的词汇和语法，这是希望非技术人员也能掌握。确实，简单的 SQL 可以当作英语阅读，即使没有程序设计经验的人也能运用。

然而，面对稍稍复杂的查询计算需求，SQL 就会显得力不从心，经常写出几百行有多层嵌套的语句。这种 SQL，不要说非技术人员难以完成，即使对于专业程序员也不是件容易的事，常常成为很多软件企业应聘考试的重头戏。三行五行的 SQL 仅存在教科书和培训班，现实中用于报表查询的 SQL 通常是以“K”计的。

SQL 困难的分析探讨

这是为什么呢？我们通过一个很简单的例子来考察 SQL 在计算方面的缺点。

设有一个由三个字段构成的销售业绩表（为了简化问题，省去日期信息）：

sales_amount	销售业绩表
sales	销售员姓名，假定无重名
product	销售的产品
amount	该销售员在该产品上的销售额

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

这个问题并不难，人们会很自然地设计出如下计算过程：

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 的个重要缺点：不支持步骤化。把复杂的计算分步可以在很大程度地降低问题的难度，反过来，把多步计算汇成一步则很大程度地提高了问题的难度。

可以想象，如果老师要求小学生做应用题时只能列一个算式完成，小朋友们会多么苦恼（当然，不乏一些聪明孩子搞得定）。

SQL 查询不能分步，但用 SQL 写出的存储过程可以分步，那么用存储过程是否可以方便地解决这个问题呢？

暂先不管使用存储过程的技术环境有多麻烦和数据库的差异性造成的不兼容，我们只从理论上来看用分步 SQL 是否能让这个计算更简单捷些。

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 不允许某个字段取值是集合（即临时表），这样，有些计算即使容忍了繁琐也做不到。

如果我们把问题改为计算所有产品销售额都在前 10 名的销售员，试想一下应当如何计算，延用上述的思路很容易想到：

1．将数据按产品分组，将每组排序，取出前 10 名；

2．将所有的前 10 名取交集；

由于我们事先不知道会有多个产品，这样需要把分组结果也存储在一个临时表中，而这个表有个字段要存储对应的分组成员，这是 SQL 不支持的，办法就行不通了。

如果有窗口函数的支持，可以转换思路，按产品分组后，计算每个销售员在所有分组的前 10 名中出现的次数，若与产品总数相同，则表示该销售员在所有产品销售额中均前在前 10 名内。

select salesfrom ( select sales,     from ( select sales,                   rank() over (partition by product order by amount desc ) ranking            from sales_amount)     where ranking <=10 )group by saleshaving count(*)=(select count(distinct product) from sales_amount)

这样的 SQL，有多少人会写呢？

况且，窗口函数在有些数据库中还不支持。那么，就只能用存储过程写循环依次计算每个产品的前 10 名，与上一次结果做交集。这个过程比用语言编写程序并不简单多少，而且仍然要面向临时表的繁琐。

现在，我们知道了 SQL 的第二个重要缺点：集合化不彻底。虽然 SQL 有集合概念，但并未把集合作为一种基础数据类型提供，这使得大量集合运算在思维和书写时都需要绕路。

我们在上面的计算中使用了关键字 top，事实上关系代数理论中没有这个东西（它可以被别的计算组合出来），这不是 SQL 的标准写法。

我们来看一下没有 top 时找前 10 名会有多困难？

大体思路是这样：找出比自己大的成员个数作为是名次，然后取出名次不超过 10 的成员，写出的 SQL 如下：

select salesfrom ( 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 salesfrom ( 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 ) ) Awhere A.sales=employee.namegroup by employee.gender

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

这就是我们要说的 SQL 的第四个重要困难：缺乏对象引用机制，关系代数中对象之间的关系完全靠相同的外键值来维持，这不仅在寻找时效率很低，而且无法将外键指向的记录成员直接当作本记录的属性对待，试想，上面的句子可否被写成这样：

select sales.gender,count(*)from (…) // …是前面计算“好”销售员的SQLgroup by sales.gender

显然，这个句子不仅更清晰，同时计算效率也会更高（没有连接计算）。

我们通过一个简单的例子分析了 SQL 的四个重要困难，这也是 SQL 难写或要写得很长的主要原因。基于一种计算体系解决业务问题的过程，也就是将业务问题的解法翻译成形式化计算语法的过程（类似小学生解应用题，将题目翻译成形式化的四则运算）。SQL 的上述困难会造成问题解法翻译的极大障碍，极端情况就会发生这样一种怪现象：将问题解法形式化成计算语法的难度要远远大于解决问题本身。

再打个程序员易于理解的比方，用 SQL 做数据计算，类似于用汇编语言完成四则运算。我们很容易写出 3+5*7 这样的算式，但如果用汇编语言（以 X86 为例），就要写成

mov ax,3mov bx,5mul bx,7add ax,bx

这样的代码无论书写还是阅读都远不如 3+5*7 了（要是碰到小数就更要命了）。虽然对于熟练的程序员也算不了太大的麻烦，但对于大多数人而言，这种写法还是过于晦涩难懂了，从这个意义上讲，FORTRAN 确实是个伟大的发明。

为了理解方便，我们举的例子还是非常简单的任务。现实中的任务要远远比这些例子复杂，过程中会面临诸多大大小小的困难。这个问题多写几行，那个问题多写几行，一个稍复杂的任务写出几百行多层嵌套的 SQL 也就不奇怪了。而且这个几百行常常是一个语句，由于工程上的原因，SQL 又很难调试，这又进一步加剧了复杂查询分析的难度。

更多例子

我们再举几个例子来分别说明这几个方面的问题。

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

计算不分步

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

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

销售部的人数

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

其中北京籍的人数

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

再其中的女员工人数

select count (*) from employeewhere 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 Awhere department in ( select distinct department from B ) and seq=1union allselect name, department from Bwhere department in (select distinct department from A ) and seq=1

计算不分步有时不仅造成书写麻烦和计算低效，甚至可能导致思路严重变形。

这个任务的直观想法：针对每个部门循环，如果该部门有男女员工则各取一名添进结果集中。但 SQL 不支持这种逐步完成结果集的写法（要用存储过程才能实现此方案），这时必须转变思路为：从每个部门中选出男员工，从每个部门选出女员工，对两个结果集分别选出部门出现在另一个结果集的成员，后再做并集。

好在还有 with 子句和窗口函数，否则这个 SQL 语句简直无法看了。

集合无序

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

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

select name, birthdayfrom (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  else 1 END rise_mark                from stock_price) )     group by days_no_gain)

无序的集合也会导致思路变形。

常规的计算连涨日数思路：设定一初始为 0 的临时变量记录连涨日期，然后和上一日比较，如果未涨则将其清 0，涨了再加 1，循环结束看该值出现的大值。

使用 SQL 时无法描述此过程，需要转换思路，计算从初始日期到当日的累计不涨日数，不涨日数相同者即是连续上涨的交易日，针对其分组即可拆出连续上涨的区间，再求其大计数。这句 SQL 读懂已经不易，写出来则更困难了。

集合化不彻底

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

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

select * from employeewhere 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 namefrom (select name      from (select name,                   rank() over(partition by subject order by score DESC) ranking            from score_table)      where ranking<=10)group by namehaving count(*)=(select count(distinct subject) from score_table)

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

缺乏对象引用

在 SQL 中，数据表之间的引用关系依靠同值外键来维系，无法将外键指向的记录直接用作本记录的属性，在查询时需要借助多表连接或子查询才能完成，不仅书写繁琐而且运算效率低下。

任务 7 女经理的男员工们

用多表连接

select A.*from employee A, department B, employee Cwhere A.department=B.department and B.manager=C.name andA.gender='male' and C.gender='female'

用子查询

select * from employeewhere gender='male' and department in    (select department from department     where manager in          (select name from employee where gender='female'))

如果员工表中的部门字段是指向部门表中的记录，而部门表中的经理字段是指向员工表的记录，那么这个查询条件只要简单地写成这种直观高效的形式：

where gender='male' and department.manager.gender='female'

但在 SQL 中则只能使用多表连接或子查询，写出上面那两种明显晦涩的语句。

任务 8 员工的首份工作公司

用多表连接

select name, company, first_companyfrom (select employee.name name, resume.company company,             row_number() over(partition by resume. name                               order by resume.start_date) work_seq      from employee, resume where employee.name = resume.name)where work_seq=1

用子查询

select name,    (select company from resume     where name=A.name and           start date=(select min(start_date) from resume                       where name=A.name)) first_companyfrom employee A

没有对象引用机制和彻底集合化的 SQL，也不能将子表作主表的属性（字段值）处理。针对子表的查询要么使用多表连接，增加语句的复杂度，还要将结果集用过滤或分组转成与主表记录一一对应的情况（连接后的记录与子表一一对应）；要么采用子查询，每次临时计算出与主表记录相关的子表记录子集，增加整体计算量（子查询不能用 with 子句了）和书写繁琐度。

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，提供了方便的调试功能，可以单步跟踪代码，进一步降低代码的编写复杂度。

对于应用程序中的计算，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();...

相关文章