如何在MongoDB中按时间戳计算相邻整点的energy字段差值

需求分析 #

从给定时序数据中，按code分组，提取每个整点时刻的首个energy值，计算相邻整点（如11:00与10:00）的energy差值，最终输出每个code对应最晚整点的差值结果。

聚合管道解决方案 #

以下是实现需求的完整MongoDB聚合查询：

db.collection.aggregate([
  // 1. 将字符串timestamp转换为Date类型，支持时间函数处理
  {
    $addFields: {
      timestamp: { $toDate: "$timestamp" }
    }
  },
  // 2. 按code和整点时间分组，取每个整点的第一条energy记录
  {
    $group: {
      _id: {
        code: "$code",
        hour: { $dateTrunc: { date: "$timestamp", unit: "hour" } }
      },
      firstEnergy: { $first: "$energy" },
      hourTimestamp: { $first: "$timestamp" }
    }
  },
  // 3. 按code和整点时间升序排序，保证时间顺序
  {
    $sort: {
      "_id.code": 1,
      "_id.hour": 1
    }
  },
  // 4. 用窗口函数获取上一个整点的energy值
  {
    $setWindowFields: {
      partitionBy: "$_id.code",
      sortBy: { "_id.hour": 1 },
      output: {
        prevEnergy: { $lag: "$firstEnergy" },
        currentTimestamp: { $first: "$hourTimestamp" }
      }
    }
  },
  // 5. 过滤无前置整点的记录，仅保留有差值的条目
  {
    $match: {
      prevEnergy: { $exists: true }
    }
  },
  // 6. 格式化输出字段，整理为目标结构
  {
    $project: {
      _id: 0,
      timestamp: "$currentTimestamp",
      code: "$_id.code",
      energy: { $subtract: ["$firstEnergy", "$prevEnergy"] }
    }
  }
])

步骤详解 #

1. 类型转换：将原始字符串格式的timestamp转为MongoDB原生Date类型，确保后续时间处理函数可用。
2. 整点分组取值：通过$dateTrunc将时间截断到小时维度，按code+整点时间分组，用$first提取每个整点的第一条energy数据（即该整点的首个值）。
3. 排序：按code和整点时间升序排列，保证每个code下的记录按时间先后顺序排列。
4. 窗口函数取值：使用$setWindowFields按code分区，通过$lag获取上一个整点的energy值，为差值计算提供数据。
5. 过滤无效记录：剔除没有前置整点的条目（如数据中的10:00整点，无更早的整点数据），仅保留能计算差值的记录。
6. 字段格式化：重新整理输出结构，将差值作为energy字段，保留timestamp和code，去除冗余字段。

最终输出 #

执行查询后将得到符合需求的结果：

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

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