如何用SQL检测从GOOD直接切换至UNDEFINED的状态变更日期

假设你的表名为status_changes，包含字段：entity_id（跟踪状态变更的实体ID）、status（状态值：GOOD/BAD/UNDEFINED）、change_date（状态变更日期）。

核心思路 #

通过窗口函数LAG()获取每条记录的上一个状态及上上个状态，以此匹配两种流转场景：

• 直接从GOOD切换到UNDEFINED
• 从GOOD→BAD→UNDEFINED（捕获最终进入UNDEFINED的日期）

实现SQL #

WITH status_with_prev AS (
    SELECT
        entity_id,
        status,
        change_date,
        -- 获取上一个状态
        LAG(status) OVER (PARTITION BY entity_id ORDER BY change_date) AS prev_status,
        -- 获取上上个状态
        LAG(status, 2) OVER (PARTITION BY entity_id ORDER BY change_date) AS prev_prev_status
    FROM status_changes
)
SELECT
    entity_id,
    change_date,
    status
FROM status_with_prev
WHERE
    status = 'UNDEFINED'
    AND (
        -- 场景1：直接从GOOD跳转至UNDEFINED
        prev_status = 'GOOD'
        -- 场景2：经BAD中转后进入UNDEFINED
        OR (prev_status = 'BAD' AND prev_prev_status = 'GOOD')
    )
ORDER BY change_date;

说明 #

• PARTITION BY entity_id确保按单个实体独立跟踪状态流转
• ORDER BY change_date保证状态顺序按时间先后排列
• 筛选条件会同时命中两种流转场景，返回所有从GOOD最终进入UNDEFINED的变更日期，包含你需要的2022年8月18日和2022年8月19日的记录

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