sql - 获取 SQL 中连续日期列表之间的平均时间

Question

我有一个事件表，每行都有一个 StartDateTime 列。我需要查询事件的子集(例如通过用户 ID)并确定连续事件之间的平均天数。

表格基本上看起来像这样。

TransactionID   TransactionStartDateTime
----------------------------------------
277             2011-11-19 11:00:00.000
278             2011-11-19 11:00:00.000
279             2012-03-20 15:19:46.160
288             2012-03-20 19:23:06.507
289             2012-03-20 19:43:41.980
291             2012-03-20 19:55:17.523

我尝试调整此 Question 中引用的以下查询:

select a.TransactionID, b.TransactionID, avg(b.TransactionStartDateTime-a.TransactionStartDateTime) from
     (select *, row_number() over (order by TransactionStartDateTime) rn from Transactions) a
join (select *, row_number() over (order by TransactionStartDateTime) rn from Transactions) b on (a.rn=b.rn-1)
group by
a.TransactionID, b.TransactionID

但是我在这里没有任何运气，因为原始查询并不期望 DateTimes

我的预期结果是代表平均天数的一位数字(我现在意识到这不是上面的查询给出的结果)

有什么想法吗？

Answer 1

我不知道哪个答案最适合您的情况。但你的问题提出了一个我认为数据库开发人员(以及一般程序员)应该更加意识到的问题。

取平均值很容易，但平均值通常是集中趋势的错误衡量标准。

transactionid  start_time               end_time                 elapsed_days
--
277            2011-11-19 11:00:00      2011-11-19 11:00:00      0
278            2011-11-19 11:00:00      2012-03-20 15:19:46.16   122
279            2012-03-20 15:19:46.16   2012-03-20 19:23:06.507  0
288            2012-03-20 19:23:06.507  2012-03-20 19:43:41.98   0
289            2012-03-20 19:43:41.98   2012-03-20 19:55:17.523  0
291            2012-03-20 19:55:17.523     

这是该分布的直方图。

平均经过天数为 24.4，但中位数为 0。中位数显然是这里集中趋势的更好衡量标准。如果你必须打赌下一个值是更接近 0、更接近 24 还是更接近 122，聪明的钱会押注 0。