查找多個增量時間的平均值

我正在尋找整個查詢中連續行之間的平均間隔 -delta-previous在下面的範例中：

SELECT *,
 "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
FROM "Detection" 
WHERE
 "RecognitionTimestamp" >= '2018-10-03 09:01:00' AND 
 "RecognitionTimestamp" <= '2018-10-04 09:00:00'

我怎樣才能做到這一點？

我試過了avg("delta_previous")，，avg(delta_previous)``avg("RecognitionTimestamp" - lag("RecognitionTimestamp"))

不可能對涉及同一查詢級別中的視窗函式的表達式執行聚合函式，因為聚合函式在視窗函式*之前進行評估。*您需要一個子查詢，例如：

SELECT avg(delta_previous) AS avg_delta_previous
FROM (
  SELECT "RecognitionTimestamp"
       - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
  FROM   "Detection" 
  WHERE  "RecognitionTimestamp" >= '2018-10-03 09:01:00'
  AND    "RecognitionTimestamp" <  '2018-10-04 09:00:00'  -- typically, you'd want "<" here, not "<="!
  ) sub;

考慮查詢中的事件序列SELECT：

• 在應用 LIMIT 之前獲取結果計數的最佳方法

但是這個更簡單的查詢應該給你同樣的結果，只是便宜得多：

SELECT (max("RecognitionTimestamp")
     - min("RecognitionTimestamp")) / (count(*) - 1) AS avg_delta_previous
FROM   "Detection" 
WHERE  "RecognitionTimestamp" >= '2018-10-03 09:01:00'
AND    "RecognitionTimestamp" <  '2018-10-04 09:00:00';

從最大時間戳中減去最小值以獲得總間隔，然後將其除以行數 - 1。瞧。

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