SQL平均时间 [英] SQL AVERAGE TIME

问题描述

我在MSSQL中有以下查询：

  select 
 TRANSACTION_TYPE_ID 
，COUNT（TRANSACTION_TYPE_ID） AS NUMBER_OF_TRANSACTIONS 
，CAST（SUM（AMOUNT）AS DECIMAL（30,2））AS TOTAL 
 FROM 
 [ONLINE_TRANSACTION] 
 WHERE CONVERT（CHAR（8），CREATED_ON，114 ）> ='17:30'，金额不为空，
 TRANSACTION_TYPE_ID！='CHECK-STOP-TRANS-TYPE'
 GROUP BY TRANSACTION_TYPE_ID 
 ORDER BY TRANSACTION_TYPE_ID 
  

我想显示交易类型 TRANSATION_TYPE_ID 如上所述每种类型的交易的金额如上所述，但也是这些交易发生的平均时间 CREATED_ON 这是 datetime 我还没有找到一个很好的方法？

解决方案

根据Randolph Potter的回答，你可以找到平均时间： >

  avg（DATEPART（hh，created_on）* 60 + DATEPART（mi，created_ on））％24 as AvgHour，
 avg（DATEPART（hh，created_on）* 60 + DATEPART（mi，created_on））/ 24 as AvgMinute 
  

I have the following query in MSSQL:

select 
TRANSACTION_TYPE_ID
,COUNT(TRANSACTION_TYPE_ID)AS NUMBER_OF_TRANSACTIONS
,CAST(SUM(AMOUNT)AS DECIMAL (30,2)) AS TOTAL
FROM 
    [ONLINE_TRANSACTION] 
WHERE CONVERT(CHAR(8), CREATED_ON, 114) >='17:30' AND AMOUNT IS NOT NULL AND     
TRANSACTION_TYPE_ID !='CHEQUE-STOP-TRANS-TYPE' 
GROUP BY TRANSACTION_TYPE_ID
ORDER BY TRANSACTION_TYPE_ID

I want to show the type of transactions TRANSATION_TYPE_ID as above the total amount of each type of transaction as above BUT also the average time these transactions occurred CREATED_ON which is datetime I still have not find a good way of doing this?

解决方案

Based on Randolph Potter's answer, you can find the average time like:

avg(DATEPART(hh,created_on)*60 + DATEPART(mi,created_on)) % 24 as AvgHour,
avg(DATEPART(hh,created_on)*60 + DATEPART(mi,created_on)) / 24 as AvgMinute

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