檢索只有最后一個唯一條目與postgresql中的條件匹配的SQL記錄

我有一張長表，用于跟蹤歷史上“設備”集合的數字“狀態”值（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";

顯然沒有成功。

我想我可能需要將這些條目“分組”在一起，但我不知道如何在 SQL 或 PostgreSQL中指定“僅當 new_state = 4 時回傳最后一個條目”。

任何正確方向的花絮或戳將不勝感激。

uj5u.com熱心網友回復：

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

DISTINCT ON關鍵字連同將ORDER BY拉取每個設備的最新行。外部查詢然后根據您的條件過濾這些。

uj5u.com熱心網友回復：

您可以將Row_Number()函式與 partition bydevice_id和 order by一起使用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的演示。

uj5u.com熱心網友回復：

問題在于：

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 )

uj5u.com熱心網友回復：

您可以使用 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

檢查演示

標籤：sql PostgreSQL 不同的值

上一篇：SQL查詢以僅基于條件A、僅B和A&B計算帳戶數

下一篇：SQL：將長文本分成多行