Teradata技术咨询：如何在OLAP分区中忽略取消订单行并传递历史已完成订单的服务状态值

当然可以搞定这个需求！你之前遇到的问题，核心是单纯用前一行窗口没法跳过Canceled订单，也没法正确延续最近已完成订单的服务状态——咱们来一步步解决它。

首先先明确核心规则，避免走偏：

• 只有Complete状态的订单才会更新服务状态，Canceled订单直接跳过，完全沿用最近一笔Complete订单的状态
• 服务状态一旦在Complete订单中确定，就要一直传递到后续所有订单，直到下一笔Complete订单更新状态

为什么你原来的语句不行？ #

你用的Sum(SUBSCR1_ORD) Over (PARTITION BY ACCT_NO ORDER BY ORDER_DATE ROWS BETWEEN 1 Preceding AND 1 Preceding)只会机械取上一行的值，但如果上一行是Canceled的，就会拿到错误的变动值，而且没法跳过这类无效订单。

解决方案：分三步构建逻辑 #

我们需要先标记有效订单，再分组锁定服务状态周期，最后把状态向下传递：

步骤1：标记有效订单并生成状态周期序号

先给每个账户的订单按日期排序，只为Complete订单生成递增的序号——Canceled订单会继承上一个有效订单的序号，这样就能把同一状态周期内的订单归为一组：

WITH valid_orders AS (
    SELECT 
        ACCT_NO,
        ORDER_DATE,
        ORDER_STATUS,
        SUBSCR1_ORD,
        SUBSCR2_ORD,
        -- 仅Complete订单让序号+1，Canceled订单序号不变
        SUM(CASE WHEN ORDER_STATUS = 'Complete' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY ACCT_NO ORDER BY ORDER_DATE ROWS UNBOUNDED PRECEDING) AS valid_seq
    FROM your_table_name -- 替换成你的实际表名
)

步骤2：锁定每个状态周期的服务状态

针对每个状态周期（同一个valid_seq），取该周期内最后一笔Complete订单的服务状态——因为Canceled订单的SUBSCR值不参与计算，这里用LAST_VALUE确保只取有效状态：

, service_state_cycles AS (
    SELECT 
        ACCT_NO,
        ORDER_DATE,
        ORDER_STATUS,
        SUBSCR1_ORD,
        SUBSCR2_ORD,
        valid_seq,
        -- 取当前周期内的有效服务状态，无值时用0填充
        LAST_VALUE(COALESCE(SUBSCR1_ORD, 0)) 
            OVER (PARTITION BY ACCT_NO, valid_seq ORDER BY ORDER_DATE ROWS UNBOUNDED PRECEDING) AS current_svc1,
        LAST_VALUE(COALESCE(SUBSCR2_ORD, 0)) 
            OVER (PARTITION BY ACCT_NO, valid_seq ORDER BY ORDER_DATE ROWS UNBOUNDED PRECEDING) AS current_svc2
    FROM valid_orders
)

步骤3：向下传递服务状态

最后用LAST_VALUE IGNORE NULLS来取当前订单之前的最新有效状态，完美实现"此前服务状态向下传递"的需求：

SELECT 
    ACCT_NO,
    ORDER_DATE,
    ORDER_STATUS,
    SUBSCR1_ORD,
    SUBSCR2_ORD,
    -- 取当前行之前的最新有效服务状态，初始为0（第一行无历史订单）
    COALESCE(LAST_VALUE(current_svc1 IGNORE NULLS) 
        OVER (PARTITION BY ACCT_NO ORDER BY ORDER_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS EXISTING_SVC1,
    COALESCE(LAST_VALUE(current_svc2 IGNORE NULLS) 
        OVER (PARTITION BY ACCT_NO ORDER BY ORDER_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS EXISTING_SVC2
FROM service_state_cycles
ORDER BY ACCT_NO, ORDER_DATE;

验证你的示例数据 #

把你的测试数据代入，结果完全符合期望：

这里关键的IGNORE NULLS是Teradata支持的窗口函数特性，能跳过Canceled订单带来的无效值，确保我们拿到的是最近的有效服务状态。如果后续有新的Complete订单更新服务值，这个逻辑也会自动把新状态传递下去。

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