CODEWITHSUNDEEP
sqlpostgresql
Goro
Goro

Reputation: 10249

Get average and standard deviation on difference between row values

Given the following table:

CREATE TABLE datapoints
(
  id serial NOT NULL,
  datasource text,
  "timestamp" integer,
  value text,
  CONSTRAINT datapoints_pkey PRIMARY KEY (id)
)

How can I calculate the average and standard deviation of the difference in timestamp1 from one row to the next?

What I mean is, if the data looks like this:

timestamp
---------
1385565639
1385565641
1385565643

I would like to calculate the average and standard deviation on the following data:

timestamp difference
--------------------
0
2
2

Is this even possible in a single query?

Upvotes: 0

Views: 1249

Answers (2)

peter.petrov
peter.petrov

Reputation: 39477

Unless I misunderstood or oversimplified your question something like this might be helpful.

select t2.timestamp - t1.timestamp
from
TableName t1
join TableName t2 on 
(
    t1.timestamp < t2.timestamp
    and
    (
    not exists select null from TableName tMid
    where
    tMid.timestamp > t1.timestamp and tMid.timestamp < t2.timestamp
    )
)

I doubt this is the most efficient thing to do but you mentioned you want it done with one single query.

Just giving you an idea.

If your IDs are consecutive, you could do the join much simpler (on t1.ID = t2.ID-1 or something similar).

Then also you need to see how to also include the last/first difference (maybe you try an outer join). I think my query misses that one.

Never mind, seems I probably misunderstood your question.

This seems useful for your case.

SQL: Show average and min/max within standard deviations

Upvotes: 1

Bradia
Bradia

Reputation: 857

First one returns the difference and second one ruturns the stddev and avg:

--difference
WITH rn as(
    SELECT timestamp , row_number()over() rown
    FROM datapoints order by timestamp
)
SELECT  ta.rown, tb.rown,tb.timestamp - ta.timestamp 
FROM rn as ta,rn as tb
WHERE ta.rown=tb.rown+1 ;

--avg, stddev
WITH rn as(
    SELECT timestamp , row_number()over() rown
    FROM datapoints 
    ORDER BY timestamp
)
SELECT  stddev(tb.timestamp - ta.timestamp), avg(tb.timestamp - ta.timestamp) 
FROM rn as ta,rn as tb
WHERE ta.rown=tb.rown+1 ;

Upvotes: 2

Related Questions