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

1. Parity grouping

Example 1: Number of countries using Chinese, English, French as official languages in order

MySQL8:

with t(name,ord) as (select 'Chinese',1

union all select 'English',2

union all select 'French',3)

select t.name, count(countrycode) cnt

from t left join world.countrylanguage s on t.name=s.language

where s.isofficial='T'

group by name,ord

order by ord;

Note: Keep the character set of the table consistent with the character set of the database session.

(1)show variables like 'character_set_connection' View the current session character set

(2)show create table world.countrylanguage View table character set

(3)set character_set_connection=[character set] Update the character set of the current session

Concentrator SPL:

A1=connect("mysql")2=A1.query@x("select * from world.countrylanguage where isofficial='T'")3[Chinese,English,French]4=A2.align@a(A3,Language)5=A4.new(A3(#):name, ~.len():cnt)A1: connect to database

A2: Find records in all official languages

A3: Languages to be listed

A4: Align all records to the corresponding position of A3 according to Language

A5: Construct an order table by language and the number of countries that use that language as an official language

Example 2: Number of countries using Chinese, English, French, and other languages as official languages in order

MySQL8:

with t(name,ord) as (select 'Chinese',1 union all select 'English',2

union all select 'French',3 union all select 'Other', 4),

s(name, cnt) as (

select language, count(countrycode) cnt

from world.countrylanguage s

where s.isofficial='T' and language in ('Chinese','English','French')

group by language

union all

select 'Other', count(distinct countrycode) cnt

from world.countrylanguage s

where isofficial='T' and language not in ('Chinese','English','French')

)

select t.name, s.cnt

from t left join s using (name)

order by t.ord;

Concentrator SPL:

A1=connect("mysql")2=A1.query@x("select * from world.countrylanguage where isofficial='T'")3[Chinese,English,French,Other]4=A2.align@an(A3.to(3),Language)5=A4.new(A3(#):name, if(#=s.start and t.population=2000000

and district in ('Shanghai','Jiangshu', 'Shandong','Zhejiang','Anhui','Jiangxi')

union all

select 'Other&Big', count(*)

from t

where population>=2000000

and district not in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi')

union all

select 'Not Big', count(*)

from t

where population=2000000 && A3.contain(? (2)), ? (1)>=2000000 && ! A3.contain(? (2))]5[East&Big,Other&Big, Not Big]6= A2.enumer@n (A4, [Population,District])7=A6.new(A5(#):class, A6(#).len():cnt)A5: enum@ nStore records that do not satisfy all conditions in A4 in the last group added

Example 3: List the number of large cities in all regions, the number of large cities in East China, and the number of non-large cities

MySQL8:

with t as (select * from world.city where CountryCode='CHN')

select 'Big' class, count(*) cnt

from t

where population>=2000000

union all

select 'East&Big' class, count(*) cnt

from t

where population>=2000000

and district in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi')

union all

select 'Not Big' class, count(*) cnt

from t

where population=2000000, ? (1)>=2000000 && A3.contain(? (2))]5[Big, East&Big, Not Big]6= A2.enumer@rn (A4, [Population,District])7=A6.new(A5(#):class, A6(#).len():cnt)A6: If a record in A2 satisfies multiple conditions in A4, enum@r appends it to each corresponding group

3. The returned value is directly used as the serial number for positioning grouping.

Example 1: Number of cities in order

MySQL8: See SQL in "Enumeration grouping"

Concentrator SPL:

A1=connect("mysql")2=A1.query@x("select * from world.city where CountryCode='CHN'")3=[0,20,100,200]. (~*10000)4[tiny,small,medium,big]5=A2.group@n(A3.pseg(Population))6=A5.new(A4(#):class, ~.len():cnt)A5: First calculate the middle number of A2.Population in A3, and then locate and group according to the segment number.

4. Adjacent record grouping under original order preservation

Example 1: List the top 10 Olympic gold medals (the Olympic table only has information about the top 3 of all previous results, and no medals are exactly the same)

MySQL8:

with t1 as (select *,rank() over(partition by game order by gold*1000000+silver*1000+copper desc) rn from olympic where game

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.