Oracle SQL：基于其他列值生成唯一故障ID列

核心思路 #

要让故障起始行（Failure Flag='Y'且Failure Type非空）与对应的结束行（Trouble Flag='Y'）共享同一ID，需先标记故障触发点，再通过累计触发点数量的窗口函数实现分组，确保同一故障周期内的所有行（含起始、结束）归为同一个ID。

具体实现代码 #

WITH marked_failures AS (
    SELECT 
        *,
        -- 标记故障起始行：符合故障定义的行记为1，其余为0
        CASE WHEN Failure_flag = 'Y' AND Failure_Type IS NOT NULL THEN 1 ELSE 0 END AS failure_start
    FROM your_table
    ORDER BY Prprty, Date -- 按物业+时间戳排序，保证时序正确
)
SELECT
    *,
    -- 累计故障起始点数量，生成唯一Failure ID
    SUM(failure_start) OVER (
        PARTITION BY Prprty 
        ORDER BY Date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Failure_ID
FROM marked_failures;

代码说明 #

1. 标记故障起始点：在CTE中用failure_start字段识别每一行是否为新故障的触发点，仅符合故障定义的行标记为1。
2. 生成Failure ID：通过SUM(failure_start)窗口函数，按物业分区、时间戳排序，累计从当前行往前所有的故障起始点数量：
   • 第一个故障起始行的累计值为1，后续直到下一个故障起始行前的所有行（含对应的Trouble Flag结束行）共享此ID
   • 第二个故障起始行的累计值为2，对应周期内的行也会使用这个新ID，以此类推

适配特殊场景 #

针对你提到的「同一物业、同一天或后续日期可能出现多个同类型故障」的情况，该方案完全适配：

• 只要故障起始行按时间顺序触发，每个新起始点都会生成新的累计ID，不受故障类型重复影响
• 时间戳无重叠的设定保证了时序排序的准确性，不同故障周期不会被错误合并

对比原有方案的问题 #

你之前用count(Trouble_Type)的思路，仅能统计非空Trouble_Type的数量，无法建立故障起始与结束的对应关系。而累计起始点的方式直接基于故障触发的时间线，能精准划分每个故障周期。

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