如何在MongoDB中基于timestamp按小时计算字段值差值

需求说明 #

针对每个code字段分组，将当前小时的首个energy值与前一小时的首个energy值做差值计算，仅输出存在有效差值的结果。

原始数据集 #

[
  { 
    _id: 1,
    "timestamp": "2023-05-15T10:00:00Z",
    "code": "abc",
    "energy": 2333
  },
  { 
    _id: 2,
    "timestamp": "2023-05-15T10:10:00Z",
    "code": "abc",
    "energy": 2340
  },
  { 
    _id: 3,
    "timestamp": "2023-05-15T10:30:00Z",
    "code": "abc",
    "energy": 2349
  },
  { 
    _id: 4,
    "timestamp": "2023-05-15T10:40:00Z",
    "code": "abc",
    "energy": 2355
  },
  { 
    _id: 5,
    "timestamp": "2023-05-15T10:50:00Z",
    "code": "abc",
    "energy": 2360
  },
  { 
    _id: 6,
    "timestamp": "2023-05-15T11:00:00Z",
    "code": "abc",
    "energy": 2370
  },
  { 
    _id: 7,
    "timestamp": "2023-05-15T10:00:00Z",
    "code": "def",
    "energy": 3455
  },
  { 
    _id: 8,
    "timestamp": "2023-05-15T10:10:00Z",
    "code": "def",
    "energy": 3460
  },
  { 
    _id: 9,
    "timestamp": "2023-05-15T10:30:00Z",
    "code": "def",
    "energy": 3470
  },
  { 
    _id: 10,
    "timestamp": "2023-05-15T10:40:00Z",
    "code": "def",
    "energy": 3480
  },
  { 
    _id: 11,
    "timestamp": "2023-05-15T10:50:00Z",
    "code": "def",
    "energy": 3490
  },
  { 
    _id: 12,
    "timestamp": "2023-05-15T11:00:00Z",
    "code": "def",
    "energy": 3500
  }
]

实现方案 #

简化版（MongoDB 5.0+ 支持窗口函数） #

利用$setWindowFields窗口函数直接获取前一小时的energy值，代码更简洁高效：

db.collection.aggregate([
  // 1. 将字符串时间戳转换为Date类型，方便时间截断
  { $addFields: { date: { $toDate: "$timestamp" } } },
  // 2. 按code和小时分组，取每个小时的首个energy值
  {
    $group: {
      _id: { 
        code: "$code", 
        hour: { $dateTrunc: { date: "$date", unit: "hour" } } 
      },
      firstEnergy: { $first: "$energy" },
      firstTimestamp: { $first: "$timestamp" }
    }
  },
  // 3. 按code和小时排序，确保时间顺序正确
  { $sort: { "_id.code": 1, "_id.hour": 1 } },
  // 4. 用窗口函数$lag获取同一code下前一小时的energy值
  {
    $setWindowFields: {
      partitionBy: "$_id.code",
      sortBy: { "_id.hour": 1 },
      output: { prevHourEnergy: { $lag: "$firstEnergy", offset: 1 } }
    }
  },
  // 5. 过滤无前置数据的记录，计算差值
  { $match: { prevHourEnergy: { $exists: true } } },
  // 6. 调整输出格式匹配预期结果
  {
    $project: {
      _id: 0,
      timestamp: "$firstTimestamp",
      code: "$_id.code",
      energy: { $subtract: ["$firstEnergy", "$prevHourEnergy"] }
    }
  }
])

兼容版（支持MongoDB 5.0以下版本） #

通过数组分组遍历实现差值计算：

db.collection.aggregate([
  { $addFields: { date: { $toDate: "$timestamp" } } },
  {
    $group: {
      _id: { code: "$code", hour: { $dateTrunc: { date: "$date", unit: "hour" } } },
      firstEnergy: { $first: "$energy" },
      firstTimestamp: { $first: "$timestamp" }
    }
  },
  { $sort: { "_id.code": 1, "_id.hour": 1 } },
  // 将同一code的小时数据聚合为数组
  {
    $group: {
      _id: "$_id.code",
      hourlyData: { $push: { timestamp: "$firstTimestamp", energy: "$firstEnergy" } }
    }
  },
  // 遍历数组计算差值
  {
    $project: {
      _id: 0,
      code: "$_id",
      diffData: {
        $map: {
          input: "$hourlyData",
          as: "item",
          index: "index",
          in: {
            $cond: {
              if: { $gt: ["$$index", 0] },
              then: {
                timestamp: "$$item.timestamp",
                energy: { $subtract: ["$$item.energy", { $arrayElemAt: ["$hourlyData.energy", { $subtract: ["$$index", 1] }] }] }
              },
              else: "$$REMOVE"
            }
          }
        }
      }
    }
  },
  { $unwind: "$diffData" },
  {
    $replaceRoot: {
      newRoot: { $mergeObjects: ["$diffData", { code: "$code" }] }
    }
  }
])

输出结果 #

[
  {
    "timestamp": "2023-05-15T11:00:00Z",
    "code": "abc",
    "energy": 37
  },
  {
    "timestamp": "2023-05-15T11:00:00Z",
    "code": "def",
    "energy": 45
  }
]

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