强大的SQL计算利器-SPL

迈不过友情╰ 2023-09-28 11:16 1阅读 0赞

现代应用开发中，通常只用SQL实现简单的数据存取动作，而主要的计算过程和业务逻辑直接在应用程序中实现，主要原因在于：

*  过于复杂的SQL很难调试、编写、阅读、修改。
 *  SQL有方言特征，大量使用SQL后，会导致程序很难移植。
 *  架构方面要求业务逻辑在应用中实现，而不能依赖于数据库，否则耦合性过高。
 *  有些计算SQL不擅长，包括复杂的集合计算、有序计算、关联计算、多步骤计算，经常也需要移到数据库外实现。
 *  实现流程控制时，因为更难移植、耦合性更高、影响数据安全，不方便使用存储过程。

此外，还有涉及多数据库和非数据库的场景，也无法使用SQL完成计算任务，只能在外部完成。

这样，就要在应用程序中实现SQL后计算任务。

SQL返回的数据一般都是结构化数据，那么好的SQL后计算技术也要有方便的结构化数据对象，能够进一步计算和处理返回的数据；提供丰富的库函数，拥有不亚于SQL的计算能力；最好还能支持循环和判断语法以实现流程控制。特别地，SQL后计算技术要用在应用程序中，要易于被集成。

Java是重要的开发语言，但JDK提供的方法过于基础，虽然能实现SQL后计算，但开发效率很低。

ORM是Java中用来实现SQL后计算的常见方案。但几种较流行的ORM都缺乏专业的结构化数据对象，不支持动态数据结构。虽然可以利用Java实现流程控制，但难以进行灵活的计算。这些ORM技术的计算能力还远不如SQL，提供的计算函数非常有限，用Java硬写的现象仍然非常普遍。

Stream的链式编程比ORM的HQL更加面向对象，且有Lambda语法的加成，也常被用于SQL后计算，有些ORM还能直接生成Stream对象。但Stream同样没有专业的结构化数据对象，不支持动态数据结构。此外，Stream的计算能力也较差，甚至不如ORM，即使排序、分组汇总、关联这样的基础计算，也要辅以大量编码。

Kotlin基于JVM，且在链式编程和Lambda语法上对Stream进行了一系列改进，也可以用于SQL后计算。但因为编译型语言的底层，Kotlin只能对Stream小幅微调，重大缺点一个没少。

Python Pandas有较强大的结构化数据处理能力，有时也可以用于SQL后计算，但因为缺乏易用的接口，很难被Java集成，很少出现在正式项目中。

esProc SPL是更好的SQL后计算技术。

## 专业的结构化数据对象 ##

SPL是JVM下的开源结构化数据计算引擎，内置专业的结构化数据对象序表，可以和数据库表/记录方便地互转，支持动态数据结构，提供了灵活易用的访问方法、维护方法、计算函数。序表专业性强，为数据计算和流程控制提供了有力的底层支撑，可以方便地实现SQL后计算中的各类业务逻辑。  
![在这里插入图片描述][c8eead4f288e459baf3420746cef8a76.png]

**直接的数据库交换方法**，可以在数据库表（SQL结果集）和SPL序表之间进行互转。

比如，使用query函数执行SQL，生成单条记录序表：

<table> 
 <tbody> 
  <tr> 
   <td> </td> 
   <td>A</td> 
   <td>B</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>=connect("orcl")</td> 
   <td>//连接数据库</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>.query("select * from sales where orderid=?",201)=r=A1</td> 
   <td>//查询单条记录</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>=db.close()</td> 
   <td>//关闭数据库连接</td> 
  </tr> 
 </tbody> 
</table>

如果SQL返回多条记录，则自动生成多条记录序表：

=T=A1.query(“select \* from salesR where SellerID=?”,10)

反过来也简单，用update函数就可以将序表记录批量地持久化到数据库。比如，原序表为T，经过多条件记录的增删改之后的序表为NT，将两者的变更结果统一写入数据库：

=A1.update(NT:T,sales;ORDERID)

**灵活的序表访问方法**，可以按字段名或记录号自由地访问序表。

取序表的第3条记录：T(3)

取后3条记录:T.m(\[-1,-2,-3\])

取记录的字段值：T(3).Amount\*0.05

取一列，返回集合：T.(Amount)

取几列，返回集合的集合：T.(\[CLIENT,AMOUNT\])

先按字段名取再按记录序号取：T.(AMOUNT)(3)

先按记录序号取再按字段名取：T(3).AMOUNT

**易用的序表维护方法**，可以对单条或多条记录记录进行统一的增删改操作。

追加记录：T.insert(200,“APPL”,10,2400.4,date(“2010-10-10”))

修改记录：T(3).Amount = T(3). Amount\*1.05

