如何在MongoDB中使用构面计算百分比? [英] How to calculate the percentage using facet in MongoDB?

问题描述

我正在计算应用中的通知百分比以跟踪一些统计信息.

I am calculating the notification percentage in my app for tracking some statistics.

我的收藏集:

[
  {
    _id: "123",
    status: "seen",
    userId: "589"
  },
  {
    _id: "223",
    status: "seen",
    userId: "589"
  },
  {
    _id: "474",
    status: "unseen",
    userId: "589"
  },
  {
    _id: "875",
    status: "seen",
    userId: "112"
  },
  {
    _id: "891",
    status: "unseen",
    userId: "112"
  }
]

预期结果:

在这里我们可以看到 UserId-589 已收到3条通知，其中2条可见.因此计算为(totalNumOfSeen/totalNumOfNoticationsSent)* 100

Here we can see that, UserId - 589 has received 3 notifications out of which 2 are seen. So the calculation is (totalNumOfSeen/totalNumOfNoticationsSent) * 100

[{
    userId: "589",
    notificationPercentage : 66.66
},{
    userId: "112",
    notificationPercentage : 50
}]

我正在使用构面进行分组和匹配，但是这将返回一个对象数组，但我没有获得如何对此进行除法的方法.

I am using a facet for grouping and matching but that is returning me an array of object and I am not getting how to perform divide on this.

我的查询:

db.collection.aggregate([
  {
    $facet: {
      totalNumOfSeen: [
        {
          $match: {
            userId: "589",
            status: "seen"
          }
        },
        {
          $group: {
            _id: "$userId",
            totalNumOfSeen: {
              $sum: 1
            }
          }
        }
      ],
      totalNumOfNoticationsSent: [
        {
          $match: {
            userId: "589",
            
          }
        },
        {
          $group: {
            _id: "$userId",
            totalNumOfNoticationsSent: {
              $sum: 1
            }
          }
        }
      ]
    }
  }
])

上述查询给了我以下结果:

The Above Query is giving me the below Result:

[
  {
    "totalNumOfNoticationsSent": [
      {
        "_id": "589",
        "totalNumOfNoticationsSent": 3
      }
    ],
    "totalNumOfSeen": [
      {
        "_id": "589",
        "totalNumOfSeen": 2
      }
    ]
  }
]

MongoPlayground- https://mongoplayground.net/p/jHn2ZlshgDL

MongoPlayground - https://mongoplayground.net/p/jHn2ZlshgDL

现在，我需要再添加一个字段作为 notificationPercentage ，并根据上述方面结果计算通知百分比.非常感谢您的帮助.

Now I need to add one more field as notificationPercentage and calculate the notification percentage based on the above facet result. Really appreciate the help.

推荐答案

您可以尝试

userId 的
• $ group ，如果状态为 $ cond 获取 totalSeen 计数>已查看 ，使用 $ sum 获取通知总数，
• $ project 显示必填字段，并使用 $ divide 和 $ multiply
计算百分比

• $group by userId and get totalSeen count using $cond if status is seen, get total count of notification using $sum,
• $project to show required fields, and calculate percentage using $divide and $multiply

db.collection.aggregate([
  {
    $group: {
      _id: "$userId",
      totalSeen: {
        $sum: { $cond: [{ $eq: ["$status", "seen"] }, 1, 0] }
      },
      total: { $sum: 1 }
    }
  },
  {
    $project: {
      _id: 0,
      userId: "$_id",
      notificationPercentage: {
        $multiply: [{ $divide: ["$totalSeen", "$total"] }, 100]
      }
    }
  }
])

游乐场

这篇关于如何在MongoDB中使用构面计算百分比?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！