如何在SQL中获取分组内最新非负值替换当前<=0的数值

你原来的SQL存在两个核心逻辑问题：

1. 排序规则错误：你按date DESC倒序排序，窗口函数取的是比当前行日期更晚的数值，不符合你要取「更早时间」数值的需求
2. 未过滤无效值：计算时没有排除<=0的数值，会把负数值也纳入取值范围，无法拿到正确的非负结果

注：不同数据库的字符串转日期函数存在差异：MySQL使用STR_TO_DATE(date, '%m-%d')，SQL Server使用CONVERT(DATE, date, 110)，请根据你使用的数据库适配转换逻辑。

方案1（支持IGNORE NULLS语法的数据库可用，如PostgreSQL 11+、Oracle、BigQuery、Hive 2.3+等） #

SELECT 
    date,
    orig,
    dest,
    value,
    LAST_VALUE(CASE WHEN value > 0 THEN value END IGNORE NULLS) OVER (
        PARTITION BY orig, dest 
        ORDER BY TO_DATE(date, 'MM-DD') ASC -- 转换为日期类型按时间正序排列，更早的日期排在前面
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS new_value
FROM 你的表名

逻辑说明：先把所有<=0的value替换为null，窗口函数会忽略空值，取当前行及之前所有行里最后一个非空的有效数值，也就是时间更早的最新非负数值。

方案2（兼容所有支持窗口函数的数据库，无IGNORE NULLS也可用） #

WITH marked_data AS (
    SELECT 
        *,
        -- 遇到value>0的有效行就计数+1，把连续的无效行和前面最近的有效行分到同一组
        SUM(CASE WHEN value > 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY orig, dest 
            ORDER BY TO_DATE(date, 'MM-DD') ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS value_group
    FROM 你的表名
)
SELECT 
    date,
    orig,
    dest,
    value,
    MAX(value) OVER (PARTITION BY orig, dest, value_group) AS new_value
FROM marked_data

逻辑说明：给每个有效数值和它后面跟随的所有无效数值打同一个分组标记，取每组的最大值就是该组所有行对应的new_value。

以上两种方案的输出完全匹配你给出的预期结果。

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