使用聚合框架使用 MongoDB 进行组计数

Group count with MongoDB using aggregation framework

提问by Matthew Ratzloff

Let's say my MongoDB schema looks like this:

假设我的 MongoDB 架构如下所示：

{car_id: "...", owner_id: "..."}

This is a many-to-many relationship. For example, the data might look like this:

这是一个多对多的关系。例如，数据可能如下所示：

+-----+----------+--------+
| _id | owner_id | car_id |
+-----+----------+--------+
|   1 |        1 |      1 |
|   2 |        1 |      2 |
|   3 |        1 |      3 |
|   4 |        2 |      1 |
|   5 |        2 |      2 |
|   6 |        3 |      4 |
|   7 |        3 |      5 |
|   8 |        3 |      6 |
|   9 |        3 |      7 |
|  10 |        1 |      1 | <-- not unique
+-----+----------+--------+

I want to get the number of cars owned by each owner. In SQL, this might look like:

我想获得每个车主拥有的汽车数量。在 SQL 中，这可能如下所示：

SELECT owner_id, COUNT(*) AS cars_owned
FROM (SELECT owner_id FROM car_owners GROUP BY owner_id, car_id) AS t
GROUP BY owner_id;

In this case, the result would look like this:

在这种情况下，结果将如下所示：

+----------+------------+
| owner_id | cars_owned |
+----------+------------+
|        1 |          3 |
|        2 |          2 |
|        3 |          4 |
+----------+------------+

How can I accomplish this same thing using MongoDB using the aggregation framework?

如何使用聚合框架使用 MongoDB 完成同样的事情？

回答by JohnnyHK

To accommodate the potential duplicates, you need to use two $groupoperations:

要容纳潜在的重复项，您需要使用两个$group操作：

db.test.aggregate([
    { $group: {
        _id: { owner_id: '$owner_id', car_id: '$car_id' }
    }},
    { $group: {
        _id: '$_id.owner_id',
        cars_owned: { $sum: 1 }
    }},
    { $project: {
        _id: 0,
        owner_id: '$_id',
        cars_owned: 1
    }}]
    , function(err, result){
        console.log(result);
    }
);

Gives a result with a format of:

给出以下格式的结果：

[ { cars_owned: 2, owner_id: 10 },
  { cars_owned: 1, owner_id: 11 } ]

回答by xameeramir

$groupis similar to SQL Group by command. In the below example, we're going to aggregate companies on the basis of the year in which they were founded. And calculate the average number of employees for each company.

$group类似于 SQL Group by 命令。在下面的示例中，我们将根据公司成立的年份汇总公司。并计算每家公司的平均员工人数。

db.companies.aggregate([{
    $group: {
      _id: {
        founded_year: "$founded_year"
      },
      average_number_of_employees: {
        $avg: "$number_of_employees"
      }
    }
  }, {
    $sort: {
      average_number_of_employees: -1
    }
  }
])

This aggregation pipeline has 2 stages

此聚合管道有 2 个阶段

1. $group
2. $sort

1. $group
2. $sort

Now, fundamental to the $groupstage is the _idfield that we specify as the part of the document. That is the value of the $groupoperator itself using a very strict interpretation of the arrogation framework syntax. _idis how we define, how we control, how we tune what the group stage uses to organize the documents that it sees.

现在，$group阶段的_id基础是我们指定为文档部分的字段。这是$group运算符本身的值，它使用了对 arrogation 框架语法的非常严格的解释。_id是我们如何定义、如何控制、如何调整小组阶段用来组织它看到的文档的内容。

The below query find the relationships of the people with companies using $sumoperator:

以下查询使用$sum运算符查找人员与公司的关系：

db.companies.aggregate([{
  $match: {
    "relationships.person": {
      $ne: null
    }
  }
}, {
  $project: {
    relationships: 1,
    _id: 0
  }
}, {
  $unwind: "$relationships"
}, {
  $group: {
    _id: "$relationships.person",
    count: {
      $sum: 1
    }
  }
}, {
  $sort: {
    count: -1
  }
}])