使用Mongo聚合计算值之和 [英] Using Mongo aggregation to calculate sum of values

问题描述

我有发票，其中每个发票都包含项目列表.每个项目(除其他外)具有以下字段:

I have invoices, where each invoice contains a list of items. Each item has (among other things) the following fields:

• 名称
• 数量
• 总计

每个发票都有(除其他外)字段:

And each invoice has (among other things) the fields:

• _id
• 创建
• 项目

这些发票存在于专门的Mongo馆藏中，称为发票.

The invoices live in a dedicated Mongo collection, called invoices.

我想获取包含指定项目的所有发票，其中每个发票都需要返回以下信息:

I would like to get all the invoices containing the specified item, where for each invoice the following information needs to be returned:

• _id
• 创建
• (给定项目的)数量
• 总计(给定项目)

让我们将元组称为< id，date，qty，total>作为发票预测.因此，结果应为发票预测清单.

Let us call the tuple <id, date, qty, total> as the invoice projection. Thus the result should be a list of invoice projections.

如果发票两次列出给定的项目，则相应的发票将产生两个投影实例.如果发票根本没有列出给定的项目，那么该发票就不会出现在结果中.

If an invoice lists the given item twice, then the respective invoice yields two projection instances. If an invoice does not list the given item at all, then this invoice is not present in the result.

无论如何，我正在使用以下Mongo聚合管道来检索所需的预测:

Anyway, I am retrieving the required projections using the following Mongo aggregation pipeline:

  pipeline = [
    {$match: {'items.name': req.params.name}},
    {$project: {created: 1, 'items.name': 1, 'items.qty': 1, 'items.total': 1}},
    {$unwind: '$items'},
    {$match: {'items.name': req.params.name}},
    {$project: {created: 1, qty: '$items.qty', total: '$items.total'}}
  ],

管道的工作方式如下:

1. 首先匹配所有包含具有给定名称项目的发票. items.name上有一个Mongo索引，因此此$match是有效的.
2. 发票是一个大对象，因此剥离所有字段，仅保留以下结构:{_id: ?, created: ?, items: [{name: ?, qty: ?, total: ?}, ..., {name: ?, qty: ?, total: ?}]}
3. 展开items数组，现在我们有了{_id: ?, created: ?, 'items.name': ?, 'items.qty': ?, 'items.total': ?}对象的列表.
4. 删除所有与给定名称不匹配的项目.
5. 确定最终发票的投影形状.

1. First match all the invoices having an item with the given name. There is a Mongo index on items.name, so this $match is efficient.
2. An invoice is a large object, so strip all the fields leaving just the following structure: {_id: ?, created: ?, items: [{name: ?, qty: ?, total: ?}, ..., {name: ?, qty: ?, total: ?}]}
3. Unwind the items array, now we have a list of {_id: ?, created: ?, 'items.name': ?, 'items.qty': ?, 'items.total': ?} objects.
4. Remove all the items which do not match the given name.
5. Shape the final invoice projection.

然后通过以下代码输入发票预测的最终列表:

The final list of invoice projections is then put through the following code:

function prepareResult(projections) {
  var res = projections.reduce(function (acc, item) {
    acc.itemCount += item.qty;
    acc.total += item.total;
    delete item.total;
    return acc;
  }, {itemCount: 0, total: 0});
  res.items = projections;
  return res;
}

它对所有发票投影上的qty和total字段求和，并返回包含投影和计算得出的总和的新对象. total字段已从每个发票预测中删除，因为它只是所需的最终金额.

It sums the qty and total fields over all of the invoice projections and returns a new object containg the projections as well as the calculated sums. The total field is deleted from each and every invoice projection, since it is only the final sum that is needed.

我的问题-我可以将prepareResult函数的逻辑移到Mongo聚合管道中吗?

My question - can I move the logic of the prepareResult function into the Mongo aggregation pipeline?

推荐答案

您需要在管道中添加$ group步骤.

You need to add a $group step to the pipeline.

group的_id将是您求和的值(在这种情况下为常数，因为您希望总计).由于您要保留发票清单，因此可以通过$ push运算符将其累积到数组字段中.总计和数量的总和将用$ sum进行处理.

The _id of group will be what you are summing by (in this case a constant since you want a grand total). Since you want to retain the list of invoices, you can accumulate them into an array field via the $push operator. Sums for total and quantity would be handled with $sum.

{$group : { _id : 1,
            Total : { $sum : "$total" },
            ItemCount : { $sum : "$qty" },
            Invoices : { $push : { id : "$_id", created : "$created" }}
} }

这篇关于使用Mongo聚合计算值之和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！