Shulou(Shulou.com)06/03 Report--

Although SQL operates on the set of records, it is often repeated in the repeated use of records and ordered operations, so the efficiency is not good. The aggregator SPL is much more intuitive and can write operations according to natural thinking habits. In this paper, a comparison is made between SQL and the aggregator SPL in the use of records and ordered operations.

1. Find the record where the maximum / minimum value is located

Example 1: calculate all the transaction information of China Merchants Bank (600036) when the closing price reached the lowest price in 2017.

MySQL8:

With t as (select * from stktrade where sid='600036'

And tdate between '2017-01-01-01' and '2017-12-31')

Select * from t where close= (select min (close) from t)

Aggregator SPL:

A

one

= connect ("mysql")

two

= A1.query@x ("select * from stktrade where sid='600036'and tdate between'2017-01-01'and'2017-12-31'")

three

= A2.minp@a (close)

A3: calculate all records in A2 with a minimum close

Example 2: calculate the natural days between the last lowest price and the earliest highest price of China Merchants Bank (600036) in 2017

MySQL8:

With t as (select *, row_number () over (order by tdate) rn from stktrade

Where sid='600036' and tdate between '2017-01-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.tdateret4.tdate)) inteval

From t3,t4

Aggregator SPL:

A

one

= connect ("mysql")

two

= A1.query@x ("select * from stktrade where sid='600036'and tdate between'2017-01-01'and'2017-12-31'order by tdate")

three

= A2.minp@z (close)

four

= A2.maxp (close)

five

= abs (A3.tdate-A4.tdate)

A3: find the record of the 1st minimum value of close from back to front

A4: find the record of the 1st maximum value of close from the back

2. Find records that meet the criteria

Example 1: calculate the transaction information of China Merchants Bank (600036) when the closing price exceeds 25 yuan in 2017.

MySQL8:

With t as (select * from stktrade where sid='600036' and tdate between '2017-01-01-01' and '2017-12-31')

Select * from t

Where tdate= (select min (tdate) from t where close > = 25)

Aggregator SPL:

A

one

= connect ("mysql")

two

= A1.query@x ("select * from stktrade where sid='600036'and tdate between'2017-01-01'and'2017-12-31'order by tdate")

three

= A2.select@1 (close > = 25)

A3: find the first record with a closing price of more than 25 yuan after going there.

Example 1: calculate the increase of China Merchants Bank (600036) in the previous week (consider suspension)

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

Select t.* from t join T1 using (countrycode)

Aggregator SPL:

A

one

= connect ("mysql")

two

= A1.query@x ("select * from world.countrylanguage where percentage > = 42")

three

= A2.group (CountryCode)

four

= A3.select (~ .len () > = 2) .conj ()

A3: grouped by country code

A4: summation set of groups with more than 2 members

3. Find the record with the lowest value of the first n expressions

Example 1: calculate the 3-day trading information of China Merchants Bank (600036) with the largest trading volume in 2017.

MySQL8:

Select * from stktrade

Where sid='600036' and tdate between '2017-01-01' and '2017-12-31'

Order by volume desc limit 3

Aggregator SPL:

A

one

= connect ("mysql")

two

= A1.query@x ("select * from stktrade where sid='600036'and tdate between'2017-01-01'and'2017-12-31'")

three

= A2.top (3b)

A3: sort by-volume, and then take the first 3 records

Example 2: calculate the increase of China Merchants Bank (600036) in the last 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

Aggregator SPL:

A

one

= connect ("mysql")

two

= A1.query@x ("select * from stktrade where sid='600036'")

three

= A2.top (2)

four

= A3 (1) .close / A3 (2) .close-1

A3: take the last 2 records in reverse order according to the transaction date (the effect is equivalent to A2.top). The transaction serial number of the last day is 1, and the transaction number of the penultimate day is 2.

A4: calculate the increase

Example 3: calculate information about the top 5 populous cities in the largest city in each country

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

Aggregator SPL:

A

one

= connect ("mysql")

two

= A1.query@x ("select * from world.city")

three

= A2.groups (CountryCode; top@1 (1th talk population): city)

four

= A3. (city) .top (5th Musi population)

A3: grouped by country, grouped back to the record of the most populous city

A4: take the city records of the top 5 people in the largest cities in all countries

4. Foreign key reference record

Example 1: calculate information about the top three cities 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 rnA3.switch@i (CountryCode,A4)

six

= A3.group (CountryCode.Continent:Continent;~.top (3b). (Name/ "," / CountryCode.Name/ "," / Population) .concat (";"): Cities)

A4: set the key of the sequence table in A2 to the Code field

A5: convert the CountryCode field of the sequence table in A3 to the corresponding record in A2, and delete it if there is no corresponding record.

A6: first group according to Continent, then calculate the top 3 cities in each group, then put the city name, country name and population in each record into a string, and finally connect the strings in each group.

Example 2: returns all superiors of a specified employee in the form of "superior name / subordinate name"

MySQL8:

With recursive emp (id,name,manager_id) as (

Select 29pr Pedrology dwelling 198

Union all select 72 is famous Pierresia 29

Union all select 123, 692

Union all select 198pr John Jr 333

Union all select 333 is invalid Yasmina

Union all select 692, 333

), T2 (id,name,manager_id,path) as (

Select id,name,manager_id,cast (name as char)

From emp where id= (select manager_id from emp where id=123)

Union all

Select t1.idrecoveryt1.name, t1.manager_id, concat (t1.namerecoveryhand handwriting, t2.path)

From T2 join emp T1 on t2.manager_id=t1.id)

Select path from t2 where manager_id is null

Aggregator SPL:

A

one

= connect ("mysql")

two

= A1.query@x ("with emp (id,name,manager_id) as (select 29 union all select 72) select * from emp"). 29 union all select 123 from emp.

three

= A2.switch (manager_id, A2:id)

four

= A2.select@1 (id:123)

five

= A4.manager_id.prior (manager_id)

six

= A5.rvs (). (name) .concat ("/")

A3: convert manager_id to the record where the manager_id equivalent id is located in A2

A4: find records with an id of 123

A5: list A4 superiors, superiors, and... Up to the highest level (i.e. manager_id is null)

A6: arrange all superiors from the highest to the lowest, and then connect the names of all superiors with / separation

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.