SQL 难点解决：循环计算

【摘要】

        SQL 虽然可以对集合中的记录进行循环计算, 但在循环计算过程中利用中间变量、同时计算多个值、前后记录访问、减少循环次数等方面差强人意。而集算器 SPL 则要直观许多，可以按自然思维习惯写出运算。这里对 SQL 和集算器 SPL 在循环计算方面进行了对比，如果需要了解更多，请前往乾学院：SQL 难点解决：循环计算!

1、    求 20 以内的质数

MySQL8:

with recursive t(n) as (

select 1

union all select n+1 from t where n<20)

select n from t

where n!=1 and n not in (select t1.n*t2.n from t t1 join t t2 on t1.n<=t2.n where t1.n>1 and t2.n between 2 and 20/2 and t1.n*t2.n<=20);

集算器SPL:

A3: 针对A2中每一个成员，求出它在20以内的n倍数(n>1)，并将所有计算结果组成序列返回

A4: 除去1和所有20以内的合数即为20以内的素数，其中A3.conj()求出20以内的合数

2、    求格力电器 (000651) 最长上涨天数

MySQL8:

with recursive t1 as (select *,row_number() over(order by tdate) rn from stktrade where sid='000651'),

t2 as (select *,0 rise from t1 where rn=1

union all

select t1.*, if(t1.close>t2.close,t2.rise+1,0) from t1 join t2 on t1.rn=t2.rn+1)

select max(rise) from t2;

集算器SPL:

A2: 按交易日期升序排序

A3: 循环计算 a，若收盘价比上一交易日收盘价高则加 1，否则重新置为 0，然后求每条记录算出来的 a 的最大值

3、    求信息发展 (300469)2018 年 2 月 14 日距历史最高收盘价跌幅

MySQL8:

select 1-t2.close/t1.close fall

from (select max(close) close from stktrade where sid='300469') t1,

(select close from stktrade where sid='300469' and tdate='2018-02-14') t2;

集算器SPL:

A3: 在循环计算A2中-close最小值(即close最大值)的过程中，将2018年2月14日收盘价close设到A4

A5: @表示本格格值

4、    求信息发展 (300469)2018 年 1 月 1 日到 10 日每天累积成交量

MySQL8:

select tdate,volume,sum(volume) over(order by tdate) cum

from stktrade

where sid='300469' and tdate between '2018-01-01' and '2018-01-10';

集算器SPL:

A3: 针对A2中每条记录，将volume列重新赋值为上条记录的cum列和本条记录的volume列之和，最后返回A2

5、    从 2018 年 1 月 1 日起信息发展 (300469) 经过多少交易日总成交量达到 1000000 股

MySQL8:

with t as (select row_number() over(order by tdate) rn,

sum(volume) over(order by tdate) cum

from stktrade

where sid='300469' and tdate>='2018-01-01')

select min(rn) from t where cum>=1000000;

集算器SPL:

A3: A.iterate(x,a,c)相当于变量初始赋值为a，然后对A中成员循环，每次将x的计算结果赋给，再判断c是否为真，若真则继续循环，否则返回~~。@表示本格格值，在此用作中间变量。

6、    重叠部分不重复计数时求多个时间段包含的总天数

MySQL8:

with tt(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'),

t as (select * from tt order by start),

tmp as (

select t.start, t.end, @m:=if(@m>@p,@m,@p) m,@p:=end,

case when @m>end then 0 when @m<start then datediff(end,start)+1 else datediff(end,@m) end days

from t,(select @m:=date'1970-01-01',@p:=@m) s

)

select sum(days) from tmp;

说明：@m为本记录以前的结束日期最大值，@p为本记录结束日期， days为本记录自以前最大结束日期后经过的天数。此问题的其它解决方法可参考《SQL 难点解决：集合及行号》和《SQL 难点解决：记录的引用》中相同示例。

集算器SPL:

A4: A.iterate函数无第3个参数表示循环A中所有成员

7、    列出信息发展 (300469) 和招商银行 (600036) 从 2018 年 6 月 11 日到 15 日的交易信息及累积换手率

MySQL8:

with k as (select sid,circulation,tdate start,lead(tdate,1, date_add(now(),interval 1 day))over(partition by sid order by tdate) end

from stocks)

select t.*, k.circulation circ, sum(t.volume/k.circulation/10000) over(partition by sid order by tdate) rate

from stktrade t join k on t.sid=k.sid and t.tdate>=k.start and t.tdate<k.end

where t.sid in ('300469','600036') and t.tdate between '2018-06-11' and '2018-06-15';

集算器SPL:

A2: 流通股本信息按股票代码和日期排序

A3: 交易信息按股票代码和日期排序

A4: 先用A2.pselect@bs(sid:A3.sid,tdate:A3.tdate)查找对应日期的流通股本，若找到则返回行号，否则返回-插入位置，然后用p=if(p<0,-p-1,p)计算出当前交易日对应的流通股本在A2中行号，最后取出流通股本并赋值给circ列；在循环函数中，iterate(x,a;G)在初始或G发生变化时将a赋值给变量，然后将x的计算结果赋给。

8、    列出招商银行 (600036)2018 年 1 月 1 日到 10 日每天的 20 日收盘均价

MySQL8:

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

t1 as (select * from t where tdate between '2018-01-01' and '2018-01-10')

select t1.tdate, t1.close, avg(t.close) ma20

from t1 join t on t.rn between t1.rn-19 and t1.rn

group by t1.tdate;

集算器SPL:

A3: 查找所有符合的记录在A2中的行号

A4: A2(A3)根据A3里的行号从A2中取出对应的记录，然后再派生出新记录，新记录复制原记录的字段及值，并增加一列ma20，其值为A2中原记录的20日收盘均价

9、    列出官方语言最多的国家的名称、人口、元首及官方语言数

MySQL8:

select Name, Population, HeadOfState, top.Num

from world.country

join ( 

  select countrycode, count(*) as num

  from world.countrylanguage

  where isofficial='T'

  group by countrycode

  having num = (

    select max(summary.n)

    from  (

      select countrycode, count(*) as n

      from world.countrylanguage 

      where isofficial='T' 

      group by countrycode

    ) as summary

  )

) as top on country.code=top.countrycode;

集算器SPL:

A4: 将将各国语言按 CountryCode 分组，然后取出成员数最多的所有组

A5: A4.news对 A4 中每组循环，每组先在 A2 查找对应的国家记录，然后再构造新记录，Num 字段赋值为 A4 中当前组的成员组