是否有很好的方式保存状态变化的记录以及状态变化后的天数?

Question

我有一个记录当前产品状态的表，如下所示:

<表类> product_id 日期 状态 tbody><<tr>A120/10/2021生产A122/10/2021包装A124/10/2021等待交付

您需要LAG()或LEAD()的SELECT来计算按需天数-存储它们是不必要的，也不建议，因为您需要在每次更新日期列时更改此列。下面的示例检查一条记录的状态，并在状态为pending to deliver的情况下计算从date列到当前日期的间隔(以天为单位)。如果它没有挂起，它计算下一个状态更改(1 FOLLOWING)的实际记录:

SELECT 
*,
CASE 
WHEN status = 'pending to deliver' AND LEAD(dt) OVER w IS NULL 
THEN CURRENT_DATE - LEAD(dt) OVER w
WHEN status = 'pending to deliver' AND LEAD(dt) OVER w IS NOT NULL 
THEN LEAD(dt) OVER w-dt
WHEN status = 'delivered' 
THEN NULL
ELSE LEAD(dt) OVER w-dt
END AS days
FROM 
t
WINDOW w AS (PARTITION BY product_id ORDER BY dt ASC 
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);

product_id |     dt     |       status       | days 
------------+------------+--------------------+------
A1         | 2021-10-20 | manufacturing      |    2
A1         | 2021-10-22 | packaging          |    2
A1         | 2021-10-24 | pending to deliver |    3
A1         | 2021-10-27 | delivered          |     
A2         | 2021-10-22 | packaging          |    2
A2         | 2021-10-24 | pending to deliver |     

Demo:db<>fiddle