马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
1、 生成连续整数序列 MySQL8: with recursive t(n) as (
select 1
union all
select n+1 from t where n<7
)
select * from t;
Oracle:select level n
from dual connect by level<=7;
集算器 SPL:
A1:构造从 1 到 7 的整数序列 示例 1:百鸡问题,鸡翁一值钱五,鸡母一值钱三,鸡雏三值钱一。百钱买百鸡,问鸡翁、母、雏各几 MySQL8: with recursive jg(n) as (select 1 union all select n+1 from jg where n<100/5), jm(n) as (select 1 union all select n+1 from jm where n<100/3), jc(n) as (select 3 union all select n+3 from jc where n<98) select jg.n jw, jm.n jm, jc.n jc
from jg cross join jm cross join jc where jg.n*5+jm.n*3+jc.n/3=100 and jg.n+jm.n+jc.n=100
集算器 SPL:
| A | 1 | =to(100/5)
| 2 | =to(100/3)
| 3 | =33.(~*3)
| 4 | =create(jw,jm,jc)
| 5 | >A1.run(A2.run(A3.run(if(A1.~+A2.~+A3.~==100 && A1.~*5+A2.~*3+A3.~/3==100,A4.insert(0,A1.~,A2.~,A3.~)))))
| A1:构造1到20的整数序列 A2:构造1到33的整数序列 A3:构造1到99且步长为3的整数序列 A4:创建数据结构为(jw,jm,jc)的序表 A5:对A1、A2、A3的数据进行嵌套循环,若满足于A1成员+A2成员+A3成员==100且A1成员*5+A2成员*3+A3成员/3==100则追加到A4序表中 示例2:将指定列中冒号分隔的串划分成多行 Oracle: with t(k,f) as (select 1 , 'a1:a2:a3' from dual union all select 2, 'b1:b2' from dual), t1 as (select k,f, length(f)-length(replace(f,':',''))+1 cnt from t), t2 as (select level n from dual connect by level<=(select max(cnt) from t1)), t3 as (select t1.k, t1.f, n, cnt, case when n=1 then 1 else instr(f,':',1,n-1)+1 end p1, case when n=cnt then length(f)+1 else instr(f,':',1,n) end p2 from t1 join t2 on t2.n<=t1.cnt) select k,substr(f,p1,p2-p1) f from t3 order by k; 集算器 SPL:
| A | 1 | =create(k,f).record([1,"a1:a2:a3",2,"b1:b2"])
| 2 | >A1.run(f=f.split(":"))
| 3 | =A1.(f.new(A1.k:k, ~:f))
| 4 | =A3.conj()
| A1:创建数据结构为(k,f)的序表,并追加2条记录(1, “a1:a2:a3)和(2,”b1:b2”) A2:将A1的字段f用冒号划分成序列并重新赋值给字段f A3:针对A1每条记录构造数据结构为(k,f)的序表,并根据字段f中成员构造记录(A1.k,f成员)追加到此序表中 2、 生成连续日期序列 MySQL8:
with recursive
t(d) as (select date'2018-10-03'
union all
select d+1 from t where d<date'2018-10-09')
select d,dayofweek(d) w from t;
集算器 SPL:
| A | 1 | =periods("2018-10-03", "2018-10-09")
| A1:生成2018-10-03到2018-10-09的日期序列
示例:列出2015-01-03到2015-01-07每天的销量汇总
MySQL8:
with recursive
t(d,v) as (select date'2015-01-04',30
union all select date'2015-01-06',50
union all select date'2015-01-07',50
union all select date'2015-01-03',40
union all select date'2015-01-04', 80),
s(d) as (select date'2015-01-03'
union all
select d+1 from s where d<date'2015-01-07')
select s.d, sum(t.v) v
from s left join t on s.d=t.d
group by s.d;
集算器 SPL:
| A | 1 | [2015-01-04, 30, 2015-01-06,50, 2015-01-07,50, 2015-01-03,40, 2015-01-04,80]
| 2 | =create(d,v).record(A1)
| 3 | =periods("2015-01-03", "2015-01-07")
| 4 | =A2.align@a(A3,d)
| 5 | =A4.new(A3(#):d, ~.sum(v):v)
| A4:A2中记录按字段d的值对齐到A3
A5:根据A4和A3对位构造统计后的序表
3、 生成连续的工作日(不包含周六周日)序列 MySQL8: with recursive
t(d) as (select date'2018-10-03' union all select d+1 from t where d<date'2018-10-09') select d,dayofweek(d) w from t
where dayofweek(d)<=5; 集算器 SPL:
| A | 1 | =workdays(date("2018-10-03"), date("2018-10-09"))
| 2 | =A1.new(~:d,day@w(~)-1:w)
| A1:构造从2018-10-03到2018-10-09不包含周六周日的日期序列
A2:根据A1构造日期及相应周几的序表
4、 根据序列生成表 MySQL8: with recursive t1(n) as (select 1 union all select n+1 from t1 where n<14),
t2(n, name) as (select n, concat('a',n) name from t1) select max(if(n%4=1, name, null)) f1,
max(if(n%4=2, name, null)) f2, max(if(n%4=3, name, null)) f3, max(if(n%4=0, name, null)) f4 from t2
group by floor((n+3)/4); 集算器 SPL:
| A | 1 | =to(14).("a"/~)
| 2 | =create(f1,f2,f3,f4).record(A1)
|
来源:润乾软件
|