删除记录：T.delete(T.select(ORDERID==209 || ORDERID==208))

**丰富的序表计算函数**，可进行完整的SQL式计算。

过滤：T.select(Amount>1000 && Amount<=3000 && like(Client,“*bro*”))

排序：T.sort(-Client,Amount)

去重：T.id(Client)

汇总：T.max(Amount)

分组汇总后过滤： T.groups(year(OrderDate),Client; avg(Amount):amt).select(amt>2000)

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

交集：T1.id(Client) ^ T2.id(Client)

TopN：T.top(-3;Amount)

分组topN：T.groups(Client;top(3,Amount))

**支持动态数据结构**，可根据上一步的计算结果推断出新数据结构，并自动生成新序表，新序表可直接进行计算。比如先分组汇总，再过滤，最后排序：

T.groups(SellerId, Client; sum(Amount):amt, count(1):cnt).select(amt>10000 && amt<=30000 && like(Client,“\*bro\*”)).sort(amt)

使用支持动态数据结构的序表，开发者可以更加关注计算本身，而不是思考如何事先定义结果集。这样的编码风格不仅简短易懂，而且更符合自然思维，开发效率可以显著提升。在多步骤的复杂业务逻辑中，动态数据结构带来的优势更加明显。

## 强大的结构化数据计算能力 ##

SPL提供了多种方便易用的语法，内置大量功能强大的函数，可以简化复杂的有序运算、集合运算、分布计算、关联计算。很多用SQL和存储过程难以表达的计算，用SPL都可以轻松实现。

**函数选项、层次参数等方便的语法**，功能相似的函数可以共用一个函数名，只用函数选项区分差别，比SQL更加灵活方便。比如select函数的基本功能是过滤，如果只过滤出符合条件的第1条记录，可使用选项@1：

T.select@1(Amount>1000)

并行过滤，适合数据量较大的情况，使用选项@m：

T.select@m(Amount>1000)

二分法排序，即对有序数据用二分法进行快速过滤，使用@b：

T.select@b(Amount>1000)

有序分组，即对分组字段有序的数据，将相邻且字段值相同的记录分为一组，使用@b：

T.groups@b(Client;sum(Amount))

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

Orders.select@1b(Amount>1000)

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

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

**内置大量日期函数和字符串函数**，在数量和功能上远远超过其他技术甚至SQL，同样的运算代码量更短。比如：

时间类函数，日期增减：elapse(“2020-02-27”,5) //返回2020-03-03

星期几：day@w(“2020-02-27”) //返回5，即星期4

N个工作日之后的日期：workday(date(“2022-01-01”),25) //返回2022-02-04

字符串类函数，判断是否全为数字：isdigit(“12345”) //返回true

取子串前面的字符串：substr@l(“abCDcdef”,“cd”) //返回abCD

按竖线拆成字符串数组：“aa|bb|cc”.split(“|”) //返回\[“aa”,“bb”,“cc”\]

SPL还支持年份增减、求年中第几天、求季度、按正则表达式拆分字符串、拆出SQL的where或select部分、拆出单词、按标记拆HTML等功能。

**简化复杂的有序运算**。涉及跨行的有序运算，通常都有一定的难度，比如比上期和同期比。SPL使用"字段\[相对位置\]"引用跨行的数据，可显著简化代码，还可以自动处理数组越界等特殊情况，经常比SQL更方便。比如，追加一个计算列rate，计算每条订单的金额增长率：

=T.derive(AMOUNT/AMOUNT\[-1\]-1: rate)

综合运用位置表达式和有序函数，很多SQL难以实现的有序运算，都可以用SPL轻松解决。比如，根据考勤表，找出连续 4 周每天均出勤达 7 小时的学生：

<table> 
 <tbody> 
  <tr> 
   <td> </td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>=connect("mysql")</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>=A1.query@x("SELECT SID,ATTDATE,DURATION,null AS W FROM STUTEST WHERE DURATION&gt;=7 ORDER BY SID,ATTDATE").run(W=pdate@w(ATTDATE))</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>=A2.group@o(SID;~.groups@o(W;count(~):CNT).select(CNT==7).group@i(W-W[-1]!=7).max(~.len()):weeks)</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>=A3.select(weeks&gt;=4).(SID)</td> 
  </tr> 
 </tbody> 
</table>

**简化复杂的集合运算**，SPL序表的集合化更加彻底，配合灵活的语法和强大的集合函数，可大幅简化复杂的集合计算。比如，在各部门找出比本部门平均年龄小的员工：

<table> 
 <tbody> 
  <tr> 
   <td> </td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>…//省略序表Employees的生成过程</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>=Employees.group(DEPT; (a=~.avg(age(BIRTHDAY)),~.select(age(BIRTHDAY)&lt;a)):YOUNG)</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>=A2.conj(YOUNG)</td> 
  </tr> 
 </tbody> 
