# Overcome SQL Headache – Generating a Sequence

## 1. Generating a continuous integer sequence

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：

A | |
---|---|

1 | =to(1,7) |

A1: Create a sequence consisting integers from 1 to 7

**Example 1**: As Hundred Fowls Problem asks: If a rooster is worth 5 coins, a hen 3 coins, and 3 chicks together are worth 1 coin. How many roosters, hens and chicks totaling 100 can be bought for 100 coins?

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 Rooster, jm.n Hen, jc.n Chick

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(Rooster,Hen,Chick) |

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: Create an integer sequence from 1 to 20;

A2: Create an integer sequence from 1 to 33;

A3: Create an integer sequence from 1 to 99 with the step being 3;

A4: Create a table sequence consisting of fields Rooster, Hen, and Chick;

A5: Perform nested loop through members in A1, A2 and A3. If *A1’s member + A2’s member + A3’s member ==100 & A1’s member *5+A2’s member *3+A3’s member/3==100*, insert them to A4’s table sequence.

**Example 2**: Convert colon-delimited strings in certain columns into multiple rows.

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: Create a table sequence whose data structure is (k,f) and add two records - (1, “a1:a2:a3) and (2,”b1:b2”) to it;

A2: Separate each value of A1’s f field into a sequence by colon and reassign the sequence members to the field;

A3: Create a table sequence whose data structure is (k,f) over each of A1’s records, generate records in the form of (A1.k,member of f field) according to members of each f field value, and insert them into the newly-created sequence.

## 2. Generating a continuous date sequence

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

**Example**: List sales of each day from 2015-01-03 to 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: Align A2’s records to A3 according to d field values;

A5: Create a table sequence according to the correspondence between A4 and A3.

## 3. Generating a continuous sequence of workdays(excluding Saturdays and Sundays)

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: Create a date sequence without Saturdays and Sundays from 2018-10-03 to 2018-10-09;

A2: Create a table sequence consisting of date and the corresponding day according to A1.

## 4. Generating a table from a sequence

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