Geek asks and answers

SQL Design the average time difference between full rows

I searched around SO and didn't seem to find a question with an answer that worked great for me. I have a table with almost 2 million rows, and each row has a formatted MySQL Date field.

I would like to work (in seconds) how often a row was inserted, so work out the average difference between the dates of all rows with the SQL query.

Any ideas?

- EDIT -

Here my table looks like

id, name, date (datetime), age, gender
+3
source share
3 answers

If you want to know how often (on average) a row was inserted, I don’t think you need to calculate all the differences. You just need to summarize the differences between adjacent lines (adjacent based on a timestamp) and divide the result by the number of terms.

((T1-T0) + (T2-T1) + … + (TN-TN-1)) / N

,

(TN-T0) / N

, :

SELECT TIMESTAMPDIFF(SECOND, MIN(date), MAX(date)) / (COUNT(*) - 1)
FROM atable

, 1, Division By Zero. , , :

SELECT
  IFNULL(TIMESTAMPDIFF(SECOND, MIN(date), MAX(date)) / NULLIF(COUNT(*) - 1, 0), 0)
FROM atable

.

+10

:

select AVG(theDelay) from (

    select TIMESTAMPDIFF(SECOND,a.date, b.date) as theDelay
    from myTable a
    join myTable b on b.date = (select MIN(x.date) 
                                from myTable x 
                                where x.date > a.date)

) p

( ) . .

: ID , , , ID, MIN.

select AVG(theDelay) from (

    select TIMESTAMPDIFF(SECOND,a.date, b.date) as theDelay
    from myTable a
    join myTable b on b.date = (select MIN(x.id) 
                                from myTable x 
                                where x.id > a.id)

) p

EDIT2: , :

select (TIMESTAMPDIFF(SECOND,(MAX(date),MIN(date)) / COUNT(*)) from myTable

, , .

+3

Try the following:

select avg(diff) as AverageSecondsBetweenDates
from (
    select TIMESTAMPDIFF(SECOND, t1.MyDate, min(t2.MyDate)) as diff
    from MyTable t1
    inner join MyTable t2 on t2.MyDate > t1.MyDate
    group by t1.MyDate
) a
+1
source

More articles:


All Articles