SQL 嵌套 N 层太长太难写怎么办？

我们工作中写 SQL 处理数据是家常便饭，不管是应用内数据处理还是临时查询分析都可以用 SQL 完成，相对其他技术（如 Java 等高级语言）也更简单。不过，SQL 的简单只限于简单需求，有些复杂计算场景 SQL 写起来却很难，嵌套 N 层以至于达到几百上千行，说 SQL 代码长度时通常不会以行计而是以 KB 计。这种情况并不少见，相信经常写 SQL 的小伙伴并不陌生。

为什么会出现这种情况呢？在http://c.raqsoft.com.cn/article/1639032922105 里详细分析了这个问题。虽然 SQL 比其他数据处理技术更简单，但仍存在一些短板（如缺乏有序支持、集合化不彻底、没有高级语言的对象引用机制等），导致复杂计算用 SQL 表达出来很繁琐。这种既复杂又很长的 SQL 会带来很多问题。

长 SQL 有什么危害？

复杂长 SQL 给我们带来的第一个困扰是难写。

其实，代码长本身还不是大问题，很多任务步骤多也就会写得长，但并不难。而 SQL 的长，常常是伴随着难，而且会随着长度增加变得异常难。SQL 在解题时很绕（思维方式上的难），你没法按照正常思路去实施算法，明明想出一个好的解法，简单一二三四步就完成了，但用 SQL 就要绕来绕去，不嵌套几层写个几十上百行好像体现不出你水平一样。而且不光嵌套子查询，复杂 SQL 还伴随多表关联和各种过滤条件，写的时候要保持头脑异常清醒，一旦写错不仅很难定位，有时还会把数据库跑死。这当然跟 SQL 不提倡过程（CTE 语法提供了一定支持）有关，一个任务写 100 行代码，分成 100 个语句还是只有 1 个语句，其复杂度完全不是一个层面的。

另外，写代码还离不开调试，而 SQL 的调试功能可谓难用至极。直到今天各大数据库都没提供像样的调试功能，相比其他高级语言的开发调试环境简直不忍直视。一句嵌套 N 层的长 SQL 发现写的不对只能把子查询从长语句里一层一层摘出来单独执行调试定位，费时费力，这更增大了 SQL 的书写难度。

难写就意味着开发周期长，我们写 SQL 都是为前端业务服务的，一个计算需求连写带调试搞个三五天甚至一周，恐怕业务时效性都没了，被怒怼也是有苦难言。

除了难写，复杂 SQL 还很难维护。数据处理业务的稳定性往往很差，经常要修改，不过想要修改这些 SQL 恐怕并非易事。别说新人接手，就是作者本人也经常出现过一段时间自己都看不懂的尴尬情况。改一个 SQL 跟重新写一个的时间差不多，这还玩啥。

另外复杂 SQL 还会影响数据库移植。虽然数据库移植不经常发生，不过一旦发生就要命。近些年随着业务的高速发展以及开源数据库的崛起，更换数据库时有发生。这就涉及到 SQL 迁移，我们知道数据库都是有方言的，在一个数据库上使用的函数到另外一个数据库未必管用，而且各类数据库对 SQL 标准的支持程度不同（如 Oracle 窗口函数支持的很好，其他数据库就要差很多），这些“特殊的”内容夹杂在复杂 SQL 里就很难实现数据库迁移了。

不管怎样，复杂 SQL 都是数据开发人员的噩梦。

怎么办？

SQL 难的问题可不是第一天出现，大家也都在积极寻找解决办法。其实现在很多开发方法已经不再推荐写复杂 SQL，像 ORM 技术算是革掉 SQL 半条命，而微服务等框架更是要求 SQL 仅仅用于完成基本的数据读写，而不能用于做复杂计算和业务处理。这些方法的思路很清晰，即把 SQL 的应用局限于基本的读写任务，在应用端完成复杂的数据处理和业务逻辑，这样就可以规避 SQL 的那些问题，架构上也更符合现代应用的需要。

不用 SQL，那些复杂的计算和业务逻辑该用什么技术来做呢？

当然只能是其它程序语言了，几乎所有应用级程序语言都可以指挥 SQL 工作，实现这种开发架构不是问题。那么，这个问题是不是就被轻易解决了？

我们来考察一下这些常用的技术。

Java

Java 肯定是第一选择，毕竟众多应用程序都是 Java 开发的，如果能搞定这些数据处理就会有很多优势。Java 天然支持过程计算，实现复杂计算时虽然代码可能更长，但可以按照正常思维实现算法，这样是不是就可以替代 SQL 了？

