1

I'm looking to find the average interval between successive rows across the whole query - delta-previous in below example:

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'

How can I do this?
I've tried avg("delta_previous"), avg(delta_previous),avg("RecognitionTimestamp" - lag("RecognitionTimestamp"))

  • It helps to disclose your Postgres version and table definition (CREATE TABLE script). So we see data types and constraints. And the version is often relevant for the best solution. – Erwin Brandstetter Oct 9 '18 at 3:09
1

It's not possible to run aggregate function over an expression involving a window function in the same query level, because aggregate functions are evaluated before window functions. You'd need a subquery like:

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;

Consider the sequence of events in a SELECT query:

But this simpler query should give you the same result, only much cheaper:

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';

Subtract the minimum from the maximum timestamp to get the total interval and divide it by the number or rows - 1. Voilá.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.