Mongodb聚合5分钟 [英] Mongodb Aggregation by 5 minutes

问题描述

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

I have IoT sensors which keep sending events every second, and storing the events in MongoDB below format:

{
  "_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是获得错误的时间戳数组

I need to create a aggregation every 5 minutes based on TS values. I have followed the below query to execute, since TS is array of timestamp getting Error

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

Unsupported conversion from array to date in $convert with no onError value

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

我正在寻找这样的结果:

I'm looking for a result like this:

[
  {
    "_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
  }
]

推荐答案

假设

• 您的HP和TS具有相同的数组大小，每个时间戳对应HP值
• 修改了最后3个时间戳(对于当前示例)，以获取不同的时间范围

• Your HP and TS has the same array size, each timestamp corresponds HP value
• Modified last 3 timestamps (for current example) in order to get differente time range

设置

1. 我们需要将数组更改为单个对象以应用$toDate
2. 此外，我们需要将每个HP值链接为对应的TS值链接
3. 然后我们应用您的汇总(略有变化)并获得理想的结果

1. We need to change arrays into single object to apply $toDate
2. Also, we need to link each HP value for correspond TS value link
3. Then we apply your aggregation (changed a bit) and get desired result

---

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")
  }
]

MongoPlayground

这篇关于Mongodb聚合5分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！