1、 對位分組
示例 1：按順序分别列出使用 Chinese、English、French 作為官方語言的國家數量
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;
注意：表的字元集和資料庫會話的字元集要保持一緻。
(1) show variables like 'character_set_connection'檢視目前會話字元集
(2) show create table world.countrylanguage檢視表的字元集
(3) set character_set_connection=[字元集]更新目前會話字元集
集算器SPL:
A1: 連接配接資料庫
A2: 查詢出所有官方語言的記錄
A3: 需要列出的語言
A4: 将所有記錄按Language對位到A3相應位置
A5: 構造以語言和使用此語言為官方語言的國家數量的序表
示例 2：按順序分别列出使用 Chinese、English、French 及其它語言作為官方語言的國家數量
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
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;
A4: 将所有記錄按Language對位到A3.to(3)相應位置，并追加一組用于存放不能對位的記錄
A5: 第4組計算不同CountryCode的數量
2、 枚舉分組
示例 1：按順序列出各類型城市的數量
with t as (select * from world.city where CountryCode='CHN'),
segment(class,start,end) as (select 'tiny', 0, 200000
union all select 'small', 200000, 1000000
union all select 'medium', 1000000, 2000000
union all select 'big', 2000000, 100000000
select class, count(1) cnt
from segment s join t on t.population>=s.start and t.population
group by class, start
order by start;
A3: ${…}宏替換，以大括号内表達式的結果作為新表達式進行計算，結果為序列["?<200000","?<1000000","?<2000000","?<100000000"]
A5: 針對 A2 中每條記錄，尋找 A3 中第 1 個成立的條件，并追加到對應的組中
示例 2：列出華東地區大型城市數量、其它地區大型城市數量、非大型城市數量
with t as (select * from world.city where CountryCode='CHN')
select 'East&Big' class, count(*) cnt
from t
where population>=2000000
and district in ('Shanghai','Jiangshu', 'Shandong','Zhejiang','Anhui','Jiangxi')
select 'Other&Big', count(*)
and district not in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi')
select 'Not Big', count(*)
where population<2000000;
A5: enum@n将不滿足 A4 中所有條件的記錄存放到追加的最後一組中
示例 3：列出所有地區大型城市數量、華東地區大型城市數量、非大型城市數量
select 'Big' class, count(*) cnt
and district in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi')
select 'Not Big' class, count(*) cnt
A6: 若A2中記錄滿足A4中多個條件時，enum@r會将其追加到對應的每個組中
3、 傳回值直接作為序号進行定位分組
示例 1: 按順序列出各類型城市的數量
MySQL8: 參見“枚舉分組”中 SQL
A5: 先計算 A2.Population 在 A3 中段号，然後根據段号進行定位分組
4、 原序保持下的相鄰記錄分組
示例 1: 列出前 10 屆奧運金牌榜 (olympic 表中隻有曆屆成績前 3 名的資訊，且沒有獎牌完全相同的情況)
with t1 as (select ,rank() over(partition by game order by gold1000000+silver*1000+copper desc) rn from olympic where game<=10)
select game,nation,gold,silver,copper from t1 where rn=1;
A3: 按原序分到各組，每組取第 1 條記錄組成新序表
示例 2: 求奧運會國家總成績蟬聯第 1 的最長屆數
with t1 as (select ,rank() over(partition by game order by gold1000000+silver*1000+copper desc) rn from olympic),
t2 as (select game,ifnull(nation<>lag(nation) over(order by game),0)neq from t1 where rn=1),
t3 as (select sum(neq) over(order by game) acc from t2),
t4 as (select count(acc) cnt from t3 group by acc)
select max(cnt) cnt from t4;
t1: 求出成績排名
t2: 列出曆屆第1名，并根據nation是否與上屆不同置标志neq(不同置1，相同置0)
t3: 累積标志neq到acc，可以保證相鄰nation相同的acc相同，不相鄰nation的acc不相同
A4: 将相鄰nation相同的記錄按原序分到同組
A5: 求各組長度的最大值即最大屆數
示例3：列出奧運會總成績排名第一最長蟬聯時的各屆資訊
MySQL:
t2 as (select *,ifnull(nation<>lag(nation) over(order by game),0)neq from t1 where rn=1),
t3 as (select *, sum(neq) over(order by game) acc from t2),
t4 as (select acc,count(acc) cnt from t3 group by acc),
t5 as (select * from t4 where cnt=(select max(cnt) cnt from t4))
select game,nation,gold,silver,copper from t3 join t5 using (acc);
A5: 求出長度最大組
示例 4：求奧運會前3名金牌總數連續增長的最大屆數
with t1 as (select game,sum(gold) gold from olympic group by game),
t2 as (select game,gold, gold<=lag(gold,1,-1) over(order by game) lt from t1),
t3 as (select game, sum(lt) over(order by game) acc from t2),
t4 as (select count(*) cnt from t3 group by acc)
select max(cnt)-1 cnt from t4;
A3: 根據條件值按原序分組，若gold小于等于上一個gold則産生新分組