集算器SPL，SQLite的挑战者

[Q神日志]

许多小微应用也需要一定的数据处理和计算能力。集成数据库会使应用程序变得太重。在这种情况下，SQLite是一个不错的选择，因为它框架简单，易于集成，能够持久存储数据，并且提供SQL来实现计算。

然而，对于一些更复杂的场景，SQLite 还是有不足的。

SQLite面对复杂场景的缺点

数据源支持

SQLite就像一个数据库，可以为自己的数据库文件提供良好的支持，但有时应用程序需要处理其他形式的数据，例如文本文件、Excel、其他数据库以及像Restful这样来自网络的数据。SQLite仅支持csv文件的读取，不支持其他数据源，除非硬编码。而且，SQLite虽然支持csv文件，但是过程比较繁琐。具体来说，需要先在命令行中创建数据库，然后使用create命令创建结构化表，接下来使用import命令导入数据，最后在SQL中查询数据。

除了常规的结构化数据外，现代应用还经常遇到Json、XML等复杂格式的数据。SQLite具有计算Json字符串的能力，但不支持直接读取多层数据源，包括Json文件/RESTful，因此必须硬编码或借助第三方库拼凑一条insert语句插入数据表。结果就是代码非常繁琐。SQLite 无法计算 XML 字符串，更不用说读取 XML 文件/Web 服务了。

有时，应用程序需要将数据写入通用格式的文件来导出、传输和交换，有时应用程序需要主动将数据写入其他数据源。然而，SQLite只能将数据持久化到自己的数据库文件中，不能直接写入外部数据源，包括基本的csv文件。

复杂的计算

由于SQLite使用SQL语句进行计算，因此继承了SQL的优点和缺点。SQL接近自然语言，易于学习并实现简单的计算。然而SQL不擅长复杂的计算，常常会造成代码繁琐、难以理解。

即使对于不太复杂的计算，用 SQL 实现也不容易。例如，计算每个客户销售额排名前 3 的订单：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>select * from (select *, row_number() over (partition by Client order by Amount desc) as row_number from Orders) where row_number<=3
</code></span></span>

 

由于该计算任务是计算组内前N条记录，因此需要先利用窗函数生成组内伪序号列，然后对伪列进行过滤，代码显得比较复杂。
对于复杂的计算，SQL代码会更长并且更难理解。例如，计算股票持续上涨的最大连续天数：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>select max(continuousdays)
from (
    select count(*) continuousdays
from (
        select sum(risingflag) over (order by day) norisingdays
        from (
            select day, case when price>lag(price) over (order by day) then 0 else 1 end risingflag
            from tbl
        )
) group by norisingdays
)
</code></span></span>

 

由于在SQL中很难直接表达连续上涨的概念，所以我们不得不采取间接的方式，即通过股票不上涨的累计天数来计算连续上涨的天数。这种方法需要很强的技巧，因此很难编码和理解。而且SQL调试困难，导致维护困难。
再比如，找出累计销售额占总销售额一半的前n名客户，并按照销售额降序排列：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>with A as
(select client,amount,row_number() over (order by amount) ranknumber
from sales)
select client,amount
from (select client,amount,sum(amount) over (order by ranknumber) acc
     from A)
where acc>(select sum(amount)/2 from sales)
order by amount des
</code></span></span>

 

SQL中很难处理恰好处于关键位置的客户，所以我们不得不采用间接的方式，即先按升序计算累计销售额，然后找出累计销售额不在关键位置的客户计算结果的后半部分。这种方法也需要较强的技巧，而且代码较长，调试困难。

另外，SQLite不提供丰富的日期和字符串函数。例如：SQLite缺少获取指定季度数之前或之后的日期的功能，以及获取N个工作日之后的日期的功能等，这限制了SQLite，使其不适合计算需求复杂的场景。

流水处理

SQL本身缺乏流处理能力，因此数据库会借助存储过程来实现完整的业务逻辑。但由于SQLite不支持存储过程，因此无法直接实现完整的业务逻辑，必须求助于主应用程序。具体来说，首先将SQL的数据对象转换为应用程序中的数据对象（如Java的resultSet/List），然后使用主应用程序的for/if语句来处理流程，最后转换回SQL的数据对象。结果就是代码非常繁琐。当业务逻辑非常复杂时，需要在SQL对象和主应用对象之间进行多次转换，比较麻烦，相关SOL代码在此不再展示。

