CODEWITHSUNDEEP
mysqldatabase
TS-
TS-

Reputation: 4411

Average time difference between rows in database

Using MySQL, I have a table that keep track of user visit:

USER_ID | TIMESTAMP --------+---------------------- 1 | 2014-08-11 14:37:36 2 | 2014-08-11 12:37:36 3 | 2014-08-07 16:37:36 1 | 2014-07-14 15:34:36 1 | 2014-07-09 14:37:36 2 | 2014-07-03 14:37:36 3 | 2014-05-23 15:37:36 3 | 2014-05-13 12:37:36

Time is not important, more concern about answer to "how many days between entries"

How do I go about figuring how the average number of days between entries through SQL queries?

For example, the output should look like something like:

(output is just a sample, not reflection of the data table above)

USER_ID | AVG TIME (days) --------+---------------------- 1 | 2 2 | 3 3 | 1

Upvotes: 0

Views: 359

Answers (1)

Marc B
Marc B

Reputation: 360702

MySQL has no direct "get something from a previous row" capabilities. Easiest workaround is to use a variable to store that "previous" value:

SET last = null;

SELECT user_id, AVG(diff)
FROM (
    SELECT user_id, IF(last IS NULL, 0, timestamp - last) AS diff, @last := timestamp
    FROM yourtable
    ORDER BY user_id, timestamp ASC
) AS foo
GROUP BY user_id

The inner query does your "difference from previous row" calculations, and the outer query does the averaging.

Upvotes: 1

Related Questions