PostgreSQL查询：筛选最新状态为4的非活跃设备列表

你有一张名为devstatechange的状态变更流水长表，用于全量跟踪设备的状态变化历史，状态枚举定义为：

• 0：new（新建）
• 1：setup mode（配置模式）
• 2：retired（已退役）
• 3：active（活跃）
• 4：inactive（非活跃）

设备全年会多次触发激活/停用操作，因此表中持续追加写入状态变更记录，记录自带自增id与状态变更时间戳字段when，表结构与示例数据如下：

id    | device_id | new_state |        when         
 ----------+-----------+-----------+----------------------------
 218010581 |      2505 |         0 | 2022-06-06 16:28:11.174084
 218010580 |      2505 |         1 | 2022-06-06 16:28:11.174084
 218010634 |      2505 |         3 | 2022-06-06 16:29:25.129019
 218087737 |       659 |         3 | 2022-06-07 22:55:48.705208
 218087744 |      1392 |         3 | 2022-06-07 22:55:59.016974
 218087757 |      1556 |         3 | 2022-06-07 22:56:09.811876
 218087758 |      2071 |         1 | 2022-06-07 22:56:20.850095
 218087765 |      2071 |         3 | 2022-06-07 22:56:29.122074

现有可用查询 #

查询单个指定设备的全量状态变更历史可使用如下语句，按时间正序返回变更记录：

select * 
from devstatechange 
where device_id = 2345 
order by "when";

返回结果示例：

id     | device_id | new_state |            when            
-----------+-----------+-----------+----------------------------
 184682659 |      2345 |         0 | 2021-05-27 17:03:36.894429
 184682658 |      2345 |         1 | 2021-05-27 17:03:36.894429
 184684721 |      2345 |         3 | 2021-05-27 17:31:01.968314
 194933399 |      2345 |         4 | 2021-08-31 23:30:05.555407
 195213746 |      2345 |         3 | 2021-09-03 16:53:39.043005
 206278232 |      2345 |         4 | 2021-12-31 22:30:08.820068
 206515355 |      2345 |         3 | 2022-01-03 16:06:01.223759
 215709888 |      2345 |         4 | 2022-04-30 23:30:30.309389
 215846807 |      2345 |         3 | 2022-05-02 19:40:31.525514

查询设备2351的历史记录同理：

select * 
from devstatechange 
where device_id = 2351 
order by "when";

返回结果示例：

id     | device_id | new_state |            when            
-----------+-----------+-----------+----------------------------
 186091252 |      2351 |         0 | 2021-06-09 15:36:02.775035
 186091253 |      2351 |         1 | 2021-06-09 15:36:02.775035
 186091349 |      2351 |         3 | 2021-06-09 15:37:56.965599
 197880878 |      2351 |         4 | 2021-09-30 23:30:06.691835
 197945073 |      2351 |         3 | 2021-10-01 15:32:35.907913
 208981857 |      2351 |         4 | 2022-01-31 22:30:09.521694
 209722639 |      2351 |         3 | 2022-02-09 15:20:12.412816
 217666572 |      2351 |         4 | 2022-05-31 23:30:30.881928

返回去重的设备ID列表，仅保留*每个设备最新时间对应的记录new_state值为4（inactive/非活跃状态）*的设备，过滤掉不符合条件的设备。

按示例数据判断：设备2345和2351都存在3、4状态的反复切换，但2351最新一条记录的状态为4，属于当前非活跃设备，需要纳入结果；2345最新一条记录状态为3，属于活跃设备，需要排除。

目前试了如下语句，均无法返回正确结果：

SELECT DISTINCT * 
FROM devstatechange 
WHERE MAX("when") AND new_state = 4 
ORDER BY "when";

SELECT DISTINCT device_id, new_state, MAX("when") 
FROM devstatechange 
WHERE new_state = 4  
ORDER BY "when";

已知实现该逻辑可能需要用到分组，但不清楚PostgreSQL中如何实现“取每个设备最新一条记录，再筛选状态为4的设备”的逻辑，需要可落地的实现方案。

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