【摘要】
SQL 虽然是针对记录的集合进行运算, 但在记录的多次利用以及有序运算却经常要重复计算,效率不佳。而集算器 SPL 则要直观许多,可以按自然思维习惯写出运算。这里对 SQL 和集算器 SPL 在记录的利用及有序运算方面进行了对比,如果需要了解更多,请前往乾学院:SQL 难点解决:记录的引用!
1、 求最大值 / 最小值所在记录
示例 1:计算招商银行 (600036)2017 年收盘价达到最低价时的所有交易信息。
MySQL8:
with t as (select * from stktrade where sid='600036'
and tdate between '2017-01-01' and '2017-12-31')
select * from t where close=(select min(close) 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'") |
3 |
=A2.minp@a(close) |
A3: 计算 A2 中 close 为最小值的所有记录
示例 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)),
t3 as (select * from t1 where rn=(select max(rn) from t1)),
t4 as (select * from t2 where rn=(select min(rn) from t2))
select abs(datediff(t3.tdate,t4.tdate)) inteval
from t3,t4;
集算器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.minp@z(close) |
4 |
=A2.maxp(close) |
5 |
=abs(A3.tdate-A4.tdate) |
A3: 从后往前查找 close 第 1 个最小值的记录
A4: 从前往后查找 close 第 1 个最大值的记录
2、 查找满足条件的记录
示例 1:计算招商银行 (600036)2017 年收盘价超过 25 元时的交易信息
MySQL8:
with t as (select * from stktrade where sid='600036' and tdate between '2017-01-01' and '2017-12-31')
select * from t
where tdate=(select min(tdate) from t where close>=25);