使用集算器SPL解决SQLite难点

要为小型、微型Java应用提供数据处理和计算能力，有一个更好的选择：集算器SPL。

集算器SPL是一个Java开源数据处理引擎，框架简单，易于集成。另外，集算器SPL能够持久存储数据，并具有足够的计算能力。这些特性与 SQLite 类似。

框架简单：无需配置服务器、节点、集群。只要导入SPL jar，就可以将SPL部署在Java环境中。

SPL提供了JDBC驱动程序，可以轻松集成到Java应用程序中。对于简单的查询任务，编码复杂度与SQL类似。

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = conn.createStatement();
ResultSet result = statement.executeQuery("=T(\"D:/Orders.csv\").select(Amount>1000 && like(Client,\"*s*\"))");
</code></span></span>

 

SPL支持数据持久化，可以将数据保存为自己的数据格式（bin文件）。例如批量添加记录：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>    A
1   =create(OrderID,Client,SellerID,Amount,OrderDate)
2   
=A1.record([201,"HDR",9,2100.0,date("2021-01-01"),
202,"IBM",9,1900,date("2021-01-02"),
203,"APPLE",4,1900,date("2021-01-03")])
3   =file("d:/Orders.btx").export@ab(A2)
</code></span></span>

 

对于导出@abin A3、@ameans 附加、@bmeans bin 文件格式。

除了能够直接持久化数据之外，SPL还允许我们处理内存中的表序列（SPL的结构化数据对象，类似于SQL的结果集），然后覆盖到bin文件中。具体做法是将export@ab改为export@b。虽然该方法的性能不如SQLite，但由于小微应用涉及的数据量普遍较小，覆盖速度通常可以接受。

组合表是SPL自有的另一种数据格式，支持高性能的插入、删除、更新记录，适合大数据量的高性能计算（这不是本文的重点）。

除了自己的数据格式外，SPL 还可以将数据保存为 csv 文件。我们只需要把A3改成：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>file("d:/Orders.csv").export@tc(A2)
</code></span></span>

 

