检索在postgresql中只有最后一个唯一条目符合条件的SQL记录

Question

我有一个长表，它跟踪历史上"设备"集合的数值"状态"值(0=新，1=设置模式，2=退役，3=活动，4=非活动)。这些设备可能在一年中被激活或去激活，因此该表是状态变化的连续集合-主要是状态3和4，按id排序，末尾有时间戳，例如:

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

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

我真正想要的是一个查询，它返回一个唯一的设备列表，其中每个设备的最新日期条目仅包含状态'4'('非活动状态')，并且不包含不匹配的记录。

因此，在使用上述数据样本时，尽管设备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";

显然没有成功。

我想我可能需要'组'的条目在一起，但我不知道如何指定'返回最后一个条目只有如果new_state = 4'在SQL中，或者更确切地说PostgreSQL。

在正确的方向上的任何花絮或戳将是感激的。

Answer 1

SELECT * FROM (
SELECT DISTINCT ON (device_id)
*
FROM devstatechange
ORDER BY device_id, "when" DESC
) AS latest
WHERE new_state = 4;

DISTINCT ON关键字和ORDER BY将为每个设备拉出最新的行。外部查询然后根据您的条件过滤这些。

Answer 2

可以使用Row_Number()函数，按device_id划分，按when排序。

试试下面的CTE:

with cte as 
(
Select id ,device_id ,new_state  ,when_ ,
row_number() over (partition by device_id order by when_ desc) as rn
from devstatechange
)
select * from cte where rn=1 and new_state=4

查看db-fiddle的演示。

Answer 3

问题:

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

表示MAX("when")指的是表中的所有条目。

应该改成:

when = (select max(when) from devstatechange dev2 where dev2.device_id = dev1.device_id )

Answer 4

您可以使用CTE获取每个设备的最后状态，然后只选择那些最后状态为4的设备，如下所示

WITH device_last_state AS (
SELECT DISTINCT ON (device_id)
id,
device_id, 
last_value (new_state) over (partition by device_id order by "when" desc) as new_state, 
"when" 
FROM devicestatechange
)
SELECT * FROM device_last_state
WHERE new_state = 4

检查演示