SQL 的后计算脚本

【摘要】

SQL 的后计算脚本用于实现 SQL 不适合的某些复杂运算，本文从此类工具中精心挑选了三种，从开发效率、语法表达能力、结构化函数库等方面进行深度对比，考察了各脚本在集合计算、有序计算等重点运算上的表现，esProc 在这几款工具中的表现最为出色。点击SQL 的后计算脚本了解详情。

大多数情况下，我们用 SQL（存储过程）就可以完成数据库计算，但如果遇到 SQL 不擅长的某些复杂运算，就只能用其他程序语言把数据读出库外，然后在数据库外完成计算，这样的程序语言经常是以简单脚本的形式出现，我们在这里称为 SQL 的后计算脚本。

SQL 不擅长的运算主要包括复杂的集合计算、有序计算、关联计算、多步骤计算等。SQL 集合化不够彻底，没有显式的集合数据类型，导致计算过程中产生的集合难以复用，比如分组后必须强制汇总，而基于分组后的子集无法再计算；SQL 基于无序集合理论设计，处理跨行组及排名等有序运算非常麻烦，经常用 JOIN 或子查询临时生成序号，不仅难写而且运算效率很低。SQL 还不支持记录的引用，只能用子查询或 JOIN 语句描述关联关系，一旦遇到层级较多或自关联的情况，代码就会异常复杂；SQL 本身也不提倡多步骤代码，经常迫使程序员写出嵌套很多层的长语句，虽然用存储过程可以一定程度解决这个问题，但有时实际环境不允许我们使用存储过程，比如 DBA 严格控制存储过程的权限、旧数据库和小型数据库不支持存储过程等，而且存储过程的调试也很不方便，并不是很适合写出有过程的计算。

除了上述复杂运算，还有一些情况也会用到 SQL 的后计算脚本。比如，计算逻辑需要在不同种类的数据库间迁移，涉及到非关系数据库；输入源或输出目标不止数据库，而是 Excel、文本等文件；还可能在多个数据库之间进行混合计算。这些都会涉及库外计算，用到 SQL 的后计算脚本。

对 SQL 的后计算脚本而言，最重要的功能当然还是实现 SQL 不擅长的那些复杂运算。除此之外，最好还能具备一些更高级的特性，比如计算文件、非关系数据库等多样性数据、能处理较大的数据量、运算性能不能太慢等。当然，最基本的是要方便地支持读写数据库，这样才能实现 SQL 的后计算。

常见的用于 SQL 后计算脚本有 JAVA、Python pandas、esProc，下面就让我们深入了解这些脚本，看看它们进行 SQL 后计算时的能力差异。

JAVA

C++、JAVA 等高级语言理论上无所不能，自然也能实现 SQL 不擅长的运算。JAVA 支持泛型，集合化比较彻底，可以实现复杂的集合运算。JAVA 的数组本来就有序号，可以实现有序运算。JAVA 支持对象引用，可用引用来表示关系，关联运算也没什么问题。JAVA 支持分支、循环等过程性语法，可轻松实现多步骤复杂运算。

但是，JAVA 缺乏结构化类库，连最简单的结构化计算都必须硬编码实现，最基本的结构化数据类型也要手工建立，这会导致代码冗长繁琐。

举个有序计算的例子：求某支股票最长连续上涨天数。库表 AAPL 存储某支股票的股价信息，主要字段有交易日期、收盘价，请计算该股票最长的连续上涨天数。

按自然思路实现这个任务：对日期有序的股票记录进行循环，如果本条记录与上一条记录相比是上涨的，则将连续上涨天数（初始为 0）加 1，如果是下跌的，则将连续上涨天数和当前最大连续上涨天数（初始为 0）相比，选出新的当前最大连续上涨天数，再将连续上涨天数清 0。如此循环直到结束，当前最大连续上涨天数即最终的最大连续上涨天数。

SQL 不擅长有序计算，无法用上述自然思路实现，只能用一些古怪难懂的技巧：把按日期有序的股票记录分成若干组，连续上涨的记录分成同一组，也就是说，某天的股价比上一天是上涨的，则和上一天记录分到同一组，如果下跌了，则开始一个新组。最后看所有分组中最大的成员数量，也就是最多连续上涨的天数。

具体 SQL 如下：

这段 SQL 并不算很长，但嵌套了四层，所用技巧古怪难懂，一般人很难想出这样的代码。

用 JAVA 实现时，就可以回归自然思路：

后面那段代码就是前面讲述的思路，只要一层循环就可以完成了。

然而，我们也发现，Java 写出的这段代码，虽然思路简单，难度不大，但显然代码很冗长。

这个问题的复杂度并不高，还没涉及到常见的分组、连接等结构化数据计算，否则代码量将更为惊人，限于篇幅，就不再用 JAVA 举例了。

在多样性数据、优化性能、处理大数据等高级功能方面，JAVA 的特点同样是“能实现，但太繁琐”，这里也不再赘述。

JAVA 是个优秀的企业级通用语言，但通用的另一层意思往往是不专业，换句话说，JAVA 缺乏专业的结构化计算类库，代码冗长繁琐，算不上理想的 SQL 后计算脚本。

Python pandas

