logo标签列表

每天计算两个日期时间字段之间的平均时间差

sqlt-sqldatedatetime
8
我有一个出租车数据库，其中包含两个日期时间字段“BookedDateTime”和“PickupDateTime”。客户需要知道从预订出租车到司机实际“接载”顾客的平均等待时间。
数据库中有大量行涵盖了几个月的数据。
目标是编写一个查询，显示每日平均值。
因此，一个超级简单的例子如下：
BookedDateTime           | PickupDateTime
2014-06-09 12:48:00.000    2014-06-09 12:45:00.000
2014-06-09 12:52:00.000    2014-06-09 12:58:00.000    
2014-06-10 20:23:00.000    2014-06-10 20:28:00.000
2014-06-10 22:13:00.000    2014-06-10 22:13:00.000

2014-06-09 ((-3 + 6) / 2) = 平均值为00:03:00.000（3分钟）

2014-06-10 ((5 + 0) / 2) = 平均值为00:02:30.000（2.5分钟）

这是否可行，或者我需要在代码（例如C＃）中进行一些数字计算？

任何提示都将不胜感激。

预订时间怎么比取车时间早呢？我的意思是你的示例中的第一条记录。 - NeedAnswers
预订时间是指乘客指定的接送时间。接送时间是指出租车司机实际到达的时间。这意味着接送日期时间值可以是正数或负数。谢谢。 - ChrisCurrie
4个回答
6
我认为这样就可以了：
select Convert(date, BookedDateTime) as Date, AVG(datediff(minute, BookedDateTime, PickupDateTime)) as AverageTime
    from tablename 
    group by Convert(date, BookedDateTime)  
    order by Convert(date, BookedDateTime)
谢谢。关于我得到的所有答案，我遇到了以下错误，这可能与数据而不是查询有关：“将表达式转换为int数据类型时发生算术溢出错误。” - ChrisCurrie
我认为我收到的错误是由以下问题引起的：“要计算平均值，服务器需要能够首先对所有整数求和，因此您正在计算平均值的数据类型需要能够存储这些值的总和，即使返回的答案在int范围内。”每天有很多行，意味着数字很容易比int更大。是否有任何方法将计算默认为使用bigint？ - ChrisCurrie
在调用AVG之前，您可以将表达式转换为bigint类型：AVG(Convert(bigint, datediff(minute, BookedDateTime, PickupDateTime))) - NeedAnswers
非常感谢。你是第一个回复，这对我的需求完美地起作用了。因此，我已将其选为所选答案。我遇到的问题是由于一些日期在导入时默认为1970年而引起的。一旦我从数据中删除了它们，一切都运行得很完美。非常感谢！ - ChrisCurrie
2

将预订时间的日期作为报告日期：

select
    convert(date, BookedDateTime) as day,
    AVG(DATEDIFF(minute, PickupDateTime, BookedDateTime)) as avg_minutes
from bookings
group by convert(BookedDateTime, datetime, 101)
谢谢。关于我得到的所有答案，我遇到了以下错误，这可能与数据而不是查询有关：“将表达式转换为int数据类型时发生算术溢出错误。” - ChrisCurrie
再次感谢您的帮助。我遇到的问题是由于一些日期在导入时默认为1970年造成的。一旦我从数据中删除了这些日期，我就不再收到错误消息了！ - ChrisCurrie
1

Please try this:

IF OBJECT_ID(N'tempdb..#TEMP') > 0
    BEGIN
       DROP TABLE #TEMP
    END
CREATE TABLE #TEMP(BookedDateTime DateTime,
                PickupDateTime DateTime)
INSERT INTO #TEMP
VALUES
('2014-06-09 12:48:00.000', '2014-06-09 12:45:00.000'),
('2014-06-09 12:52:00.000', '2014-06-09 12:58:00.000'), 
('2014-06-10 20:23:00.000', '2014-06-10 20:28:00.000'),
('2014-06-10 22:13:00.000', '2014-06-10 22:13:00.000'),
('2014-06-10 23:59:00.000', '2014-06-11 00:01:00.000')

SELECT CAST(BookedDateTime AS DATE) AS YMDDate,
      CONVERT(CHAR(8), DATEADD(second, AVG(DATEDIFF(s, BookedDateTime, PickupDateTime)), 0), 108) [hh:mi:ss],
      CONVERT(CHAR(15), DATEADD(second, AVG(DATEDIFF(s, BookedDateTime, PickupDateTime)), 0), 114) [hh:mi:ss:mmm(24h)]
FROM #TEMP
GROUP BY CAST(BookedDateTime AS DATE)
非常感谢。这个很好用。至于我得到的所有答案，我遇到了以下错误，这一定与数据有关而不是查询：'算术溢出错误将表达式转换为数据类型int。'我已经查看了日期，但我没有看到任何看起来不正确的... - ChrisCurrie
再次感谢您的帮助。我遇到的问题是由于一些日期在导入时默认为1970年。一旦我从数据中删除了这些日期，我就不再收到错误消息了！ - ChrisCurrie
0

可以通过找到每行的差异，将它们相加并除以行数来完成。

在SQLSERVER中，它看起来像下面这样。

SELECT SUM(DATEDIFF(MINUTE,BookedDateTime,PickupDateTime)) * 1.0 
           / (SELECT COUNT(*) * 1.0 FROM MyTable)
FROM MyTable
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接