mongodb - 使用 MongoDB 查询计算百分比和计数文档

Question

我正在尝试创建一个查询，它计算总金额、计算交易数量以及计算总金额的百分比。下面提供了示例文档和预期的输出。

样本文件

[
  {
    "_id": ObjectId("60dc27ac54b7c46bfa1b84b4"),
    "orders": [
      {
        "_id": ObjectId("60dc27ac54b7c46bfa1b84be"),
        "amount": 2000
      },
      {
        "_id": ObjectId("606557900dd9a34bcc0faf90"),
        "amount": 1500
      }
    ],
    "orderdate": "2021-08-29T00:00:00.000Z",
    "orderarea": "East Branch"
  },
  {
    "_id": ObjectId("60dc27ac54b7c46bfa1b75ge2"),
    "orders": [
      {
        "_id": ObjectId("6068016a7feeb34a6013e687"),
        "amount": 3000
      },
      {
        "_id": ObjectId("60dca0ea4550fd32beb5ad38"),
        "amount": 5000
      }
    ],
    "orderdate": "2021-08-30T00:00:00.000Z",
    "orderarea": "West Branch"
  },
  {
    "_id": ObjectId("60dc9ecf188cf96dd6f58c45"),
    "orders": [
      {
        "_id": ObjectId("60dca0ea4550fd32beb5ad3a"),
        "amount": 4500
      },
      {
        "_id": ObjectId("60dcac6d51b2bd1e009d7bdc"),
        "amount": 6000
      }
    ],
    "orderdate": "2021-08-31T00:00:00.000Z",
    "orderarea": "East Branch"
  }
]

输出

{"East Branch": 14000, "countoftransaction": 4, "amountpercentage": 63.63%},
{"West Branch": 8000, "countoftransaction": 2, "amountpercentage": 36.36%}

百分比应该计算它是从东支和西支总计22,000的百分比。

谢谢！

标签： mongodbmongodb-query

Answer 1

使用此聚合，但在 mongo 中我们无法创建动态字段名称"East Branch": 14000

这是聚合

[
    {
        '$unwind': {
            'path': '$orders'
        }
    }, {
        '$group': {
            '_id': None, 
            'total': {
                '$sum': '$orders.amount'
            }, 
            'orig': {
                '$push': '$$ROOT'
            }
        }
    }, {
        '$unwind': {
            'path': '$orig'
        }
    }, {
        '$group': {
            '_id': '$orig.orderarea', 
            'countoftransaction': {
                '$sum': 1
            }, 
            'ff': {
                '$sum': '$orig.orders.amount'
            }, 
            'dd': {
                '$addToSet': '$total'
            }
        }
    }, {
        '$addFields': {
            'total': {
                '$first': '$dd'
            }
        }
    }, {
        '$project': {
            'amountpercentage': {
                '$round': [
                    {
                        '$multiply': [
                            {
                                '$divide': [
                                    '$ff', '$total'
                                ]
                            }, 100
                        ]
                    }, 2
                ]
            }, 
            'countoftransaction': 1, 
            'amount': '$ff'
        }
    }
]

结果是这样的：

[
{_id:"West Branch",countoftransaction:2,amountpercentage:36.36,amount:8000},
{_id:"Eest Branch",countoftransaction:4,amountpercentage:63.64,amount:14000}
]