Python 有简捷的语法，还拥有众多的第三方函数库，其中就有服务于结构化计算的 Pandas。也正因为如此，Pandas 常被用作 SQL 的后计算脚本。

作为结构化计算函数库，Pandas 简化 SQL 复杂运算的能力要比 JAVA 强很多。

比如，同样的有序运算 “求最长连续上涨天数”，Pandas 代码是这样的：

上述代码中，Pandas 提供了用于结构化计算的数据结构 dataFrame，这种数据结构天然带序号，在有序运算中可以简化代码，比 JAVA 更容易进行跨行取数。此外，Pandas 对 SQL 取数的封装也很紧凑，比 JAVA 代码更加简短。

再比如集合计算例子：一行拆分为多行。库表 tb 有 2 个字段，其中 ANOMALIES 存储以空格为分隔符的字符串，需将 ANOMALIES 按空格拆分，使每个 ID 字段对应一个成员。

处理前的数据

处理后的数据：

Pandas 核心代码如下（省略数据库输入输出，下同）：

上述代码中，Pandas 用集合函数将字符串直接拆分为 dataFrame，再用集合函数将多个 dataFrame 直接合并，代码非常简练。JAVA 虽然可以实现类似的功能，但都要手工实现，代码要繁琐得多。

作为结构化计算函数库，Pandas 代码的确比 JAVA 简练，但这仅限于复杂度有限的情况下，如果复杂度进一步提高，Pandas 代码也会变得冗长难懂。

比如这个涉及集合计算 + 有序计算的例子：连续值班情况。库表 duty 记录着每日值班情况，一个人通常会持续值班几个工作日，之后再换人，现在请根据 duty 依次计算出每个人连续的值班情况。数据结构示意如下：

处理前（duty）

处理后

核心的 pandas 代码如下：

上面已经省略了数据库输出输出的过程，可以看到代码还是有点繁琐。

再比如集合计算 + 多步骤运算的例子：计算分期贷款明细。库表 loan 记录着贷款信息，包括贷款 ID，贷款总额、期数、年利率，示意如下：

需要计算出各期明细，包括：当期还款额、当期利息、当期本金、剩余本金。计算结果如下：

实现上述运算的 Pandas 核心代码如下：

可以看到，在简化 SQL 复杂运算方面 Python 虽然比 JAVA 强很多，但只限于简单情况，如果需求再复杂些，代码也会变得冗长难懂。之所以出现这种现象，可能因为 Pandas 只是第三方函数库，不能得到 Python 从语法层面的底层支撑，设计的专业性也不足。

Pandas 的专业性不足，还体现在多样性数据上。Pandas 没有为各类数据源开发统一接口，只支持常见的本地文件，但不支持复杂的数据源，比如 Hadoop、MongoDB，用户还要自己寻找第三方（实际是第四方）函数库，并编写复杂的访问代码。Pandas 甚至没有统一数据库接口，比如 MySQL 就有好几种第三方函数库，常见的有 PyMySQL、sqlalchemy、MySQLdb。不过，这个问题对于大多数桌面应用场景还不严重，常见的数据库 Python 基本上都能简单地支持。

对于多源混合关联问题，只要能读出各种数据源的数据，基本上也就能实现了，Pandas 在这方面的表现基本令人满意。不过，还是上面的说法，对于简单的混合关联关系，Pandas 都容易实现，而一旦出现较复杂的关联运算，实现过程就会变得困难起来。

在大数据量方面，Pandas 的表现就不尽如人意了。Pandas 没有游标数据类型，这导致解决较大数据量的计算时，必须硬编码实现循环取数，而不能自动进行内外存交换，代码因此异常繁琐。详情可参考《How Python Handles Big Files》

Pandas 的运算性能也一般，但基本够用。令人经常诟病的主要是多线程并行，Python 下很难实现此类运算。比如数据库 IO 一般都较慢，但可以在数据库不忙时使用并行取数的办法来提高取数性能。而 Python 要借助其他第三方函数库才能实现并行，代码异常繁琐，且在表达效率、执行效率、稳定性等方便均缺乏保障。

Pandas 虽然是结构化计算函数库，但仍不够好用。

esProc

与 Pandas 类似，esProc 也具有丰富的结构化计算函数，与 Pandas 不同的是， esProc 是由商业公司支持的产品，是专业的结构化计算语言，而不是开源社区的第三方库函数，也不存在一个松散的上级组织。esProc 可以从全局角度设计一致的结构化计算语法，可以自底向上设计统一的结构化数据类型，使函数之间以最大的灵活度搭配组合，从而快捷方便地解决 SQL 后计算中遇到的问题。

作为专业的结构化计算语言，esProc 擅长简化 SQL 复杂运算，比如，求最长连续上涨天数，实现前面说过的自然思路，esProc 只需短短 2 行：

上述代码使用了序表和循环函数，序表是专用于结构化计算的数据结构，可以比 Pandas 更容易进行跨行取数，可以更方便地实现有序计算，循环函数可以避免大部分的 for 语句（复杂情况下还是应该用 for），可以大幅简化代码。此外，esProc 对 SQL 取数的封装更紧凑，比 Pandas 代码更加简短。

再比如一行拆分为多行，esProc 代码依然简短：