MongoDB 中聚合统计计算--$SUM表达式$group/$project

参考：MongoDB 中聚合统计计算–$SUM表达式

测试一：$group

一、建表并插入数据：

db.createCollection("mycol")
db.mycol.insertMany([
  {
    title: 'MongoDB Overview',
    description: 'MongoDB is no sql database',
    by_user: 'runoob.com',
    url: 'http://www.runoob.com',
    tags: ['mongodb', 'database', 'NoSQL'],
    likes: 100
  },
  {
    title: 'NoSQL Overview',
    description: 'No sql database is very fast',
    by_user: 'runoob.com',
    url: 'http://www.runoob.com',
    tags: ['mongodb', 'database', 'NoSQL'],
    likes: 10
  },
  {
    title: 'Neo4j Overview',
    description: 'Neo4j is no sql database',
    by_user: 'Neo4j',
    url: 'http://www.neo4j.com',
    tags: ['neo4j', 'database', 'NoSQL'],
    likes: 750
  }
])

二、计算每个作者所写的文章数

db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : 1}}}])

_id: 表示以什么字段分组
$sum : 1 : 表示计算_id的个数

三、结果

[
  { "_id" : "runoob.com", "num_tutorial" : 2 },
  { "_id" : "Neo4j", "num_tutorial" : 1 }
]

四、统计每个作者被like的总和

db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : "$likes"}}}])

五、结果

[
  { "_id" : "runoob.com", "num_tutorial" : 110 },
  { "_id" : "Neo4j", "num_tutorial" : 750 }
]

测试二：$group

一、建表并插入数据：

db.createCollection("sales")
db.sales.insertMany([
{ "_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 }
         }
     }
   ]
)

这里用到了字段的加减乘除中的乘法 $multiply

$add：加法：
$subtract：减法
$multiply：乘法
$divide：除法
$concat：是用来连接两个或者多个字符串的

三、结果

{ "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 45, "count" : 2 }
{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 150, "count" : 2 }
{ "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 20, "count" : 1 }

四、日期过滤

db.sales.aggregate( [
  { $match : 
      { date : { 
          $gt : ISODate("2014-02-01T09:05:00Z"), 
          $lte : ISODate("2014-02-05T09:05: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") }

五、日期过滤并分组计算个数

db.sales.aggregate( [
  { 
	  $match : { 
		  date : { 
			  $gt : ISODate("2014-02-01T09:05:00Z"), 
			  $lte : ISODate("2014-02-05T09:05:00Z") 
		  } 
	  } 
  },
  { 
	  $group: { 
		  _id: null, 
		  count: { $sum: 1 } 
	  } 
  }
] );

 { "_id" : null, "count" : 2 }

测试三:$project

一、建表并插入数据：

db.createCollection("students")
db.students.insertMany([
{ "_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 }