T-SQL计算平均时间

Question

我在计算平均时间的过程中遇到了问题。情况如下： 我有多行数据，每行都有一个时间格式的数据。 因此，我需要计算所有行的平均时间，并将其乘以行数。但是，由于我需要将时间与整数相乘，所以我遇到了数据格式的问题。

有人能给我一些建议吗？ 谢谢。 这是一些数据：

times
00:00:00.7400000
00:00:01.1870000
00:00:00.6430000
00:00:00.6100000
00:00:12.9570000
00:00:01.1000000
00:00:00.7400000
00:00:00.5300000
00:00:00.6330000
00:00:01.6000000
00:00:02.6200000
00:00:01.0300000
00:00:01.9630000
00:00:00.9800000
00:00:01.0170000
00:00:00.7600000
00:00:00.7130000
00:00:00.9730000
00:00:01.0000000
00:00:01.0530000
00:00:02.9400000
00:00:00.8200000
00:00:00.8400000
00:00:01.1800000
00:01:25.8230000
00:00:01.0000000
00:00:00.9700000
00:00:01.2930000
00:00:01.3270000
00:00:13.5570000
00:00:19.3170000
00:00:58.2730000
00:00:01.6870000
00:00:18.7570000
00:00:42.8570000
00:01:12.3770000
00:00:01.2170000
00:00:09.9470000
00:00:01.4730000
00:00:00.9030000
00:00:01.0070000
00:00:01.1100000
00:00:01.6270000
00:00:05.0570000
00:00:00.6570000
00:00:00.7900000
00:00:03.2930000
00:00:00.8600000
00:00:01.0330000
00:00:00.9300000
00:00:00.8730000
00:00:00.9600000
00:00:00.8070000
NULL

因此，根据这些数据需要计算平均时间和/或总和。

- user2158645

---

你需要将 '2013-01-13 08:00 am' 乘以一个整数吗？ - Mike Sherrill 'Cat Recall'

如果你取所有行的平均值并乘以行数，那不就等于所有行的总和吗？ - Colin Mackay

你能发布一些示例数据吗？ - Taryn

TIME是用来存储持续时间的吗？你能展示一些样本数据和期望结果的几行吗？ - Aaron Bertrand

总和永远不会超过24小时。 - user2158645

Answer 1

你应该能够使用类似以下的内容：

select 
  cast(cast(avg(cast(CAST(times as datetime) as float)) as datetime) as time) AvgTime,
  cast(cast(sum(cast(CAST(times as datetime) as float)) as datetime) as time) TotalTime
from yourtable;

请参阅带演示的SQL Fiddle.

此操作将 times 数据转换为 datetime，然后转换为 float 以便获取 avg/sum，再将值转换回 datetime，最后再转换为 time。

- Taryn

Answer 2

您可以通过进行一些日期算术运算来实现此操作：

DECLARE @t TABLE(x TIME);

INSERT @t VALUES('00:01:30'),('00:02:25'),('00:03:25');

SELECT CONVERT(TIME, DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, x)), 0)) FROM @t;

然而，您应该不使用TIME来存储时间间隔/持续时间。 TIME用于存储时间点（如果需要存储超过24小时的时间，则会出现问题）。相反，您应该为事件存储开始时间和结束时间。如果您有这两个端点，您始终可以计算持续时间（以及平均持续时间）。

- Aaron Bertrand

---

当我看到你发表评论时，我不知道为什么会试图回答。 - Taryn

@bluefeet哈哈，你的回答也很好，尽管我认为有太多的强制类型转换让我不喜欢。 :-) - Aaron Bertrand

是的，我已经存储了事件的起始点和结束点，但我认为计算持续时间然后再处理它会更容易。 - user2158645

@user2158645 不，不是这样的。尤其是当您使用“TIME”存储持续时间时 - 实际上，您只是美化了持续时间，并使其无法执行常规计算。 - Aaron Bertrand

@AaronBertrand 谢谢建议，我将尝试从端点计算平均值和总和。 - user2158645

@aaron 我从来没有说过我的答案很漂亮。 :) - Taryn

Answer 3

假设您已将持续时间存储在时间字段中，您可以尝试像下面这样使用Datediff函数来获取持续时间的总和（或总时间）。只需使用Avg获取平均值。

演示：http://sqlfiddle.com/#!3/f8c09/2

select sum(datediff(millisecond,0,mytime)) TotalTime
from t

- Kaf

Answer 4

这在这种及类似情况下可能会很有用。

以下将把日期/时间转换为自午夜以来的分钟数。换句话说，时间表示为整数。您可以对其进行平均处理，然后再转换回时间。

declare @dt datetime = GETDATE();
select DATEDIFF(minute, dateadd(dd, datediff(dd, 0, @dt), 0), @dt);
--DATEDIFF(minute, [date without time i.e. midnight], [date/time of interest])

- user2758686