我要选择最后一行(按 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的行。
感谢您的帮助和建议!
1条答案
按热度按时间xuo3flqw1#
我想你想要:
这是一把小提琴。