MongoDB 聚合统计
MongoDB 中聚合统计SQL
MongoDB 中聚合(aggregate)主要用于处理数据(诸如统计平均值，求和等)，并返回计算后的数据结果。有点类似 SQL 语句中的 count(*)。
aggregate() 方法
MongoDB中聚合的方法使用aggregate()。
语法格式
aggregate() 方法的基本语法格式如下所示：
db.COLLECTION_NAME.aggregate(AGGREGATE_OPERATION)
示例
集合中的数据如下：
{
_id: ObjectId(7df78ad8902c)
title: 'MongoDB Overview',
description: 'MongoDB is no sql database',
by_user: 'runoon.com',
url: 'http://www.runoon.com',
tags: ['mongodb', 'database', 'NoSQL'],
likes: 100
},
{
_id: ObjectId(7df78ad8902d)
title: 'NoSQL Overview',
description: 'No sql database is very fast',
by_user: 'runoon.com',
url: 'http://www.runoon.com',
tags: ['mongodb', 'database', 'NoSQL'],
likes: 10
},
{
_id: ObjectId(7df78ad8902e)
title: 'Neo4j Overview',
description: 'Neo4j is no sql database',
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}}}])
{
"result" : [
{
"_id" : "runoon.com",
"num_tutorial" : 2
},
{
"_id" : "Neo4j",
"num_tutorial" : 1
}
],
"ok" : 1
}
>
以上实例类似sql语句：
select by_user, count(*) from mycol group by by_user
在上面的例子中，我们通过字段 by_user 字段对数据进行分组，并计算 by_user 字段相同值的总和。
下表展示了一些聚合的表达式:
| 表达式 | 描述 | 实例 |
|---|---|---|
| $sum | 计算总和。 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “by_user”, num_tutorial : {sum:“sum : “sum:“likes”}}}]) |
| $avg | 计算平均值 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “by_user”, num_tutorial : {avg:“avg : “avg:“likes”}}}]) |
| $min | 获取集合中所有文档对应值得最小值。 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “by_user”, num_tutorial : {min:“min : “min:“likes”}}}]) |
| $max | 获取集合中所有文档对应值得最大值。 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “by_user”, num_tutorial : {max:“max : “max:“likes”}}}]) |
| $push | 将值加入一个数组中，不会判断是否有重复的值。 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “by_user”, url : {push:“push: “push:“url”}}}]) |
| $addToSet | 将值加入一个数组中，会判断是否有重复的值，若相同的值在数组中已经存在了，则不加入。 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “by_user”, url : {addToSet:“addToSet : “addToSet:“url”}}}]) |
| $first | 根据资源文档的排序获取第一个文档数据。 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “by_user”, first_url : {first:“first : “first:“url”}}}]) |
| $last | 根据资源文档的排序获取最后一个文档数据 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “by_user”, last_url : {last:“last : “last:“url”}}}]) |
Mongo与Mysql聚合类比
下面举了一些常用的mongo聚合例子和mysql对比，假设有一条如下的数据库记录（表名：orders）作为例子：
| SQL 操作/函数 | mongodb聚合操作 |
| where | $match |
| group by | $group |
| having | $match |
| select | $project |
| order by | $sort |
| limit | $limit |
| sum() | $sum |
| count() | $sum |
| join | $lookup （v3.2 新增） |
为了便于理解，先将常见的mongo的聚合操作和mysql的查询做下类比：
{
cust_id: "abc123",
ord_date: ISODate("2012-11-02T17:04:11.102Z"),
status: 'A',
price: 50,
items: [ { sku: "xxx", qty: 25, price: 1 },
{ sku: "yyy", qty: 25, price: 1 } ]
}
1. 统计orders表所有记录
db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )
类似mysql:
SELECT COUNT(*) AS count FROM orders
2.对orders表计算所有price求和
db.orders.aggregate( [
{
$group: {
_id: null,
total: { sum:"sum: "sum:"price" }
}
}
] )
类似mysql;
SELECT SUM(price) AS total FROM orders
```
3.对每一个唯一的cust\_id, 计算price总和
```
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] )
类似mysql:
SELECT cust_id,
SUM(price) AS total
FROM orders
GROUP BY cust_id
4.对每一个唯一对cust\_id和ord\_date分组，计算price总和，不包括日期的时间部分
db.orders.aggregate( [
{
KaTeX parse error: Expected '}', got 'EOF' at end of input: … cust_id: "cust_id",
ord_date: {
month: { month:"month: "month:"ord_date" },
day: { dayOfMonth:"dayOfMonth: "dayOfMonth:"ord_date" },
year: { year:"year: "year:"ord_date"}
}
},
total: { sum:"sum: "sum:"price" }
}
}
] )
类似mysql：
SELECT cust_id,
ord_date,
SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date
```
5.对于有多个记录的cust\_id，返回cust\_id和对应的数量
```
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
count: { $sum: 1 }
}
},
{ $match: { count: { $gt: 1 } } }
] )
类似mysql:
SELECT cust_id,
count()
FROM orders
GROUP BY cust_id
HAVING count() > 1
6.对每个唯一的cust\_id和ord\_date分组，计算价格总和，并只返回price总和大于250的记录，且排除日期的时间部分
db.orders.aggregate( [
{
KaTeX parse error: Expected '}', got 'EOF' at end of input: … cust_id: "cust_id",
ord_date: {
month: { month:"month: "month:"ord_date" },
day: { dayOfMonth:"dayOfMonth: "dayOfMonth:"ord_date" },
year: { year:"year: "year:"ord_date"}
}
},
total: { sum:"sum: "sum:"price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )
类似mysql:
SELECT cust_id,
ord_date,
SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date
HAVING total > 250
```
7.对每个唯一的cust\_id且status=A，计算price总和
```
db.orders.aggregate( [
{ $match: { status: 'A' } },
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] )
类似mysql:
SELECT cust_id,
SUM(price) as total
FROM orders
WHERE status = ‘A’
GROUP BY cust_id
8.对每个唯一的cust\_id且status=A，计算price总和并且只返回price总和大于250的记录
db.orders.aggregate( [
{ $match: { status: ‘A’ } },
{
KaTeX parse error: Expected '}', got 'EOF' at end of input: … _id: "cust_id",
total: { sum:"sum: "sum:"price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )
类似mysql:
SELECT cust_id,
SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY cust_id
HAVING total > 250
```
9.对于每个唯一的cust\_id，将与orders相关联的相应订单项order\_lineitem的qty字段进行总计
```
db.orders.aggregate( [
{ $unwind: "$items" },
{
$group: {
_id: "$cust_id",
qty: { $sum: "$items.qty" }
}
}
] )
类似mysql:
SELECT cust_id,
SUM(li.qty) as qty
FROM orders o,
order_lineitem li
WHERE li.order_id = o.id
GROUP BY cust_id
10.统计不同cust\_id和ord\_date分组的数量，排除日期的时间部分
db.orders.aggregate( [
{
KaTeX parse error: Expected '}', got 'EOF' at end of input: … cust_id: "cust_id",
ord_date: {
month: { month:"month: "month:"ord_date" },
day: { dayOfMonth:"dayOfMonth: "dayOfMonth:"ord_date" },
year: { year:"year: "year:"ord_date"}
}
}
}
},
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )
类似mysql:
SELECT COUNT(*)
FROM (SELECT cust_id, ord_date
FROM orders
GROUP BY cust_id, ord_date)
as DerivedTable
```
华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。
更多推荐
0
1- 0
已为社区贡献1条内容
所有评论(0)