SQL实现同公会相邻赛季grank百分比差值计算

直接用窗口函数LAG()就能快速实现同公会跨赛季取值，不需要写冗余的自连接逻辑，完整SQL如下：

WITH guild_season_stats AS (
    -- 原有公会赛季聚合统计逻辑，抽成公用表表达式方便后续计算
    SELECT 
        season,
        guildname,
        COUNT(guildname) AS mp_count, 
        (COUNT(guildname)/600::FLOAT)*100 AS grank
    FROM mp_rankings
    GROUP BY season, guildname
)
SELECT 
    season,
    guildname,
    mp_count,
    grank,
    -- 计算当前赛季和上赛季的grank差值，保留两位小数后拼接百分号
    ROUND(grank - LAG(grank) OVER (PARTITION BY guildname ORDER BY season), 2) || '%' AS prev_season_percent_diff
FROM guild_season_stats
-- 自动过滤出最新赛季的数据，不需要硬编码赛季值
WHERE season = (SELECT MAX(season) FROM mp_rankings)
ORDER BY grank DESC;

逻辑说明 #

• LAG() OVER (PARTITION BY guildname ORDER BY season)：将聚合后的数据按公会名分组，每组内按赛季升序排列，取当前行的上一行（即对应公会的上一赛季）的grank值，完全匹配相邻赛季差值的计算规则
• 差值计算后用ROUND()保留2位小数，拼接%即可匹配预期的百分比展示格式
• 用子查询动态取最大赛季值，后续赛季更新时不需要手动修改筛选条件，代码可复用性更高
• 若某公会在最新赛季首次出现、无历史赛季数据，prev_season_percent_diff会返回null，符合常规统计逻辑

内容的提问来源于stack exchange，提问作者kams