如何在 PostgreSQL 中查詢給定日期範圍之間的時間序列？

我的問題是我不知道如何在指定的日期範圍內查詢時間序列數據，其中數據有間隙，並且每個日期應該結轉的最後一個已知值超出查詢的日期範圍。

我在 PostgreSQL 表中有以下數據：

DeviceID    State    Date
1           L7       2020-12-05 15:18:00
1           L1       2020-12-10 00:15:00
1           L5       2020-12-11 15:00:00
1           L3       2020-12-14 13:00:00
2           L2       2020-12-08 14:00:00
2           L1       2020-12-14 05:32:00

我想在 之間進行查詢2020-12-09 -- 2020-12-12，但我的問題是我不知道 DeviceID 1 的狀態，2020-12-09因為系列數據中有間隙。

在上面的範例中，DeviceID 1 狀態是L7at 2020-12-09，所以我需要在給定日期範圍之外查詢才能找到L7狀態。

對於指定的日期範圍，我的查詢表應如下所示：

DeviceID    State    Date
1           L7       2020-12-09 
1           L1       2020-12-10
1           L5       2020-12-11
1           L5       2020-12-12
2           L2       2020-12-09
2           L2       2020-12-10
2           L2       2020-12-11
2           L2       2020-12-12

我的最終目標是計算 DeviceID 的狀態變化之間的時間差，所以我仍然可以從有間隙的數據中做到這一點，我唯一的問題是我不知道最後一次狀態變化事件超出了查詢的日期範圍，在上面的範例中，我不知道L7DeviceID 1 和L2DeviceID 2 的狀態2020-12-09。

WITH cte AS (
SELECT Dates."Date",
      test.DeviceID,
      test.State,
      ROW_NUMBER() OVER (PARTITION BY test.DeviceID, 
                                      Dates."Date" 
                         ORDER BY test."Date" DESC) rn
FROM generate_series('2020-12-09'::timestamp, '2020-12-12', '1 DAY') Dates ("Date")
JOIN test ON Dates."Date" >= test."Date"::date
)
SELECT DeviceID, State, "Date"
FROM cte
WHERE rn = 1
ORDER BY 1, 3

小提琴

通常在查詢某個時間點時，使用相關子查詢是產生所需輸出的最直接方法。使用正確的主鍵，它將執行得非常好（應該(DeviceID,Date)在您的範例中）。

SELECT
 Device.DeviceID
,Dates."Date"
,DeviceState.State
,DeviceState."Date" AS State_Date
FROM
 Device Device
CROSS JOIN
 generate_series('2020-12-09'::timestamp, '2020-12-12', '1 DAY') Dates ("Date")
LEFT JOIN
 DeviceState DeviceState
   ON DeviceState.DeviceId = Device.DeviceId
        AND DeviceState."Date" =
          (
            SELECT
              MAX("Date")
            FROM
              DeviceState
            WHERE
              DeviceID = Device.DeviceID
                AND "Date" < Dates."Date" + INTERVAL '1 DAY' /* Gets value as of end of date provided */
          )
ORDER BY
 DeviceID
,"Date"

小提琴（歸功於 Akina 完成了大部分打字工作）

引用自：https://dba.stackexchange.com/questions/281602