LAG sql or no? postgresql

Question

我有一个表，我想显示一个以前的记录

select id_1 
, lag(kd_1) over(order by kd_1) as ID_PREV
, kd_1
, dt_change
, id_usr 
from t1.status_hist 
where kd_sys_1 = 6
order by id_sys_1 

1   19  19  2020-12-01 14:35:40 id_usr
1   18  18  2020-12-01 14:35:49 id_usr
2   19  19  2020-12-01 14:35:50 id_usr
3   18  18  2020-12-01 14:38:14 id_usr
3   19  19  2020-12-01 14:37:27 id_usr
4   19  19  2020-12-01 14:37:54 id_usr
4       18  2020-12-01 14:38:14 id_usr

但我只需要一个条目，会发生什么，比如这个

1   18  19  2020-12-01 14:35:40 id_usr
2   19  19  2020-12-01 14:35:50 id_usr
3   18  19  2020-12-01 14:37:27 id_usr

仅以前的条目

Answer 1

在Postgres中，您将使用distinct on:

select distinct on (id_1) sh.*
from t1.status_hist sh
where kd_sys_1 = 6
order by id_1, dt_change desc;

以上内容不会返回以前的条目。我把问题的那部分解释为试图获得最新的条目。如果你真的想在结果集中这样做，只需使用一个子查询：

select distinct on (id_1) sh.*,
lag(kd_1) over (order by kd_1) as id_prev
from (select sh.*
from t1.status_hist sh
where kd_sys_1 = 6
) sh
order by id_1, dt_change desc;