SQL难点解决：记录的引用

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写法：

+查看SPL语句

示例 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写法：

+查看SPL语句

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);

SPL写法：

+查看SPL语句

示例 2：计算招商银行 (600036) 上一周的涨幅(考虑停牌)

MySQL8:

with t1 as (select * from stktrade where sid='600036'),
t11 as (select max(tdate) tdate from t1),
t2 as (select subdate(tdate, weekday(tdate)+3)m from t11),
t3 as (select max(tdate) m from t1,t2 where t1.tdate<=t2.m),
t4 as (select subdate(m, weekday(m)+3)m from t3),
t5 as (select max(tdate) m from t1,t4 where t1.tdate<=t4.m)
select s1.close/s2.close-1
from (select * from t1,t3 where t1.tdate=t3.m) s1,
(select * from t1,t5 where t1.tdate=t5.m) s2;

SPL写法：

+查看SPL语句

示例 3：重叠部分不重复计数时求多个时间段包含的总天数

MySQL8:

with 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 as (select *, row_number() over(order by start,end desc) rn from t),
t2 as (select * from t1 where not exists(select * from t1 s where s.rn<t1.rn and s.end>=t1.end))

select sum(end-start+1) from t2;

SPL写法：

+查看SPL语句

示例 4：列出超 42% 人口使用的语言有 2 种以上的国家里使用人口超 42% 的语言的相关信息

MySQL8:

with t as (select * from world.countrylanguage where percentage>=42),
t1 as (select countrycode, count(*) cnt from t
group by countrycode having cnt>=2)

select t.* from t join t1 using (countrycode);

SPL写法：

+查看SPL语句

3、求前 n 个表达式值最小的记录

示例 1：计算招商银行 (600036)2017 年成交量最大的 3 天交易信息

MySQL8:

select * from stktrade
where sid='600036' and tdate between '2017-01-01' and '2017-12-31'
order by volume desc limit 3;

SPL写法：

+查看SPL语句

示例 2：计算招商银行 (600036) 最近 1 天的涨幅

MySQL8:

with t as (select *, row_number() over(order by tdate desc) rn from stktrade where sid='600036')

select t1.close/t2.close-1 rise
from t t1 join t t2
where t1.rn=1 and t2.rn=2;

SPL写法：

+查看SPL语句

示例 3：计算每个国家最大城市中人口前 5 的城市的相关信息

MySQL8:

with t as (select *,row_number() over(partition by countrycode order by population desc) rn from world.city),
t1 as (select id,name,countrycode,district,population from t where rn=1)

select * from t1 order by population desc limit 5;

SPL写法：

+查看SPL语句

4、外键引用记录

示例 1：计算亚洲和欧洲人口前 3 城市的相关信息

MySQL8:

with t as (
select co.Continent, co.name CountryName, ci.name CityName, ci.Population,
row_number()over(partition by continent order by population desc) rn
from world.country co join world.city ci on co.code=ci.countrycode
where continent in ('Asia','Europe')
)

select Continent, group_concat(cityname,',',countryname, ',', population order by population desc separator ';') Cities 
from t
where rn<=3
group by continent;

SPL写法：

+查看SPL语句

示例 2：以“上级姓名 / 下级姓名”的形式返回指定雇员的所有上级

MySQL8:

with recursive emp(id,name,manager_id) as (
select 29,'Pedro',198
union all select 72,'Pierre',29
union all select 123,'Adil', 692
union all select 198,'John',333
union all select 333,'Yasmina',null
union all select 692,'Tarek', 333), 

t2(id,name,manager_id,path) as(
select id,name,manager_id,cast(name as char(400))
from emp where id=(select manager_id from emp where id=123)

union all

select t1.id,t1.name, t1.manager_id, concat(t1.name,'/',t2.path)
from t2 join emp t1 on t2.manager_id=t1.id)
select path from t2 where manager_id is null;

SPL写法：

+查看SPL语句