Mongodb聚合5分钟

我有 IoT 传感器，它们每秒发送事件，并将事件存储在 MongoDB 中，格式如下：

{
  "_id" : "stp_23", 
    "HP" : [
      1261.0, 
      1357.0, 
      1337.0, 
      1250.0, 
      1269.0, 
      1134.0, 
      1219.0, 
      1269.0, 
      1166.0, 
      1361.0, 
      1246.0,
      ... ]
    "TS" : [
      "2019-12-20T00:00:04.6440124Z", 
      "2019-12-20T00:00:14.6440124Z", 
      "2019-12-20T00:00:24.6440124Z", 
      "2019-12-20T00:00:34.6450042Z", 
      "2019-12-20T00:00:44.6450042Z", 
      "2019-12-20T00:00:54.6450042Z", 
      "2019-12-20T00:01:55.6460113Z", 
      "2019-12-20T00:02:05.6460113Z", 
      "2019-12-20T00:02:15.6460113Z", 
      "2019-12-20T00:02:35.6460113Z"
      ... ]
    "_id" : "stp_24" : ....
  }

我需要根据 TS 值每 5 分钟创建一次聚合。我按照下面的查询执行，因为 TS 是时间戳数组，出现错误

$convert 中不支持从数组到日期的转换，且没有 onError 值

db.hp_daily.aggregate([
  { "$group": {
    "_id": {
      "$toDate": {
        "$subtract": [
          { "$toLong": { "$toDate": "$TS" }  },
          { "$mod": [ { "$toLong": { "$toDate": "$TS" } }, 1000 * 60 * 5 ] }
        ]
      }
    },
    "count": { "$sum": 1 }
  }}
])

我正在寻找这样的结果：

[
  {
    "_id" : "stp_23",
    "ts" : ISODate("2019-12-20T00:05:00.000Z"),
    "avgHP" : 1200
  },
  {
    "_id" : "stp_24",
    "ts" : ISODate("2019-12-20T00:10:00.000Z"),
    "avgHP" : 1350
  }
]

假设

• Your HP and TS数组大小相同，每个时间戳对应HP值
• 修改最后 3 个时间戳（对于当前示例）以获得不同的时间范围

SETUP

1. 我们需要将数组更改为单个对象来应用$toDate
2. 另外，我们需要链接每个 HP 值以进行对应TS value link https://stackoverflow.com/a/39388932/3710490
3. 然后我们应用您的聚合（稍作更改）并获得所需的结果

db.hp_daily.aggregate([
  {
    $unwind: {
      path: "$HP",
      includeArrayIndex: "HP_index",

    }
  },
  {
    $unwind: {
      path: "$TS",
      includeArrayIndex: "TS_index",

    }
  },
  {
    $project: {
      HP: 1,
      TS: 1,
      compare: {
        $cmp: [
          "$HP_index",
          "$TS_index"
        ]
      }
    }
  },
  {
    $match: {
      compare: 0
    }
  },
  {
    $project: {
      _id: 1,
      HP: 1,
      TS: 1
    }
  },
  {
    "$group": {
      "_id": {
        _id: "$_id",
        "ts": {
          "$toDate": {
            "$subtract": [
              {
                "$toLong": {
                  "$toDate": "$TS"
                }
              },
              {
                "$mod": [
                  {
                    "$toLong": {
                      "$toDate": "$TS"
                    }
                  },
                  {
                    $multiply: [
                      1000,
                      60,
                      5
                    ]
                  }
                ]
              }
            ]
          }
        }
      },
      "avgHP": {
        "$avg": "$HP"
      }
    }
  },
  {
    $sort: {
      "_id._id": 1,
      "_id.ts": 1
    }
  },
  {
    $project: {
      _id: "$_id._id",
      ts: "$_id.ts",
      avgHP: "$avgHP"
    }
  }
])

[
  {
    "_id": "stp_23",
    "avgHP": 1261,
    "ts": ISODate("2019-12-20T00:00:00Z")
  },
  {
    "_id": "stp_23",
    "avgHP": 1265.3333333333333,
    "ts": ISODate("2019-12-20T00:05:00Z")
  }
]

蒙戈游乐场 https://mongoplayground.net/p/LoToCwBrPO3