具有线性分布MongoDb聚合框架的分组和求和

I have to group by the values of myArray and sum the total with a linear attribution, it's mean for the both documents we should make a linear distribution of the total value on all the values :

我必须按照myArray的值进行分组,并将总和与线性归因相加,这对于两个文档都是平均值,我们应该对所有值的总值进行线性分布:

/* 1 */
{
    "_id" : ObjectId("5a535c48a4d86ed94a7e8618"),
    "total" : 5.8,
    "myArray" : [ 
        {
            "value" : "a"
        }, 
        {
            "value" : "b
        }, 
        {
            "value" : "a"
        }, 
        {
            "value" : "c"
        },
        {
            "value" : "a"
        },
        {
            "value" : "a"
        }
    ]
}


/* 2 */
{
    "_id" : ObjectId("5a535c48a4d86ed94a7e8619"),
    "total" : 4.5,
    "myArray" : [ 
        {
            "value" : "a"
        }, 
        {
            "value" : c"
        }, 
        {
            "value" : "c"
        }
    ]
}

in this example we have to get :

在这个例子中我们必须得到:

"a" -> 4/6 * 5.8 + 1/3 * 4.5 / "b" -> 1/6*5.8 +0*4.5 / "c" -> 1/6*5.8 + 2/3*4.5

The result have to be like this :

结果必须是这样的:

 [
      {
        "_id": "a",
        "linear_total": 4/6 * 5.8 + 1/3 * 4.5
      },
      {
        "_id": "b",
        "linear_total": 1/6*5.8 +0*4.5
      },
      {
        "_id": "c",
        "linear_total": 1/6*5.8 + 2/3*4.5
      }
    ]

I don't know if this is an available features in aggregation that can calculate the distribution of a value in array, so I can easily multiply by the total and sum .. or you have any ideas of how to make it. Thank you so much

我不知道这是否是聚合中的可用功能,可以计算数组中值的分布,因此我可以轻松地乘以总数和总和..或者您对如何制作它有任何想法。非常感谢

2 个解决方案

#1

This would work:

这可行:

db.collection.aggregate([
{
  $project: {
    _id: 1,
    total: 1,
    myArray: 1,
    arraySize: { $size: "$myArray"} // count the size of your array
  }
},
{
  $unwind: "$myArray" // flatten your array
},
{
  $group: {
    _id: {id: "$_id", value: "$myArray.value"},
    total: { $first: "$total"},
    myArray: { $first: "$myArray"},
    arraySize: { $first: "$arraySize" },
    countVal: { $sum: 1} // count the occurrence of value out of total array size in each document  
  }
},
{
  $project: {
    _id: 1,
    divmulti: { "$multiply": [ { "$divide": ["$countVal","$arraySize"] }, "$total" ] } 
  }
},
{
  $group: {
    _id: "$_id.value", // group by value
    "linear_total": { $sum: "$divmulti" } // and some the results
  }
}
])

With this example, you would get this as result:

在这个例子中,你会得到这个结果:

{ 
    "_id" : "c", 
    "linear_total" : 3.966666666666667
}
{ 
    "_id" : "b", 
    "linear_total" : 0.9666666666666666
}
{ 
    "_id" : "a", 
    "linear_total" : 5.366666666666666
}

#2

You can try below aggregation.

您可以尝试以下聚合。

$addFields to add extra field to keep the size of "myArray". $unwind "myArray" $group to count the occurrence for each value within each "myArray" $group on "value" to calculate linear total.

$ addFields添加额外字段以保持“myArray”的大小。 $ unwind“myArray”$ group计算每个“myArray”$ group中“value”的每个值的出现次数,以计算线性总数。

db.col.aggregate([
{"$addFields":{
  "tot":{"$size":"$myArray"}
}},
{"$unwind":"$myArray"},
{"$group":{
  "_id":{
    "id":"$_id",
    "value":"$myArray.value"
  },
  "total":{"$first":"$total"},
  "tot":{"$first":"$tot"},
  "occ":{"$sum":1}
}},
{"$group":{
  "_id":"$_id.value",
  "linear_total":{"$sum":{"$multiply":[{"$divide":["$occ","$tot"]},"$total"]}}
}},
{"$sort":{"_id":1}}])

#1