聚合来自两个数组的$ sum值 [英] Aggregate $sum values from two arrays
问题描述
我有这个收藏
{
"_id" : ObjectId("54f46f18c36dcc206d0cec38"),
"project" : 23123,
"title" : "Change of windows",
"description": "Change to better windows on building A"
"costs":[
{
category: 'Produktionskostnad',
value: 3000
},
{
category: 'Projekteringskostnad',
value: 2000
},
{
category: 'Overhead',
value: 1000
}
],
"energySaving" : [
{
"energy" : "electricity",
"type" : "lighting",
"value" : 24324
},
{
"energy" : "electricity",
"type" : "equipment",
"value" : 24324
},
{
"energy" : "electricity",
"type" : "fans",
"value" : 24324
},
{
"energy" : "electricity",
"type" : "distribution",
"value" : 24324
},
{
"energy" : "electricity",
"type" : "chiller",
"value" : 24324
},
{
"energy" : "electricity",
"type" : "other",
"value" : 24324
}
]
}
我需要一个计算总成本和总节能量的汇总.
I need a aggregation that calculates the total cost and the total energySaving.
要获得节省，我有以下查询:
To get the saving I have this query:
db.collection.aggregate( [
{ $unwind: "$energySaving" },
{
$group: {
_id: {
title: '$title',
description: '$description'
},
totalEnergySaving: { $sum: '$energySaving.value' }
}
}
]);
但是，如何在同一查询中计算总费用?我无法在同一查询中添加$ unwind费用.我可以以某种方式重置" $ group并再次查询吗?
But how do I calculate the total cost in the same query? I cant add $unwind cost in the same query. Can I "reset" the $group somehow and to the query again?
推荐答案
TLDR;
在现代MongoDB版本中，我们只执行一个 $group ，因为我们可以将数组项直接传递给 $sum 依次对数组和"和累加器"使用双"符号:
TLDR;
In Modern MongoDB releases we simply do a single $group since we can pass the array items directly to $sum with the "double" notation for "array sum" and "accumulator" in succession:
db.collection.aggregate([
{ "$group": {
"_id": {
"title": "$title",
"description": "$description"
},
"totalCosts": { "$sum": { "$sum": "$costs.value" } },
"totalEnergySaving": { "$sum": { "$sum": "$energySaving.value" } }
}}
])
2015年原始答案
这需要花点时间才能正确完成，但是描述它的最好方法是先对每个文档进行分组"，然后对总计进行分组":
Original 2015 answer
This takes a bit of juggling to do correctly but the best way to describe it is "deal with grouping per document first" and then "group the totals later":
db.collection.aggregate([
// Do cost per document
{ "$unwind": "$costs" },
{ "$group": {
"_id": "$_id",
"title": { "$first": "$title" },
"description": { "$first": "$description" },
"totalCosts": { "$sum": "$costs.value" },
"energySaving": { "$first": "$energySaving" }
}},
// Do energy saving per document
{ "$unwind": "$energySaving" },
{ "$group": {
"_id": "$_id",
"title": { "$first": "$title" },
"description": { "$first": "$description" },
"totalCosts": { "$first": "$totalCosts" },
"totalEnergySaving": { "$sum": "$energySaving.value" }
}},
// Now sum the real grouping
{ "$group": {
"_id": {
"title": "$title",
"description": "$description"
},
"totalCosts": { "$sum": "$totalCosts" },
"totalEnergySaving": { "$sum": "$totalEnergySaving" }
}}
])
通过为每个文档计算数组值的奇异值，并通过一次展开和分组一次一个数组"以避免每个数组成员的项重复，您可以为实际的奇数分组奠定基础想要.
By working out of the array values a singular value per document and also by unwinding and grouping "one array at a time" to avoid the replication of items per array member, you form a base to to the singular grouping that you actually want.
因此，当您> $unwind 一个数组，您将获得文档的多个副本，每个数组成员现在在每个文档副本中都表示为一个奇异值.在这里您不想做的是$unwind另一个数组，而您已经解开一个数组，因为这样会以相同的方式为该数组具有多少成员创建文档的更多副本".
So when you $unwind an array you get multiple copies of the document with each array member now represented as a singular value in each document copy. What you do not want to do here is $unwind another array while you already have one un-wound because that will create as many "more copies" of the document per how many members that array has in the same way.
使用> $group 返回到文档_id的值，这可以确保我们仅处理文档的原始部分，这些原始部分最初是未缠绕的".普通分组运算符，例如 $sum 仍然适用，但是 $first 可用于仅将复制的字段值中的仅一个"拉出数组外"，并将文档保留为您想要保留的字段的原始形式"，以及与您故意从中聚合的任何内容一起使用数组内容.
Using $group back to the document _id value at this point ensures we are only working the the original parts of the document that was initially "un-wound". Normal grouping operators like $sum still apply, but $first can be used to just pull up "only one" of those copied field values "outside the array" and pretty much return the document to it's "original form" for the fields you want to keep and also with anything you intentionally aggregated from the array contents.
为每个要重复的数组重复，然后继续执行另一个$group语句，这次语句一次添加了不止一个文档，并带有您之前创建的新奇异值.
Repeat for each array you want to, then move on to another $group statement that this time adds up more than just one document at a time with the new singular summed values that you made earlier.
这是在任何级别的分组中添加多个数组项的过程.当然，如果无论如何唯一的分组都是在文档级别进行的，那么您可以在将每个数组分组之后就放弃，或者确实接受在客户端代码中进行分组可能会更好.
That's the process to adding up multiple array items in any level of grouping. Of course if the only grouping was being done at the document level anyway, you could just give up after grouping each array, or indeed accept that it was probably better to do in client side code anyway.
这篇关于聚合来自两个数组的$ sum值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
