多个文档中的数组编号值总和 [英] Sum array number values across multiple documents

问题描述

我有Mongo文档，这些文档按顺序（按天）具有数组编号值，并且我希望对多个文档中按数组外部字段分组的每个位置的相同值求和。

I have Mongo documents which have array number values in order (it's by day) and I want to sum the same values across multiple documents for each position grouped by field outside of the array.

{"_id" : "1",
 "group" : "A",
 "value_list" : [1,2,3,4,5,6,7]
},
{"_id" : "2",
 "group" : "B",
 "value_list" : [10,20,30,40,50,60,70]
},
{"_id" : "3",
 "group" : "A",
 "value_list" : [1,2,3,4,5,6,7]
},
{"_id" : "4",
 "group" : "B",
 "value_list" : [10,20,30,40,50,60,70]
}

所以下面列出了我想要的结果。

So the results I'm after is listed below.

在value_list数组的上方和位置1处有两个A组文档，两个文档的值均为1，因此1 + 1 = 2。在两个文档中，位置2的值均为2，所以2 + 2 = 4，依此类推。

There are two group A documents above and at position 1 of the value_list array, both documents have the value of 1. so 1+1=2. Position 2 the value is 2 in both documents so 2+2=4, etc.

在value_list数组的上方和位置1有两个B组文档，两个文档的值为10。因此10 + 10 = 20。在两个文档中，位置2的值均为20，所以20 + 20 = 40，依此类推。

There are two group B documents above and at position 1 of the value_list array, both documents have the value of 10. so 10+10=20. Position 2 the value is 20 in both documents so 20+20=40, etc.

{"_id" : "30",
 "group" : "A",
 "value_list" : [2,4,6,8,10,12,14]
},
{"_id" : "30",
 "group" : "A",
 "value_list" : [20,40,60,80,100,120,140]
}

我如何使用Mongo Script做到这一点？谢谢，马特（Matt）

How would I do this using Mongo Script? Thanks, Matt

推荐答案

当然，最可扩展的方法是使用 includeArrayIndex $ unwind 选项$ c> 以便跟踪位置，然后 $ sum 展开组合，然后重新添加为数组格式：

Certainly the most "scalable" way is to use the includeArrayIndex option of $unwind in order to track the positions and then $sum the "unwound" combinations, before adding back into array format:

db.getCollection('test').aggregate([
  { "$unwind": { "path": "$value_list", "includeArrayIndex": "index" } },
  { "$group": {
    "_id": {
      "group": "$group",
      "index": "$index"
    },
    "value_list": { "$sum": "$value_list" }
  }},
  { "$sort": { "_id": 1 } },
  { "$group": {
      "_id": "$_id.group",
      "value_list": { "$push": "$value_list" }
  }},
  { "$sort": { "_id": 1 } }  
])

请注意，您需要 $ sort = https://docs.mongodb.com/manual/reference/operator/aggregation/group/ rel = nofollow noreferrer> $ group 为了保持阵列位置。

Note you need to $sort after the first $group in order to maintain the array positions.

如果您可以摆脱它，还可以将所有阵列应用于 $ reduce ：

If you can get away with it, you could also apply all arrays into $reduce:

db.getCollection('test').aggregate([
  { "$group": {
    "_id": "$group",
    "value_list": { "$push": "$value_list" }  
  }},
  { "$addFields": {
    "value_list": {
      "$reduce": {
        "input": "$value_list",
        "initialValue": [],
        "in": {
          "$map": {
            "input": {
              "$zip": {
                "inputs": ["$$this", "$$value"],
                "useLongestLength": true,
              }
            },
            "in": { "$sum": "$$this"}
          }
        }         
      } 
    }  
  }},
  { "$sort": { "_id": 1 } }
])

您使用初始 创建一个数组数组 $ push ，您可以使用 $ reduce 。 $ zip 对每个元素执行成对分配，然后在 $ map 使用 $ sum 。

Essentially you create an "array of arrays" using the initial $push, which you process with $reduce. The $zip does a "pairwise" assignment per element, which are then added together at each position during $map using $sum.

虽然效率更高，但并非如此对于大数据而言非常实用，因为在减少之前，您可能会通过将所有分组的数组添加到分组中的单个数组中来打破BSON限制。

While a bit more efficient, it's not really practical for large data as you would probably break the BSON limit by adding all grouped "arrays" into a single array on the grouping, before you "reduce" it.

两种方法都会产生相同的结果：

Either method produces the same result:

/* 1 */
{
    "_id" : "A",
    "value_list" : [ 
        2.0, 
        4.0, 
        6.0, 
        8.0, 
        10.0, 
        12.0, 
        14.0
    ]
}

/* 2 */
{
    "_id" : "B",
    "value_list" : [ 
        20.0, 
        40.0, 
        60.0, 
        80.0, 
        100.0, 
        120.0, 
        140.0
    ]
}

这篇关于多个文档中的数组编号值总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！