基于Rollup按多列及N个最新赛事实现电竞数据统计的SQL方案问询

核心思路 #

通过窗口函数预先标记每场比赛所属的统计范围（最近5场/最近10场/所有比赛），再结合ROLLUP或GROUPING SETS实现多维度汇总，彻底避免重复编写指标计算逻辑。

具体SQL实现 #

WITH ranked_matches AS (
    SELECT
        tournamentId,
        stage,
        tower_kills,
        dragon_kills,
        first_tower,
        -- 按赛事+阶段分组，按比赛结束时间倒序排名（替换为实际时间字段）
        ROW_NUMBER() OVER (PARTITION BY tournamentId, stage ORDER BY match_end_time DESC) AS match_rank
    FROM matches
    -- 可选：过滤目标赛事
    WHERE tournamentId = 'TARGET_TOURNAMENT_ID'
)
SELECT
    tournamentId,
    stage,
    -- 映射统计范围标签
    CASE
        WHEN match_rank <= 5 THEN '最近5场'
        WHEN match_rank <= 10 THEN '最近10场'
        ELSE '所有比赛'
    END AS stats_scope,
    -- 仅需编写一次指标汇总逻辑
    SUM(tower_kills) AS total_tower_kills,
    SUM(dragon_kills) AS total_dragon_kills,
    COUNT(CASE WHEN first_tower = 1 THEN 1 END) AS total_first_tower_wins
FROM ranked_matches
-- 按指定维度做Rollup自动汇总
GROUP BY ROLLUP(tournamentId, stage, stats_scope)
-- 可选：过滤掉不需要的汇总层级（根据需求调整GROUPING_ID值）
HAVING GROUPING_ID(tournamentId, stage, stats_scope) IN (0, 3, 5, 7)

方案细节说明 #

1. 范围标记逻辑：使用ROW_NUMBER()按赛事和阶段分组，对比赛按时间倒序排名，快速区分不同统计范围。若存在同时结束的比赛，可改用RANK()避免遗漏并列场次。
2. 无重复代码：所有核心指标（推塔数、小龙击杀数等）仅需编写一次汇总逻辑，无需通过UNION重复复制代码块。
3. 灵活汇总层级：ROLLUP(tournamentId, stage, stats_scope)会自动生成以下层级的汇总结果：
   • 赛事+阶段+统计范围的明细汇总
   • 赛事+阶段的整体汇总
   • 单赛事的整体汇总
   • 全量数据的总汇总
     若需自定义汇总组合，可替换为GROUPING SETS精准指定：

   GROUP BY GROUPING SETS(
       (tournamentId, stage, stats_scope),
       (tournamentId, stage),
       (tournamentId)
   )
   

4. 物化视图适配：该查询为单条独立语句，可直接用于创建物化视图。由于赛事更新频率低，几秒级的查询耗时完全可接受，物化视图刷新频率可设置为每日或按需触发。

注意事项 #

• 确保时间字段（如match_end_time）的准确性，否则会导致"最近场次"的统计偏差。
• 若需要统计范围包含并列排名的比赛，将ROW_NUMBER()替换为RANK()或DENSE_RANK()。

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