Access中分组连续订单记录：提取订单变更及变更日期

嘿，这个需求我太熟了——本质上就是要从每日订单快照里揪出订单状态（ETD+目的地）的变更节点，过滤掉那些连续重复的快照记录对吧？这属于SQL里经典的「孤岛与间隙」问题变种，我给你两个实用的方案，适配不同的数据库环境：

这个方案最直观，通过对比当前记录和前一天的状态，直接筛选出发生变化的记录（包括初始状态的第一条记录）。假设你的表名叫daily_order_snapshots，字段对应order_id（订单号）、snapshot_date（日期）、etd、destination：

WITH order_changes AS (
    SELECT 
        order_id,
        snapshot_date,
        etd,
        destination,
        -- 用LAG获取前一天的ETD+目的地组合，用|分隔避免拼接冲突
        LAG(CONCAT(etd, '|', destination)) OVER (
            PARTITION BY order_id 
            ORDER BY CAST(snapshot_date AS DATE) -- 确保日期正确排序，根据你的数据库调整类型转换
        ) AS prev_status
    FROM daily_order_snapshots
)
SELECT 
    order_id,
    snapshot_date AS change_date,
    etd AS new_etd,
    destination AS new_destination,
    -- 处理初始状态的情况
    CASE 
        WHEN prev_status IS NULL THEN '初始状态'
        ELSE SPLIT_PART(prev_status, '|', 1) 
    END AS old_etd,
    CASE 
        WHEN prev_status IS NULL THEN '初始状态'
        ELSE SPLIT_PART(prev_status, '|', 2) 
    END AS old_destination
FROM order_changes
-- 筛选状态变更的记录，包括第一条初始记录
WHERE prev_status IS NULL 
   OR CONCAT(etd, '|', destination) != prev_status
ORDER BY order_id, snapshot_date;

结果说明 #

针对你给出的示例数据，运行后会得到这样的结果：

完美过滤掉了5/3/2018这条和前一天状态重复的记录，只保留了变更节点。

如果需要同时知道每个状态持续的时间段，这个方案更合适——先把连续相同状态的快照分组，再通过自连接关联前后分组，生成变更记录：

WITH grouped_orders AS (
    SELECT 
        order_id,
        etd,
        destination,
        MIN(snapshot_date) AS start_date,
        MAX(snapshot_date) AS end_date,
        -- 给每个连续分组编号
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY MIN(snapshot_date)) AS group_seq
    FROM (
        SELECT 
            *,
            -- 计算分组ID：两个ROW_NUMBER的差值，相同连续状态的记录会得到相同的group_id
            ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY CAST(snapshot_date AS DATE)) 
            - ROW_NUMBER() OVER (PARTITION BY order_id, etd, destination ORDER BY CAST(snapshot_date AS DATE)) AS group_id
        FROM daily_order_snapshots
    ) t
    GROUP BY order_id, etd, destination, group_id
)
SELECT 
    curr.order_id,
    curr.start_date AS change_date,
    curr.etd AS new_etd,
    curr.destination AS new_destination,
    COALESCE(prev.etd, '初始状态') AS old_etd,
    COALESCE(prev.destination, '初始状态') AS old_destination
FROM grouped_orders curr
LEFT JOIN grouped_orders prev 
    ON curr.order_id = prev.order_id 
    AND curr.group_seq = prev.group_seq + 1
ORDER BY curr.order_id, curr.start_date;

额外优势 #

这个方案除了能得到变更节点，还能在grouped_orders CTE里看到每个状态的起止日期，方便后续分析状态持续时长。

小提示 #

• 如果你的数据库不支持SPLIT_PART（比如MySQL），可以用SUBSTRING_INDEX替代；
• 一定要确保snapshot_date是日期类型，或者转换为日期类型后再排序，避免字符串排序导致的日期顺序错误；
• 如果ETD或目的地可能为空，要调整状态拼接的逻辑，避免空值导致的对比错误。

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