Get average time between times in SQL
I'm really clueless as how开发者_开发问答 to do this.
Say my table looks like so:
MESSAGES
- id
- date_posted
- content
How do I get the average time between postings? (date_posted)
SELECT AVG(period)
FROM (
SELECT TIME_TO_SEC(TIMEDIFF(@date_posted, date_posted)) AS period,
@date_posted := date_posted
FROM (
SELECT @date_posted := NULL
) vars,
messages
ORDER BY
date_posted
) q
Try this:
SELECT AVG(DATEDIFF(s, M2.Date_Posted, M1.Date_Posted)) as AverageSeconds
FROM Messages M1 INNER JOIN Messages M2 ON M1.Id = M2.Id+1
Sorry this is MS-SQL solution not mySQL - apologies only just realised that is what you were after. This may be of use to someone else.
In response to comment below - in SQL Server 2005 or 2008 - the following would be a better solution because it makes no assumption about sequential or in-fact integer Primary Keys as it uses Row_Number to generate a unique, sequential key:
SELECT AVG(DATEDIFF(s, M2.Date_Posted, M1.Date_Posted)) as AverageSeconds
FROM (SELECT ROW_NUMBER() OVER(ORDER BY Date_Posted ASC) as Id, Date_Posted FROM
Messages) M1
INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY Date_Posted ASC) as Id, Date_Posted
FROM Messages) M2 ON M1.Id = M2.Id+1
精彩评论