如何获取订单拒绝链中各订单的首次拒绝日期？

我有一个名为orders的表，包含id、date_updated、forwarded_to_order_id字段。当订单被拒绝时，会创建新订单，并将旧订单的forwarded_to_order_id字段更新为新订单的ID。我需要获取指定订单的列表，同时显示该订单所在拒绝链的首次拒绝日期。

举个例子：订单A在2023-04-01被拒绝，转发至订单B；订单B在2023-05-01被拒绝，转发至订单C；订单C未被进一步拒绝。期望结果如下：

id first_decline_date
A      2023-04-01
B      2023-04-01
C      2023-04-01

我尝试用递归CTE编写SQL，目前能正确计算拒绝次数，但无法正确获取链中的首次拒绝日期。

我的SQL代码 #

WITH RECURSIVE OrderChain (id, forwarded_to_order_id, date_updated, times_declined) AS (
SELECT
    id,
    forwarded_to_order_id,
    date_updated,
    0 AS times_declined
FROM orders
WHERE forwarded_to_order_id IS NULL

UNION ALL

SELECT
    i.id,
    i.forwarded_to_order_id,
    i.date_updated,
    c.times_declined + 1 as times_declined
FROM orders i
JOIN OrderChain c
    ON i.forwarded_to_order_id = c.id
)

select * from
(
SELECT
    id AS order_id,
    MIN(date_updated) AS first_declined_date,
    MAX(times_declined) AS times_declined
FROM OrderChain
WHERE forwarded_to_order_id IS NOT NULL
GROUP BY 1
) x
where order_id in (14973, 16777, 18822, 21327, 24087, 27224, 28349);

当前错误结果 #

id         first_decline_date       times_declined
14973   2016-11-14 10:02:01.000000      6
16777   2016-12-14 10:02:19.000000      5
18822   2017-01-14 10:02:45.000000      4
21327   2017-02-14 10:03:13.000000      3
24087   2017-03-14 10:01:21.000000      2
27224   2017-03-23 20:57:46.000000      1

拒绝次数正确，但每个订单的首次拒绝日期都是自身的更新日期，没有继承链中最早的日期。

修正后的SQL代码 #

问题出在递归过程中没有传递链的最早拒绝日期，而是用聚合函数取当前订单的日期。修改递归CTE，在递归时直接传递并更新链的最早日期：

WITH RECURSIVE OrderChain (id, forwarded_to_order_id, times_declined, chain_first_date) AS (
    -- 锚点：链的末端订单（无转发目标），初始最早日期为自身更新日期
    SELECT
        id,
        forwarded_to_order_id,
        0 AS times_declined,
        date_updated AS chain_first_date
    FROM orders
    WHERE forwarded_to_order_id IS NULL

    UNION ALL

    -- 递归：关联上游旧订单，继承链的最早日期（取自身日期和链最早日期的较小值），拒绝次数+1
    SELECT
        i.id,
        i.forwarded_to_order_id,
        c.times_declined + 1 AS times_declined,
        LEAST(i.date_updated, c.chain_first_date) AS chain_first_date
    FROM orders i
    JOIN OrderChain c ON i.forwarded_to_order_id = c.id
)
SELECT
    id AS order_id,
    chain_first_date AS first_decline_date,
    times_declined
FROM OrderChain
WHERE id IN (14973, 16777, 18822, 21327, 24087, 27224, 28349);

说明 #

• 在递归CTE中新增chain_first_date字段，用于存储当前订单所在拒绝链的最早拒绝日期；
• 锚点成员中，末端订单的chain_first_date初始化为自身的date_updated；
• 递归成员中，通过LEAST(i.date_updated, c.chain_first_date)比较当前订单的更新日期和上一级链的最早日期，保留更小的那个，确保整个链的最早日期被传递下去；
• 最终直接查询每个订单的chain_first_date即可得到首次拒绝日期，无需额外聚合。

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