如何用同ID最近非0值填充连续0值？LAG函数失效求助

原始数据 #

需求说明 #

按id分组，将value列中值为0的行，替换为该id下最近的非0值。此前尝试使用LAG(1)函数，但遇到连续多个0值时无法生效（如id=1的连续两行0）。

期望结果 #

无效尝试代码 #

SELECT *, LAG(VALUE) OVER (ORDER BY VALUE, DATE ASC) FROM TABLE
ORDER BY VALUE, DATE ASC

解决方案 #

方法1：使用LAST_VALUE + IGNORE NULLS #

直接在窗口函数中取当前行及之前最近的非零值，忽略空值：

SELECT 
    id,
    日期,
    LAST_VALUE(CASE WHEN value != 0 THEN value END IGNORE NULLS) 
        OVER (PARTITION BY id ORDER BY 日期 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_value
FROM 你的表名
ORDER BY id, 日期;

方法2：分组标识法 #

先通过累加非零值计数生成分组ID，再取组内非零值填充：

WITH grouped_data AS (
    SELECT 
        *,
        SUM(CASE WHEN value != 0 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY id ORDER BY 日期) AS group_id
    FROM 你的表名
)
SELECT 
    id,
    日期,
    MAX(value) OVER (PARTITION BY id, group_id) AS filled_value
FROM grouped_data
ORDER BY id, 日期;

说明 #

• 两种方法均按id分区、按日期排序，确保只在同一id范围内找最近非零值。
• 方法1适合支持IGNORE NULLS的SQL方言（如PostgreSQL、BigQuery等）；方法2兼容性更强，几乎所有主流SQL引擎都支持。

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