如何在SQL中的字段中查找日期（日期时间）之间的平均值（以天为单位）？(How to find the average (in days) between dates (datetime) in a

如何在SQL中的字段中查找日期（日期时间）之间的平均值（以天为单位）？(How to find the average (in days) between dates (datetime) in a field in SQL?)

我的数据有两个字段。 一个是会员ID，另一个是会员多年来的通话日期。 我很想找到每位会员之间的平均天数。 我的日期只有一个字段（列）。

MembershipId CallDates 123 01/01/2014 123 01/06/2014 123 01/15/2014 234 01/01/2014 234 01/15/2014 345 01/06/2014 456 01/06/2014

我的查询：

WITH OrderDates AS ( SELECT SC_CALL_MBR_ID, ROW_NUMBER() OVER (partition by SC_CALL_MBR_ID ORDER BY SC_DT DESC) AS RowNumber, SC_DT FROM [D3Reporting].[dbo].[ASC_ARCH_CALL] WHERE sc_dt >'2015-06-17 00:00:00.0000000' ) SELECT tab1.SC_CALL_MBR_ID, avg(DATEDIFF(DD, tab2.SC_DT ,tab1.SC_DT)) AS 'AverageDate' FROM OrderDates tab1 LEFT JOIN OrderDates tab2 ON tab2.RowNumber = tab1.RowNumber + 1 GROUP BY tab1.SC_CALL_MBR_ID

My data has two fields. One is the membership id and the other one is the call dates that members have made throughout the years. I'm interested to find the average days in between calls per member. I have only one field (column)for my dates.

MembershipId CallDates 123 01/01/2014 123 01/06/2014 123 01/15/2014 234 01/01/2014 234 01/15/2014 345 01/06/2014 456 01/06/2014

My query:

WITH OrderDates AS ( SELECT SC_CALL_MBR_ID, ROW_NUMBER() OVER (partition by SC_CALL_MBR_ID ORDER BY SC_DT DESC) AS RowNumber, SC_DT FROM [D3Reporting].[dbo].[ASC_ARCH_CALL] WHERE sc_dt >'2015-06-17 00:00:00.0000000' ) SELECT tab1.SC_CALL_MBR_ID, avg(DATEDIFF(DD, tab2.SC_DT ,tab1.SC_DT)) AS 'AverageDate' FROM OrderDates tab1 LEFT JOIN OrderDates tab2 ON tab2.RowNumber = tab1.RowNumber + 1 GROUP BY tab1.SC_CALL_MBR_ID

最满意答案

一种方法是通过自联接来获取先前日期，计算呼叫日期之间的差异，然后计算平均值。 请参阅下面的第一个查询。

该问题被标记为SQL Server 2008，因此您无法使用LAG和LEAD。 我在下面添加了另一个示例，它将LAG用于使用更新版本的用户。

declare @Membership table ( id int, calldate datetime ) -- generate some test data insert into @Membership select 1, '1/1/2015' union all select 1, '1/2/2015' union all select 1, '1/5/2015' union all select 1, '2/1/2015 8:00 AM' -- test using a time value union all select 1, '2/1/2015 9:00 AM' union all select 2, '1/1/2015' union all select 2, '1/14/2015' union all select 2, '2/14/2015' -- This method should work on older SQL Server versions -- Number the membership/call date rows. ;with CallTimesOrdered (num, id, calldate) as ( select ROW_NUMBER() over (partition by id order by calldate), id, calldate from @Membership ), -- Join the numbered rows back to each other. AvgCallTimes (id, timespan) as ( select CurrentDate.id, datediff(d, PriorDate.calldate, CurrentDate.calldate) from CallTimesOrdered CurrentDate inner join CallTimesOrdered PriorDate on PriorDate.num = CurrentDate.num - 1 and PriorDate.id = CurrentDate.id ) -- Compute the average days between calls by membership ID. select id, avg(timespan) as AvgTimeBetweenCalls from AvgCallTimes group by id -- This version works with SQL 2012 and higher versions. -- It uses LAG function. -- First, calculate the difference in days between the current and the prior dates. ;with AvgCallTimes (id, avgtimespan) as ( select id, datediff(d, lag(calldate, 1, null) over (partition by id order by calldate), calldate) from @Membership ) -- Calculate the average time span in days by membership ID. select id, avg(avgtimespan) as AvgTimeBetweenCalls from AvgCallTimes group by id

在使用下面的测试数据的两种情况下，我得到以下结果：

ID AvgTimeBetweenCalls 1 7 2 22

One way to do this is by a self-join to get the prior date, calculate the differences between the call dates, and then compute the average. See the first query below.

The question was tagged as SQL Server 2008, so you cannot use LAG and LEAD. I included another example below that uses LAG for those who are using a more recent version.

declare @Membership table ( id int, calldate datetime ) -- generate some test data insert into @Membership select 1, '1/1/2015' union all select 1, '1/2/2015' union all select 1, '1/5/2015' union all select 1, '2/1/2015 8:00 AM' -- test using a time value union all select 1, '2/1/2015 9:00 AM' union all select 2, '1/1/2015' union all select 2, '1/14/2015' union all select 2, '2/14/2015' -- This method should work on older SQL Server versions -- Number the membership/call date rows. ;with CallTimesOrdered (num, id, calldate) as ( select ROW_NUMBER() over (partition by id order by calldate), id, calldate from @Membership ), -- Join the numbered rows back to each other. AvgCallTimes (id, timespan) as ( select CurrentDate.id, datediff(d, PriorDate.calldate, CurrentDate.calldate) from CallTimesOrdered CurrentDate inner join CallTimesOrdered PriorDate on PriorDate.num = CurrentDate.num - 1 and PriorDate.id = CurrentDate.id ) -- Compute the average days between calls by membership ID. select id, avg(timespan) as AvgTimeBetweenCalls from AvgCallTimes group by id -- This version works with SQL 2012 and higher versions. -- It uses LAG function. -- First, calculate the difference in days between the current and the prior dates. ;with AvgCallTimes (id, avgtimespan) as ( select id, datediff(d, lag(calldate, 1, null) over (partition by id order by calldate), calldate) from @Membership ) -- Calculate the average time span in days by membership ID. select id, avg(avgtimespan) as AvgTimeBetweenCalls from AvgCallTimes group by id

In both cases using the test data below, I get the following results:

ID AvgTimeBetweenCalls 1 7 2 22