SQL实现按球队分区为ORtg_home连续填充最近历史非零值

原有LAG方案失效原因 #

LAG窗口函数仅能取固定偏移行数的前序值，无法回溯查找最近的非零值：当出现连续客场行时，LAG取到的前序行本身是ORtg_home=0的客场行，自然会返回0，无法关联到更早的主场有效值。

核心实现思路 #

本质是实现按球队分区的「非零值向前填充」：把每一段连续客场（以主场赛事为分割点）归为同一组，组内所有行共享该组对应的最近主场ORtg_home值，首场主场之前的行统一返回0。

通用兼容写法（适配所有SQL引擎） #

不需要依赖特殊语法，用分组标记实现：

SELECT 
  Team,
  Game_total,
  Home_away,
  ORtg_home,
  -- 计算x列
  CASE 
    WHEN cumulative_home_cnt = 0 THEN 0 -- 还没打过主场赛事
    ELSE MAX(ORtg_home) OVER (PARTITION BY Team, cumulative_home_cnt)
  END AS x
FROM (
  -- 子查询：计算每个行之前（含当前行）累计出现的主场次数，作为分组标记
  SELECT 
    *,
    SUM(CASE WHEN ORtg_home <> 0 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY Team ORDER BY Game_total) AS cumulative_home_cnt
  FROM 你的赛事表名
  -- 如果Game_total不是严格按赛事时间排序，先补上赛事日期字段，把ORDER BY改成赛事日期即可
) t

逻辑说明：

• cumulative_home_cnt是累计出现的主场次数：每遇到一个主场赛事（ORtg_home非0），计数就加1，后续的客场行计数不变，相当于把连续客场归到同一个主场的分组下。
• 每个分组内只有第一行的ORtg_home是非0的，其他都是0，直接取分组内的MAX(ORtg_home)就能拿到对应的有效值。
• 计数为0的行就是首场主场之前的赛事，直接返回0。

示例验证（球队A的数据）

高效简洁写法（支持IGNORE NULLS语法的引擎） #

如果用的是Spark SQL、Hive 2.1+、PostgreSQL 11+、BigQuery等支持IGNORE NULLS的引擎，可以直接用LAST_VALUE实现，逻辑更直观：

SELECT 
  Team,
  Game_total,
  Home_away,
  ORtg_home,
  CASE 
    WHEN SUM(CASE WHEN ORtg_home <> 0 THEN 1 ELSE 0 END) OVER (PARTITION BY Team ORDER BY Game_total) = 0 THEN 0
    ELSE LAST_VALUE(CASE WHEN ORtg_home <> 0 THEN ORtg_home ELSE NULL END) 
      IGNORE NULLS OVER (PARTITION BY Team ORDER BY Game_total)
  END AS x
FROM 你的赛事表名

逻辑说明：把ORtg_home为0的行转成NULL，LAST_VALUE ... IGNORE NULLS会自动回溯取当前行之前最近的非空（也就是非零）值，不需要手动做分组标记。

注意事项 #

• 排序字段必须统一：如果现有Game_total不是按每队参赛时间递增的，一定要补充赛事日期字段，把窗口函数的ORDER BY换成赛事日期，避免赛事顺序错乱导致取到未来的无效值。
• 分区严格按球队加：PARTITION BY Team不能漏，保证不同球队的计算完全独立，不会跨队取到其他球队的ORtg_home值。
• 可以根据业务调整有效值判断：如果担心主场ORtg_home存在异常0值，可以把ORtg_home <> 0改成ORtg_home > 0更稳妥。

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