SQL 我想获得列之间时间差的平均值
我有个表A

ID Time1 Time2
---------------------------------------------------
12011/10/18 14:57:002011/10/18 14:58:00
22011/10/18 15:10:002011/10/18 15:28:00
32011/10/18 15:38:002011/10/18 15:48:00
42011/10/18 15:58:002011/10/18 16:08:00
...

我想要求第二列的 Time1与第一列 Time2 的差，以此类推，然后求所有差的平均值，这样的需求能不能用sql语句实现。

[解决办法]
http://blog.163.com/happy_2010_zyj/blog/static/1511487562010424113143364/
[解决办法]
用 datediff这个函数计算差值就可以了
自己写个存储过程实现吧
[解决办法]
求平均值再用：AVG()

SQL code

select avg(datediff(yy,'2010-06-01','2012-05-01'))
[解决办法]
探讨

SQL用：datediff()方法


SQL code


select datediff(yy,'2010-06-01','2012-05-01')
select datediff(mm,'2010-06-01','2012-05-01')
select datediff(dd,'2010-06-01','2012-05-01')

[解决办法]
SQL code
CREATE TABLE TEST (ID int,Time1 datetime,Time2 datetime);  INSERT INTO TEST VALUES(1, '18.10.2011 14:57:00', '18.10.2011 14:58:00');INSERT INTO TEST VALUES(2, '18.10.2011 15:10:00', '18.10.2011 15:28:00');INSERT INTO TEST VALUES(3, '18.10.2011 15:38:00', '18.10.2011 15:48:00');INSERT INTO TEST VALUES(4, '18.10.2011 15:58:00', '18.10.2011 16:08:00');declare @result int = 0select @result = @result + DATEDIFF(SECOND, Time1, time2)from TESTselect @result / (select COUNT(ID) from Test)
[解决办法]
SQL code
DECLARE @table table(id int identity(1,1),time1 datetime,time2 datetime)INSERT INTO @table(time1,time2)SELECT '2011/10/18 14:57:00','2011/10/18 14:58:00' UNION ALLSELECT '2011/10/18 15:10:00','2011/10/18 15:28:00' UNION ALLSELECT '2011/10/18 15:38:00','2011/10/18 15:48:00' UNION ALLSELECT '2011/10/18 15:58:00','2011/10/18 16:08:00';WITH CTE AS(    SELECT *,ROW_NUMBER() OVER(ORDER BY id) AS Row FROM @table),RS AS(    SELECT id,DATEDIFF(n,time1,time11) timespan1,DATEDIFF(n,time2,time22) timespan2    FROM(SELECT *,(SELECT time1 FROM CTE WHERE Row=T.Row+1) time11,(SELECT time2 FROM CTE WHERE Row=T.Row+1) time22 FROM CTE T WHERE Row%2=1) TT)SELECT * FROM rs/*(4 行受影响)id          timespan1   timespan2----------- ----------- -----------1           13          303           20          20(2 行受影响)*/---------------;WITH CTE AS(    SELECT *,ROW_NUMBER() OVER(ORDER BY id) AS Row FROM @table),RS AS(    SELECT id,DATEDIFF(n,time1,time11) timespan1,DATEDIFF(n,time2,time22) timespan2    FROM(SELECT *,(SELECT time1 FROM CTE WHERE Row=T.Row+1) time11,(SELECT time2 FROM CTE WHERE Row=T.Row+1) time22 FROM CTE T WHERE Row%2=1) TT)SELECT AVG(timespan1) timeavg1,AVG(timespan2) timeavg1 FROM rs/*timeavg1    timeavg1----------- -----------16          25(1 行受影响)*/
[解决办法]
探讨

引用:

你求日期的平均值是以年、月还是天为最小单位，可以不写存储过程也一样的方便。问题是你需要什么

按照我的需求话，最小单位是秒。
我的需求是第二行的time 1与上一行的time2 的差值,然后将所有差值求平均值

[解决办法]
SQL code
select AVG(diff) from (select DATEDIFF(SECOND, t1.Time2, t2.Time1) as diff from (select ROW_NUMBER() OVER (ORDER BY id) AS ROWNUMBER, Time1, Time2 from TestTime) t1left join (select ROW_NUMBER() OVER (ORDER BY id) AS ROWNUMBER, Time1, Time2 from TestTime) t2 on t2.ROWNUMBER = t1.ROWNUMBER + 1) as t 
 
[解决办法]
SQL code
//在ID连续的情况下面可以这样，如果ID连续可以用RowNumber来代替可以实现同样的效果SELECT a.[id],        a.timea,        a.timeb,        Datediff(second, b.[timea], a.[timeb]) AS TimeSpan FROM   testa a        LEFT JOIN testa b               ON a.[id] = b.[id] + 1 UNION ALL SELECT Max(a.[id]),        Max(a.timea),        Max(a.timeb),        Avg(Datediff(second, b.[timea], a.[timeb])) AS TimeSpan FROM   testa a        LEFT JOIN testa b               ON a.[id] = b.[id] + 1