测试一:$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 }