sql一年各月差异分析（SQL难点解决直观分组）
sql一年各月差异分析（SQL难点解决直观分组）where s.isofficial='T'from t left join world.countrylanguage s on t.name=s.languageunion all select 'English' 2union all select 'French' 3)select t.name count(countrycode) cnt
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 及其它语言作为官方语言的国家数量
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;
集算器SPL:
A4: 将所有记录按Language对位到A3.to(3)相应位置，并追加一组用于存放不能对位的记录
A5: 第4组计算不同CountryCode的数量
2、 枚举分组
示例 1：按顺序列出各类型城市的数量
MySQL8:
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;
集算器SPL:
A3: ${…}宏替换，以大括号内表达式的结果作为新表达式进行计算，结果为序列["?<200000" "?<1000000" "?<2000000" "?<100000000"]
A5: 针对 A2 中每条记录，寻找 A3 中第 1 个成立的条件，并追加到对应的组中
示例 2：列出华东地区大型城市数量、其它地区大型城市数量、非大型城市数量
MySQL8:
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')
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;
集算器SPL:
A5: enum@n将不满足 A4 中所有条件的记录存放到追加的最后一组中
示例 3：列出所有地区大型城市数量、华东地区大型城市数量、非大型城市数量
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;
集算器SPL:
A6: 若A2中记录满足A4中多个条件时，enum@r会将其追加到对应的每个组中
3、 返回值直接作为序号进行定位分组
示例 1: 按顺序列出各类型城市的数量
MySQL8: 参见“枚举分组”中 SQL
集算器SPL:
A5: 先计算 A2.Population 在 A3 中段号，然后根据段号进行定位分组
4、 原序保持下的相邻记录分组
示例 1: 列出前 10 届奥运金牌榜 (olympic 表中只有历届成绩前 3 名的信息，且没有奖牌完全相同的情况)
MySQL8:
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;
集算器SPL:
A3: 按原序分到各组，每组取第 1 条记录组成新序表
示例 2: 求奥运会国家总成绩蝉联第 1 的最长届数
MySQL8:
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不相同
集算器SPL:
A4: 将相邻nation相同的记录按原序分到同组
A5: 求各组长度的最大值即最大届数
示例3：列出奥运会总成绩排名第一最长蝉联时的各届信息
MySQL:
with t1 as (select rank() over(partition by game order by gold1000000 silver*1000 copper desc) rn from olympic)
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);
集算器SPL:
A5: 求出长度最大组
示例 4：求奥运会前3名金牌总数连续增长的最大届数
MySQL8:
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;
集算器SPL:
A3: 根据条件值按原序分组，若gold小于等于上一个gold则产生新分组
