mongodb聚合

数据库 > mongodb shell mongodb

Created At : 2019-11-12 14:03

Views 👀 :

mongodb进行多段聚合

数据结构

{
    "_id" : ObjectId("5dc4d9e2992a64618449871f"),
    "xmlname" : {
        "space" : "",
        "local" : "SPC"
    },
    "devicename" : "gateway",
    "spcname" : "ZST-2#",
    "time" : "2019-11-08 02:24:17.920",
    "data" : [ 
        {
            "cardid" : 13,
            "cable" : [ 
                {
                    "id" : 3,
                    "list" : [ 
                        {
                            "id" : 1,
                            "value" : "0.2"
                        }
                    ]
                }
            ]
        }
    ]
}

Step1: 筛选数据

{
  "$match": {
      "spcname": "ZST-2#",
      "devicename" : "gateway",
      "data.cardid" : 13,
      "data.cable.id" : 1
  }
}

Step2: 拆分数据data
```
{
  "$unwind": "$data"
}
```
Step3: 拆分数据cable
```
{
  "$unwind": "$data.cable"
}
```
Step4: 拆分数据list
```
{
  "$unwind": "$data.cable.list"
}
```

Step5: 筛选拆分之后的数据

{
  "$match": {
      "data.cardid" : 13,
      "data.cable.id" : 1
  }
}

Step6: 分组数据格式化(时间格式，数据格式)

{
  "$project": {
      "_id": 1,
      "listid": "$data.cable.list.id",
      "date": {
          "$dateToString": {
              "format": "%Y-%m-%d %H:00:00",
              "date": {
                  "$toDate": "$time"
              }
          }
      },
      "value": {
          "$toDouble": "$data.cable.list.value"
      }
  }
}

Step7: 多条件分组

{
  "$group": {
      "_id": { "listid": "$listid", "date": "$date"},
      "value": {
          "$avg": "$value"
      }
  }
}

Step8: 时间排序

{
  "$sort": {
      "_id.date": 1
  }
}

Step9: 再次分组

{
  "$group": {
      "_id": "$_id.listid",
      "series": {
          "$push": {
              "date": "$_id.date",
              "value": {
                  "$avg": "$value"
              }
          }
      }
  }
}

Step10: 格式化输出数据

{
  "$project": {
      "_id": 0,
      "listid": "$_id",
      "series": 1
  }
}

Step11: 输出数据排序
```
{
  "$sort": {
      "listid": 1
  }
}
```

完整聚合语句

db.getCollection('data_history').aggregate([
    {
        "$match": {
            "spcname": "ZST-2#",
            "devicename" : "gateway",
            "data.cardid" : 13,
            "data.cable.id" : 1
        }
    },
    {
        "$unwind": "$data"
    },
    {
        "$unwind": "$data.cable"
    },
    {
        "$unwind": "$data.cable.list"
    },
    {
        "$match": {
            "data.cardid" : 13,
            "data.cable.id" : 1
        }
    },
    {
        "$project": {
            "_id": 1,
            "listid": "$data.cable.list.id",
            "date": {
                "$dateToString": {
                    "format": "%Y-%m-%d %H:00:00",
                    "date": {
                        "$toDate": "$time"
                    }
                }
            },
            "value": {
                "$toDouble": "$data.cable.list.value"
            }
        }
    },
    {
        "$group": {
            "_id": { "listid": "$listid", "date": "$date"},
            "value": {
                "$avg": "$value"
            }
        }
    },
    {
        "$sort": {
            "_id.date": 1
        }
    },
    {
        "$group": {
            "_id": "$_id.listid",
            "series": {
                "$push": {
                    "date": "$_id.date",
                    "value": {
                        "$avg": "$value"
                    }
                }
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "listid": "$_id",
            "series": 1
        }
    },
    {
        "$sort": {
            "listid": 1
        }
    }
])

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 wind.kaisa@gmail.com