SPL有足够的计算能力，支持各种SQL式的计算，包括分组后的计算（窗口函数）：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>    A   B
1   =Orders.new(Client,Amount)  // Select some of fields
2   =Orders.select(Amount>1000 && like(Client,\"*s*\")) // Fuzzy query
3   = Orders.sort(Client,-Amount)   // Sorting
4   = Orders.id(Client) // Distinct
5   =Orders.groups(year(OrderDate):y,Client;sum(Amount):amt).select(amt>3000)   // Grouping and aggregating
6   =[Orders.select(Amount>3000),A1.select(year(OrderDate)==2009)].union()  // Union
7   =Orders.groups(year(OrderDate):y,Client;sum(Amount):amt).select(like(Client,\"*s*\"))   // Subquery
8   =A5.derive(amt/amt[-1]-1: rate) // Cross-row calculation
</code></span></span>

 

SPL提供了基本的SQL语法，例如分组和聚合：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>$select year(OrderDate) y,month(OrderDate) m, sum(Amount) s,count(1) c from {Orders}
Where Amount>=? and Amount<? ;arg1,arg2
</code></span></span>

 

除了这些基本能力之外，SPL还克服了SQLite的各种缺点。SPL全面支持各种数据源，具有更强的计算能力，方便流处理，使得处理更复杂的应用场景成为可能。

数据源支持

在SPL中读取csv文件只需要一步：在Java中嵌入以下SPL代码：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>T("d:/Orders.csv").select(Amount>2000 && Amount<=3000)
</code></span></span>

 

函数T可以读取bin文件和csv文件，并生成表序列。在SPL中导入数据时，会自动解析数据类型，无需手动指定。整个过程不需要额外的代码，比SQLite方便很多。

如果csv文件的格式不规则，我们可以使用import函数来指定分隔符、字段类型、跳行数，以及处理转义符、引号、括号等。因此，SPL的功能比SQLite多得多。

SPL内置了多种数据源接口，包括tsv、xls、Json、XML、RESTful、WebService等数据库，甚至支持Elasticsearch、MongoDB等特殊数据源。

所有这些数据源都可以直接访问，非常方便。对于上面未列出的其他数据源，SPL 提供了接口规范。只需按照规范将数据导出为SPL的结构化数据对象，后续的计算就可以了。

SPL可以直接解析多层数据源。例如读取并计算Json文件：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>json(file("d:/xml/emp_orders.json").read()).select(Amount>2000 && Amount<=3000)
json(httpfile("http://127.0.0.1:6868/api/orders").read()).select(Amount>2000 && Amount<=3000)
</code></span></span>

 

XML 文件：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>    A
1   =file("d:/xml/emp_orders.xml").read()
2   =xml(A1,"xml/row")
3   =A2.select(Amount>1000 && Amount<=2000 && like@c(Client,"*business*"))
</code></span></span>

 

网络服务：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>    A
1   =ws_client("http://127.0.0.1:6868/ws/RQWebService.asmx?wsdl")
2   =ws_call(A1,"RQWebService":"RQWebServiceSoap":"getEmp_orders")
3   =A2.select(Amount>1000 && Amount<=2000 && like@c(Client,"*business*"))
</code></span></span>

 

SPL的表序列支持多层结构化数据，并且比SQL数据库表的二维结构更容易表达Json/XML。而且，计算代码也更简单。这部分内容不是本文的重点，所以我们跳过。

跨源计算

SPL开放性好，可以直接计算多个数据源；我们可以在这些数据源和SPL bin文件之间进行跨源计算。例如，下面的代码是先对bin文件和csv文件进行内连接，然后进行分组聚合：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>join(T("d:/Orders.btx"):o,SellerId; T("d:/Emp.csv"):e,EId).groups(e.Dept;sum(o.Amont))
</code></span></span>

 

外部数据源之间的跨源计算也可以在SPL中轻松进行。例如，以下代码是对csv文件和RESTful进行左连接：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>join@1(json(httpfile("http://127.0.0.1:6868/api/orders").read()):o,SellerId; T("d:/Emp.csv"):e,EId)
</code></span></span>

 

以多步骤形式更容易阅读：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>    A
1   =Orders=json(httpfile("http://127.0.0.1:6868/api/orders").read())
2   =Employees=T("d:/Emp.csv")
3   =join@1(Orders:o,SellerId;Employees:e,EId)
</code></span></span>

 

总之，我们只需通过SPL就可以实现跨源计算，无需借助Java或命令行，SPL代码简短易懂，而且SPL的开发效率比SQL高很多。

将数据保存到任何数据源

除了支持数据持久化为自己的格式之外，SPL 还支持数据持久化为其他数据源。同样，SPL 使用表顺序作为媒介。例如：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>file("d:/Orders.csv").export@t(A2)          //csv file
file("d:/Orders.xlsx").xlsexport@t(A2)          //xls file
file("d:/Orders.json").write(json(A2))          //json file
</code></span></span>

 

特别是，SPL 支持将数据持久保存到任何数据库。我们以 Oracle 为例：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>    A   B
1   =connect("orcl")    /Connect to external oracle
2   =T=A1.query("select * from salesR where SellerID=?",10) /Batch query, table sequence T
3   =NT=T.derive()  /Copy and generate new table sequence NT
4   =NT.field("SELLERID",9) /Batch modify new table sequence
5   =A1.update(NT:T,sales;ORDERID)  /Persist the modification to database
</code></span></span>

 

持久化数据到数据库仍然采用表序作为介质，其优点非常明显：update功能可以自动比较修改（插入、删除、更新）前后的表序，可以轻松持久化批量数据。

计算能力

SPL支持有序计算、集合计算、逐步计算和关联计算。通过这些能力，可以简化复杂的结构化数据计算。

举个简单的例子：找出每个客户销售额排名前 3 的订单：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>Orders.group(Client).(~.top(3;Amount))
</code></span></span>

 

这段SPL代码很直观，它首先按照Client进行分组，然后计算每组的TopN（用符号~表示）。SPL代码之所以简单，表面上是因为SPL直接支持SQL不提供的top函数，但本质原因是SPL有实数行号字段，换句话说，SPL支持有序集。另一个原因是SPL中的集合定向操作更加彻底，可以实现真正的分组（只分组，不聚合）。这样就可以直观地计算出组内数据。

对于更复杂的计算，在SPL中实现也不难。例如，计算股票持续上涨的最大连续天数：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>    A
1   =tbl.sort(day)
2   =t=0,A1.max(t=if(price>price[-1],t+1,0))
</code></span></span>

 

在SPL中表达连续上升的概念很容易，只需要两步：按日期排序，然后遍历记录。如果发现股价上涨，则计数器加1。这段代码中，同时使用了循环函数max和有序集，[-1]代表上一条记录，即相对位置的表示方法， Price[-1]代表前一个交易日的股价，比整行平移（滞后函数）更直观。

再比如：找出累计销售额占总销售额一半的前n名客户：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>    A   B
2   =sales.sort(amount:-1)  /Sort records by sales in descending order (can be done in SQL)
3   =A2.cumulate(amount)    /Get a sequence of cumulative amounts
4   =A3.m(-1)/2 /Calculate the final accumulative amount, i.e., the total
5   =A3.pselect(~>=A4)  /Get position where the amount reaches half of the total
6   =A2(to(A5)) /Get records by position
</code></span></span>

 

SPL中的集合定位更加彻底，可以方便地用变量来表达集合，并在后续计算中继续使用变量来引用集合，因此SPL特别适合多步计算。将一个大问题分解为多个小步骤可以轻松实现复杂的计算目标，而且代码不仅短，而且易于理解。另外，多步计算天然支持调试，无形中提高了开发效率。

该代码利用了SPL的有序计算、集合计算和逐步计算的能力。借助这些能力，SPL 能够很好地实现简单和复杂的计算。而且，SPL支持离散记录，可以使用点符号直观地引用关联表，从而简化复杂的关联计算。

SPL还提供了更丰富的日期和字符串函数，在数量和功能上远远超过传统数据库。

值得一提的是，为了进一步提高开发效率，SPL发明了独特的函数语法。

流水处理

SPL 带有流控制语句。此类语句与SPL内置的顺序表对象配合使用，可以方便地实现完整的业务逻辑。

分支机构结构：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>    A   B
2   … 
3   if T.AMOUNT>10000   =T.BONUS=T.AMOUNT*0.05
4   else if T.AMOUNT>=5000 && T.AMOUNT<10000    =T.BONUS=T.AMOUNT*0.03
5   else if T.AMOUNT>=2000 && T.AMOUNT<5000 =T.BONUS=T.AMOUNT*0.02
</code></span></span>

 

循环结构：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>    A   B
1   =db=connect("db")   
2   =T=db.query@x("select * from sales where SellerID=? order by OrderDate",9)
3   for T   =A3.BONUS=A3.BONUS+A3.AMOUNT*0.01
4   
=A3.CLIENT=CONCAT(LEFT(A3.CLIENT,4), "co.,ltd.")
5   
</code></span></span>

 

…
除了上述代码之外，SPL还具有更多针对结构化数据的流程处理功能，可以进一步提高开发效率。例如，每轮循环取一批记录而不是一条记录；当某个字段的值发生变化时循环一轮。

上述业务逻辑可以保存为脚本文件，并放置在应用程序外部，并作为存储过程调用：

<span style="color:var(--syntax-text-color)"><span style="color:var(--syntax-text-color)"><code>Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement statement = conn.prepareCall("{call queryOrders()}");
statement.execute();
</code></span></span>

 

SPL是解释型代码，修改后可以直接运行，无需编译或重启应用程序，可以有效降低维护成本。外部SPL脚本不仅可以有效降低系统耦合性，而且还具有热插拔的特点。相比之下，SQLite不支持存储过程，因此业务逻辑无法放置在主应用程序之外，导致紧密耦合和较差的应用程序框架。
在Java应用中SPL明显优于SQLite。但对于非Java应用程序来说，就变得有点麻烦了。在这种情况下，我们必须求助于独立的ODBC或HTTP服务器，这将使框架变得笨重并降低集成度。需要说明的是，SPL可以在属于Java系统的android上正常运行，但由于iOS上还没有相对成熟的JVM环境，SPL目前还不支持iOS。

SPL源代码： https: //github.com/SPLWare/esProc