No，没有这么简单。

由于 Java 缺乏专业的结构化数据对象，缺少来自底层的有力支持，在实现 SQL 这类的复杂计算时并不容易。

结构化数据计算的返回值的结构随计算过程而变，大量的中间结果同样是动态结构，这些都难以事先定义，而 Java 是强类型语言，又必须事先定义数据对象的结构（否则只能用 map 这类操作繁琐的数据对象），这就使 Java 的结构化计算僵化死板，lambda 语法的能力严重受限。解释性语言可以简化参数的定义，函数本身就可指定参数表达式应解释成值参数还是函数参数，而 Java 是编译型语言，难以区分不同类型的参数，必须设计复杂的接口才能实现匿名函数（主要指 lambda 语法），这连 SQL 程序员都不易掌握。省略数据对象而直接引用字段（比如写成"单价*数量"），可显著简化结构化数据计算，但 Java 缺乏专业的结构化数据对象，目前还无法支持此类表面简单实则巧妙的语法，这就使 Java 代码冗长且不直观（只能写成"x.单价*x.数量"）。

缺少结构化数据计算类库还会导致代码过长，同样的一个分组汇总用 SQL 一句就能写出来改成 Java 就要写几十行，这显然也对简化复杂 SQL 无益。这个问题即使在 Java8 增加了 Stream 后也没有明显改善，为了简化运算用 Java 取代 SQL 基本不可能（有些应用用 Java 做数据处理往往是由于架构上的要求，就其简便性上还远不及 SQL）。

另外，Java 作为编译型语言很难热部署，也就难以及时应对多变的数据计算场景。

Python

直接的 Java 不行，那大热的 Python 怎么样呢？Python+SQL 可比 Java+SQL 容易得多了吧。

的确，Python（主要是 Pandas）提供了丰富结构化计算类库，计算实现要比 Java 简单很多。不过，实际使用 Pandas 处理数据尤其是复杂运算时也会碰到代码很难写的情况。其实，Pandas 本来就不是为结构化数据设计的，它并不是我们熟悉的数据表（一行行数据记录的集合），而是个矩阵。用来处理结构化数据时，做些过滤合并这些简单运算还好点，碰到分组有序等复杂一些运算，就会比较麻烦了，思路上也经常要绕一下。

这还不是 Python 的主要问题，Python 与应用结合的难点在于其集成性上。

Python 很难与 Java 应用集成！

Python 和 Java 集成主要有两种方式。一种是服务式调用，既然 Python 和 Java 是两套体系，那就单独部署通过服务（网络通信）的方式交互，这种方式要维护两套应用比较麻烦，还会存在一些安全认证系统权限等管理问题，而且数据交换的性能也很差，不到万不得已不会采用这种方式。另一种是使用 Jython 这种解释器（JVM 上的 Python），由于采用 Java 开发可以很方便与应用集成，但 Jython 又缺乏足够的计算库（如 Pandas）很难应付那些复杂计算。无论采用何种方式，都会在应用方面产生巨大限制，导致在 Java 应用中用 Python 来实现复杂计算的可行性不高。

除非你把整个应用都用 Python 写，但是 Java 的各种企业级能力又用不上了，做做小应用还行吧。

Scala

Scala 的特性与 Python 类似，都提供了 DataFrame 对象，实现简单的结构化数据计算也比较简单，作为高级语言有序、对象属性化等特性支持良好，相对 Java 在集合运算上也更简单。Scala 运行于 JVM 之上，天生就容易被 JAVA 集成，这些都是 Scala 的优点。

Scala 的缺点在于使用难度较大，难学更难精，用于复杂数据处理与 SQL 相比尤有劣势，也就不用想通过 Scala 简化复杂 SQL 了。这大概也是为什么 Spark 要回归 SQL 的原因了吧。而且，Scala 作为编译型语言同样不支持热部署。

SPL

这些高级语言都存在这样那样的缺点，从简化复杂 SQL 的角度来看无一能胜任。那还有什么办法呢？

其实，从前面的分析中我们明显能够感受到，面向结构化数据计算尤其是复杂计算，现有技术（开发语言）都各有优缺点，SQL 擅长复杂计算但对有序、过程支持不好，Java 恰好反过来支持有序和过程但集合计算能力很弱。如果能综合这些技术的优点，那简化复杂 SQL 的目标也就达成了。

开源 SPL 恰好是这样一个产品。

SPL 全称 Structured Process Language，是一个专门用于结构化数据计算的程序语言。

常规计算能力

一致的数据类型

