一個比 SQLite 還好用的資料庫神器

很多小微型應用程式也需要一些數據處理和計算能力，如果集成一個資料庫就顯得太沉重了，這種情況下 SQLite 是一個不錯的選擇，它架構簡單，集成方便，可持久化存儲數據，並提供 SQL 實現計算能力。但是，對於某些較複雜的場景，SQLite 仍有不足之處。 ...

很多小微型應用程式也需要一些數據處理和計算能力，如果集成一個資料庫就顯得太沉重了，這種情況下 SQLite 是一個不錯的選擇，它架構簡單，集成方便，可持久化存儲數據，並提供 SQL 實現計算能力。

但是，對於某些較複雜的場景，SQLite 仍有不足之處。

SQLite 面對複雜場景的不足

數據源支持

SQLite 像個資料庫，可以對自有的庫文件提供較好的支持，但應用程式有時還要處理其它形式的數據，比如文本文件、Excel、其它資料庫、Restful 等 Web 上的數據。SQLite 只支持 csv 文件的讀取，不支持其他數據源，除非硬編碼。而且，SQLite 雖然支持 csv 文件，但使用過程很繁瑣，需要先用命令行創建資料庫，再用 create 命令創建表結構，然後用 import 命令導入數據，最後才能用 SQL 查詢數據。

除了常規結構化數據，現代應用還會經常碰到 Json,XML 等複雜格式的數據。SQLite 有計算 Json 串的能力，但不支持直接讀取多層數據源，包括 Json 文件 /RESTful，需要硬寫代碼，或再藉助第三方類庫，拼成 insert 語句插入數據表，代碼很繁瑣。SQLite 也不能計算 XML 串，更不能讀取 XML 文件 /WebService。

應用程式有時需要把數據寫成格式通用的文件，以便輸出、轉移或交換，有時候還要把數據主動寫入其他數據源。但 SQLite 只能將數據持久化到自有的庫文件，不能直接寫入外部數據源，包括基本的 csv 文件。

複雜計算

SQLite 採用 SQL 語句做計算，SQL 的優點和缺點都會繼承下來。SQL 接近自然語言，學習門檻低，容易實現簡單的計算，但不擅長複雜的計算，經常會造成代碼會繁瑣難懂。

即使一些不太複雜的計算，SQL 實現起來也不容易。比如，計算每個客戶銷售額最大的 3 筆訂單：

select * from (select *, row_number() over (partition by Client order by Amount desc) as row_number from Orders) where row_number<=3

這個例子要計算組內的前 N 條記錄，需要用視窗函數生成組內的序號偽列，再過濾偽列，代碼因而顯得複雜。

複雜些的計算，SQL 代碼更加冗長難懂。比如，某支股票的最大連續上漲天數：


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
)

SQL 很難直接表達連續上漲的概念，只能換個方法變相實現，即通過累計不漲天數來計算連續上漲天數，這種方法技巧性強，編寫難度大且不易理解。而且 SQL 難以調試，導致維護困難。

再看個例子：找出銷售額占到一半的前 n 個客戶，並按銷售額從大到小排序。


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

SQL 很難處理恰好要過線的客戶，只能換個方法變相實現，即計算銷售額從小到大的累計值，反過來找出累計值不在後一半的客戶。這種方法技巧性強，代碼冗長，而且難以調試。

除此之外，SQLite 的日期和字元串函數也不夠豐富，比如缺乏季度增減、工作日計算等，這些缺點限制了 SQLite，不適合計算需求較複雜的場景。

流程處理

SQL 本身缺乏流程處理能力，資料庫會藉助存儲過程實現完整的業務邏輯，但 SQLite 不支持存儲過程，也就無法直接實現完整的業務邏輯，只能藉助主應用的能力，將 SQL 數據對象轉為應用中的數據對象（比如 Java 的 resultSet/List<EntityBean> 等對象），再用主程式的 for/if 等語句處理流程，最後再轉回 SQL 的數據對象，代碼非常繁瑣。複雜的業務邏輯要在 SQL 對象和主應用的對象之間轉換多次，更加麻煩，這裡就不展示了。

esProc SPL 解決 SQLite 困難

如果要為 Java 小微型應用提供數據處理和計算能力，還有一個更好的選擇：esProc SPL。

esProc SPL 一個開源的數據處理引擎，架構簡單，集成方便，可持久化存儲數據，有足夠的計算能力，這些特點與 SQLite 類似。

SPL 架構簡單，不用配置服務、節點、集群，只要引入 SPL 的 Jar 包，就可以部署在 Java 環境中。

SPL 提供了 JDBC 介面，可以方便地集成到 Java 應用中，簡單的查詢類似 SQL。

