Java嵌入SPL轻松实现数据分组

问题介绍

要在Java代码中实现类似SQL中的GroupBy分组聚合运算，是比较繁琐的，通常先要声明数据结构（Java实体类），然后用Java集合进行循环遍历，最后根据分组条件添加到某个子集合中。Java 8 有了Lambda（stream）代码简洁了许多，分组后往往还要跟着聚合操作，仍然需要单写聚合函数sum(),count(*),topN()等。这些还都是最常规的分组和聚合运算，遇到对位分组、枚举分组、多重分组等非常规分组加上其他聚集函数(FIRST，LAST…)，代码就变得非常冗长且不通用。如果能有一个中间件专门负责这类计算，采用类似SQL脚本做算法描述，在Java中直接调用脚本并返回结果集就好了。Java版集算器和SPL脚本，就是这样的机制，下面举例说明如何使用。

SPL实现

• 常规分组

duty.xlsx文件中保存着每个人的加班记录:

workday	name
2016-02-05	Ashley
2016-02-08	Ashley
2016-02-09	Ashley
2016-02-10	Johnson
2016-02-11	Johnson
2016-02-12	Johnson
2016-02-15	Ashley
2016-02-16	Ashley
2016-02-17	Ashley

汇总每个人的值班天数：

	A
1	=file("/Users/test/duty.xlsx").importxls@tx()
2	=A1.groups(name;count(name):count)

保存脚本文件CountName.dfx(嵌入Java会用到)

• 每组TopN

取每个月、每个人、头三天的加班记录

	A
1	=file("/Users/test/duty.xlsx").importxls@tx()
2	=A1.group(month(workday):mon,name;~.top(3):top3)

保存脚本文件RecMonTop3.dfx(嵌入Java会用到)

Java调用

SPL嵌入到Java应用程序十分方便，通过JDBC调用存储过程方法加载，用常规分组保存的文件CountName.dfx，示例调用如下：

...
 Connection con = null;
 Class.forName("com.esproc.jdbc.InternalDriver");
 con= DriverManager.getConnection("jdbc:esproc:local://");
//调用存储过程，其中CountName是dfx的文件名
 st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call CountName()");
 //执行存储过程
 st.execute();
 //获取结果集
 ResultSet rs = st.getResultSet();
...

替换成RecMonTop3.dfx是同样的道理，只需call RecMonTop3()即可，也可同时返回两个结果集。这里只用Java片段粗略解释了如何嵌入SPL，详细步骤请参阅 http://c.raqsoft.com.cn/article/1543657951867，也非常简单，不再赘述。同时，SPL也支持ODBC驱动，集成到支持ODBC的语言，嵌入过程类似。

拓展节选

之前没有相关的总结，其实关于数据分组，细分起来其实还有很多种，对位分组、枚举分组、多重分组…，在乾学院SPL官方论坛都有总结和示例，这里节选其中两种。

• SPL对位分组

示例 1：按顺序分别列出使用 Chinese、English、French 作为官方语言的国家数量

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;

注意：表的字符集和数据库会话的字符集要保持一致。

(1) show variables like 'character_set_connection’查看当前会话字符集

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

(3) set character_set_connection=[字符集]更新当前会话字符集

集算器SPL:

	A
1	=connect("mysql")
2	=A1.query@x("select   * from world.countrylanguage where isofficial='T'")
3	[Chinese,English,French]
4	=A2.align@a(A3,Language)
5	=A4.new(A3(#):name, ~.len():cnt)

A1: 连接数据库

A2: 查询出所有官方语言的记录

A3: 需要列出的语言

A4: 将所有记录按Language对位到A3相应位置

A5: 构造以语言和使用此语言为官方语言的国家数量的序表

示例 2：按顺序分别列出使用 Chinese、English、French 及其它语言作为官方语言的国家数量

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:

	A
1	=connect("mysql")
2	=A1.query@x("select   * from world.countrylanguage where isofficial='T'")
3	[Chinese,English,French,Other]
4	=A2.align@an(A3.to(3),Language)
5	=A4.new(A3(#):name, if(#<=3,~.len(), ~.icount(CountryCode)):cnt)

A4: 将所有记录按Language对位到A3.to(3)相应位置，并追加一组用于存放不能对位的记录

A5: 第4组计算不同CountryCode的数量

• SPL枚举分组

示例 1：按顺序列出各类型城市的数量

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:

	A
1	=connect("mysql")
2	=A1.query@x("select * from world.city where   CountryCode='CHN'")
3	=${string([20,100,200,10000].(~*10000).("?<"/~))}
4	[tiny,small,medium,big]
5	=A2.enum(A3,Population)
6	=A5.new(A4(#):class, ~.len():cnt)

A3: ${…}宏替换，以大括号内表达式的结果作为新表达式进行计算，结果为序列["?<200000","?<1000000","?<2000000","?<100000000"]

A5: 针对 A2 中每条记录，寻找 A3 中第 1 个成立的条件，并追加到对应的组中

示例 2：列出华东地区大型城市数量、其它地区大型城市数量、非大型城市数量

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:

	A
1	=connect("mysql")
2	=A1.query@x("select * from world.city where   CountryCode='CHN'")
3	[Shanghai,Jiangshu, Shandong,Zhejiang,Anhui,Jiangxi]
4	[?(1)>=2000000   && A3.contain(?(2)), ?(1)>=2000000 && !A3.contain(?(2))]
5	[East&Big,Other&Big, Not Big]
6	=A2.enum@n(A4, [Population,District])
7	=A6.new(A5(#):class, A6(#).len():cnt)

A5: enum@n将不满足 A4 中所有条件的记录存放到追加的最后一组中

示例 3：列出所有地区大型城市数量、华东地区大型城市数量、非大型城市数量

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:

	A
1	=connect("mysql")
2	=A1.query@x("select * from world.city where   CountryCode='CHN'")
3	[Shanghai,Jiangshu, Shandong,Zhejiang,Anhui,Jiangxi]
4	[?(1)>=2000000, ?(1)>=2000000 && A3.contain(?(2))]
5	[Big, East&Big, Not Big]
6	=A2.enum@rn(A4, [Population,District])
7	=A6.new(A5(#):class, A6(#).len():cnt)

A6: 若A2中记录满足A4中多个条件时，enum@r会将其追加到对应的每个组中

优势总结

• 有库写SQL，没库写SPL
  用Java程序直接汇总计算数据，还是比较累的，代码很长，并且不可复用，很多情况数据也不在数据库里，有了SPL，就能像在Java中用SQL一样了，十分方便。

• 常用无忧，不花钱就能取得终身使用权的入门版
  如果要分析的数据是一次性或临时性的，润乾集算器每个月都提供免费试用授权，可以循环免费使用。但要和Java应用程序集成起来部署到服务器上长期使用，定期更换试用授权还是比较麻烦，润乾提供了有终身使用权的入门版，解决了这个后顾之忧，获得方式参考 如何免费使用润乾集算器？

• 技术文档和社区支持
  官方提供的集算器技术文档本身就有很多现成的例子，常规问题从文档里都能找到解决方法。如果获得了入门版，不仅能够使用SPL的常规功能，碰到任何问题都可以去乾学院上去咨询，官方通过该社区对入门版用户提供免费的技术支持。