MongoDB 中聚合统计计算--$SUM表达式
我们一般通过表达式$sum来计算总和。因为MongoDB的文档有数组字段,所以可以简单的将计算总和分成两种:
1,统计符合条件的所有文档的某个字段的总和;
2,统计每个文档的数组字段里面的各个数据值的和。这两种情况都可以通过$sum表达式来完成。
以上两种情况的聚合统计,分别对应与聚合框架中的$group操作步骤和$project操作步骤。
1.$group
直接看例子吧。
Case1
测试集合mycol中的数据如下:
{
title:'MongoDBOverview',
description:'MongoDBisnosqldatabase',
by_user:'runoob.com',
url:'http://www.runoob.com',
tags:['mongodb','database','NoSQL'],
likes:100
},
{
title:'NoSQLOverview',
description:'Nosqldatabaseisveryfast',
by_user:'runoob.com',
url:'http://www.runoob.com',
tags:['mongodb','database','NoSQL'],
likes:10
},
{
title:'Neo4jOverview',
description:'Neo4jisnosqldatabase',
by_user:'Neo4j',
url:'http://www.neo4j.com',
tags:['neo4j','database','NoSQL'],
likes:750
}
现在我们通过以上集合计算每个作者所写的文章数,使用aggregate()计算
db.mycol.aggregate([{$group:{_id:"$by_user",num_tutorial:{$sum:1}}}])
查询结果如下:
/*1*/
{
"_id":"Neo4j",
"num_tutorial":1
},
/*2*/
{
"_id":"runoob.com",
"num_tutorial":2
}
Case2
统计每个作者被like的总和,计算表达式:
db.mycol.aggregate([{$group:{_id:"$by_user",num_tutorial:{$sum:"$likes"}}}])
查询结果如下;
/*1*/
{
"_id":"Neo4j",
"num_tutorial":750
},
/*2*/
{
"_id":"runoob.com",
"num_tutorial":110
}
Case3
上面例子有些简单,我们再丰富一下,测试集合sales的数据如下:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":ISODate("2014-01-01T08:00:00Z")}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":ISODate("2014-02-03T09:00:00Z")}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":ISODate("2014-02-03T09:05:00Z")}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":ISODate("2014-02-15T08:00:00Z")}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":ISODate("2014-02-15T09:05:00Z")}
需要完成的目标是,基于日期分组,统计每天的销售额,聚合公式为:
db.sales.aggregate(
[
{
$group:
{
_id:{day:{$dayOfYear:"$date"},year:{$year:"$date"}},
totalAmount:{$sum:{$multiply:["$price","$quantity"]}},
count:{$sum:1}
}
}
]
)
查询结果是:
{"_id":{"day":46,"year":2014},"totalAmount":150,"count":2}
{"_id":{"day":34,"year":2014},"totalAmount":45,"count":2}
{"_id":{"day":1,"year":2014},"totalAmount":20,"count":1}
2.$project阶段
Case4
假设存在一个students集合,其数据结构如下:
{"_id":1,"quizzes":[10,6,7],"labs":[5,8],"final":80,"midterm":75}
{"_id":2,"quizzes":[9,10],"labs":[8,8],"final":95,"midterm":80}
{"_id":3,"quizzes":[4,5,5],"labs":[6,5],"final":78,"midterm":70}
现在的需求是统计每个学生的平常的测验分数总和、实验分数总和、期末其中分数总和。
db.students.aggregate([
{
$project:{
quizTotal:{$sum:"$quizzes"},
labTotal:{$sum:"$labs"},
examTotal:{$sum:["$final","$midterm"]}
}
}
])
其查询输出结果如下:
{"_id":1,"quizTotal":23,"labTotal":13,"examTotal":155}
{"_id":2,"quizTotal":19,"labTotal":16,"examTotal":175}
{"_id":3,"quizTotal":14,"labTotal":11,"examTotal":148}
参考文献:
https://www.runoob.com/mongodb/mongodb-aggregate.html
https://docs.mongodb.com/manual/reference/operator/aggregation/sum/index.html
总结
以上所述是小编给大家介绍的MongoDB中聚合统计计算--$SUM表达式,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持!
如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!