根据另一列中的更改创建最后一个修改的列，该列具有时间戳

Question

我试图添加一个新列，它指示另一列的最后修改日期。就我而言，我有一些项目是在特定时间(例如5/1/19(启动的，具有特定状态。在某些情况下，项目可能会更改其状态(已取消或已停止(。每天早上都会拍摄一张快照，在这种情况下，可以用来跟踪最后的修改日期。

有不同的项目(ID、名称(和不同类型的状态。

目前：

project_ID    Name    Status     Date    
1             ABC     Cancelled  1/4/20
1             ABC     Cancelled  1/3/20
1             ABC     Continued  1/2/20
1             ABC     Continued  1/1/20
..            ...     .........  ......
1             ABC     Continued  5/1/19

我想实现下表：

project_ID    Name    Status     Date    LastModified
1             ABC     Cancelled  1/4/20  1/3/20
1             ABC     Cancelled  1/3/20  1/3/20
1             ABC     Continued  1/2/20  5/1/19
1             ABC     Continued  1/1/20  5/1/19
..            ...     .........  ......  ......
1             ABC     Continued  5/1/19  5/1/19

Answer 1

您也可以使用lag()和累积最大值：

select t.*,
max(date) filter (where prev_status is distinct from status) over (partition by project_id, name order by date) as last_change_date 
from (select t.*,
lag(status) over (partition by project_id, name order by date) as prev_status
from t
) t;

子查询计算以前的状态，以识别任何更改。然后，外部查询取检测到状态更改的最大日期。

这种方法的一个优点(或者可能是缺点？(是，如果缺少任何快照日期，它是稳健的。任何这样的差距都会被忽略。

Answer 2

这是一种缺口和孤岛问题，需要确定每个孤岛的起点。每天有一条记录的事实稍微简化了解决方案：我会使用row_number()和日期算法来定义相邻记录的组，然后用一个最小窗口来获得每组的第一个日期。

select t.*, 
min(date) over(partition by project_id, status, date - rn * interval '1 day') last_modified
from (
select t.*, row_number() over(partition by project_id, status order by date) rn
from mytable t
) t
order by project_id, date

DB Fiddle演示

Answer 3

我使用@Philipp Johannis建议的查询解决了这个问题。

这里我用了MIN而不是MAX:

SELECT project_id,
status,
date,
MIN(date)OVER(PARTITION BY project_id, status) AS LastModified
FROM TableABC
ORDER BY date DESC

我无法正确运行另外两个答案，而且这个解决方案似乎更容易阅读和理解，这就是为什么我将重点介绍这个解决方案。