Java 搞定 SQL 集合運算的最簡方法

問題介紹

??作為 java 程序員，用代碼直接實現(xiàn)類似 SQL 中的交并補差的集合運算，總是要編寫大量的代碼，如果能有一個專門的外部數(shù)據(jù)工具，通過寫簡單類似 SQL 的腳本來實現(xiàn)，在 java 中直接調(diào)用并可以返回結(jié)果集，就再好不過了。Java 版集算器正是解決這一難題的神器，通過 SPL 腳本可以直觀自然得寫出運算，再使用 java 調(diào)用 SPL 腳本，使用起來簡單，快捷，高效。另外，雖然 SQL 有集合概念，但對于有序集合運算提供的支持卻很有限，經(jīng)常要采用很費解的思路才可以完成， SPL 基于離散數(shù)據(jù)集模型，能輕松處理有序集合運算。下面我們就由淺入深，舉例說明如何使用。

SPL 實現(xiàn)

和集

??示例 1： 求重疊時間段的總天數(shù)

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;

??說明：此例先將各時間段轉(zhuǎn)成時間段內(nèi)所有日子對應的日期，然后再求不同日期的個數(shù)

集算器 SPL:

??A3: 對 A2 中的每一個時間段構造從 start 到 end 的日期序列
??A4: 求 A3 中所有日期序列的和
??A5: 求 A4 中不重復日期的個數(shù)

??保存腳本文件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 只是演示通過雙重否定實現(xiàn)差集為空

集算器 SPL:

??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:

??A3: 按次序依次查詢英語人口超 0.3%、法語人口超 0.2%、西班牙語超 0.1% 的國家代碼，并轉(zhuǎn)成序列
??A5: A3 中所有序列交集

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

Java 調(diào)用

??SPL 嵌入到 Java 應用程序十分方便，通過 JDBC 調(diào)用存儲過程方式加載，用和集保存的文件SumSet.dfx，示例調(diào)用如下：

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

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

擴展節(jié)選

??關于集合運算除了上面講的和差交運算，還可以獲取與行號有關的計算，以及有序集合的對位運算。

根據(jù)行號取數(shù)據(jù)

??示例 1： 計算招商銀行 (600036) 2017 年第 3 個交易日和倒數(shù)第 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:

??A3: 第 3 條記錄和倒數(shù)第 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:

??A2: 將 600036 的交易記錄按日期排序
??A3: 取從倒數(shù) 20 條到末尾的所有記錄
??A4: 求 A3 中所有記錄收盤價的平均值

?

求滿足條件的記錄的行號

??示例 1： 計算招商銀行 (600036)2017 年經(jīng)過多少交易日收盤價達到 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:

? 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:

??A2: 數(shù)據(jù)按交易日從小到大排序
??A3: 從后往前查找交易日在 2017-01-01 之前的最后一條記錄在序列中的行號
??A4: 求 2016 年收盤價
??A5: 求 2017 年收盤價，其中 A2.m(-1) 取倒數(shù)第 1 條記錄，即 2017 年最后一個交易日對應的記錄

?
??示例 3： 列出 2017 年信息發(fā)展 (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:

??A3: 求出 2017 年交易量超 250 萬股所有記錄的行號
??A4: 根據(jù)行號計算相應的日期、收盤價、交易量、漲幅

?

求最大值或最小值所在記錄的行號

??示例 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:

??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:

??A3: 從后往前找最大收盤價在序列中的行號
??A4: 從后往前找最小收盤價在序列中的行號

?

有序集合間的對位計算

??示例 1： 求 2018 年 3 月 6 日到 8 日創(chuàng)業(yè)板指 (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:

??A2: 依次查詢 399006 和 399001 從 2018 年 3 月 5 日到 8 日的交易數(shù)據(jù)
??A4: 依次計算 A2 中 2 個序表從第 2 條記錄到第 4 條記錄的漲幅，也就是 399006 和 399001 從 2018 年 3 月 6 日到 8 日的每天漲幅
??A5: 對位相減，即可算出每日相對收益率

SPL 優(yōu)勢

• 有庫寫 SQL，沒庫寫 SPL

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

• 常用無憂，不花錢就能取得終身使用權的入門版

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

• 技術文檔和社區(qū)支持

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