SQL 我想获得列之间时间差的平均值

Question

我有个表A ID      Time1                   Time2
---------------------------------------------------
1 2011/10/18 14:57:00 2011/10/18 14:58:00
2 2011/10/18 15:10:00 2011/10/18 15:28:00
3 2011/10/18 15:38:00 2011/10/18 15:48:00
4 2011/10/18 15:58:00 2011/10/18 16:08:00
...我想要求第二列的 Time1与第一列 Time2 的差，以此类推，然后求所有差的平均值，这样的需求能不能用sql语句实现。

Answer 1

http://blog.sina.com.cn/s/blog_5cab17130100ufhs.html

Answer 2

http://blog.163.com/happy_2010_zyj/blog/static/1511487562010424113143364/

Answer 3

用 datediff这个函数计算差值就可以了
自己写个存储过程实现吧

Answer 4

SQL用：datediff()方法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')

Answer 5

求平均值再用：AVG()select avg(datediff(yy,'2010-06-01','2012-05-01'))

Answer 6

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)

Answer 7

datediff()方法话，相对于我的需求我该怎么用。

Answer 8

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 ALL
SELECT '2011/10/18 15:10:00','2011/10/18 15:28:00' UNION ALL
SELECT '2011/10/18 15:38:00','2011/10/18 15:48:00' UNION ALL
SELECT '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          30
3           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 行受影响)
*/

Answer 9

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

Answer 10

SELECT [id], 
       timea, 
       timeb, 
       Datediff(second, timea, timeb) AS TimeSpan 
FROM   TestA 
UNION ALL 
SELECT Max([id]), 
       Max(timea), 
       Max(timeb), 
       Avg(CONVERT(FLOAT, Datediff(second, timea, timeb))) AS TimeSpan 
FROM   TestA  

Answer 11

不好意思我发帖时候把行打成列了，不是求列之间平均差，是求下一行 time1 与当前行的time2 差的平均值

Answer 12

不是当前行 time1 与time2 的差,而是求下一行 time1 与当前行的time2 差，有办法么.

Answer 13

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) t1
left join (select ROW_NUMBER() OVER (ORDER BY id) AS ROWNUMBER, Time1, Time2 from TestTime) t2 on t2.ROWNUMBER = t1.ROWNUMBER + 1) as t

Answer 14

//在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 

Answer 15

select NID,MAX(Time1),MIN(Time2),DATEDIFF(MI,MIN(Time2),MAX(Time1)) as 时间差
from 
(
select ID,(case when id%2=0 then ID-1 else id end) as NID,Time1,Time2 from 表A
) t
group by NID