</table>

计算某支股票最长的连续上涨天数：

<table> 
 <tbody> 
  <tr> 
   <td> </td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>…//省略序表AAPL的生成过程</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>=a=0,AAPL.max(a=if(price&gt;price[-1],a+1,0))</td> 
  </tr> 
 </tbody> 
</table>

SPL可以**方便地实现分步计算**，序表的集合化更加彻底，可以用变量方便地表达集合，适合多步骤计算，很多SQL难以表达的集合运算，用SPL都可以轻松实现。比如，找出销售额累计占到一半的前n个大客户，并按销售额从大到小排序：

<table> 
 <tbody> 
  <tr> 
   <td> </td> 
   <td>A</td> 
   <td>B</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>//省略取数据的过程</td> 
   <td> </td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>=A1.sort(amount:-1)</td> 
   <td>/销售额逆序排序，可在SQL中完成</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>=A2.cumulate(amount)</td> 
   <td>/计算累计序列</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>=A3.m(-1)/2</td> 
   <td>/最后的累计即总额</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>=A3.pselect(~&gt;=A4)</td> 
   <td>/超过一半的位置</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>=A2(to(A5))</td> 
   <td>/按位置取值</td> 
  </tr> 
 </tbody> 
</table>

**简化复杂的关联计算**。序表的专业性体现在多方面，其中之一是支持对象引用的形式表达关联，开发者可以通过点号直观地访问关联表，从而提高开发效率。很多SQL难以表达的关联计算，用SPL都可以轻松实现。比如，根据员工表计算女经理的男员工：

=employees.select(gender:“male”,dept.manager.gender:“female”)

## 灵活的流程控制能力 ##

SPL提供了灵活易用的分支判断语句、循环语句，配合专业的结构化数据对象，可以方便地实现各类业务逻辑。

**分支判断语句**：

<table> 
 <tbody> 
  <tr> 
   <td> </td> 
   <td>A</td> 
   <td>B</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>…</td> 
   <td> </td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>if T.AMOUNT&gt;10000</td> 
   <td>=T.BONUS=T.AMOUNT*0.05</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>else if T.AMOUNT&gt;=5000 &amp;&amp; T.AMOUNT&lt;10000</td> 
   <td>=T.BONUS=T.AMOUNT*0.03</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>else if T.AMOUNT&gt;=2000 &amp;&amp; T.AMOUNT&lt;5000</td> 
   <td>=T.BONUS=T.AMOUNT*0.02</td> 
  </tr> 
 </tbody> 
</table>

**循环语句**：

<table> 
 <tbody> 
  <tr> 
   <td> </td> 
   <td>A</td> 
   <td>B</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>=db=connect("db")</td> 
   <td> </td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td colspan="2">=T=db.query@x("select * from sales where SellerID=? order by OrderDate",9)</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>for T</td> 
   <td>=A3.BONUS=A3.BONUS+A3.AMOUNT*0.01</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td> </td> 
   <td>=A3.CLIENT=CONCAT(LEFT(A3.CLIENT,4), " co.,ltd.")</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td> </td> 
   <td>　…</td> 
  </tr> 
 </tbody> 
</table>

与Java的循环类似，SPL还可用break关键字跳出（中断）当前循环体，或用next关键字跳过（忽略）本轮循环，不展开说了。

**流程控制语句配合序表，可以用统一的语法风格实现业务逻辑**，包括数据库读写、事务处理、流程处理、数据计算。比如，根据一定的规则计算奖金：

<table> 
 <tbody> 
  <tr> 
   <td> </td> 
   <td>A</td> 
   <td>B</td> 
   <td>C</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>=db=connect@e("dbName")</td> 
   <td> </td> 
   <td>/连接数据库，开启事务</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>=db.query@1("select sum(Amount) from sales where sellerID=? and year(OrderDate)=? and month(OrderDate)=?", p_SellerID,year(now()),month(now()))</td> 
   <td> </td> 
   <td>/查询当月销售额</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>=if(A2&gt;=10000 :200, A2&lt;10000 &amp;&amp; A2&gt;=2000 :100, 0)</td> 
   <td> </td> 
   <td>/本月累计奖金</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>=p_Amount*0.05</td> 
   <td> </td> 
   <td>/本单固定奖金</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>=BONUS=A3+A4</td> 
   <td> </td> 
   <td>/总奖金</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>=create(ORDERID,CLIENT,SELLERID,AMOUNT,BONUS,ORDERDATE)</td> 
   <td> </td> 
   <td>/创建订单的数据结构</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>=A6.record([p_OrderID,p_Client,p_SellerID,p_Amount,BONUS, date(now())])</td> 
   <td> </td> 
   <td>/生成一条订单记录</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>&gt;db.update@ik(A7,sales;ORDERID)</td> 
   <td> </td> 
   <td>/尝试写入库表</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>=db.error()</td> 
   <td> </td> 
   <td>/入库结果</td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>if A9==0</td> 
   <td>&gt;A1.commit()</td> 
   <td>/成功，则提交事务</td> 
  </tr> 
  <tr> 
   <td>11</td> 
   <td>Else</td> 
   <td>&gt;A1.rollback()</td> 
   <td>/失败，则回滚事务</td> 
  </tr> 
  <tr> 
   <td>12</td> 
   <td>&gt;db.close()</td> 
   <td> </td> 
   <td>/关闭数据库连接</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>return A9</td> 
   <td> </td> 
   <td>/返回入库结果</td> 
  </tr> 
 </tbody> 
