如何获取符合正确状态序列的产品最新状态并排除异常序列？

我们有一张存储产品生产与发货状态的数据表，状态包括product_created、packed、shipped、delivered。其中packed和shipped状态可能由遗留系统延迟插入，甚至出现在delivered之后。需求是：只保留**严格遵循状态序列（product_created→packed→shipped→delivered）**的产品，且允许同一状态重复（比如多次packed属于合法情况），最终获取这些合规产品的最新状态。

输入数据示例 #

PRODUCT_ID  STATE           INSERTION_TIME
1           product_created 2023-01-10 07:00:00
1           product_created 2023-01-10 09:00:00
1           packed          2023-01-11 01:00:00
1           packed          2023-01-11 02:00:00
1           packed          2023-01-11 09:00:00
1           shipped         2023-01-12 01:00:00
1           delivered       2023-01-12 02:00:00
2           product_created 2023-01-10 07:00:44
2           packed          2023-01-11 01:00:00
2           delivered       2023-01-11 09:00:00
2           shipped         2023-01-12 02:00:00
3           product_created 2023-01-10 07:00:00
3           packed          2023-01-11 01:00:00
3           product_created 2023-01-11 09:00:00
3           packed          2023-01-11 09:00:00
3           shipped         2023-01-12 01:00:00
3           delivered       2023-01-12 02:00:00

预期输出 #

PRODUCT_ID  STATE       INSERTION_TIME
1           delivered   2023-01-12 02:00:00

说明：产品2（shipped出现在delivered之后）、产品3（在packed后又出现product_created）不符合序列要求，被排除。

现有查询的问题 #

当前的SQL只能获取每个产品的最新状态，无法过滤状态序列异常的产品：

SELECT * FROM datatable
QUALIFY ROW_NUMBER() OVER ( PARTITION BY PRODUCT_ID ORDER BY INSERTION_TIME DESC) = 1

解决方案 #

核心是先给每个状态按合法序列分配权重，再检查每个产品的状态时间线里有没有出现“状态倒退”的情况，同时允许同一状态重复。

最终SQL查询 #

WITH ranked_states AS (
    SELECT 
        PRODUCT_ID,
        STATE,
        INSERTION_TIME,
        -- 为状态分配对应权重，匹配合法序列顺序
        CASE STATE
            WHEN 'product_created' THEN 1
            WHEN 'packed' THEN 2
            WHEN 'shipped' THEN 3
            WHEN 'delivered' THEN 4
        END AS state_weight,
        -- 计算当前记录之前，该产品出现过的最高状态权重
        MAX(CASE STATE
            WHEN 'product_created' THEN 1
            WHEN 'packed' THEN 2
            WHEN 'shipped' THEN 3
            WHEN 'delivered' THEN 4
        END) OVER (PARTITION BY PRODUCT_ID ORDER BY INSERTION_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_prev_weight
    FROM datatable
),
valid_products AS (
    SELECT 
        PRODUCT_ID,
        -- 检查是否存在状态倒退：如果有任何记录权重低于之前的最高权重，则标记为无效
        CASE 
            WHEN MIN(CASE WHEN state_weight < COALESCE(max_prev_weight, state_weight) THEN 1 ELSE 0 END) = 0 THEN 'valid'
            ELSE 'invalid'
        END AS product_status
    FROM ranked_states
    GROUP BY PRODUCT_ID
)
SELECT d.PRODUCT_ID, d.STATE, d.INSERTION_TIME
FROM datatable d
JOIN valid_products v ON d.PRODUCT_ID = v.PRODUCT_ID AND v.product_status = 'valid'
QUALIFY ROW_NUMBER() OVER (PARTITION BY d.PRODUCT_ID ORDER BY d.INSERTION_TIME DESC) = 1

逻辑拆解 #

1. ranked_states 临时表：给每条状态记录标上权重，同时算出这条记录之前（按插入时间排序）该产品出现过的最高状态权重。第一条记录没有前置记录，用COALESCE兜底取自身权重。
2. valid_products 临时表：按产品分组检查，如果该产品的所有记录都没有出现“状态权重低于之前最高权重”的情况，就标记为合法产品；反之则是无效产品。
3. 最后关联原表和合法产品表，用QUALIFY筛选出每个合法产品的最新状态。

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