GitHub：https://github.com/SPLWare/esProc


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*\"))");

SPL 支持數據持久化，可以將數據保存到自有數據格式（集文件）中，比如批量新增記錄：

	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)

上面 A3 代碼 export@ab，@a 表示追加，@b 表示集文件格式

除了直接持久化，也可以先處理記憶體中的序表（SPL 的結構化數據對象，可類比為 SQL 結果集），再將序表覆蓋寫入集文件，具體做法是將 export@ab 改為 export@b。這種方式性能不如 SQLite，但小微型應用的數據量普遍不大，覆寫的速度通常可接受。

組表是 SPL 的另一種自有數據格式，支持高性能批量增刪改，適用於大數據量高性能計算（這不是本文重點）。

除了自有格式，SPL 也可以將數據保存到 csv 文件中，只要把 A3 改為：

file("d:/Orders.csv").export@tc(A2)

SPL 有足夠的計算能力，支持各類 SQL 式計算，包括分組後計算（視窗函數）：

	A	B
1	=Orders.new(Client,Amount)	// 選出部分欄位
2	=Orders.select(Amount>1000 && like(Client,\"s\"))	// 模糊查詢
3	= Orders.sort(Client,-Amount)	// 排序
4	= Orders.id(Client)	// 去重
5	=Orders.groups(year(OrderDate):y,Client;sum(Amount):amt).select(amt>3000)	// 分組彙總
6	=[Orders.select(Amount>3000),A1.select(year(OrderDate)==2009)].union()	// 並集
7	=Orders.groups(year(OrderDate):y,Client;sum(Amount):amt).select(like(Client,\"s\"))	// 子查詢
8	=A5.derive(amt/amt[-1]-1: rate)	// 跨行

SPL 提供了基本的 SQL 語法，比如分組彙總：

$select year(OrderDate) y,month(OrderDate) m, sum(Amount) s,count(1) c from {Orders} Where Amount>=? and Amount<? ;arg1,arg2

除了這些基礎能力外，SPL 還能剋服了 SQLite 的各種不足，全面支持各類數據源，具有更強的計算能力，流程處理方便，可以面對更複雜的應用場景。

數據源支持

SPL 讀取 csv 文件只需一步，在 Java 里嵌入下麵的 SPL 代碼：T("d:/Orders.csv").select(Amount>2000 && Amount<=3000)

函數 T 不僅可以讀取集文件，也可以讀取 csv 文件，並生成序表。SPL 導入數據時，數據類型會自動解析，不必手工指定。整個過程無需多餘編碼，比 SQLite 方便多了。

如果 csv 格式不規範，還可以使用 import 函數指定分隔符、欄位類型、跳過行數，並處理轉義符、引號、括弧等，比 SQLite 提供的功能豐富多了。

SPL 內置多種數據源介面，包括 tsv、xls、Json、XML、RESTful、WebService，以及其他資料庫，甚至支持 Elasticsearch、MongoDB 等特殊數據源。

這些數據源都可以直接使用，非常方便。對於其他未列入的數據源，SPL 也提供了介面規範，只要按規範輸出為 SPL 的結構化數據對象，就可以進行後續計算。

SPL 可直接解析多層數據源。讀取並計算 Json 文件：

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)

XML 文件：

	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"))

WebService：

	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"))

SPL 序表支持多層結構數據，比 SQL 庫表的二維結構更容易表達 Json/XML，計算代碼也更簡單。這部分內容不是本文重點，就此略過。

跨源計算

SPL 開放性較好，可以直接計算多種數據源，這些數據源可以和 SPL 集文件進行跨源計算。比如，對集文件和 csv 進行內關聯分組彙總：

join(T("d:/Orders.btx"):o,SellerId; T("d:/Emp.csv"):e,EId).groups(e.Dept;sum(o.Amont))

外部數據源之間也可以方便地進行跨源計算。比如 csv 和 RESTful 左關聯：

join@1(json(httpfile("http://127.0.0.1:6868/api/orders").read()):o,SellerId; T("d:/Emp.csv"):e,EId)

寫成多步驟的形式更易讀：

	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)

只用 SPL 語言就可以實現跨源計算，不必藉助 Java 或命令行，代碼簡短易懂，比 SQL 的開發效率高得多。

任意數據源的持久化

SPL 除了支持自有數據格式的持久化，也支持其他數據源，同樣是通過序表為媒介。比如：


file("d:/Orders.csv").export@t(A2)          //csv文件
file("d:/Orders.xlsx").xlsexport@t(A2)      //xls文件
file("d:/Orders.json").write(json(A2))      //json文件

特別地，SPL 支持任意資料庫的持久化，以 Oracle 為例：

	A	B
1	=connect("orcl")	/ 連接外部 oracle
2	=T=A1.query("select * from salesR where SellerID=?",10)	/ 批量查詢，序表 T
3	=NT=T.derive()	/ 複製出新序表 NT
4	=NT.field("SELLERID",9)	/ 批量修改新序表
5	=A1.update(NT:T,sales;ORDERID)	/ 持久化

資料庫的持久化以序表為媒介，其優點相當明顯：函數 update 可自動比對修改（增改刪）前後的序表，能夠方便地實現批量數據地持久化。

計算能力

SPL 支持有序計算、集合計算、分步計算、關聯計算，可以簡化複雜的結構化數據計算。

簡單的比如，計算每個客戶銷售額最大的 3 筆訂單：

Orders.group(Client).(~.top(3;Amount))

SPL 代碼很直觀，先按 Client 分組，再對各組（即符號～）計算 TopN。SPL 之所以代碼簡單，錶面上是因為 SQL 沒有 top 函數而 SPL 直接提供了，本質是因為 SPL 有真正的行號欄位，或者說，SPL 支持有序集合。SPL 代碼簡單，還因為集合化更加徹底，可以實現真正的分組，即只分組不彙總，這就可以直觀地計算組內數據。

複雜些的計算，SPL 實現起來也不難。最大連續上漲天數：

	A
1	=tbl.sort(day)
2	=t=0,A1.max(t=if(price>price[-1],t+1,0))

SPL 容易表達連續上漲的概念，先按日期排序；再遍歷記錄，發現上漲則計數器加 1。這裡既用到了迴圈函數 max，也用到了有序集合，代碼中 [-1] 表示上一條，是相對位置的表示方法，price [-1] 表示上一個交易日的股價，比整體移行（lag 函數）更直觀。

再看個例子，求銷售額占到一半的前 n 個客戶：

	A	B
2	=sales.sort(amount:-1)	/ 銷售額逆序排序，可在 SQL 中完成
3	=A2.cumulate(amount)	/ 計算累計序列
4	=A3.m(-1)/2	/ 最後的累計即總額
5	=A3.pselect(~>=A4)	/ 超過一半的位置
6	=A2(to(A5))	/ 按位置取值

SPL 集合化成更徹底，可以用變數方便地表達集合，併在下一步用變數引用集合繼續計算，因此特別適合多步驟計算。將大問題分解為多個小步驟，可以方便地實現複雜的計算目標，代碼不僅簡短，而且易於理解。此外，多步驟計算天然支持調試，無形中提高了開發效率。

上面例子使用了有序計算、集合計算、分步計算，SPL 從簡單到複雜的計算都可以很好的完成。此外，SPL 還支持游離記錄，可以用點號直觀地引用關聯表，從而簡化複雜的關聯計算。

SPL 還提供了更豐富的日期和字元串函數，在數量和功能上遠遠超過傳統資料庫。

值得一提的是，為了進一步提高開發效率，SPL 還創造了獨特的函數語法。

流程處理

SPL 本身提供了流程式控制制語句，配合內置的序表對象，可以方便地實現完整的業務邏輯。

分支結構：

	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

迴圈結構：

	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		…

上述代碼之外，SPL 還有更多針對結構化數據的流程處理功能，可進一步提高開發效率，比如：每輪迴圈取一批而不是一條記錄；某欄位值變化時迴圈一輪。

上面的業務邏輯可保存為腳本文件，並置於應用程式外，以存儲過程的形式被調用：


Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement statement = conn.prepareCall("{call queryOrders()}");
statement.execute();

SPL 是解釋型代碼，修改後不必編譯就可直接運行，也不必重啟應用，可有效降低維護成本。外置的 SPL 腳本不僅可以有效降低系統耦合性，還具有熱切換的特點。SQLite 不支持存儲過程，也就不能將業務邏輯外置於主應用，耦合性高，應用結構差。

SPL 在 Java 下明顯優於 SQLite，但對於非 Java 應用就會麻煩一點了，只能使用獨立的 ODBC 服務或 HTTP 服務的方式，架構不夠輕便，集成性也下降了。需要註意的是，android 屬於 Java 體系，SPL 可以正常運行，但 iOS 目前還沒有較成熟的 JVM 環境，SPL 就無法支持了。

GitHub：https://github.com/SPLWare/esProc

作者|GitHubDaily

本文來自博客園，作者：古道輕風，轉載請註明原文鏈接：https://www.cnblogs.com/88223100/p/A-database-artifact-that-works-better-than-SQLite.html