选择给定值更改的最后一行

zlhcx6iw  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(313)

我要选择最后一行(按 mod_date )where列 new_status 与给定的上一个条目不同 object_id .
一开始我尝试了行数,但没有成功,后来我提出了超前/滞后函数,我认为我更接近解决方案,但仍然不是理想的结果。
下面是代码和小提琴:https://www.db-fiddle.com/f/ks8sai2wsajfflomd7t2it/0

CREATE TABLE changes
(object_id integer,
 new_status smallint,
 comment text,
 mod_date timestamp);

INSERT INTO changes
 VALUES
 (1001, 0, null, '2020-06-01 12:01'),
  (1001, 1, 'XYZ', '2020-06-01 12:05'),
   (1001, 1, 'YZX', '2020-06-01 12:11'),
    (1002, 1, 'XYZ', '2020-06-01 13:21'),
     (1002, 1, 'AAA', '2020-06-01 13:25'),
      (1002, 0, 'BCA', '2020-06-01 14:11'),
       (1003, 1, 'AXX', '2020-06-01 14:12'),
        (1003, 0, 'YZX', '2020-06-01 14:13'),
         (1003, 0, 'YYY', '2020-06-01 14:17');

SELECT object_id, min(mod_date), new_status FROM (
    SELECT 
      object_id
    , mod_date 
    , new_status
    --, row_number() over (partition BY object_id ORDER BY mod_date desc) rn
    , lag(new_status) OVER (partition by object_id ORDER BY mod_date desc) as next_status

    FROM changes
    ORDER BY 1)x
WHERE new_status = next_status 
OR next_status is null
GROUP BY 1,3

1001和1003的输出正常,1002的输出应该是状态为0的行。
感谢您的帮助和建议!

xuo3flqw

xuo3flqw1#

我想你想要:

select distinct on (object_id) c.*
from (select c.*,
              lag(new_status) over (partition by object_id order by mod_date) as prev_ns
      from changes c
     ) c
where prev_ns is distinct from new_status
order by object_id, mod_date desc;

这是一把小提琴。

相关问题