Postgresql
查找多個增量時間的平均值
我正在尋找整個查詢中連續行之間的平均間隔 -
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。瞧。