SQL Server复杂场景：状态对比与后续行值填充实现

针对你的需求——当issue状态从null/open/draft切换到pending sustainability/pending validation/closed时，将当前as_of_date填充到后续行直到状态反向切换，且支持多次触发场景，你可以通过会话分组+窗口函数的方式实现，具体步骤如下：

步骤1：标记触发与反向事件 #

先给每行数据标记两类关键事件：

• 正向触发事件：符合状态切换条件（触发填充的起始点）
• 反向结束事件：状态从目标集合切回初始集合（终止当前填充的节点）

WITH event_marked AS (
    SELECT 
        as_of_date,
        issue_id,
        issue_status_name,
        prev_issue_status_name,
        -- 标记正向触发：从初始状态切到目标状态
        CASE 
            WHEN prev_issue_status_name IN (NULL, 'open', 'draft') 
                 AND issue_status_name IN ('pending sustainability', 'pending validation', 'closed')
            THEN 1
            ELSE 0
        END AS is_trigger,
        -- 标记反向结束：从目标状态切回初始状态
        CASE 
            WHEN issue_status_name IN (NULL, 'open', 'draft') 
                 AND prev_issue_status_name IN ('pending sustainability', 'pending validation', 'closed')
            THEN 1
            ELSE 0
        END AS is_reverse
    FROM your_source_table
    ORDER BY issue_id, as_of_date
),

步骤2：生成会话ID区分填充区间 #

对每个issue_id，通过累计触发/反向事件的次数生成会话ID，这样每个“触发-反向”区间会被划分为独立会话，确保多次触发的场景不会互相干扰：

session_grouped AS (
    SELECT 
        *,
        -- 每个issue_id内，累计事件次数生成会话ID
        SUM(is_trigger + is_reverse) OVER (
            PARTITION BY issue_id 
            ORDER BY as_of_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session_id
    FROM event_marked
),

步骤3：在会话内填充触发日期 #

在每个会话中，提取触发事件对应的as_of_date，填充到该会话的所有行中：

final_result AS (
    SELECT 
        as_of_date,
        issue_id,
        issue_status_name,
        prev_issue_status_name,
        -- 提取当前会话的触发日期，无触发则为NULL
        MAX(CASE WHEN is_trigger = 1 THEN as_of_date END) OVER (
            PARTITION BY issue_id, session_id
        ) AS desired_output
    FROM session_grouped
)
SELECT * FROM final_result;

关键逻辑说明 #

1. 会话ID的作用：将每次触发到反向切换的区间独立划分，避免多次触发的日期互相覆盖。
2. 窗口函数MAX(...)的用法：每个会话内只会有一个触发事件，因此取该会话内触发事件的as_of_date即可完成填充。
3. 处理边界场景：如果反向切换后没有新的触发，desired_output会保持为NULL，直到下一次触发事件出现。

为什么你之前的尝试失败？ #

你用CASE加SUM直接累加日期的思路错误：日期累加没有业务意义，且未对多次触发的区间进行分组，导致不同触发事件的日期被错误合并，无法区分独立的填充周期。

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