SQL 的后计算脚本
SQL 的后计算脚本用于实现 SQL 不适合的某些复杂运算，本文从此类工具中精心挑选了三种，从开发效率、语法表达能力、结构化函数库等方面进行深度对比，考察了各脚本在集合计算、有序计算等重点运算上的表现，集算器 SPL 在这几款工具中的表现最为出色。点击SQL 的后计算脚本了解详情。
大多数情况下，我们用SQL（存储过程）就可以完成数据库计算，但如果遇到SQL不擅长的某些复杂运算，就只能用其他程序语言把数据读出库外，然后在数据库外完成计算，这样的程序语言经常是以简单脚本的形式出现，我们在这里称为SQL的后计算脚本。
SQL不擅长的运算主要包括复杂的集合计算、有序计算、关联计算、多步骤计算等。SQL集合化不够彻底，没有显式的集合数据类型，导致计算过程中产生的集合难以复用，比如分组后必须强制汇总，而基于分组后的子集无法再计算；SQL基于无序集合理论设计，处理跨行组及排名等有序运算非常麻烦，经常用JOIN或子查询临时生成序号，不仅难写而且运算效率很低。SQL还不支持记录的引用，只能用子查询或JOIN语句描述关联关系，一旦遇到层级较多或自关联的情况，代码就会异常复杂；SQL本身也不提倡多步骤代码，经常迫使程序员写出嵌套很多层的长语句，虽然用存储过程可以一定程度解决这个问题，但有时实际环境不允许我们使用存储过程，比如DBA严格控制存储过程的权限、旧数据库和小型数据库不支持存储过程等，而且存储过程的调试也很不方便，并不是很适合写出有过程的计算。
除了上述复杂运算，还有一些情况也会用到SQL的后计算脚本。比如，计算逻辑需要在不同种类的数据库间迁移，涉及到非关系数据库；输入源或输出目标不止数据库，而是Excel、文本等文件；还可能在多个数据库之间进行混合计算。这些都会涉及库外计算，用到SQL的后计算脚本。
对SQL的后计算脚本而言，最重要的功能当然还是实现SQL不擅长的那些复杂运算。除此之外，最好还能具备一些更高级的特性，比如计算文件、非关系数据库等多样性数据、能处理较大的数据量、运算性能 不能太慢等。当然，最基本的是要方便地支持读写数据库，这样才能实现SQL的后计算。
常见的用于SQL后计算脚本有JAVA、Python pandas、esProc SPL，下面就让我们深入了解这些脚本，看看它们进行SQL后计算时的能力差异。
JAVA
C++、JAVA等高级语言理论上无所不能，自然也能实现SQL不擅长的运算。JAVA支持泛型，集合化比较彻底，可以实现复杂的集合运算。JAVA的数组本来就有序号，可以实现有序运算。JAVA支持对象引用，可用引用来表示关系，关联运算也没什么问题。JAVA支持分支、循环等过程性语法，可轻松实现多步骤复杂运算。
但是，JAVA缺乏结构化类库，连最简单的结构化计算都必须硬编码实现，最基本的结构化数据类型也要手工建立，这会导致代码冗长繁琐。
举个有序计算的例子：求某支股票最长连续上涨天数。库表AAPL存储某支股票的股价信息，主要字段有交易日期、收盘价，请计算该股票最长的连续上涨天数。
按自然思路实现这个任务：对日期有序的股票记录进行循环，如果本条记录与上一条记录相比是上涨的，则将连续上涨天数（初始为0）加1，如果是下跌的，则将连续上涨天数和当前最大连续上涨天数（初始为0）相比，选出新的当前最大连续上涨天数，再将连续上涨天数清0。如此循环直到结束，当前最大连续上涨天数即最终的最大连续上涨天数。
SQL不擅长有序计算，无法用上述自然思路实现，只能用一些古怪难懂的技巧：把按日期有序的股票记录分成若干组，连续上涨的记录分成同一组，也就是说，某天的股价比上一天是上涨的，则和上一天记录分到同一组，如果下跌了，则开始一个新组。最后看所有分组中最大的成员数量，也就是最多连续上涨的天数。
具体SQL如下：
select max(continue_inc_days) from (select count(*) continue_inc_days from (select sum(inc_de_flag) over(order by transDate) continue_de_days from (select transDate, case when price>LAG(price) over(order by transDate) then 0 else 1 end inc_de_flag from AAPL) ) group by continue_de_days)
|
这段SQL并不算很长，但嵌套了四层，所用技巧古怪难懂，一般人很难想出这样的代码。
用JAVA实现时，就可以回归自然思路：
package stock;
import java.sql.*;
public class APP { public static void main(String[] args) throws SQLException, ClassNotFoundException { Connection con = null; Class.forName("com.mysql.cj.jdbc.Driver"); con = DriverManager .getConnection( "jdbc:mysql://127.0.0.1:3306/mysql?&useSSL=false&serverTimezone=UTC", "root", ""); String dql = "select * from AAPL order by transDate"; PreparedStatement stmt = con.prepareStatement(dql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet aapl = stmt.executeQuery(); int continue_inc_days = 0; int max_continue_inc_days = 0; float last_Price = 0; while (aapl.next()) { float price = aapl.getFloat("price"); if (price >= last_Price) { continue_inc_days++; } else { if (continue_inc_days >= max_continue_inc_days) { max_continue_inc_days = continue_inc_days; } continue_inc_days = 0; } last_Price = price; } System.out.println("max_continue_inc_days=" + max_continue_inc_days); if (con != null) con.close(); } } |
后面那段代码就是前面讲述的思路，只要一层循环就可以完成了。
然而，我们也发现，Java写出的这段代码，虽然思路简单，难度不大，但显然代码很冗长。
这个问题的复杂度并不高，还没涉及到常见的分组、连接等结构化数据计算，否则代码量将更为惊人，限于篇幅，就不再用JAVA举例了。
在多样性数据、优化性能、处理大数据等高级功能方面，JAVA的特点同样是“能实现，但太繁琐”，这里也不再赘述。
JAVA是个优秀的企业级通用语言，但通用的另一层意思往往是不专业，换句话说，JAVA缺乏专业的结构化计算类库，代码冗长繁琐，算不上理想的SQL后计算脚本。
Python pandas
Python有简捷的语法，还拥有众多的第三方函数库，其中就有服务于结构化计算的Pandas。也正因为如此，Pandas常被用作SQL的后计算脚本。
作为结构化计算函数库，Pandas简化SQL复杂运算的能力要比JAVA强很多。
比如，同样的有序运算 “求最长连续上涨天数”，Pandas代码是这样的：
import pymysql import pandas as pd conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "root", password = "", database = "mysql", ) aapl = pd.read_sql_query("select price from AAPL order by transDate", conn) continue_inc_days=0 ; max_continue_inc_days=0 for i in aapl['price'].shift(0)>aapl['price'].shift(1): continue_inc_days =0 if i==False else continue_inc_days +1 max_continue_inc_days = continue_inc_days if max_continue_inc_days < continue_inc_days else max_continue_inc_days print(max_continue_inc_days) conn.close() |
上述代码中，Pandas提供了用于结构化计算的数据结构dataFrame，这种数据结构天然带序号，在有序运算中可以简化代码，比JAVA更容易进行跨行取数。此外，Pandas对SQL取数的封装也很紧凑，比JAVA代码更加简短。
再比如集合计算例子：一行拆分为多行。库表tb有2个字段，其中ANOMALIES存储以空格为分隔符的字符串，需将ANOMALIES按空格拆分，使每个ID字段对应一个成员。
处理前的数据
ID |
ANOMALIES |
1 |
A1 B1 C1 D1 |
2 |
A2 |
3 |
A3 B3 C3 |
4 |
A3 B4 D4 |
… |
… |
处理后的数据：
ID |
ANOMALIES |
1 |
A1 |
1 |
B1 |
1 |
C1 |
1 |
D1 |
2 |
A2 |
… |
… |
Pandas核心代码如下（省略数据库输入输出，下同）：
… split_dict = pd.read_sql_query("select * from tb", conn) split_list = [] for key,value in split_dict.items(): anomalies = value[0].split(' ') key_array = np.tile(key,len(anomalies)) split_df = pd.DataFrame(np.array([key_array,anomalies]).T,columns=['ID','ANOMALIES']) split_list.append(split_df) df = pd.concat(split_list,ignore_index=True) |
上述代码中，Pandas用集合函数将字符串直接拆分为dataFrame，再用集合函数将多个dataFrame直接合并，代码非常简练。JAVA虽然可以实现类似的功能，但都要手工实现，代码要繁琐得多。
作为结构化计算函数库，Pandas代码的确比JAVA简练，但这仅限于复杂度有限的情况下，如果复杂度进一步提高，Pandas代码也会变得冗长难懂。
比如这个涉及集合计算+有序计算的例子：连续值班情况。库表duty记录着每日值班情况，一个人通常会持续值班几个工作日，之后再换人，现在请根据duty依次计算出每个人连续的值班情况。数据结构示意如下：
处理前（duty）
Date |
Name |
2018-03-01 |
Emily |
2018-03-02 |
Emily |
2018-03-04 |
Emily |
2018-03-04 |
Johnson |
2018-04-05 |
Ashley |
2018-03-06 |
Emily |
2018-03-07 |
Emily |
… |
… |
处理后
Name |
Begin |
End |
Emily |
2018-03-01 |
2018-03-03 |
Johnson |
2018-03-04 |
2018-03-04 |
Ashley |
2018-03-05 |
2018-03-05 |
Emily |
2018-03-06 |
2018-03-07 |
… |
… |
… |
核心的pandas代码如下：
…… duty = pd.read_sql_query("select date,name from duty order by date", conn) name_rec = '' start = 0 duty_list = [] for i in range(len(duty)): if name_rec == '': name_rec = duty['name'][i] if name_rec != duty['name'][i]: begin = duty['date'].loc[start:i-1].values[0] end = duty['date'].loc[start:i-1].values[-1] duty_list.append([name_rec,begin,end]) start = i name_rec = duty['name'][i] begin = duty['date'].loc[start:i].values[0] end = duty['date'].loc[start:i].values[-1] duty_list.append([name_rec,begin,end]) duty_b_e = pd.DataFrame(duty_list,columns=['name','begin','end']) |
上面已经省略了数据库输出输出的过程，可以看到代码还是有点繁琐。
再比如集合计算+多步骤运算的例子：计算分期贷款明细。库表loan记录着贷款信息，包括贷款 ID，贷款总额、期数、年利率，示意如下：
LoanID |
LoanAmt |
Term |
Rate |
L01 |
100000 |
5 |
4.8 |
L02 |
20000 |
2 |
5.0 |
L03 |
500000 |
12 |
4.5 |
需要计算出各期明细，包括：当期还款额、当期利息、当期本金、剩余本金。计算结果如下：
LoanID |
LoanAmt |
Payment |
Term |
Rate |
interest |
princepal |
princeplebalance |
L01 |
100000 |
20238.13 |
5 |
4.75 |
395.83 |
19842.29 |
80159.71 |
L01 |
100000 |
20238.13 |
5 |
4.75 |
317.29 |
19920.83 |
60236.87 |
L01 |
100000 |
20238.13 |
5 |
4.75 |
238.44 |
19999.69 |
40237.18 |
L01 |
100000 |
20238.13 |
5 |
4.75 |
159.27 |
20078.85 |
20158.33 |
… |
实现上述运算的Pandas核心代码如下：
loan_data = pd.read_sql_query("select loanID,LoanAmt,Term,Rate from loan", conn) loan_data['mrate'] = loan_data['Rate']/(100*12) loan_data['mpayment'] = loan_data['LoanAmt']*loan_data['mrate']*np.power(1+loan_data['mrate'],loan_data['Term']) \ /(np.power(1+loan_data['mrate'],loan_data['Term'])-1) loan_term_list = [] for i in range(len(loan_data)): loanid = np.tile(loan_data.loc[i]['LoanID'],loan_data.loc[i]['Term']) loanamt = np.tile(loan_data.loc[i]['LoanAmt'],loan_data.loc[i]['Term']) term = np.tile(loan_data.loc[i]['Term'],loan_data.loc[i]['Term']) rate = np.tile(loan_data.loc[i]['Rate'],loan_data.loc[i]['Term']) payment = np.tile(np.array(loan_data.loc[i]['mpayment']),loan_data.loc[i]['Term']) interest = np.zeros(len(loanamt)) principal = np.zeros(len(loanamt)) principalbalance = np.zeros(len(loanamt)) loan_amt = loanamt[0] for j in range(len(loanamt)): interest[j] = loan_amt*loan_data.loc[i]['mrate'] principal[j] = payment[j] - interest[j] principalbalance[j] = loan_amt - principal[j] loan_amt = principalbalance[j] loan_data_df = pd.DataFrame(np.transpose(np.array([loanid,loanamt,term,rate,payment,interest,principal,principalbalance])), columns = ['loanid','loanamt','term','rate','payment','interest','principal','principalbalance']) loan_term_list.append(loan_data_df) loan_term_pay = pd.concat(loan_term_list,ignore_index=True) print(loan_term_pay) |
可以看到，在简化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虽然是结构化计算函数库，但仍不够好用。
集算器 SPL
与Pandas类似，集算器 SPL也具有丰富的结构化计算函数，与Pandas不同的是，集算器 SPL是由商业公司支持的开源产品，是专业的结构化计算语言，不是开源社区的第三方库函数，也不存在一个松散的上级组织。集算器 SPL可以从全局角度设计一致的结构化计算语法，可以自底向上设计统一的结构化数据类型，使函数之间以最大的灵活度搭配组合，从而快捷方便地解决SQL后计算中遇到的问题。
作为专业的结构化计算语言，SPL擅长简化SQL复杂运算，比如，求最长连续上涨天数，实现前面说过的自然思路，SPL只需短短2行：
A |
|
1 |
=mysqlDB.query@x("select price from AAPL order by transDate") |
2 |
=a=0,A1.max(a=if(price>price[-1],a+1,0)) |
上述代码使用了序表和循环函数，序表是专用于结构化计算的数据结构，可以比Pandas更容易进行跨行取数，可以更方便地实现有序计算，循环函数可以避免大部分的for语句（复杂情况下还是应该用for），可以大幅简化代码。此外，SPL对SQL取数的封装更紧凑，比Pandas代码更加简短。
再比如一行拆分为多行，SPL代码依然简短：
A |
|
1 |
=orcl.query@x("select * from tb") |
2 |
=A1.news(ANOMALIES.split(" ");ID,~: ANOMALIES) |
即使需求复杂度进一步提高，SPL仍然可以轻松实现。
比如连续值班情况，SPL代码要比Pandas简短很多：
A |
|
1 |
=orcl.query("select date,name from duty order by date") |
2 |
=A1.group@o(name) |
3 |
=A2.new(name,~.m(1).date:begin,~.m(-1).date:end) |
再比如计算分期贷款明细，SPL同样比Pandas简短：
A |
|
1 |
=orcl.query@x("select loanID,LoanAmt,Term,Rate from loan") |
2 |
=A1.derive(Rate/100/12:mRate,LoanAmt*mRate*power((1+mRate),Term)/(power((1+mRate),Term)-1):mPayment) |
3 |
=A2.news((t=LoanAmt,Term);LoanID, LoanAmt, mPayment:payment, Term, Rate, t* mRate:interest, payment-interest:principal, t=t-principal:principlebalance) |
对于Pandas很难实现的复杂运算，SPL通常也能轻松实现，而且代码不难。比如涉及多步骤算法+集合运算+动态表结构的任务：将子表横向插入子表。
源表关系
Order（主表） |
OrderDetail（子表） |
|
ID(pk) |
ß- |
OrderID(PK) |
Customer |
Number(pk) |
|
Date |
Product |
|
Amount |
目标结果
ID |
Customer |
Date |
Product1 |
Amount1 |
Product2 |
Amount2 |
Product3 |
Amount3 |
1 |
3 |
2019-01-01 |
Apple |
5 |
Milk |
3 |
Salt |
1 |
2 |
5 |
2019-01-02 |
Beef |
2 |
Pork |
4 |
||
3 |
2 |
2019-01-02 |
Pizza |
3 |
SPL可以大幅简化这段代码：
A |
B |
|
1 |
=orcl.query@x("select * from OrderDetail left join Order on Order.ID=OrderDetail.OrderID") |
|
2 |
=A1.group(ID) |
=A2.max(~.count()).("Product"+string(~)+","+"Amount"+string(~)).concat@c() |
3 |
=create(ID,Customer,Date,${B2}) |
>A2.run(A3.record([ID,Customer,Date]|~.([Product,Amount]).conj())) |
作为专业的结构化计算语言，集算器 SPL不仅可以大幅简化SQL不擅长的复杂运算，还具备更高级的能力去解决一些特殊情况。
在多样性数据方面，SPL支持多种文件格式和复杂的数据源，比如Hadoop、MongoDB等。更进一步，只需使用相同的代码，数据分析师就能计算来源各异的数据，既包括数据库，也包括非数据库。
在大数据量方面，SPL从底层提供了游标机制，对上层隐藏了内外存交换细节，允许数据分析师用类似处理小数据量的语法，直观地处理较大的数据量。
比如，库表orders记录着电商的大量订单，全部读出会超出内存，现在需要在库外计算每个销售员销售额最大的3笔订单。SPL代码如下：
A |
|
1 |
=my.cursor@x("select sellerid,amount from orders") |
2 |
=A1.groups(sellerid;top(3; -amount):top3) |
3 |
=A2.conj(top3) |
SPL也提供了很多简单易用的方法进行性能优化。比如：orders表每月的数据大致相等，请按月份进行多线程并行查询，从而大幅提高查询性能。SPL只需如下代码：
A |
B |
|
1 |
fork to(1,12) |
/12线程并行 |
2 |
=connect(“my”) |
|
3 |
=B2.query@x(“select * from orders where month(orderdate)=?”,A1) |
|
4 |
=A1.conj() |
/ 合并查询结果 |
经过前面的比较我们可以发现，集算器 SPL具备丰富的结构化函数，是专业的结构化计算语言，可以大幅简化SQL不擅长的复杂运算逻辑，是更加理想的SQL后计算脚本。
