如何获得一连串拒绝中订单的第一个拒绝日期？-解网

问：

我有一个包含列，， .当订单被拒绝时，将创建一个新订单，并更新旧订单的列以包含新的订单 ID。我正在尝试弄清楚如何获取所有订单的列表，其中包含该订单链首次拒绝的日期。因此，如果订单 A 在 2023-04-01 被拒绝，那么它被转发给订单 B，订单 B 在 2023-05-01 被拒绝，然后订单 C 在 2023-06-01 被拒绝，我希望结果表是：ordersiddate_updatedforwarded_to_order_idforwarded_to_order_id

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

我想我已经接近了，但不确定为什么它不能完全工作：

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 PostgreSQL 递归 Amazon-Redshift

create temp table test as
(
select
    i1.id as id,
    i1.date_updated as date_updated,
    i1.forwarded_to_order_id,
    i2.id as forwarded_from
from orders i1
left join orders i2
    on i2.forwarded_to_order_id = i1.id
);

WITH RECURSIVE OrderChain (id, forwarded_to_order_id, date_updated, decline_num) AS (
  SELECT
      id,
      forwarded_to_order_id,
      date_updated,
      0 AS decline_num
  FROM test
  WHERE forwarded_from IS NULL

  UNION ALL

  SELECT
      i.id,
      i.forwarded_to_order_id,
      c.date_updated,
      c.decline_num + 1 as decline_num
  FROM test i
  JOIN OrderChain c
      ON c.forwarded_to_order_id = i.id
)
select * from (
SELECT
  id AS order_id,
  date_updated,
  decline_num,
  MAX(decline_num) over(partition by date_updated) AS times_declined
FROM OrderChain
) x
where order_id in (14973, 16777, 18822, 21327, 24087, 27224, 28349)
order by order_id;

返回：

id         first_decline_date       decline_num    times_declined
14973  2016-11-14 10:02:01.000000       0                6
16777  2016-11-14 10:02:01.000000       1                6
18822  2016-11-14 10:02:01.000000       2                6
21327  2016-11-14 10:02:01.000000       3                6
24087  2016-11-14 10:02:01.000000       4                6
27224  2016-11-14 10:02:01.000000       5                6
28349  2016-11-14 10:02:01.000000       6                6

上一个：使用递归输出数组

下一个：如何确保 Typescript 类型递归函数正确？

如何获得一连串拒绝中订单的第一个拒绝日期？

How to get the first decline date of an order in a chain of declines?

评论