作为 java 程序员,用代码直接实现类似 SQL 中的交并补差的集合运算,总是要编写大量的代码,如果能有一个专门的外部数据工具,通过写简单类似 SQL 的脚本来实现,在 java 中直接调用并可以返回结果集,就再好不过了。Java 版集算器正是解决这一难题的神器,通过 SPL 脚本可以直观自然得写出运算,再使用 java 调用 SPL 脚本,使用起来简单,快捷,高效。另外,虽然 SQL 有集合概念,但对于有序集合运算提供的支持却很有限,经常要采用很费解的思路才可以完成, SPL 基于离散数据集模型,能轻松处理有序集合运算。下面我们就由浅入深,举例说明如何使用。
示例 1: 求重叠时间段的总天数
MySQL8:
with recursive t(start,end) as (select date'2010-01-07',date'2010-01-9' union all select date'2010-01-15',date'2010-01-16' union all select date'2010-01-07',date'2010-01-12' union all select date'2010-01-08',date'2010-01-11'), t1(d,end) as (select start,end from t union all select d+1,end from t1 where d select count(distinct d) from t1;with recursive t(start,end) as (select date'2010-01-07',date'2010-01-9' union all select date'2010-01-15',date'2010-01-16' union all select date'2010-01-07',date'2010-01-12' union all select date'2010-01-08',date'2010-01-11'), t1(d,end) as (select start,end from t union all select d+1,end from t1 where dselect count(distinct d) from t1;
说明:此例先将各时间段转成时间段内所有日子对应的日期,然后再求不同日期的个数
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | =A1.query@x("select date'2010-01-07'start,date'2010-01-9'end union all select date'2010-01-15',date'2010-01-16'union all select date'2010-01-07',date'2010-01-12'union all select date'2010-01-08',date'2010-01-11'") |
3 | =A2.(periods(start,end)) |
4 | =A3.conj() |
5 | =A4.icount() |
A3: 对 A2 中的每一个时间段构造从 start 到 end 的日期序列
A4: 求 A3 中所有日期序列的和
A5: 求 A4 中不重复日期的个数
保存脚本文件SumSet.dfx (嵌入 Java 会用到)
示例 1: 列出英语人口和法语人口均超过 5% 的国家
MySQL8:
with t1(lang) as (select 'English' union all select 'French') select name from world.country c where not exists(select * from t1 where lang not in (select language from world.countrylanguage where percentage>=5 and countrycode=c.code ) );with t1(lang) as (select 'English' union all select 'French') select name from world.country c where not exists(select * from t1 where lang not in (select language from world.countrylanguage where percentage>=5 and countrycode=c.code ) );
说明:此 SQL 只是演示通过双重否定实现差集为空
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | =A1.query("select CountryCode,Name,Language,Percentage from world.countrylanguage cl join world.country c on cl.countrycode=c.code where percentage>5") |
3 | =A2.group(CountryCode) |
4 | =A3.select(["English","French"]\~.(Language)==[]) |
5 | =A4.new(~.Name:name) |
A4: 选出 [“English”,”French”] 与本组语言集合的差为空的组,意思就是选出语言集合包含 English 和 French 的组
保存脚本文件DifferenceSet.dfx (嵌入 Java 会用到)
示例 1: 列出英语人口、法语人口、西班牙语人口分别超过 0.3%、0.2%、0.1% 的国家代码
MySQL8:
with t1 as (select countrycode from world.countrylanguage where language='English' and percentage>0.3), t2 as (select countrycode from world.countrylanguage where language='French' and percentage>0.2), t3 as (select countrycode from world.countrylanguage where language='Spanish' and percentage>0.1) select countrycode from t1 join t2 using(countrycode) join t3 using(countrycode);with t1 as (select countrycode from world.countrylanguage where language='English' and percentage>0.3), t2 as (select countrycode from world.countrylanguage where language='French' and percentage>0.2), t3 as (select countrycode from world.countrylanguage where language='Spanish' and percentage>0.1)select countrycodefrom t1 join t2 using(countrycode) join t3 using(countrycode);
说明:此例只是演示如何求解多个集合的交集
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | [English,French,Spanish] |
3 | [0.3,0.2,0.1] |
4 | =A2.(A1.query@i("select countrycode from world.countrylanguage where language=? and percentage>?",~,A3(#))) |
5 | >A1.close() |
6 | =A4.isect() |
A3: 按次序依次查询英语人口超 0.3%、法语人口超 0.2%、西班牙语超 0.1% 的国家代码,并转成序列
A5: A3 中所有序列交集
保存脚本文件IntersectionSet.dfx (嵌入 Java 会用到)
SPL 嵌入到 Java 应用程序十分方便,通过 JDBC 调用存储过程方式加载,用和集保存的文件SumSet.dfx,示例调用如下:
... Connection con = null; Class.forName("com.esproc.jdbc.InternalDriver"); con= DriverManager.getConnection("jdbc:esproc:local://"); //调用存储过程,其中SumSet是dfx的文件名 st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call SumSet()"); //执行存储过程 st.execute(); //获取结果集 ResultSet rs = st.getResultSet(); ... ... Connection con = null; Class.forName("com.esproc.jdbc.InternalDriver"); con= DriverManager.getConnection("jdbc:esproc:local://"); //调用存储过程,其中SumSet是dfx的文件名 st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call SumSet()"); //执行存储过程 st.execute(); //获取结果集 ResultSet rs = st.getResultSet(); ...
替换成DifferenceSet.dfx或IntersectionSet.dfx是同样的道理,只需 call DifferenceSet()或者 call IntersectionSet() 即可。这里只用 Java 片段粗略解释了如何嵌入 SPL,详细步骤请参阅Java 如何调用 SPL 脚本,也非常简单,不再赘述。同时,SPL 也支持 ODBC 驱动,集成到支持 ODBC 的语言,嵌入过程类似。
关于集合运算除了上面讲的和差交运算,还可以获取与行号有关的计算,以及有序集合的对位运算。
示例 1: 计算招商银行 (600036) 2017 年第 3 个交易日和倒数第 3 个交易日的交易信息
MySQL8:
with t as (select *, row_number() over(order by tdate) rn from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31') select tdate,open,close,volume from t where rn=3 union all select tdate,open,close,volume from t where rn=(select max(rn)-2 from t);with t as (select *, row_number() over(order by tdate) rn from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31') select tdate,open,close,volume from t where rn=3union all select tdate,open,close,volume from t where rn=(select max(rn)-2 from t);
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | =A1.query@x("select * from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31' order by tdate") |
3 | =A2(3)|A2.m(-3) |
A3: 第 3 条记录和倒数第 3 条记录的和集
示例 2: 计算招商银行 (600036) 最近 20 个交易日的平均收盘价
MySQL8:
with t as (select *, row_number() over(order by tdate desc) rn from stktrade where sid='600036') select avg(close) avg20 from t where rn<=20;with t as (select *, row_number() over(order by tdate desc) rn from stktrade where sid='600036')select avg(close) avg20 from t where rn<=20;
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | =A1.query@x("select * from stktrade where sid='600036' order by tdate") |
3 | =A2.m(-20:) |
4 | =A3.avg(close) |
A2: 将 600036 的交易记录按日期排序
A3: 取从倒数 20 条到末尾的所有记录
A4: 求 A3 中所有记录收盘价的平均值
示例 1: 计算招商银行 (600036)2017 年经过多少交易日收盘价达到 25 元
MySQL8:
with t as (select *, row_number() over(order by tdate) rn from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31') select min(rn) from t where close>=25;with t as (select *, row_number() over(order by tdate) rn from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31')select min(rn) from t where close>=25;
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | =A1.query@x("select * from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31' order by tdate") |
3 | =A2.pselect(close>=25) |
A3: 从前往后查找第 1 个收盘价达到 25 元的记录位置
示例 2: 计算格力电器 (000651) 2017 年涨幅 (考虑停牌)
MySQL8:
with t as (select * from stktrade where sid='000651'), t1(d) as (select max(tdate) from t where tdate<'2017-01-01'), t2(d) as (select max(tdate) from t where tdate<'2018-01-01') select s2.close/s1.close-1 rise from (select * from t,t1 where tdate=d) s1, (select * from t,t2 where tdate=d) s2;with t as (select * from stktrade where sid='000651'), t1(d) as (select max(tdate) from t where tdate<'2017-01-01'), t2(d) as (select max(tdate) from t where tdate<'2018-01-01')select s2.close/s1.close-1 risefrom (select * from t,t1 where tdate=d) s1, (select * from t,t2 where tdate=d) s2;
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | =A1.query@x("select * from stktrade where sid='000651' and tdate<'2018-01-01' order by tdate") |
3 | =A2.pselect@z(tdate < date("2017-01-01")) |
4 | =A2(A3).close |
5 | =A2.m(-1).close |
6 | =A5/A4-1 |
A2: 数据按交易日从小到大排序
A3: 从后往前查找交易日在 2017-01-01 之前的最后一条记录在序列中的行号
A4: 求 2016 年收盘价
A5: 求 2017 年收盘价,其中 A2.m(-1) 取倒数第 1 条记录,即 2017 年最后一个交易日对应的记录
示例 3: 列出 2017 年信息发展 (300469) 交易量超过 250 万股时的交易信息及各日涨幅(考虑停牌)
MySQL8:
with t as (select *, row_number() over(order by tdate) rn from stktrade where sid='300469' and tdate<=date '2017-12-31'), t1 as (select * from t where tdate>=date'2017-01-01' and volume>=2500000) select t1.tdate, t1.close, t.volume, t1.close/t.close-1 rise from t1 join t on t1.rn=t.rn+1;with t as (select *, row_number() over(order by tdate) rn from stktrade where sid='300469' and tdate<=date '2017-12-31'), t1 as (select * from t where tdate>=date'2017-01-01' and volume>=2500000) select t1.tdate, t1.close, t.volume, t1.close/t.close-1 rise from t1 join t on t1.rn=t.rn+1;
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | =A1.query@x("select * from stktrade where sid='300469' and tdate<= date '2017-12-31' order by tdate") |
3 | =A2.pselect@a(tdate>=date("2017-01-01") && volume>2500000) |
4 | =A3.new(A2(~).tdate:tdate, A2(~).close:close, A2(~).volume:volume, A2(~).close/A2(~-1).close-1:rise) |
A3: 求出 2017 年交易量超 250 万股所有记录的行号
A4: 根据行号计算相应的日期、收盘价、交易量、涨幅
示例 1: 计算招商银行 (600036) 2017 年最早的最低价与最早的最高价间隔多少交易日
MySQL8:
with t as (select *, row_number() over(order by tdate) rn from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31'), t1 as (select * from t where close=(select min(close) from t)), t2 as (select * from t where close=(select max(close) from t)) select abs(cast(min(t1.rn) as signed)-cast(min(t2.rn) as signed)) inteval from t1,t2;with t as (select *, row_number() over(order by tdate) rn from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31'), t1 as (select * from t where close=(select min(close) from t)), t2 as (select * from t where close=(select max(close) from t))select abs(cast(min(t1.rn) as signed)-cast(min(t2.rn) as signed)) intevalfrom t1,t2;
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | =A1.query@x("select * from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31' order by tdate") |
3 | =A2.pmax(close) |
4 | =A2.pmin(close) |
5 | =abs(A3-A4) |
A3: 从前往后找最大收盘价在序列中的行号
A4: 从前往后找最小收盘价在序列中的行号
示例 2: 计算招商银行 (600036) 2017 年最后的最低价与最后的最高价间隔多少交易日
MySQL8:
with t as (select *, row_number() over(order by tdate) rn from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31'), t1 as (select * from t where close=(select min(close) from t)), t2 as (select * from t where close=(select max(close) from t)) select abs(cast(max(t1.rn) as signed)-cast(max(t2.rn) as signed)) inteval from t1,t2;with t as (select *, row_number() over(order by tdate) rn from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31'), t1 as (select * from t where close=(select min(close) from t)), t2 as (select * from t where close=(select max(close) from t))select abs(cast(max(t1.rn) as signed)-cast(max(t2.rn) as signed)) intevalfrom t1,t2;
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | =A1.query@x("select * from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31' order by tdate") |
3 | =A2.pmax@z(close) |
4 | =A2.pmin@z(close) |
5 | =abs(A3-A4) |
A3: 从后往前找最大收盘价在序列中的行号
A4: 从后往前找最小收盘价在序列中的行号
示例 1: 求 2018 年 3 月 6 日到 8 日创业板指 (399006) 对深证成指 (399001) 的每日相对收益率
MySQL8:
with t1 as (select *,close/lag(close) over(order by tdate) rise from stktrade where sid='399006' and tdate between '2018-03-05' and '2018-03-08'), t2 as (select *, close/lag(close) over(order by tdate) rise from stktrade where sid='399001' and tdate between '2018-03-05' and '2018-03-08') select t1.rise-t2.rise from t1 join t2 using(tdate) where t1.rise is not null;with t1 as (select *,close/lag(close) over(order by tdate) rise from stktrade where sid='399006' and tdate between '2018-03-05' and '2018-03-08'), t2 as (select *, close/lag(close) over(order by tdate) rise from stktrade where sid='399001' and tdate between '2018-03-05' and '2018-03-08')select t1.rise-t2.risefrom t1 join t2 using(tdate)where t1.rise is not null;
集算器 SPL:
A | |
---|---|
1 | =connect("mysql") |
2 | =["399006","399001"].(A1.query("select * from stktrade where sid=? and tdate between '2018-03-05' and '2018-03-08' ",~)) |
3 | >A1.close() |
4 | =A2.(~.calc(to(2,4),close/close[-1])) |
5 | =A4(1)--A4(2) |
A2: 依次查询 399006 和 399001 从 2018 年 3 月 5 日到 8 日的交易数据
A4: 依次计算 A2 中 2 个序表从第 2 条记录到第 4 条记录的涨幅,也就是 399006 和 399001 从 2018 年 3 月 6 日到 8 日的每天涨幅
A5: 对位相减,即可算出每日相对收益率
有库写 SQL,没库写 SPL
用 Java 程序直接汇总计算数据,还是比较累的,代码很长,并且不可复用,很多情况数据也不在数据库里,有了 SPL,就能像在 Java 中用 SQL 一样了,十分方便。
常用无忧,不花钱就能取得终身使用权的入门版
如果要分析的数据是一次性或临时性的,润乾集算器每个月都提供免费试用授权,可以循环免费使用。但要和 Java 应用程序集成起来部署到服务器上长期使用,定期更换试用授权还是比较麻烦,润乾提供了有终身使用权的入门版,解决了这个后顾之忧,获得方式参考 如何免费使用润乾集算器?
技术文档和社区支持
官方提供的集算器技术文档本身就有很多现成的例子,常规问题从文档里都能找到解决方法。如果获得了入门版,不仅能够使用 SPL 的常规功能,碰到任何问题都可以去乾学院上去咨询,官方通过该社区对入门版用户提供免费的技术支持。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。