MongoDB计算同一对象字段内状态变更的平均时间差

嘿，我来帮你搞定这个MongoDB状态变更平均时间差的计算问题！因为你的status_changes是嵌套对象，状态又是单向流转（lead→verification→active），我们可以通过MongoDB的聚合框架一步步处理。

核心思路 #

我们需要：

1. 提取每个客户每一步状态变更的时间差（只计算实际存在的状态对）
2. 收集所有有效的时间差数据
3. 对这些时间差求平均值

完整聚合查询 #

db.customer.aggregate([
  // 第一步：计算每一步状态变更的时间差（单位：毫秒）
  {
    $project: {
      lead_to_verification: {
        $cond: {
          if: { $and: ["$status_changes.lead", "$status_changes.verification"] },
          then: { $subtract: ["$status_changes.verification", "$status_changes.lead"] },
          else: null
        }
      },
      verification_to_active: {
        $cond: {
          if: { $and: ["$status_changes.verification", "$status_changes.active"] },
          then: { $subtract: ["$status_changes.active", "$status_changes.verification"] },
          else: null
        }
      }
    }
  },
  // 第二步：把有效的时间差收集到数组中（过滤掉null值）
  {
    $addFields: {
      time_diffs: {
        $filter: {
          input: ["$lead_to_verification", "$verification_to_active"],
          cond: { $ne: ["$$this", null] }
        }
      }
    }
  },
  // 第三步：展开数组，让每个时间差成为独立文档
  { $unwind: "$time_diffs" },
  // 第四步：计算所有有效时间差的平均值和总变更次数
  {
    $group: {
      _id: null,
      average_time_diff_ms: { $avg: "$time_diffs" },
      total_status_changes: { $sum: 1 }
    }
  },
  // 可选：转换为更易读的分钟/小时格式
  {
    $addFields: {
      average_time_diff_minutes: { $divide: ["$average_time_diff_ms", 60000] },
      average_time_diff_hours: { $divide: ["$average_time_diff_ms", 3600000] }
    }
  }
])

各阶段解释 #

• $project阶段：通过$cond判断每对状态是否都存在，存在则用$subtract计算时间差（MongoDB中日期相减结果为毫秒数），不存在则标记为null。
• $addFields阶段：用$filter过滤掉数组中的null值，只保留真实发生的状态变更时间差。
• $unwind阶段：将时间差数组拆分为单个文档，这样后续可以对所有差值统一计算平均值。
• $group阶段：以_id: null分组（即聚合所有文档），用$avg计算平均时间差，用$sum统计总共有多少次有效状态变更。
• 最后一个$addFields：可选步骤，把毫秒值转换为分钟或小时，让结果更直观。

示例结果说明 #

针对你提供的示例数据：

• Anil有2次变更：lead→verification（75秒）、verification→active（159秒）
• Sudhir有1次变更：lead→verification（2天）

最终计算出的平均时间差会是这3个差值的平均值，结果会包含毫秒、分钟、小时三种格式供你参考。

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