MongoDB多条件分组聚合查询
1、样例数据
{
"_id" : ObjectId("5fa13fb76c3107345a82c047"),
"_class" : "java.util.HashMap",
"date" : "2020-11-03",
"result" : "命中失败",
"activityCode" : "TEST_CODE_123",
"activityName" : "测试活动123",
"channel" : "大唐"
}
{
"_id" : ObjectId("5fa13fb76c3107345a82c049"),
"_class" : "java.util.HashMap",
"date" : "2020-11-03",
"result" : "命中失败",
"activityCode" : "TEST_CODE_456",
"activityName" : "测试活动456",
"channel" : "大汉"
}
{
"_id" : ObjectId("5fa13fb76c3107345a82c050"),
"_class" : "java.util.HashMap",
"date" : "2020-11-03",
"result" : "命中失败",
"activityCode" : "TEST_CODE_456",
"activityName" : "测试活动456",
"channel" : "大秦"
}
2、需求分析
需要统计某一日期下不同活动不同渠道的调用量
3、统计SQL
db.collectionName.aggregate([{
$match: {
"date": "2020-11-03",
"result": "命中失败"
}
},
{
$group: {
_id: {
activityCode: "$activityCode",
channel: "$channel"
},
activityCode: {
"$first": "$activityCode"
},
channel: {
"$first": "$channel"
},
total: {
$sum: 1
}
}
},
{
$project: {
_id: 0
}
}])
4、SQL说明
#MongoDB的聚合管道将MongoDB文档在一个管道处理完毕后将结果传递给下一个管道处理
db.COLLECTION_NAME.aggregate({管道操作1},{管道操作2},...)
#这里其实就两个问题:如何多条件分组、如何展示各个分组条件的列
#多条件分组写法
{$group:{_id:{"别名1":"$分组字段1","别名2":"$分组字段2",····},total:{$sum:1}}}
#展示各个分组条件的列
#如果不经过处理直接查询会得到如下值:
{
"_id" : {
"activityCode" : "TEST_CODE_123",
"channel" : "大唐"
},
"total" : 1
}
#这里activityCode、channel就会被包裹在_id里面,需要手工处理
#取出_id里分组字段对应的值写法
{ $group: { _id: { activityCode: "$activityCode", channel: "$channel" }, activityCode: { "$first": "$activityCode" }, channel: { "$first": "$channel" }, total: { $sum: 1 } } }
#说明:
$first:根据资源文档的排序获取第一个文档数据 别名1:{"$first": "$分组字段1"}、别名2:{"$first": "$分组字段2"}
或者$last:根据资源文档的排序获取最后一个文档数据 别名1:{"$last": "$分组字段1"}、 别名2:{"$last": "$分组字段2"}
#得到结果
{
"_id" : {
"activityCode" : "TEST_CODE_123",
"channel" : "大唐"
},
"activityCode" : "TEST_CODE_123",
"channel" : "大唐",
"total" : 1
}
#最后在 $project: { _id: 0} 配置_id隐藏输出,得到想要的结果:
{
"activityCode" : "TEST_CODE_123",
"channel" : "大唐",
"total" : 1
}