SPL 相对 Java 提供了更专业的结构化数据类型，即序表。和 SQL 的数据表一样，序表是批量记录组成的集合，具有结构化数据类型的一般功能，可以与 SQL 无缝交互承接 SQL 的返回值。

序表支持所有的结构化计算函数，计算结果也同样是序表，而不是 Map 之类的数据类型。比如对分组汇总的结果，继续进行结构化数据处理。

Orders.groups(year(OrderDate):y; sum(Amount):m).new(y:OrderYear, m*0.2:discount)

丰富的计算类库

在序表的基础上，SPL 提供了丰富的结构化数据计算函数，比如过滤、排序、分组、去重、改名、计算列、关联、子查询、集合计算、有序计算等。这些函数具有强大的计算能力，无须硬编码辅助，就能独立完成计算。

如组合查询：

Orders.select(Amount>1000 && Amount<=3000 && like(Client,"\*bro\*"))

内关联：

join(Orders:o,SellerId ; Employees:e,EId).groups(e.Dept; sum(o.Amount))

SPL 支持简单形式的 Lambda 语法，无须定义函数名和函数体，可以直接用表达式当作函数的参数。修改业务逻辑时，也不用重构函数，只须简单修改表达式。SPL 是解释型语言，使用参数表达式时不必明确定义参数类型，使 Lambda 接口更简单。比如计算平方和，想在 sum 的过程中算平方，可以直观写作：Orders.sum(Amount*Amount)。

同时作为解释执行语言的 SPL 还天然支持动态数据结构，可以根据计算结果结构动态生成新序表，特别适合计算列、分组汇总、关联这类计算。较复杂的计算通常都要拆成多个步骤，每个中间结果的数据结构几乎都不同。SPL 支持动态数据结构，不必先定义这些中间结果的结构。比如，根据某年的客户回款记录表，计算每个月的回款额都在前 10 名的客户。

