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

問題介紹

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

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:

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

??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% 的國家代碼，并轉成序列
??A5: A3 中所有序列交集

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

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:

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

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

? 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: 數據按交易日從小到大排序
??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:

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

??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 日創業板指 (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 日的交易數據
??A4: 依次計算 A2 中 2 個序表從第 2 條記錄到第 4 條記錄的漲幅，也就是 399006 和 399001 從 2018 年 3 月 6 日到 8 日的每天漲幅
??A5: 對位相減，即可算出每日相對收益率

SPL 優勢

• 有庫寫 SQL，沒庫寫 SPL

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

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

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

• 技術文檔和社區支持

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