kaisawind's blog

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

mongodb聚合 - Tue, Nov 12, 2019