如何根据行状态设置正确标记？未取件包裹标记实现求助

需求说明 #

为每个RelayPoint标记未被客户取走的包裹，判定状态为Dropoff或preparation，标记规则如下：

• 首个状态ID为(20,71)的行
• 紧随状态ID为(50,51,90,91,30,60,10)行之后的首个状态ID为(20,71)的行

问题现状 #

当前编写的SQL查询在OrderNumber XN81、XN80、XD30上返回错误结果，需修正查询逻辑。

修正后的SQL查询 #

WITH ordered_status AS (
    SELECT 
        OrderNumber,
        RelayPoint,
        StatusID,
        Date,
        -- 获取当前行的前一个状态ID
        LAG(StatusID) OVER (PARTITION BY OrderNumber ORDER BY Date) AS prev_status,
        -- 按订单分组的行号，用于识别首个状态行
        ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY Date) AS rn
    FROM PackageStatus
)
SELECT 
    OrderNumber,
    RelayPoint,
    StatusID,
    Date,
    CASE 
        -- 匹配规则1：订单的首个状态是20或71
        WHEN rn = 1 AND StatusID IN (20,71) THEN 'Dropoff/preparation'
        -- 匹配规则2：前一个状态属于指定触发集合，当前状态为20或71
        WHEN prev_status IN (50,51,90,91,30,60,10) AND StatusID IN (20,71) THEN 'Dropoff/preparation'
        ELSE NULL
    END AS StatusTag
FROM ordered_status
ORDER BY OrderNumber, Date;

逻辑说明 #

1. CTE ordered_status：按OrderNumber分组，按Date排序，为每行生成前一个状态ID（prev_status）和组内行号（rn），用于后续规则匹配。
2. 规则匹配：
   • 规则1通过行号rn=1判断是否为订单的首个状态，同时检查状态ID是否符合要求。
   • 规则2通过prev_status判断前一个状态是否属于触发集合，再检查当前状态ID是否符合要求，确保是紧随触发状态后的目标状态行。

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