如何从ItemScannedTimes表中筛选最后一条Truck记录后的有效数据？

背景与需求 #

我有一张大型表ItemScannedTimes，包含扫描时间字段scanned_timestamp，每条记录关联action_code。扫描需遵循固定顺序：Truck扫描必须在Delivery扫描之前，Pickup扫描最后（顺序为 Truck > Delivery > Pickup）。

我需要基于这张表生成子表，实现两个目标：

• 计算每个配送站点的服务时长（站点的scanned_timestamp最大值与最小值的差值）
• 计算驾驶时间（上一站点最大扫描时间与当前站点最小扫描时间的差值）

但源数据存在错误，比如误将Pickup标记为Delivery，或是像示例中把Truck误扫为Delivery的情况。另外，我不需要统计装货时长，只关注最后一条Truck记录（离开仓库前往首个站点的记录）及其之后的所有Delivery、Pickup记录，后续用Lag()函数计算驾驶时间时从这条Truck记录开始。

错误数据示例 #

注：第4行实际应为Truck，被误扫为Delivery，后续记录扫描正确。

期望输出结果 #

解决方案（SQL） #

WITH last_truck AS (
    SELECT 
        route_name,
        MAX(scanned_timestamp) AS last_truck_time
    FROM ItemScannedTimes
    WHERE action_code = 'Truck'
    GROUP BY route_name
)
SELECT 
    ist.action_code,
    ist.scanned_timestamp,
    ist.customer_address,
    ist.route_name
FROM ItemScannedTimes ist
JOIN last_truck lt ON ist.route_name = lt.route_name
WHERE ist.scanned_timestamp >= lt.last_truck_time
    AND ist.action_code IN ('Truck', 'Delivery', 'Pickup')
ORDER BY ist.route_name, ist.scanned_timestamp;

逻辑说明 #

1. 先用CTElast_truck按路线分组，找出每条路线的最后一条Truck扫描时间
2. 关联原表，筛选出扫描时间大于等于该时间的记录，且仅保留Truck、Delivery、Pickup类型
3. 最后按路线和时间排序，确保记录顺序符合业务逻辑

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