如何计算每行v2-v1的平均差值并按处理时间加权

核心逻辑 #

你之前的计算方式不符合预期的核心原因是没有遵循时间加权平均的计算规则：时间加权平均值需要先计算每行的(v2-v1)差值 × 对应处理时长的总和，再除以所有行的总处理时长，而非直接对差值取普通平均。
这里的权重取每行的处理时长即可，也就是time2减去time1的时间差，统一用分钟/秒为单位都可以，不影响最终结果。

样例计算（基于你提供的测试数据） #

你给出的两行测试数据计算过程如下：

• 第一行：处理时长30分钟，差值为120.438 - 60.4369 ≈ 60.0011，加权计算项为60.0011 × 30 ≈ 1800.033
• 第二行：处理时长26分钟，差值为53.7444 - 25.5948 ≈ 28.1496，加权计算项为28.1496 × 26 ≈ 731.8896

加权平均结果：(1800.033 + 731.8896) ÷ (30 + 26) ≈ 45.21

各数据库SQL实现 #

MySQL #

SELECT 
  SUM((v2 - v1) * TIMESTAMPDIFF(MINUTE, time1, time2)) / SUM(TIMESTAMPDIFF(MINUTE, time1, time2)) AS weighted_avg_diff
FROM 你的表名;

PostgreSQL #

SELECT 
  SUM((v2 - v1) * EXTRACT(EPOCH FROM (time2 - time1)) / 60) / SUM(EXTRACT(EPOCH FROM (time2 - time1)) / 60) AS weighted_avg_diff
FROM 你的表名;

SQL Server #

SELECT 
  SUM((v2 - v1) * DATEDIFF(MINUTE, time1, time2)) * 1.0 / SUM(DATEDIFF(MINUTE, time1, time2)) AS weighted_avg_diff
FROM 你的表名;

内容的提问来源于stack exchange，提问作者Ale98