如何计算SQL中间隔的平均时间？

发布于 2024-12-03 09:16:02 字数 700 浏览 1 评论 0原文

我有一个 SQL 表，我需要计算出每个事务后的平均时间：

数据如下：

Tran1 07/09/2011 09:09:07  - CUSTOMER1 
Tran2 07/09/2011 09:30:46  - CUSTOMER1 
Tran3 07/09/2011 11:27:01  - CUSTOMER2 
Tran4 07/09/2011 11:29:22  - CUSTOMER2 
Tran5 07/09/2011 13:23:48  - CUSTOMER1 
Tran6 08/09/2011 14:21:29  - CUSTOMER3 
Tran7 08/09/2011 14:25:23  - CUSTOMER3 
Tran8 10/09/2011 13:28:57  - CUSTOMER1 
Tran9 10/09/2011 13:30:21  - CUSTOMER1 
Tran10 10/09/2011 13:49:13 - CUSTOMER4

该表是事务表，有三列：-

ID = UniqueID、TimeStamp = DataTime、CustomerId = UniqueID

因此，如果我传入参数 DateTime.. 假设“10/09/2011” 我试图实现的结果是..

日期：10/9/2011 AverageQueueTime：3mins2secs - 例如

原文

I have a SQL table where I need to work out the average time after each transactions:

The data looks like:

Tran1 07/09/2011 09:09:07  - CUSTOMER1 
Tran2 07/09/2011 09:30:46  - CUSTOMER1 
Tran3 07/09/2011 11:27:01  - CUSTOMER2 
Tran4 07/09/2011 11:29:22  - CUSTOMER2 
Tran5 07/09/2011 13:23:48  - CUSTOMER1 
Tran6 08/09/2011 14:21:29  - CUSTOMER3 
Tran7 08/09/2011 14:25:23  - CUSTOMER3 
Tran8 10/09/2011 13:28:57  - CUSTOMER1 
Tran9 10/09/2011 13:30:21  - CUSTOMER1 
Tran10 10/09/2011 13:49:13 - CUSTOMER4

The table is Transaction Table and there are three columns:-

ID = UniqueID, TimeStamp = DataTime, CustomerId = UniqueID

So if I pass in a parameter DateTime.. let say '10/09/2011'
The result Im trying to achieve is..

Date:10/9/2011 AverageQueueTime:3mins2secs - for exmaple

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

盛夏已如深秋| 2024-12-10 09:16:02

使用这样的查询（未经测试）

select t.customerID, TIMESTAMPDIFF(SECOND, MIN(t.timestamp), MAX(t.timestamp) ) / (COUNT(DISTINCT(t.timestamp)) -1)  as AverageTime
from Transaction_Table T
group by T.customerID

将在几秒钟内给你结果。看看这个答案以获得更好的解释

With a query like this (not tested)

select t.customerID, TIMESTAMPDIFF(SECOND, MIN(t.timestamp), MAX(t.timestamp) ) / (COUNT(DISTINCT(t.timestamp)) -1)  as AverageTime
from Transaction_Table T
group by T.customerID

Will give you the result in seconds. Look at this answer for a better explanation

回复收藏 0 原文

深海夜未眠 2024-12-10 09:16:02

假设您的表被命名为“MyTable”（它真的被命名为Transaction吗！？）并且您希望在几分钟内得到差异：

SELECT  CustomerID ,
        SUM(timeSinceLastTransaction) / COUNT(*)
FROM    ( SELECT    * ,
                    DATEDIFF(MINUTE,
                             ( SELECT TOP 1
                                        t2.DataTime
                               FROM     MyTable t2
                               WHERE    t2.DataTime < t1.DataTime
                                        AND t2.CustomerId = t1.CustomerId
                               ORDER BY t2.DataTime DESC
                             ),
                             t1.DataTime
                             ) AS timeSinceLastTransaction
          FROM      MyTable t1
        ) AS IndividualTimes

这是一个相关子查询。

Assuming your table is named "MyTable" (Is it really named Transaction!?) and that you want the difference in minutes:

SELECT  CustomerID ,
        SUM(timeSinceLastTransaction) / COUNT(*)
FROM    ( SELECT    * ,
                    DATEDIFF(MINUTE,
                             ( SELECT TOP 1
                                        t2.DataTime
                               FROM     MyTable t2
                               WHERE    t2.DataTime < t1.DataTime
                                        AND t2.CustomerId = t1.CustomerId
                               ORDER BY t2.DataTime DESC
                             ),
                             t1.DataTime
                             ) AS timeSinceLastTransaction
          FROM      MyTable t1
        ) AS IndividualTimes

This is a correlated subquery.

回复收藏 0 原文

┈┾☆殇 2024-12-10 09:16:02

的 iridio 解决方案的修改版本

a) 查询 SQL Server 数据库版本
b) 支持空值

select t.customerID,
       Case When COUNT(DISTINCT(t.timestamp)) < = 1 THEN 0
             ELSE DATEDIFF(SECOND,MIN(t.timestamp),MAX(t.timestamp))
                   /(COUNT(DISTINCT(t.timestamp)) -1)  as AverageTime
 from Transaction_Table T
 group by T.customerID

注意：数据应按时间戳排序，否则会得到错误的结果。

Modified version of the iridio solution for

a) Query for SQL Server Database version
b) Support for null values

select t.customerID,
       Case When COUNT(DISTINCT(t.timestamp)) < = 1 THEN 0
             ELSE DATEDIFF(SECOND,MIN(t.timestamp),MAX(t.timestamp))
                   /(COUNT(DISTINCT(t.timestamp)) -1)  as AverageTime
 from Transaction_Table T
 group by T.customerID

Note: The data should be sorted by timestamp else you would get wrong results.

回复收藏 0 原文

~没有更多了~