sql-server - 在 SQL Server 中计算加权平均值

Question

我正在尝试根据以下计算来计算加权平均值。

我有一个看起来像这样的数据集:

item |     Date Sent     |     Date Received
 1   |     2 Feb 10am    |      3 Feb 10am
 1   |     6 Feb 11am    |      6 Feb 12pm
 2   |     2 Feb 10am    |      3 Feb 10am
 2   |     6 Feb 11am    |      6 Feb 12pm

然后我需要根据时间差四舍五入来计算平均值:

Time Diff |   Count |
  1       |    2    |
  12      |    2    |

所以在这种情况下它将是:

1 * 2 + 12 * 2 / (12 + 1)

我已经编写了 SQL 查询来计算聚合表:

select 
    floor(datediff(hh, dateSent, dateReceived)) as hrs,
    count(item) as freq 
from 
    table
group by 
    floor(datediff(hh, dateSent, dateReceived))
having 
    floor(datediff(hh, dateSent, dateReceived)) < 100
order by 
    floor(datediff(hh, dateSent, dateReceived)) asc;

我应该做一个子查询吗？我不熟练，我已经尝试过，但不断出现语法错误。

有人可以帮我获取 SQL 查询来获取加权平均值吗？

Answer 1

如果您所说的“加权平均值”是所有时间差的平均值，那么以下内容可能会有所帮助:

select AVG(a.hrs) 
from 
(
    select floor(datediff(hh,dateSent,dateReceived)) as hrs,
    count(item) as freq from table
        group by floor(datediff(hh,dateSent,dateReceived))
            having floor(datediff(hh,dateSent,dateReceived)) <100
--              order by floor(datediff(hh,dateSent,dateReceived)) asc
) a