Postgresql

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

  • October 9, 2018

我正在尋找整個查詢中連續行之間的平均間隔 -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

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

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