如何在Snowflake中正确计算NBA球队的胜负战绩？

我在Snowflake中存储了上一NBA赛季的球员比赛统计报表，希望按月份、赛季等维度统计球队的胜负战绩。现有数据包含GAME_ID、TEAM、HOME、HOME_FINAL、AWAY、AWAY_FINAL等字段（示例数据见下表）。当前使用的SQL会将单场比赛中每位球员的记录都计为一次胜负（如NYK输球时12名球员出场会被统计为12次失利，实际应计1次），且我不确定CASE语句逻辑是否正确，请问该如何修改SQL以得到正确的统计结果？

示例数据 #

当前SQL代码 #

SELECT 
    team,
    AWAY_WINS + HOME_WINS as WINS,
    AWAY_LOSSES + HOME_LOSSES as LOSS
FROM (
SELECT 
    game_id,
    team,
    SUM(CASE WHEN away = team AND away_final > home_final THEN 1 ELSE 0 END) AS AWAY_WINS,
    SUM(CASE WHEN home = team AND away_final < home_final THEN 1 ELSE 0 END) AS HOME_WINS,
    SUM(CASE WHEN away = team AND away_final < home_final THEN 1 ELSE 0 END) AS AWAY_LOSSES,
    SUM(CASE WHEN home = team AND away_final > home_final THEN 1 ELSE 0 END) AS HOME_LOSSES
FROM PLAYER_REPORTING_TABLE t1
WHERE MONTH(TO_DATE(t1.GAME_TIME)) = 1 AND YEAR(TO_DATE(t1.GAME_TIME)) = 2024 AND team = 'NYK' 
GROUP BY team, game_id
)
GROUP BY team, wins, loss;

核心问题分析 #

1. 重复统计问题：原SQL按team, game_id分组后，单场比赛里每个球员的记录都会触发CASE语句计算，导致单场胜负被多次统计（比如5个球员就会算5次胜/负）。
2. CASE逻辑验证：你的CASE语句逻辑是正确的，但重复统计问题导致最终结果错误。

修改思路 #

解决重复统计的核心是确保单场比赛（GAME_ID）+ 球队只被计算一次，以下两种方式均可实现：

方式一：先去重再统计

通过DISTINCT获取每个GAME_ID下的唯一球队比赛信息，再进行胜负统计：

SELECT 
    team,
    SUM(CASE WHEN away = team AND away_final > home_final THEN 1 ELSE 0 END) +
    SUM(CASE WHEN home = team AND home_final > away_final THEN 1 ELSE 0 END) AS WINS,
    SUM(CASE WHEN away = team AND away_final < home_final THEN 1 ELSE 0 END) +
    SUM(CASE WHEN home = team AND home_final < away_final THEN 1 ELSE 0 END) AS LOSSES
FROM (
    -- 去重，确保每场比赛每个球队仅一条记录
    SELECT DISTINCT
        game_id,
        team,
        home,
        home_final,
        away,
        away_final
    FROM PLAYER_REPORTING_TABLE t1
    WHERE MONTH(TO_DATE(t1.GAME_TIME)) = 1 
      AND YEAR(TO_DATE(t1.GAME_TIME)) = 2024 
      AND team = 'NYK'
)
GROUP BY team;

方式二：用MAX替代SUM

在原分组基础上，用MAX()确保单场比赛只计一次胜/负（同一球队单场比赛的胜负结果一致，MAX(1)只会保留1次）：

SELECT 
    team,
    SUM(AWAY_WINS) + SUM(HOME_WINS) AS WINS,
    SUM(AWAY_LOSSES) + SUM(HOME_LOSSES) AS LOSSES
FROM (
SELECT 
    game_id,
    team,
    MAX(CASE WHEN away = team AND away_final > home_final THEN 1 ELSE 0 END) AS AWAY_WINS,
    MAX(CASE WHEN home = team AND home_final > away_final THEN 1 ELSE 0 END) AS HOME_WINS,
    MAX(CASE WHEN away = team AND away_final < home_final THEN 1 ELSE 0 END) AS AWAY_LOSSES,
    MAX(CASE WHEN home = team AND home_final < away_final THEN 1 ELSE 0 END) AS HOME_LOSSES
FROM PLAYER_REPORTING_TABLE t1
WHERE MONTH(TO_DATE(t1.GAME_TIME)) = 1 
  AND YEAR(TO_DATE(t1.GAME_TIME)) = 2024 
  AND team = 'NYK'
GROUP BY team, game_id
)
GROUP BY team;

扩展多维度统计 #

如果需要按月份、赛季等维度分组，只需在最终GROUP BY中加入对应字段即可，比如按月份统计：

SELECT 
    team,
    MONTH(TO_DATE(GAME_TIME)) AS GAME_MONTH,
    YEAR(TO_DATE(GAME_TIME)) AS GAME_YEAR,
    SUM(CASE WHEN away = team AND away_final > home_final THEN 1 ELSE 0 END) +
    SUM(CASE WHEN home = team AND home_final > away_final THEN 1 ELSE 0 END) AS WINS,
    SUM(CASE WHEN away = team AND away_final < home_final THEN 1 ELSE 0 END) +
    SUM(CASE WHEN home = team AND home_final < away_final THEN 1 ELSE 0 END) AS LOSSES
FROM (
    SELECT DISTINCT
        game_id,
        team,
        home,
        home_final,
        away,
        away_final,
        game_time
    FROM PLAYER_REPORTING_TABLE t1
    WHERE team = 'NYK'
)
GROUP BY team, GAME_MONTH, GAME_YEAR
ORDER BY GAME_YEAR, GAME_MONTH;

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