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