MySQL 5.6如何用单查询计算基于时间差的平均值

首先，你的数据表结构及数据如下：

针对你需要的计算公式：(sum((value2 - value1) * (date2 - date1))) / (dateLast - dateFirst)，在MySQL 5.6中可以通过两种单查询方式实现，以下是具体方案：

方案一：基于ID自连接（适用于ID连续无间隙的场景） #

如果你的ID字段是连续递增且无缺失的，可以直接通过自连接关联相邻行，同时结合子查询获取首尾时间差：

SELECT 
  SUM((t2.value - t1.value) * TIMESTAMPDIFF(SECOND, t1.updated_at, t2.updated_at)) 
  / TIMESTAMPDIFF(SECOND, (SELECT MIN(updated_at) FROM your_table), (SELECT MAX(updated_at) FROM your_table)) AS result
FROM your_table t1
JOIN your_table t2 ON t2.ID = t1.ID + 1;

逻辑说明： #

1. 自连接t1（当前行）和t2（下一行），通过t2.ID = t1.ID + 1关联相邻记录；
2. 计算每对相邻行的(value差值 * 时间差秒数)，并求和；
3. 用子查询获取整个表的最早和最晚updated_at，计算时间差秒数作为分母；
4. 最终得到目标平均值。

方案二：基于用户变量（适用于ID不连续的通用场景） #

如果ID存在间隙，或者需要严格按时间顺序计算相邻行，推荐使用用户变量逐行记录上一行的数据，实现更通用的计算：

SELECT 
  SUM(delta_value * delta_seconds) / TIMESTAMPDIFF(SECOND, min_dt, max_dt) AS result
FROM (
  SELECT 
    @prev_value AS prev_value,
    value AS curr_value,
    @prev_dt AS prev_dt,
    updated_at AS curr_dt,
    (value - @prev_value) AS delta_value,
    TIMESTAMPDIFF(SECOND, @prev_dt, updated_at) AS delta_seconds,
    @prev_value := value,
    @prev_dt := updated_at
  FROM your_table,
       (SELECT @prev_value := NULL, @prev_dt := NULL) vars
  ORDER BY updated_at
) calc,
(SELECT MIN(updated_at) AS min_dt, MAX(updated_at) AS max_dt FROM your_table) time_range
WHERE prev_value IS NOT NULL;

逻辑说明： #

1. 初始化用户变量@prev_value和@prev_dt，用于存储上一行的value和updated_at；
2. 按updated_at排序遍历表，逐行计算当前行与上一行的value差值和时间差秒数；
3. 外部查询中过滤掉第一行（无前置记录），对所有有效差值乘积求和；
4. 结合首尾时间差计算最终平均值。

两种方案针对你的示例数据，都会返回结果9，符合预期。

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