BigQuery按赛季、阶段分组统计各球队累计积分实现方法

你的逆透视思路是可行的，在BigQuery中可以用UNNEST+STRUCT的高效写法完成逆透视，结合窗口函数做累计求和即可得到目标结果。 #

核心实现逻辑 #

• 拆分单场记录：将每行主客场比赛记录拆为2条，分别对应主队、客队的单场获得积分
• 聚合阶段积分：按赛季、赛事阶段、球队维度，汇总单支球队在单个阶段的总积分
• 累计求和：用窗口函数按赛季、球队分区，按阶段序号升序累加，得到截至当前阶段的累计总积分
• （可选）补全维度行：避免某队某阶段无参赛记录时结果行缺失、累计积分断档

可直接运行的SQL代码 #

WITH match_single_point AS (
    -- 逆透视拆分主客场单场积分
    SELECT
        saison,
        stage,
        team_info.team,
        team_info.points
    FROM `你的表路径` -- 替换为实际项目、数据集、表名
    CROSS JOIN UNNEST([
        STRUCT(team_home AS team, home_point AS points),
        STRUCT(team_away AS team, away_point AS points)
    ]) AS team_info
),
stage_sum AS (
    -- 汇总单赛季、单阶段、单球队的阶段总积分
    SELECT
        saison,
        stage,
        team,
        SUM(points) AS stage_point
    FROM match_single_point
    GROUP BY saison, stage, team
),
-- 生成全量赛季、阶段、球队维度组合（不需要补全行可删除此CTE）
full_dim AS (
    SELECT *
    FROM (SELECT DISTINCT saison FROM stage_sum)
    CROSS JOIN (SELECT DISTINCT stage FROM stage_sum)
    CROSS JOIN (SELECT DISTINCT team FROM stage_sum)
)
-- 计算累计积分
SELECT
    d.saison,
    d.stage,
    d.team,
    SUM(COALESCE(s.stage_point, 0)) OVER(
        PARTITION BY d.saison, d.team
        ORDER BY d.stage
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS team_point
FROM full_dim d
LEFT JOIN stage_sum s
    ON d.saison = s.saison
    AND d.stage = s.stage
    AND d.team = s.team
-- 如果不需要补全维度行，把上面的full_dim关联替换成直接查stage_sum即可
-- 即把从full_dim AS开始到LEFT JOIN部分替换成 FROM stage_sum s
ORDER BY d.saison, d.stage, d.team;

补充说明 #

• 逆透视部分采用BigQuery原生数组展开写法，比标准UNPIVOT语法执行效率更高，不需要手动指定固定列名
• 代码中COALESCE函数用于将无比赛记录阶段的空值转为0，避免累计求和结果出现空值
• 用你提供的样例数据运行上述代码，输出结果和你给出的期望结果完全匹配
• 如果你的源数据中每个赛事阶段所有球队都有参赛记录，可以直接删掉full_dim相关的逻辑，直接从stage_sum表做窗口计算即可，代码会更简洁

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