MongoDB 聚合统计

MongoDB 中聚合统计SQL

yoyocn

2478人浏览 · 2022-11-03 20:35:51

yoyocn · 2022-11-03 20:35:51 发布

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 : { $s u m : “$ likes”}}}])
$avg	计算平均值	db.mycol.aggregate([{ $KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “$ by_user”, num_tutorial : { $a vg : “$ likes”}}}])
$min	获取集合中所有文档对应值得最小值。	db.mycol.aggregate([{ $KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “$ by_user”, num_tutorial : { $min : “$ likes”}}}])
$max	获取集合中所有文档对应值得最大值。	db.mycol.aggregate([{ $KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “$ by_user”, num_tutorial : { $ma x : “$ likes”}}}])
$push	将值加入一个数组中，不会判断是否有重复的值。	db.mycol.aggregate([{ $KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “$ by_user”, url : { $p u s h : “$ url”}}}])
$addToSet	将值加入一个数组中，会判断是否有重复的值，若相同的值在数组中已经存在了，则不加入。	db.mycol.aggregate([{ $KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “$ by_user”, url : { $a dd T o S e t : “$ url”}}}])
$first	根据资源文档的排序获取第一个文档数据。	db.mycol.aggregate([{ $KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “$ by_user”, first_url : { $f i rs t : “$ url”}}}])
$last	根据资源文档的排序获取最后一个文档数据	db.mycol.aggregate([{ $KaTeX parse error: Expected '}', got 'EOF' at end of input: …oup : {\_id : “$ by_user”, last_url : { $l a s t : “$ 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: { $s u m : "$ 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: \dots cust_id: "$ cust_id",
ord_date: {
month: { $m o n t h : "$ ord_date" },
day: { $d a y O f M o n t h : "$ ord_date" },
year: { $ye a r : "$ ord_date"}
}
},
total: { $s u m : "$ 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的记录，且排除日期的时间部分

类似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: \dots _id: "$ cust_id",
total: { $s u m : "$ 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分组的数量，排除日期的时间部分

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

华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

HAS2024：华为云以系统性创新加速千行万业智能化升级

华为云开发者联盟

数仓的两种轻量级数据交换格式：json与jsonb

华为云开发者联盟

如何基于香橙派AIpro对视频/图像数据进行预处理？

华为云开发者联盟

所有评论(0)

查看更多评论

yoyocn

@yoyocn

已为社区贡献1条内容