1. Get records holding the max/min value

  Example 1: Get all transaction information at the lowest closing price in 2017 for stock 600036.

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 script:

	A
1	=connect("mysql")
2	=A1.query@x("select *   from stktrade where sid='600036'and  tdate   between'2017-01-01'and'2017-12-31'")
3	=A2.minp@a(close)

  A3: Get all records with the smallest close value from A2.

  

 

  Example 2: Find how many days between the last lowest closing price and the first highest closing price in 2017 for stock 600036.

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 script:

	A
1	=connect("mysql")
2	=A1.query@x("select *   from stktrade where sid='600036'and  tdate   between'2017-01-01'and'2017-12-31'order by tdate")
3	=A2.minp@z(close)
4	=A2.maxp(close)
5	=abs(A3.tdate-A4.tdate)

  A3: Find the record with the first lowest closing price from back to front.

  A4: Find the record with the first highest closing price from front to back.

  

 

2. Get records meeting the specified condition

  Example 1: Get transaction records where the closing price is over 25 CNY in 2017 for stock 600036.

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 script:

	A
1	=connect("mysql")
2	=A1.query@x("select *   from stktrade where sid='600036'and  tdate   between'2017-01-01'and'2017-12-31'order by tdate")
3	=A2.select@1(close>=25)

  A3: Find the record with the first record where closing price is over 25 CNY from front to back.

  

 

  Example 2: Calculate the growth rate in the past week for stock 600036 (Take delisting into consideration).

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 script:

	A
1	=connect("mysql")
2	=A1.query@x("select * from   stktrade where sid='600036'order by tdate")
3	=pdate@w(A2.m(-1).tdate)
4	=A2.select@z1(tdate<=A3-2)
5	=pdate@w(A4.tdate)
6	=A2.select@z1(tdate<=A5-2)
7	=A4.close/A6.close-1

  A3: Find the date on Sunday of the week where the last transaction date falls in (Sunday is a week’s first day).

  A4: Find the first transaction record before last Friday from back to front, i.e. the last one in the past transaction week.

  A5: Get the date on Sunday of the past transaction week.

  A6: Find the first transaction record on the latest Friday before the past transaction week from back to front, i.e. the last one in the week before last week.

  

 

  Example 3: Find the number of days in multiple time periods (Count once for overlapping periods)

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 script:

	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,-end)
4	=A3.select(end>max(end[:-1]))
5	=A4.sum(if(start>end[-1],interval(start,end)+1,interval(end[-1],end)))

  A3: Sort records by start time in ascending order and by end time in descending order.

  A4: Get records where the end time is later than end values in all the previous records.

  A5: Perform a sum. max(start,end[-1]) gets the start time and the earliest end date before it. interval() function calculates the number of days between two dates.

  

  Note: We can also write A4 as =A3.run(end=max(end,end[-1])).

 

  Example 4: Get information of languages spoken by over 42% population in countries where two languages exist.

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 script:

	A
1	=connect("mysql")
2	=A1.query@x("select  * from world.countrylanguage where   percentage>=42")
3	=A2.group(CountryCode)
4	=A3.select(~.len()>=2).conj()

  A3: Group A2’s records by CountryCode.

  A4: Concatenate groups where number of members is greater than 2.

  

 

3. Get top n records that make value of the specified expression the smallest.

  Example 1: Get transaction records in the 3 days with the highest turnover in 2017 for stock 600036.

MySQL8:

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

SPL script:

	A
1	=connect("mysql")
2	=A1.query@x("select *   from stktrade where sid='600036'and  tdate   between'2017-01-01'and'2017-12-31'")
3	=A2.top(3;-volume)

  A3: Sort A2’s records by volume in descending order and then get the first 3 ones.

  

 

  Example 2: Calculate the growth rate of stock 600036 in the past one day.

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 script:

	A
1	=connect("mysql")
2	=A1.query@x("select *   from stktrade where  sid='600036'")
3	=A2.top(2,-tdate,~)
4	=A3(1).close/A3(2).close-1

  A3: Sort A2’s records by transaction dates in descending order and get the last 2 records – which is equivalent to A2.top(2;-tdate). The number of transaction in the last day is 1 and that in the second to last day is 2.

  A4: Calculate the growth rate.

 

  Example 3: Get information of the top 5 cities in population in the world.

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 script:

	A
1	=connect("mysql")
2	=A1.query@x("select *   from world.city")
3	=A2.groups(CountryCode;   top@1(1;-Population):city)
4	=A3.(city).top(5;-Population)

  A3: Group A2’s records by CountryCode and return city records with the most population.

  A4: Get city records where population ranked in top 5.

  

 

4. Reference records via the foreign key.

  Example 1: Get information of cities whose population ranks top 3 in Asia and Europe.

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 script:

	A
1	=connect("mysql")
2	=A1.query("select  * from world.country where continent in   ('Asia','Europe')")
3	=A1.query@x("select *   from world.city")
4	=A2.keys(Code)
5	>A3.switch@i(CountryCode,A4)
6	=A3.group(CountryCode.Continent:Continent;~.top(3;-Population).   (Name/","/CountryCode.Name/","/Population).concat(";"):Cities)

  A4: Set Code field as the key of A2’s table sequence.

  A5: Replace CountryCode field values in A3’s table sequence with corresponding records in A2 while deleting records with no matches in A2.

  A6: Group A3’s table sequence by Continent, get records of cities that rank top 3 in population from each group and join up city names, country names and population as a string, and then join these strings together.

  

 

  Example 2: List all superiors of a certain employee in the form of “superior name/employee name”.

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 script:

	A
1	=connect("mysql")
2	=A1.query@x("with  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)   select * from emp")
3	=A2.switch(manager_id, A2:id)
4	=A2.select@1(id:123)
5	=A4.manager_id.prior(manager_id)
6	=A5.rvs().(name).concat("/")

  A3: Replace manager_id values with A2’s records where id values are the same with manager id values.

  A4: Get the record where id is 123.

  A5: Get A4’s manager, manager’s manager, and so on until the highest manager (whose manager_id value is null).

  A6: Order the managers from high to low in positions and join up all A4’s superiors by slash “/”.