基於時間的採樣

有沒有辦法根據固定的時間間隔對 PostgreSQL (v11) 中的時間序列數據進行採樣？

例如，我們在給定的表中每秒儲存 1 條記錄 24 小時。這意味著每天表中大約有 86400 條記錄。

有沒有更簡單的方法可以為每 10 秒的數據檢索一個數據點？查詢應返回 8640 條記錄。

目前，我已經使用視窗函式實現了一個解決方案。但是對於更大的數據集，它會花費太多時間。

目前實施

SELECT
message_date,
message
FROM (
   SELECT ROW_NUMBER() OVER (
       PARTITION BY date_trunc('minute', message_date)
       ORDER BY message_date DESC
   ) AS r, t.*
   FROM rdm.telemetry_2021_01_21 t
   WHERE device_id = 'CP2'
   AND message_date >= '1/21/2021 01:00:00 AM'
   AND message_date <= '1/21/2021 11:00:00 PM'
) x
WHERE x.r <= 1 ORDER BY message_date DESC;

上述解決方案支持 1 秒、1 分鐘或 1 小時的採樣，具體取決於date_trunc選項中提供的值。而且，對於包含數百萬條記錄的表，此查詢會花費太多時間。

“解釋分析”結果

誰能建議一些其他的選擇/解決方案？也許使用橫向連接？

對任意固定時間間隔的數據進行採樣的基本查詢：

SELECT grid_time, message_date, message
FROM   generate_series(timestamp '2021-01-21 01:00:00'   -- always use unambiguous ISO format
                    , timestamp '2021-01-21 11:00:00'
                    , interval '73 minutes') grid_time  -- arbitrary time interval
LEFT   JOIN LATERAL (
  SELECT message_date, message
  FROM   rdm.telemetry_2021_01_21
  WHERE  device_id = 'CP2'
  AND    message_date >= grid_time
  AND    message_date <  grid_time + interval '73 minutes'  -- same interval
  ORDER  BY message_date
  LIMIT  1
  ) t ON true;

使用 上的多列索引來支持這一點(device_id, message_date)。

對於您提到的 10 秒的時間間隔，您不需要索引。這將獲取所有行的 10%，並且 Postgres 通常預設為順序掃描，因為這樣更快。（僅索引掃描的例外情況適用。）

添加的過濾器device_id = 'CP2'可以改變很多。但這僅在您的查詢中，而不在您表達的要求中。

索引通常僅有助於過濾器檢索所有行的百分之幾或更少的性能。在您的範例中，例如 30 秒或更長時間。對於選擇性過濾器，索引可以提供很大幫助。

進一步閱讀：

• 在 PostgreSQL 中生成兩個日期之間的時間序列
• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？
• 多列索引和性能
• PostgreSQL：“按分鐘”查詢的執行行數
• 從 generate_series() 填寫記錄集中缺失的日期

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