T-SQL计算平均时间 [英] T-SQL calculating average time

问题描述

我在计算平均时间时遇到问题。
这是这种情况：
i有多行，每行都有时间格式的数据
所以我需要计算所有行的平均时间并将其与行数相乘但是当然我有数据格式的问题，因为我需要将时间乘以整数

I have a problem with calculating average time. This is the case: i have multiple rows, in each row there is data in time format so I need to calculate an average time of all rows and multiply it with number of rows but of course I have a problem with data format because I need to multiply time with integer

有人可以帮我提一些建议吗？
thnx
以下是一些数据：

Can somebody help me with some advice? thnx Here is some data:

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

所以根据这些数据需要平均时间或/和该数据的总和

so from this data a need average time or/and sum of that data

推荐答案

你可以通过做一堆日期算术来做到这一点：

You can do this by doing a bunch of date arithmetic:

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小时，则会发生故障）。相反，您应该存储事件的开始和结束时间。如果你有两个终点，你总是可以计算持续时间（和平均持续时间）。

However, what you should be doing is not using TIME to store an interval/duration. TIME is meant to store a point in time (and breaks down if you need to store more than 24 hours, for example). Instead you should store that start and end times for an event. You can always calculation the duration (and the average duration) if you have the two end points.

这篇关于T-SQL计算平均时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！