SQL 雖然可以對集合中的記錄進行循環計算, 但在循環計算過程中利用中間變量、同時計算多個值、前后記錄訪問、減少循環次數等方面差強人意。而集算器 SPL 則要直觀許多，可以按自然思維習慣寫出運算。這里對 SQL 和集算器 SPL 在循環計算方面進行了對比。

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:

	A
1	=to(20)
2	=to(2,20/2)
3	=A2.(A1.step(~,~*2))
4	=A1.to(2,)\A3.conj()

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:

	A
1	=connect("mysql")
2	=A1.query@x("select ? * from stktrade where sid='000651'order by tdate")
3	=a=0,A2.max(a=if(close>close[-1],a+1,0))

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:

	A
1	=connect("mysql")
2	=A1.query@x("select ? * from stktrade where sid='300469'order by tdate")
3	=A2.top@1(1,(if(tdate==date("2018-02-14"),A4=close),-close))
4	=1+@/A3

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:

	A
1	=connect("mysql")
2	=A1.query@x("select ? tdate,volume,0 cum from stktrade where sid='300469'and tdate between ?'2018-01-01'and'2018-01-10'order by tdate")
3	=A2.run(cum=cum[-1]+volume)

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:

	A
1	=connect("mysql")
2	=A1.query@x("select ? tdate,volume from stktrade where sid='300469'and tdate>='2018-01-01'? order by tdate")
3	=A2.iterate((@+=volume,~~+1),0,@>1000000)

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:

	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.sort(start)
4	=A3.iterate((@=max(@, ? end[-1]), ~~ + if(@>=end:0, @<start:end-start+1, end-@)), 0)

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:

	A
1	=connect("mysql")
2	=A1.query("select ? * from stocks where sid in ('600036','300469') order by sid,tdate")
3	=A1.query@x("select ? * from stktrade where sid in ('600036','300469') and tdate between ?'2018-06-11'and'2018-06-15'order by sid,tdate")
4	=A3.derive((p=A2.pselect@bs(sid:A3.sid,tdate:A3.tdate), ? p=if(p<0,-p-1,p), A2(p).circulation):circ, iterate(~~+volume/circ/10000,0;sid):rate)

AA2: 流通股本信息按股票代碼和日期排序

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:

	A
1	=connect("mysql")
2	=A1.query@x("select ? tdate,close from stktrade where sid='600036'order by tdate")
3	=A2.pselect@a(tdate>=date("2018-01-01") ? && tdate<=date("2018-01-10"))
4	=A2(A3).derive(A2.calc(A3(#),avg(close[-19:0])):ma20)

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:

	A
1	=connect("mysql")
2	=A1.query("select ? * from world.country")
3	=A1.query@x("select ? * from world.countrylanguage where isofficial='T'")
4	=A3.group(CountryCode).maxp@a(~.len())
5	=A4.news(A2.select(Code:CountryCode);Name,Population,HeadOfState,A4.~.len():Num)

A4: 將將各國語言按 CountryCode 分組，然后取出成員數最多的所有組

A5: A4.news對 A4 中每組循環，每組先在 A2 查找對應的國家記錄，然后再構造新記錄，Num 字段賦值為 A4 中當前組的成員組

轉載于:https://blog.51cto.com/12749034/2374377

總結

以上是生活随笔為你收集整理的SQL 难点解决：循环计算的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。