Java 嵌入 SPL 輕松實現(xiàn)數(shù)據(jù)分組

要在 Java 代碼中實現(xiàn)類似 SQL 中的 GroupBy 分組聚合運算，是比較繁瑣的，通常先要聲明數(shù)據(jù)結(jié)構(gòu)（Java 實體類），然后用 Java 集合進(jìn)行循環(huán)遍歷，最后根據(jù)分組條件添加到某個子集合中。Java 8 有了 Lambda（stream）代碼簡潔了許多，分組后往往還要跟著聚合操作，仍然需要單寫聚合函數(shù) sum(),count(*),topN()等。這些還都是最常規(guī)的分組和聚合運算，遇到對位分組、枚舉分組、多重分組等非常規(guī)分組加上其他聚集函數(shù) (FIRST，LAST…)，代碼就變得非常冗長且不通用。如果能有一個中間件專門負(fù)責(zé)這類計算，采用類似 SQL 腳本做算法描述，在 Java 中直接調(diào)用腳本并返回結(jié)果集就好了。Java 版集算器和 SPL 腳本，就是這樣的機制，下面舉例說明如何使用。

SPL 實現(xiàn)

• 常規(guī)分組

duty.xlsx 文件中保存著每個人的加班記錄:

匯總每個人的值班天數(shù)：

保存腳本文件CountName.dfx(嵌入 Java 會用到)

• 每組 TopN

取每個月、每個人、頭三天的加班記錄

保存腳本文件RecMonTop3.dfx(嵌入 Java 會用到)

Java 調(diào)用

SPL 嵌入到 Java 應(yīng)用程序十分方便，通過 JDBC 調(diào)用存儲過程方法加載，用常規(guī)分組保存的文件CountName.dfx，示例調(diào)用如下：

...  Connection con = null;  Class.forName("com.esproc.jdbc.InternalDriver");  con= DriverManager.getConnection("jdbc:esproc:local://"); //調(diào)用存儲過程，其中CountName是dfx的文件名  st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call CountName()");  //執(zhí)行存儲過程  st.execute();  //獲取結(jié)果集  ResultSet rs = st.getResultSet(); ......  Connection con = null;  Class.forName("com.esproc.jdbc.InternalDriver");  con= DriverManager.getConnection("jdbc:esproc:local://"); //調(diào)用存儲過程，其中CountName是dfx的文件名  st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call CountName()");  //執(zhí)行存儲過程  st.execute();  //獲取結(jié)果集  ResultSet rs = st.getResultSet(); ...

替換成 RecMonTop3.dfx 是同樣的道理，只需 call RecMonTop3() 即可，也可同時返回兩個結(jié)果集。這里只用 Java 片段粗略解釋了如何嵌入 SPL，詳細(xì)步驟請參閱 Java 如何調(diào)用 SPL 腳本 ，也非常簡單，不再贅述。同時，SPL 也支持 ODBC 驅(qū)動，集成到支持 ODBC 的語言，嵌入過程類似。

拓展節(jié)選

之前沒有相關(guān)的總結(jié)，其實關(guān)于數(shù)據(jù)分組，細(xì)分起來其實還有很多種，對位分組、枚舉分組、多重分組…，在乾學(xué)院 SPL 官方論壇都有總結(jié)和示例，這里節(jié)選其中兩種。

• SPL 對位分組

示例 1：按順序分別列出使用 Chinese、English、French 作為官方語言的國家數(shù)量

MySQL8: with t(name,ord) as (select 'Chinese',1 union all select 'English',2 union all select 'French',3) select t.name, count(countrycode) cnt from t left join world.countrylanguage s on t.name=s.language where s.isofficial='T' group by name,ord order by ord;MySQL8: with t(name,ord) as (select 'Chinese',1 union all select 'English',2 union all select 'French',3) select t.name, count(countrycode) cnt from t left join world.countrylanguage s on t.name=s.language where s.isofficial='T' group by name,ord order by ord;

注意：表的字符集和數(shù)據(jù)庫會話的字符集要保持一致。

(1) show variables like ’character_set_connection’查看當(dāng)前會話字符集

(2) show create table world.countrylanguage 查看表的字符集

(3) set character_set_connection=[字符集] 更新當(dāng)前會話字符集

集算器 SPL:

A1: 連接數(shù)據(jù)庫

A2: 查詢出所有官方語言的記錄

A3: 需要列出的語言

A4: 將所有記錄按 Language 對位到 A3 相應(yīng)位置

A5: 構(gòu)造以語言和使用此語言為官方語言的國家數(shù)量的序表

示例 2：按順序分別列出使用 Chinese、English、French 及其它語言作為官方語言的國家數(shù)量

MySQL8: with t(name,ord) as (select 'Chinese',1 union all select 'English',2 union all select 'French',3 union all select 'Other', 4), s(name, cnt) as ( select language, count(countrycode) cnt from world.countrylanguage s where s.isofficial='T' and language in ('Chinese','English','French') group by language union all select 'Other', count(distinct countrycode) cnt from world.countrylanguage s where isofficial='T' and language not in ('Chinese','English','French') ) select t.name, s.cnt from t left join s using (name) order by t.ord;MySQL8: with t(name,ord) as (select 'Chinese',1 union all select 'English',2 union all select 'French',3 union all select 'Other', 4), s(name, cnt) as ( select language, count(countrycode) cnt from world.countrylanguage s where s.isofficial='T' and language in ('Chinese','English','French') group by language union all select 'Other', count(distinct countrycode) cnt from world.countrylanguage s where isofficial='T' and language not in ('Chinese','English','French') ) select t.name, s.cnt from t left join s using (name) order by t.ord;

