CODEWITHSUNDEEP
sqloracle-database
Siv
Siv

Reputation: 27

SQL - Query to return result

There is a table with Columns as below:

Id : long autoincrement; 
timestamp:long; 
price:long

Timestamp is given as a unix_time in ms.

Question: what is the average time difference between the records ?

Upvotes: 0

Views: 400

Answers (6)

Charles Bretana
Charles Bretana

Reputation: 146603

try this

   Select Avg(E.Timestamp - B.Timestamp)
   From Table B Join Table E 
      On E.Timestamp = 
           (Select Max(Timestamp)
            From Table
            Where Timestamp < R.Timestamp)

Upvotes: 0

Adam Ruth
Adam Ruth

Reputation: 3655

First thought is a sub-query grabbing the record immediately previous:

SELECT timestamp - 
(select top 1 timestamp from Table T1 where T1.Id < Table.Id order by Id desc)
FROM Table

Then you can take the average of that:

SELECT AVG(delta)
from (SELECT timestamp - 
    (select top 1 timestamp from Table T1 where T1.Id < Table.Id order by Id desc) as delta
    FROM Table) T

There will probably need to be some handling of the null that results for the first row, but I haven't tested to be sure.

Upvotes: 3

marc_s
marc_s

Reputation: 755451

In SQL Server, you could write something like that to get that information:

SELECT 
  t1.ID, t2.ID,
  DATEDIFF(MILLISECOND, t2.PriceTime, test2.PriceTime)
FROM table t1
INNER JOIN table t2 ON t2.ID = t1.ID-1
WHERE t1.ID > (SELECT MIN(ID) FROM table)

and if you're only interested in the AVG across all entries, you could use:

SELECT 
  AVG(DATEDIFF(MILLISECOND, t2.PriceTime, test2.PriceTime))
FROM table t1
INNER JOIN table t2 ON t2.ID = t1.ID-1
WHERE t1.ID > (SELECT MIN(ID) FROM table)

Basically, you need to join the table with itself, and use "t1.ID = t2.ID-1" to associate item no. 2 in one table with item no. 1 in the other table and then calculate the time difference between the two. In order to avoid accessing item no. 0 which doesn't exist, use the "T1.ID > (SELECT MIN(ID) FROM table)" clause to start from the second item.

Marc

Upvotes: 1

n8wrl
n8wrl

Reputation: 19765

SELECT AVG(T2.Timestamp - T1.TimeStamp)
  FROM Table T1
  JOIN Table T2 ON T2.ID = T1.ID + 1

Upvotes: 0

Dewfy
Dewfy

Reputation: 23644

If you mean difference between each-other row:

select AVG(x) from (
select a.timestamp - b.timestamp as x 
from table a, table b -- this multiplies a*b ) sub

Upvotes: 0

Gav
Gav

Reputation: 11460

At a guess:

SELECT AVG(timestamp)

I think you need to provide more information in your question for us to help.

Upvotes: 0

Related Questions