如何修正SQL逻辑，按承运商获取正确首次配送尝试时间？

核心问题出在当前SQL未针对不同承运商匹配对应的有效状态码，仅按承运商分区取全局最早日期，导致错误抓取了非目标状态的记录。以下是修正后的逻辑：

解决方案思路 #

• 先针对每个承运商筛选出其对应的有效配送尝试状态码
• 按「订单ID+承运商ID」分区，在符合条件的状态记录中取最早日期作为首次尝试时间

修正后的SQL代码 #

-- 创建临时表存储各订单对应承运商的首次配送尝试日期
CREATE TABLE #first_attempt_to_deliver (
    ORDER_ID VARCHAR(50),
    CARRIER_ID VARCHAR(50),
    FIRST_ATTEMPT_DATE DATETIME
);

WITH filtered_valid_status AS (
    SELECT
        ORDER_ID,
        CARRIER_ID,
        STATUS_DATE,
        -- 仅在当前订单+承运商的有效状态记录中排序取最早
        ROW_NUMBER() OVER (PARTITION BY ORDER_ID, CARRIER_ID ORDER BY STATUS_DATE ASC) AS record_rank
    FROM [WARS].[cust].[ORDER_STATUS_CARRIER]
    -- 按承运商指定有效状态码，可按需扩展
    WHERE 
        (CARRIER_ID = 'MRW' AND STATUS_CODE = 16)
        OR (CARRIER_ID = 'SPRING' AND STATUS_CODE IN (91, 92))
        -- 示例：添加其他承运商规则
        -- OR (CARRIER_ID = 'OTHER_CARRIER' AND STATUS_CODE IN (XX, YY))
)
INSERT INTO #first_attempt_to_deliver (ORDER_ID, CARRIER_ID, FIRST_ATTEMPT_DATE)
SELECT ORDER_ID, CARRIER_ID, STATUS_DATE
FROM filtered_valid_status
WHERE record_rank = 1;

逻辑说明 #

1. 状态过滤：通过WHERE子句精准匹配每个承运商对应的有效配送尝试状态码，确保只纳入符合规则的记录
2. 分区排序：按ORDER_ID和CARRIER_ID联合分区，保证每个订单-承运商组合独立计算首次尝试时间
3. 取最早记录：用ROW_NUMBER()标记排序后的行号，取行号为1的记录即为该组合的首次配送尝试日期

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