集算器 SPL:

A4: 將所有記錄按 Language 對位到 A3.to(3) 相應(yīng)位置，并追加一組用于存放不能對位的記錄

A5: 第 4 組計算不同 CountryCode 的數(shù)量

• SPL 枚舉分組

示例 1：按順序列出各類型城市的數(shù)量

MySQL8: with t as (select * from world.city where CountryCode='CHN'), segment(class,start,end) as (select 'tiny', 0, 200000 union all select 'small',  200000, 1000000 union all select 'medium', 1000000, 2000000 union all select 'big', 2000000, 100000000 ) select class, count(1) cnt from segment s join t on t.population>=s.start and t.population<s.end group by class, start order by start;MySQL8: with t as (select * from world.city where CountryCode='CHN'), segment(class,start,end) as (select 'tiny', 0, 200000 union all select 'small',  200000, 1000000 union all select 'medium', 1000000, 2000000 union all select 'big', 2000000, 100000000 ) select class, count(1) cnt from segment s join t on t.population>=s.start and t.population<s.end group by class, start order by start;

集算器 SPL:

A3: ${…} 宏替換，以大括號內(nèi)表達(dá)式的結(jié)果作為新表達(dá)式進(jìn)行計算，結(jié)果為序列 [“?<200000”,“?<1000000”,“?<2000000”,“?<100000000”]

A5: 針對 A2 中每條記錄，尋找 A3 中第 1 個成立的條件，并追加到對應(yīng)的組中

示例 2：列出華東地區(qū)大型城市數(shù)量、其它地區(qū)大型城市數(shù)量、非大型城市數(shù)量

MySQL8: with t as (select * from world.city where CountryCode='CHN') select 'East&Big' class, count(*) cnt from t where population>=2000000 and district in ('Shanghai','Jiangshu', 'Shandong','Zhejiang','Anhui','Jiangxi') union all select 'Other&Big', count(*) from t where population>=2000000 and district not in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi') union all select 'Not Big', count(*) from t where population<2000000;MySQL8: with t as (select * from world.city where CountryCode='CHN') select 'East&Big' class, count(*) cnt from t where population>=2000000 and district in ('Shanghai','Jiangshu', 'Shandong','Zhejiang','Anhui','Jiangxi') union all select 'Other&Big', count(*) from t where population>=2000000 and district not in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi') union all select 'Not Big', count(*) from t where population<2000000;

集算器 SPL:

A5: enum@n 將不滿足 A4 中所有條件的記錄存放到追加的最后一組中

示例 3：列出所有地區(qū)大型城市數(shù)量、華東地區(qū)大型城市數(shù)量、非大型城市數(shù)量

MySQL8: with t as (select * from world.city where CountryCode='CHN') select 'Big' class, count(*) cnt from t where population>=2000000 union all select 'East&Big' class, count(*) cnt from t where population>=2000000 and district in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi') union all select 'Not Big' class, count(*) cnt from t where population<2000000;MySQL8: with t as (select * from world.city where CountryCode='CHN') select 'Big' class, count(*) cnt from t where population>=2000000 union all select 'East&Big' class, count(*) cnt from t where population>=2000000 and district in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi') union all select 'Not Big' class, count(*) cnt from t where population<2000000;

集算器 SPL:

A6: 若 A2 中記錄滿足 A4 中多個條件時，enum@r 會將其追加到對應(yīng)的每個組中

優(yōu)勢總結(jié)

• 有庫寫 SQL，沒庫寫 SPL
  用 Java 程序直接匯總計算數(shù)據(jù)，還是比較累的，代碼很長，并且不可復(fù)用，很多情況數(shù)據(jù)也不在數(shù)據(jù)庫里，有了 SPL，就能像在 Java 中用 SQL 一樣了，十分方便。

• 常用無憂，不花錢就能取得終身使用權(quán)的入門版
  如果要分析的數(shù)據(jù)是一次性或臨時性的，潤乾集算器每個月都提供免費試用授權(quán)，可以循環(huán)免費使用。但要和 Java 應(yīng)用程序集成起來部署到服務(wù)器上長期使用，定期更換試用授權(quán)還是比較麻煩，潤乾提供了有終身使用權(quán)的入門版，解決了這個后顧之憂，獲得方式參考 如何免費使用潤乾集算器？

• 技術(shù)文檔和社區(qū)支持
  官方提供的集算器技術(shù)文檔本身就有很多現(xiàn)成的例子，常規(guī)問題從文檔里都能找到解決方法。如果獲得了入門版，不僅能夠使用 SPL 的常規(guī)功能，碰到任何問題都可以去乾學(xué)院上去咨詢，官方通過該社區(qū)對入門版用戶提供免費的技術(shù)支持。