猫鼬聚合以获取平均评分，对每个评分进行计数并返回实际评分 [英] Mongoose aggregate to get Average rating, count each rate and return the actual ratings

问题描述

我试图获取某产品的平均评级，加上每个评级的计数，还返回实际评级，并使用分页来限制返回的金额而不影响平均或计数.

Im trying to to get avg rating for a product, plus the count of each rating and also return the actual ratings and use pagination to limit amount that is returned without affecting the avg or count.

所以我正在尝试实现以下目标:

So I'm trying achieve something like this:

这是我的评分集合:

       {
            "productId": "3"
            "userid" : 5,
            "rating" : 5
            "comment": "this is nice"
        },
        {
            "productId": "3"
            "userid" : 2,
            "rating" :4
            "comment": "this is very nice"
        }

这是我想要的最终结果

{
    "_id" : 1,
    "avgRating": "3.6"
    "counts" : [
        {
            "rating" : 5,
            "count" : 8
        },
        {
            "rating" : 3,
            "count" : 2
        },
        {
            "rating" : 4,
            "count" : 4
        },
        {
            "rating" : 1,
            "count" : 4
        }
    ],
   "ratings": [
       {
            "productId": "3"
            "userid" : 5,
            "rating" : 5
            "comment": "this is nice"
        },
        {
            "productId": "3"
            "userid" : 2,
            "rating" :4
            "comment": "this is very nice"
        },
        {
            "productId": "3"
            "userid" : 12,
            "rating" : 4
            "comment": "this is okay"
        }
    ]
}

到目前为止，我已经掌握了每个评分的计数:

I have this so far which give me the count for each rating:

db.votes.aggregate([
    { $match: { postId: {$in: [1,2]} } },
    {
      $group: { _id: { post: "$postId", rating: "$vote" }, count: { $sum: 1 } }
    },
    {
      $group: {
        _id: "$_id.post",
        counts: { $push: { rating: "$_id.rating", count: "$count" } }
      }
    }
  ])

推荐答案

您并不遥远，我们只需要调整一些内容即可:

You're not far off, we just have to adjust some things:

db.votes.aggregate([
    {
        $match:
            {
                postId: {$in: [1, 2]}
            }
    },
    {
        $group: {
            _id: {post: "$postId", rating: "$vote"},
            count: {$sum: 1},
            reviews: {$push : "$$ROOT" } //keep the original document
        }
    },
    {
        $group: {
            _id: "$_id.post",
            counts: {$push: {rating: "$_id.rating", count: "$count"}},
            reviews: {$push: "$reviews"},
            totalItemCount: {$sum: "$count"}, //for avg calculation
            totalRating: {$sum: "$_id.rating"} // //for avg calculation
        }
    },
    {
        $project: {
            _id: "$_id",
            avgRating: {$divide: ["$totalRating", "$totalItemCount"]},
            counts: "$counts",
            reviews: {
                $slice: [
                    {
                        $reduce: {
                            input: "$reviews",
                            initialValue: [],
                            in: { $concatArrays: ["$$value", "$$this"] }
                        }
                    },
                    0, //skip
                    10 //limit
                ]
            }

        }
    }
])

请注意，为清晰起见，我保留了当前的管道结构，但是我觉得使用的管道利用了 $ facet 可能会更有效率，因为我们在分组时不必将整个集合保存在内存中. 我们将其分为两个部分，一个是当前管道减去检阅部分，另一个是只有$skip和$limit阶段.

Note that I preserved the current pipeline structure for clarity, however I feel that using a pipeline that utilizes $facet might be more efficient as we won't have to hold the entire collection in memory while grouping. we'll split it into two, one the current pipeline minus the review section and one with just $skip and $limit stages.

$ facet版本:

$facet version:

db.votes.aggregate([
    {
        "$match": {
            "postId": {"$in": [1, 2]}
        }
    },
    {
        "$facet": {
            "numbers": [
                {
                    "$group": {
                        "_id": {
                            "post": "$postId",
                            "rating": "$vote"
                        },
                        "count": {
                            "$sum": 1.0
                        }
                    }
                },
                {
                    "$group": {
                        "_id": "$_id.post",
                        "counts": {
                            "$push": {
                                "rating": "$_id.rating",
                                "count": "$count"
                            }
                        },
                        "totalItemCount": {
                            "$sum": "$count"
                        },
                        "totalRating": {
                            "$sum": "$_id.rating"
                        }
                    }
                }
            ],
            "reviews": [
                {
                    "$skip": 0.0
                },
                {
                    "$limit": 10.0
                }
            ]
        }
    },
    {
        "$unwind": "$numbers"
    },
    {
        "$project": {
            "_id": "$numbers._id",
            "reviews": "$reviews",
            "avgRating": {"$divide": ["$numbers.totalRating", "$numbers.totalItemCount"]},
            "counts": "$numbers.counts"
        }
    }
]);

这篇关于猫鼬聚合以获取平均评分，对每个评分进行计数并返回实际评分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！