相关文章：
1. 和集
示例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<end)
select 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中不重复日期的个数
2. 差集
示例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));
说明：此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的组
3. 交集
示例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);
说明：此例只是演示如何求解多个集合的交集
集算器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中所有序列交集
4. 根据行号取数据
示例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);
集算器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;
集算器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中所有记录收盘价的平均值
5. 求满足条件的记录的行号
示例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;
集算器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;
集算器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;
集算器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: 根据行号计算相应的日期、收盘价、交易量、涨幅
6. 求最大值或最小值所在记录的行号
示例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;
集算器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;
集算器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: 从后往前找最小收盘价在序列中的行号
7. 有序集合间的对位计算
示例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;
集算器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: 对位相减，即可算出每日相对收益率
相关文章：
239
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
