这篇文章将为大家详细讲解有关MongoDB中如何聚合统计计算,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。
我们一般通过表达式$sum来计算总和。因为MongoDB的文档有数组字段,所以可以简单的将计算总和分成两种:
1,统计符合条件的所有文档的某个字段的总和;
2,统计每个文档的数组字段里面的各个数据值的和。这两种情况都可以通过$sum表达式来完成。
以上两种情况的聚合统计,分别对应与聚合框架中的 $group
操作步骤和 $project
操作步骤。
1.$group
直接看例子吧。
Case 1
测试集合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 }
Case 2
统计每个作者被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 }
Case 3
上面例子有些简单,我们再丰富一下,测试集合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阶段
Case 4
假设存在一个 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}
关于“MongoDB中如何聚合统计计算”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。