PostgreSQL：无序表中按唯一ID计算行间日期差及状态

函数使用正确性 #

你用的LAG()函数是正确的，但用法存在关键问题：缺少按id分区的逻辑，导致函数会在整个表范围内取前一行数据，而非仅在同一id的分组内取值，这就是跨ID计算错误的根源。

修正方案：添加PARTITION BY子句 #

要实现不依赖表原始顺序、仅按唯一id计算行间日期差，必须在LAG()函数的OVER子句中加入PARTITION BY id，让函数在每个id分组内独立计算上一行数据。同时，每个id分组内部需要按ord_seq排序，确保取到的是同一ID下的上一个序列的日期。

修正后的完整代码：

select 
    id, 
    ord_seq, 
    ord_date,
    case 
        when ord_seq = 1 then null
        else ord_date - lag(ord_date) over (partition by id order by ord_seq)
    end as date_diff,
    case
        when ord_seq = 1 then 'New'
        when ord_date - lag(ord_date) over (partition by id order by ord_seq) between 1 and 200 then 'Retain'
        when ord_date - lag(ord_date) over (partition by id order by ord_seq) > 200 then 'Reactivated'
    end as status    
from t1
order by id, ord_seq, ord_date;

关键修改说明 #

• partition by id：将数据按id拆分为独立分组，LAG()仅在当前id的分组内查找前一行
• order by ord_seq：在每个id分组内，按订单序列排序，确保取到的是同一ID下上一个序列的订单日期，而非表中物理顺序的前一行
• 统一date_diff和status中的LAG()逻辑，避免之前date_diff仅按id排序的错误

这样无论表的原始顺序如何，都能保证每个id内部的日期差计算是正确的，不会出现跨ID取数的问题。

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