MongoDB如何两两计算文档时间差 统计灯光开关时长指标

该需求可直接通过MongoDB聚合管道实现，核心逻辑是按时序关联相邻事件、计算状态持续时长后做二次统计，不需要额外在业务层做数据处理。

前置校验：请先确认事件上报无乱序，所有同设备事件按timestamp升序存储，乱序数据会直接导致时长计算错误。

推荐方案（MongoDB 5.0+ 版本） #

使用$setWindowFields窗口函数可以直接按设备分组、按时间排序后取相邻文档字段，无需遍历数组，性能最优。

第一步：计算单段状态持续时长 #

先执行以下聚合，得到每一段开关状态的持续周期：

db.events.aggregate([
  // 先按设备ID、时间排序，保证窗口取值顺序正确
  { $sort: { device_id: 1, timestamp: 1 } },
  {
    $setWindowFields: {
      partitionBy: "$device_id", // 按单个设备分组，多设备场景不会混算
      sortBy: { timestamp: 1 }, // 按事件上报时间升序
      output: {
        prev_light_on: { $shift: { output: "$light_on", by: -1 } }, // 取上一条事件的开关状态
        prev_timestamp: { $shift: { output: "$timestamp", by: -1 } } // 取上一条事件的上报时间
      }
    }
  },
  // 过滤每个设备的第一条事件（无上一个状态，无法计算时长）
  { $match: { prev_timestamp: { $exists: true } } },
  // 计算当前状态段的持续时长，单位毫秒；低版本无$dateDiff可替换为{ $subtract: ["$timestamp", "$prev_timestamp"] }
  {
    $addFields: {
      duration_ms: { $dateDiff: { startDate: "$prev_timestamp", endDate: "$timestamp", unit: "millisecond" } },
      segment_state: "$prev_light_on" // segment_state为true代表这段时间是开灯状态，false为关灯状态
    }
  }
])

以你给出的两条示例数据为例，执行后会得到一条关灯状态的时长记录，对应10:00:24到10:32:00的关灯周期，时长为1896000毫秒（31.6分钟）。

第二步：按需统计目标指标 #

在上述聚合阶段后追加对应逻辑，即可得到你需要的4项结果：

• 统计灯光平均开启时长、最长开/关时长：追加$group阶段

{
  $group: {
    _id: "$device_id",
    // 平均开启时长，单位转换为小时
    avg_on_duration_hour: {
      $avg: {
        $cond: [ { $eq: ["$segment_state", true] }, { $divide: ["$duration_ms", 3600000] }, null ]
      }
    },
    // 最长开启时长，单位转换为小时
    max_on_duration_hour: {
      $max: {
        $cond: [ { $eq: ["$segment_state", true] }, { $divide: ["$duration_ms", 3600000] }, 0 ]
      }
    },
    // 最长关闭时长，单位转换为小时
    max_off_duration_hour: {
      $max: {
        $cond: [ { $eq: ["$segment_state", false] }, { $divide: ["$duration_ms", 3600000] }, 0 ]
      }
    }
  }
}

• 查询所有开启时长超过4小时的记录：追加$match+$project阶段

{
  $match: {
    segment_state: true,
    $expr: { $gt: [ "$duration_ms", 4 * 3600000 ] } // 4小时对应毫秒数
  }
},
{
  $project: {
    device_id: 1,
    start_time: "$prev_timestamp",
    end_time: "$timestamp",
    duration_hour: { $divide: ["$duration_ms", 3600000] },
    _id: 0
  }
}

低版本兼容方案（MongoDB <5.0） #

如果版本不支持窗口函数，可以通过分组转数组的方式实现相邻比对，注意单设备事件量超过10万条时该方案性能较差，优先升级版本使用窗口函数：

db.events.aggregate([
  { $sort: { device_id: 1, timestamp: 1 } },
  {
    $group: {
      _id: "$device_id",
      events: { $push: { light_on: "$light_on", timestamp: "$timestamp" } }
    }
  },
  { $addFields: { index_list: { $range: [1, { $size: "$events" }] } } },
  { $unwind: "$index_list" },
  {
    $addFields: {
      current_event: { $arrayElemAt: ["$events", "$index_list"] },
      prev_event: { $arrayElemAt: ["$events", { $subtract: ["$index_list", 1] }] }
    }
  },
  // 后续时长计算、指标统计逻辑和上述5.0+方案完全一致
  {
    $addFields: {
      duration_ms: { $subtract: ["$current_event.timestamp", "$prev_event.timestamp"] },
      segment_state: "$prev_event.light_on"
    }
  }
])

注意事项 #

• 如果设备存在离线丢事件的情况，计算出的时长会包含离线时段，需要结合设备在线状态做二次过滤
• 需要按天/周等时间维度拆分统计时，可在计算出duration_ms后通过时间分桶逻辑实现
• 时长阈值、统计单位可根据业务需求直接调整，核心计算逻辑不需要改动

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