PostgreSQL – Finding Average of Multiple Delta Times

aggregatepostgresqlselectwindow functions

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"))

Best Answer

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:

Best way to get result count before LIMIT was applied

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á.

Related Solutions

Mysql – Selecting multiple columns from multiple rows in one column (subquery?)

You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT

SELECT CONCAT(id,' # ',name,' # ',citizens) listing FROM
(
    SELECT A.id,A.name,GROUP_CONCAT(B.name,' ',B.surname) citizens
    FROM city A INNER JOIN citizen B
    ON A.id = B.city_id GROUP BY A.id,A.name
) AA;

Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/9f4b3/1

If the names become too long due to truncation, you will have to extend GROUP_CONCAT's maximum length. To set it to 16K, run the following:

SET group_concat_max_len = 1024 * 16;
SET GLOBAL group_concat_max_len = 1024 * 16;

First line is for the session, the second is for all new DB Connections.

Sql-server – Selecting Values That Differ From Section Average

You're on your way and understand CTEs and Window Functions so here's an option:

With AvgSize As
(
SELECT
     AVG(Data_Wanted) As AveragePartitionSize
    ,[partition_id]
FROM Tble
GROUP BY [partition_id]
)

,LatestValue As
(
SELECT
      surrogate_id
     ,collection_ts
    ,ROW_NUMBER() OVER(PARTITION BY surrogate_id ORDER BY collection_ts DESC) RowNumber
FROM example_table
)

SELECT
    *
FROM BaseTable BT
JOIN AvgSize
    ON BT.[partition_id] = AvgSize.[partition_id]
JOIN LatestValue LV
    ON BT.surrogate_id = LV.surrogate_id
    AND BT.collection_ts = LV.collection_ts
    AND LV.RowNumber = 1
WHERE 
    BT.Data_Wanted > AveragePartitionSize

CTE AvgSize establishes the average for the partition ID.

CTE LatestValue is your provided CTE restricted to necessary columns to establish the latest row.

The final step is to bring them all together. The CTEs restrict the query to the latest row and provide the partition average. The only thing left to do is compare the latest row partition size to the average partition size and you're left with your result set.

You could get a little more advanced with your variance check with something like:

ABS(AveragePartitionSize - BT.Data_Wanted) > 20

Best Answer

Related Solutions

Mysql – Selecting multiple columns from multiple rows in one column (subquery?)

Sql-server – Selecting Values That Differ From Section Average

Related Question