mongodb聚合

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

文章标题:mongodb聚合

本文作者:kaisawind

发布时间:2019-11-12, 06:03:06

最后更新:2020-08-10, 08:17:32

原始链接:https://kaisawind.gitee.io/2019/11/11/2019-11-12-mongodb/

版权声明: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

目录
×

喜欢就点赞,疼爱就打赏