根据职位百分比MongoDb聚合进行分组和求和 [英] Group and Sum according to the position percentage MongoDb Aggregation

问题描述

我必须对myArray的值进行分组，并以不同的归因求和，例如，对于本文档，我们应该进行分配，以使数组的第一个值占总数的40％，对数组的第一个值占总数的40％最后一次占总数的(2/5)，对等的其他人占总数的20％(1/5):

I have to group by the values of myArray and sum the total with a different attribution, it's mean for example for this documents we should make a distribution to make 40% of total for the the first value of the array and 40%(2/5) of total for the last and 20%(1/5) for others shared equitaly :

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



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

在此示例中，我们必须获得:

in this example we have to get :

"a" -> 2/5 * 5.8 + 2/5 * 5.8 + 1/10*5.8 + 1/10*5.8  2/5 * 4.5 / "b" -> 1/20*5.8 +0*4.5 / "c" -> 1/20*5.8 + 1/5*4.5 + 2/5*4.5 

结果必须是这样的:

[
  {
    "_id": "a",
    "total": 2/5 * 5.8 + 2/5 * 5.8 + 1/10*5.8 + 1/10*5.8  2/5 * 4.5
  },
  {
    "_id": "b",
    "total": 1/20*5.8
  },
  {
    "_id": "c",
    "linear_total": 1/20*5.8 + 1/5*4.5 + 2/5*4.5
  }
]

我必须按Path.value分组，并使用这种逻辑将每个数组中的对应值相加

I have to group by Path.value and sum the correspondant values in each array with this logic

非常感谢您的帮助

推荐答案

您可以在3.4管道中尝试以下聚合.

You can try below aggregation in 3.4 pipeline.

根据元素位置分配权重.

Assign weights based on element position.

$switch情况可根据数组大小提供不同的权重.

$switch case to provide different weights based on size of array.

db.col.aggregate([{"$addFields":{
  "myArray":{
    "$switch":{
      "branches":[{
        "case":{"$gt":[{"$size":"$myArray"},2]},
        "then":{"$concatArrays":[
            [{"value":{"$arrayElemAt":["$myArray.value",0]},"weight":{"$divide":[2,5]}}],
            {"$map":{
              "input":{"$slice":["$myArray",1,{"$subtract":[{"$size":"$myArray"},2]}]},
              "as":"val",
              "in":{"value":"$$val.value","weight":{"$divide": [{"$divide":[1,5]},{"$subtract":[{"$size":"$myArray"},2]} ]}}
            }},
            [{"value":{"$arrayElemAt":["$myArray.value",-1]},"weight":{"$divide":[2,5]}}]
          ]}
       },
       {
        "case":{"$eq":[{"$size":"$myArray"},2]},
        "then":{"$concatArrays":[
          [{"value":{"$arrayElemAt":["$myArray.value",0]},"weight":{"$divide":[1,2]}}],
          [{"value":{"$arrayElemAt":["$myArray.value",-1]},"weight":{"$divide":[1,2]}}]
        ]}
       }],
    "default":{"$concatArrays":[
      [{"value":{"$arrayElemAt":["$myArray.value",0]},"weight":1}],
    ]}}
  }
}},
{"$unwind":"$myArray"},
{"$group":{
  "_id":"$myArray.value",
  "linear_total":{"$sum":{"$multiply":["$myArray.weight","$total"]}}
}},
{"$sort":{"_id":1}}])

这篇关于根据职位百分比MongoDb聚合进行分组和求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！