如何在SQL中基于isLive列条件生成新列value1？

原始数据表 #

需求说明 #

新增一列value1，规则如下：

• 当isLive = 1时，value1等于当前行的value；
• 当isLive = 0时，value1等于同一id分组中最近一行isLive = 1对应的value。

目标数据表 #

SQL实现方案 #

方法一：使用LAST_VALUE窗口函数（适用于支持窗口函数的数据库） #

该方法直接利用窗口函数在分组内按日期顺序提取最近的有效value，逻辑简洁高效：

SELECT 
    id,
    date,
    value,
    isLive,
    LAST_VALUE(CASE WHEN isLive = 1 THEN value END) 
        OVER (
            PARTITION BY id 
            ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS value1
FROM 
    your_table_name;

逻辑解析

• PARTITION BY id：确保只在同一id的分组内处理数据；
• ORDER BY date：按日期排序，保证取到的是时间上最近的有效行；
• LAST_VALUE(...)：在当前行及之前的所有行中，提取最后一个isLive=1对应的value——当当前行isLive=1时，这个值就是当前行的value，完全匹配需求；
• ROWS BETWEEN ...：限定窗口范围为分组起始行到当前行，避免包含后续行的数据。

方法二：分组标记法（兼容更多数据库） #

如果你的数据库不支持LAST_VALUE，可以用累计标记分组的方式实现：

WITH temp AS (
    SELECT 
        id,
        date,
        value,
        isLive,
        -- 累计有效行数量，将连续无效行与最近的有效行归为同一组
        SUM(CASE WHEN isLive = 1 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY id ORDER BY date) AS live_group
    FROM your_table_name
)
SELECT 
    id,
    date,
    value,
    isLive,
    -- 在每个分组内取有效行的value
    MAX(CASE WHEN isLive = 1 THEN value END) 
        OVER (PARTITION BY id, live_group) AS value1
FROM temp;

逻辑解析

1. 先用SUM(...) OVER (...)生成live_group：每遇到一行isLive=1，分组编号加1，这样所有位于两个有效行之间的无效行会和前一个有效行同属一组；
2. 再在每个id+live_group分组内，取有效行的value作为该组所有行的value1，自然满足无效行取最近有效行的需求。

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