计算两行之间的百分比差

Question

我有这个查询，生成了下面的表:

select season,
guildname,
count(guildname) as mp_count, 
(count(guildname)/600::float)*100 as grank
from mp_rankings
group by season, guildname
order by grank desc

<表类>季节guildnamemp_countgranktbody><<tr>10传说569.333333333333339传说54910沼泽草地508.333333333333339神秘467.6666666666666710神秘4279沼泽草地39道明>101003669PARABELLUM335.510PARABELLUM29日4.83333333333333910029日4.83333333333333

对分组结果使用公共表表达式("CTE")，并将其与自身连接以计算与前一季的差值:

with summary as (
select
season,
guildname,
count(*) as mp_count, -- simplified equivalent expression
count(*)/6 as grank -- simplified equivalent expression
from mp_rankings
group by season, guildname
)
select
a.season,
a.guildname,
a.mp_count,
a.grank,
a.mp_count - b.mp_count as prev_season_percent_diff
from summary a
left join summary b on b.guildname = a.guildname
and b.season = a.season - 1
where a.season = (select max(season) from summary)
order by a.grank desc

如果你真的想要一个%在结果中，连接一个%到差值计算。