</table>

## 优化体系结构 ##

SPL支持JDBC接口，代码可外置于Java，耦合性更低。SPL具有解释执行的特性，支持库外计算和代码移植，支持跨库跨源计算，在SQL后计算中可提供良好的架构性。

SPL提供了**易用的JDBC接口**，可被Java代码无缝集成。比如，将前面的SPL代码存为脚本文件，再在Java中以存储过程的形式调用文件名：

```java
    Class.forName("com.esproc.jdbc.InternalDriver");
    Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
    Statement statement = connection.createStatement();
    ResultSet result = statement.executeQuery("call writeBonus()");
    ```

SPL代码外置于Java，通过文件名被调用，既不依赖数据库，也不依赖Java，业务逻辑和前端代码分离，耦合性低。

**解释执行和热切换**。业务逻辑数量多，复杂度高，变化是常态。良好的系统构架，应该有能力应对变化的业务逻辑。ORM的本质是Java代码，需要先编译再执行，一般都要停机才能部署，应对变化的业务逻辑时非常繁琐。SPL是基于Java的解释型语言，无须编译就能执行，脚本修改后立即生效，支持不停机的热切换，适合应对变化的业务逻辑。

**方便代码移植**。SPL通过数据源名从数据库取数，如果需要移植，只要改动配置文件中的数据源配置信息，而不必修改SPL代码。SPL支持动态数据源，可通过参数或宏切换不同的数据库，从而进行更方便的移植。为了进一步增强可移植性，SPL还提供了与具体数据库无关的标准SQL语法，使用sqltranslate函数可将标准SQL转为主流方言SQL，仍然通过query函数执行。

**方便管理运营**。由于支持库外计算，代码可被第三方工具管理，方便团队协作；SPL脚本可以按文件目录进行存放，方便灵活，管理成本低；SPL对数据库的权限要求类似Java，不影响数据安全。

**跨库和跨源计算**。SPL序表可以统一读取各类数据源（含RDB），可以用统一的代码计算各类数据源，可以方便地实现跨库计算。比如，对MySQL和Oracle进行内关联：

<table> 
 <tbody> 
  <tr> 
   <td> </td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>=mysql1.query("select SellerId, sum(Amount) subtotal from Orders group by SellerId")</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>=orcl.query("select EId,Name from employees")</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>=join(A1:O,SellerId; A2:E,EId).new(O.Name:name, O.Dept:dept, E.subtotal:amt)</td> 
  </tr> 
 </tbody> 
</table>

SPL支持多种非RDB数据源，可进行RDB和非RDB之间的混合计算。包括txt\\csv\\xls等文件，MongoDB、Hadoop、redis、ElasticSearch、Kafka、Cassandra等NoSQL，以及WebService XML、Restful Json等多层数据。

ORM以及Stream/Kotlin缺乏专业的结构化数据对象和运算能力，Python Pandas难以被Java集成。SPL内置专业的结构化数据对象，具有强大的结构化数据计算能力和灵活的流程控制能力，可以方便地实现各类业务逻辑。提供了易用的JDBC接口，可以被Java方便地集成。还有更多结构性优势，包括耦合性低、解释执行和热切换、库外计算，以及跨库和跨源计算。

## SPL资料 ##

*  [SPL官网][SPL]
 *  [SPL下载][SPL 1]
 *  [SPL源代码][SPL 2]

欢迎对SPL有兴趣的加小助手（VX号：SPL-helper），进SPL技术交流群

[c8eead4f288e459baf3420746cef8a76.png]: https://img-blog.csdnimg.cn/c8eead4f288e459baf3420746cef8a76.png
[SPL]: http://www.scudata.com.cn
[SPL 1]: http://c.raqsoft.com.cn/article/1595816810031
[SPL 2]: https://github.com/SPLWare/esProc