MAX() OVER 不能填缺失值,因其天然忽略NULL且不提供前向填充逻辑;正确做法是用LAST_VALUE(... IGNORE NULLS)或COUNT()模拟分组序号再关联取值。

直接用 MAX() OVER 填缺失值是错的——它不处理 NULL,默认跳过,结果还是 NULL。必须配合 COALESCE 或 LAST_VALUE(... IGNORE NULLS)(仅高版本支持)。
为什么 MAX() OVER 单独用不了?
MAX() 聚合函数天然忽略 NULL,但窗口函数 MAX() OVER 也一样:遇到当前行是 NULL,它不会“向前/向后找最近非空值”,而是直接返回该组内所有非空值里的最大值——和你要的“前向填充”或“组内最新有效值”完全不是一回事。
- 现象:
SELECT id, group_id, value, MAX(value) OVER (PARTITION BY group_id) AS max_val FROM t;→ 所有value IS NULL的行,max_val是组内最大非空值,不是“上一个非空值” - 本质:这是聚合,不是填充;要填充得靠“有序上下文 + 忽略 NULL 的取值逻辑”
正确做法:用 LAST_VALUE() OVER + IGNORE NULLS
PostgreSQL 14+、Oracle、BigQuery、Snowflake 支持 IGNORE NULLS,是最贴近“按组前向填充”的写法:
SELECT
id,
group_id,
value,
LAST_VALUE(value) IGNORE NULLS OVER (
PARTITION BY group_id
ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS filled_value
FROM t;
-
ORDER BY id必须存在,否则“前向”无意义 -
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW确保只看当前及之前行(前向填充) - 旧版 PostgreSQL / MySQL 8.0 不支持
IGNORE NULLS,会报错
兼容低版本的替代方案:用 MAX() + 行号模拟“最近非空位置”
在不支持 IGNORE NULLS 的环境(如 MySQL 8.0、旧版 PostgreSQL),得绕一下:先给每行打上“上一个非空值的序号”,再关联取值。
WITH marked AS (
SELECT *,
COUNT(value) OVER (PARTITION BY group_id ORDER BY id) AS grp_seq
FROM t
)
SELECT
m1.id,
m1.group_id,
m1.value,
m2.value AS filled_value
FROM marked m1
LEFT JOIN marked m2
ON m1.group_id = m2.group_id
AND m1.grp_seq = m2.grp_seq
AND m2.value IS NOT NULL;
-
COUNT(value)只统计非空,等价于“到当前位置为止已出现的有效值个数”,天然形成分组标识 - 同一
grp_seq内所有行共享同一个最近非空值(即该组最后一个非空行的value) - 性能注意:
LEFT JOIN在大数据量下可能比窗口函数慢,建议(group_id, grp_seq)加联合索引
真正容易被忽略的是顺序依赖——ORDER BY 字段必须业务上有明确先后(比如时间戳、自增 ID),不能随便选一列。如果排序依据本身含 NULL 或不唯一,填充结果会不可控。