Sales2021.group(month(sellDate)).(~.groups(Client;sum(Amount):sumValue)).(~.sort(-sumValue)).(~.select(#<=10)).(~.(Client)).isect()

过程控制与 SQL 协同

除了提供与 SQL 相当的集合运算能力，SPL 还对过程控制和分步计算提供了良好支持，灵活的分支判断语句、循环语句，配合专业的结构化数据对象，可以方便地实现各类业务逻辑。比如找出销售额累计占到一半的前 n 个大客户，可以这样分步实现：

<table><tbody><tr class="firstRow"><td width="28"></td><td >A</td></tr><tr><td width="28">1</td><td >=db.query(“select client,sum(amount) amount from sales group by client order by amount desc”)</td></tr><tr><td width="28">2</td><td >=A1. sum(amount)/2</td></tr><tr><td width="28">3</td><td >=0</td></tr><tr><td width="28">4</td><td >=A1.pselect((A3=A3+amount,A3>=A2))</td></tr><tr><td width="28">5</td><td >=A1(to(A4))</td></tr></tbody></table>

通过 SQL 先完成分组汇总并按汇总值降序排序，然后 SPL 承接 SQL 的计算结果再通过分步方式完成后续计算，SPL 与 SQL 有效结合，这样就很大程度达到了简化复杂计算的目标。

在应用中，SPL 很多时候都要与 SQL 交互协同，充分发挥二者的优势，包括数据库读写、事务处理、流程处理、存储过程调用等。

数据库写入（更新/插入）：

db.update@u(A7,sales;ORDERID)

执行 DML 语句：

db.execute("insert into DEPARTMENT(DEPT, MANAGER) values(?,?)","TecSupport",9)

调用存储过程：

db.proc({db_proc(?,?),,:101:"o":table1,1:0:"i": })

丰富的集合运算能力加上过程计算与流程控制（包括指挥 SQL），这样就获得了 SQL 和 Java 相当的能力，而实现上要比 Java 更简单。

超越 SQL 的能力

SPL 不仅覆盖了 SQL 的所有计算能力，还提供了更强大语言功能。基于这些特性可以很方便原来在 SQL 中不易完成的运算，简化复杂计算可不是开玩笑的。

离散性及其支持下的更彻底的集合化

集合化是 SQL 的基本特性，即支持数据以集合的形式参与运算。但 SQL 的离散性很不好，所有集合成员必须作为一个整体参于运算，不能游离在集合之外。而 Java 等高级语言则支持很好的离散性，数组成员可以单独运算。但是，更彻底的集合化需要离散性来支持，集合成员可以游离在集合之外，并与其它数据随意构成新的集合参与运算 。

SPL 兼具了 SQL 的集合化和 Java 的离散性，从而可以实现更彻底的集合化。

SPL 很容易表达“集合的集合”，适合分组后计算。比如，找到各科成绩均在前 10 名的学生：

<table><tbody><tr class="firstRow"><td width="28"></td><td >A</td></tr><tr><td width="28">1</td><td >=db.query(“select * from score”) .group(subject)</td></tr><tr><td width="28">2</td><td >=A2.(~.rank(score).pselect@a(~<=10))</td></tr><tr><td width="28">3</td><td >=A1.(~(A3(#)).(name)).isect()</td></tr></tbody></table>

有序支持

有序计算是离散性和集合化的典型结合产物，成员的次序在集合中才有意义，这要求集合化，有序计算时又要将每个成员与相邻成员区分开，会强调离散性。SPL 兼具集合化和离散性，天然支持有序计算。

具体来说，SPL 可以按绝对位置引用成员，比如，取第 3 条订单可以写成 Orders(3)，取第 1、3、5 条记录可以写成 Orders([1,3,5])。

SPL 也可以按相对位置引用成员，比如，计算每条记录相对于上一条记录的金额增长率：

Orders.derive(amount/amount[-1]-1)

SPL 还可以用 #代表当前记录的序号，比如把员工按序号分成两组，奇数序号一组，偶数序号一组：

Employees.group(#%2==1)

在有序计算的支持下，再处理结构化数据计算中关于次序的运算（诸如比上月、比去年同期、前 20%、排名等）就很方便了。

对象引用

SPL 序表的字段可以存储记录或记录集合，这样可以用对象引用的方式，直观地表达关联关系，即使关系再多，也能直观地表达。比如，根据员工表找到女经理下属的男员工：

Employees.select(gender:"male",department.manager.gender:"female")

更方便的函数语法

提供大量功能强大的结构化数据计算函数本来是一件好事，但这会让相似功能的函数不容易区分，无形中提高了学习难度。

SPL 提供了特有的函数选项语法，功能相似的函数可以共用一个函数名，只用函数选项区分差别。比如 select 函数的基本功能是过滤，如果只过滤出符合条件的第 1 条记录，可以使用选项 @1：

Orders.select@1(Amount>1000)

数据量较大时，用并行计算提高性能，只须改为选项 @m：

Orders.select@m(Amount>1000)

对排序过的数据，用二分法进行快速过滤，可用 @b：

Orders.select@b(Amount>1000)

函数选项还可以组合搭配，比如：

Orders.select@1b(Amount>1000)

结构化运算函数的参数常常很复杂，比如 SQL 就需要用各种关键字把一条语句的参数分隔成多个组，但这会动用很多关键字，也使语句结构不统一。SPL 支持层次参数，通过分号、逗号、冒号自高而低将参数分为三层，用通用的方式简化复杂参数的表达：

join(Orders:o,SellerId ; Employees:e,EId)

方便的编辑调试功能

与 SQL 难用的编辑调试功能不同，SPL 提供了简洁易用的开发环境，单步执行、设置断点，所见即所得的结果预览窗口…，使得开发调试效率更高。

SPL 采用了网格式编码方式，代码不仅天然对齐层次清晰，在实施过程计算时可以直接使用格子名称来引用上一步的计算结果而不必费心定义变量（虽然也支持）非常方便。好用的开发环境自然起到事半功倍的效果，进一步简化复杂计算实施难度。

应用集成、低耦合与热切换

SPL 提供了标准应用接口（JDBC/ODBC/RESTful）可以很方便与应用集成。尤其对于 Java 应用可以将 SPL 作为嵌入引擎集成，使得应用本身就具备强数据计算能力。

JDBC 调用 SPL 代码示例：

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

同时，SPL 采用解释执行机制，天然支持热切换。这样对于稳定性差、经常需要新增修改的数据处理需求非常友好。SPL 脚本可以与 Java 程序独立，外置在 Java 之外，修改和维护都可以独立进行，脚本修改上传后就能实时生效，保证应用可以不中断地提供数据服务。

SPL 外置算法不仅能有效降低应用与数据库的耦合性，独立的 SPL 模块还可以进一步降低应用各个模块间的耦合性，使得结构更为清晰，架构也更为合理。

总结一下，SPL 之所以更简单是因为 SPL 相当于结合了 SQL 和其他高级语言（如 Java）的优点，并在此基础上增加了诸多特性让复杂计算不再难写，同时可以与应用完美结合使得应用架构更为合理。有了 SPL 的帮助，我们相信，未来的某一天上千行的复杂 SQL 将不复存在。

SPL 资料

• SPL下载
  

• SPL源代码