我有一个出租车数据库,其中有两个日期时间字段 BookedDateTime和 PickupDateTime。客户需要知道从预定出租车到驾驶员实际接客的平均等待时间。
I have a taxi database with two datetime fields 'BookedDateTime' and 'PickupDateTime'. The customer needs to know the average waiting time from the time the taxi was booked to the time the driver actually 'picked up' the customer.
数据库中有很多行,覆盖了几个月的数据。
There are a heap of rows in the database covering a couple of month's data.
目标是
所以一个简单的例子是:
So a super simple example would be:
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.0002014-06-09((-3 + 6)/ 2)=平均值是00:03:00.000( 3分钟)
2014-06-09 ((-3 + 6) / 2) = average is 00:03:00.000 (3 mins)
2014-06-10((5 + 0)/ 2)=平均值是00:02:30.000(2.5分钟)
2014-06-10 ((5 + 0) / 2) = average is 00:02:30.000 (2.5 mins)
这可能还是我需要对代码进行一些数字运算(例如C#)?
Is this possible or do I need to do some number crunching in code (i.e. C#)?
任何指针都将不胜感激。
Any pointers would be greatly appreciated.
推荐答案我认为这可以做到:
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)更多推荐
计算每天两个日期时间字段之间的平均时差
发布评论