SQL查询以查找日期之间的差异的平均值 [英] SQL Query to find average of difference between dates
本文介绍了SQL查询以查找日期之间的差异的平均值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个列,其数据类似
I have a column that has data like
Date
13/8/2011
2/9/2011
10/9/2011
20/9/2011
我需要编写一个SQL查询/过程,这将帮助我得到日期之间的差异的平均值。
对于上面的例子,它将是(19 + 8 + 10)/3=12.33
请帮助这个。
I need to write a SQL query/procedure that will help me get the average of the differences between the dates. For the above example it would be (19+8+10)/3=12.33 Please help with this.
感谢Advance,
Geetha
Thanks in Advance, Geetha
推荐答案
我不知道你的RDBMS,但这是从SQL服务器。此外,您的一个计算机错误 - 02/09/2011 - 13/08/2011是20,而不是19。
I don't know your RDBMS, but this is from SQL Server. Also, one of your calcs is wrong - 02/09/2011 - 13/08/2011 is 20, not 19.
create table dates (
myDate date
)
insert into dates
values ({d '2011-08-13'}),
({d '2011-09-02'}),
({d '2011-09-10'}),
({d '2011-09-20'})
;with orderedDates as (
select ROW_NUMBER() OVER (order by myDate) as row, myDate
from dates
), datediffs as (
select DATEDIFF(dd, o2.myDate, o1.myDate) as diff
from orderedDates o1 left outer join
orderedDates o2 on o1.row = o2.row + 1
)
select AVG(cast(diff as decimal))
from datediffs
where diff is NOT NULL
结果生成是12.667:(20 + 8 + 10)/ 3。
Result produced is 12.667: (20 + 8 + 10)/3.
这篇关于SQL查询以查找日期之间的差异的平均值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文