MongoDB 聚合统计

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 字段相同值的总和。

下表展示了一些聚合的表达式:

Mongo与Mysql聚合类比

下面举了一些常用的mongo聚合例子和mysql对比，假设有一条如下的数据库记录（表名：orders）作为例子：

为了便于理解，先将常见的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:"$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:"$ord_date" },
day: { $dayOfMonth:"$ord_date" },
year: { $year:"$ord_date"}
}
},
total: { $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:"$ord_date" },
day: { $dayOfMonth:"$ord_date" },
year: { $year:"$ord_date"}
}
},
total: { $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:"$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:"$ord_date" },
day: { $dayOfMonth:"$ord_date" },
year: { $year:"$ord_date"}
}
}
}
},
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )

类似mysql:
SELECT COUNT(*)
FROM (SELECT cust_id, ord_dateFROM ordersGROUP BY cust_id, ord_date)as DerivedTable
```

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！