如何在MongoDB中按日期分组获取数据的百分比总数 [英] How to get percentage total of data with group by date in MongoDB

问题描述

如何在 MongoDB 中按日期分组获取数据的百分比总数?

How to get percentage total of data with group by date in MongoDB ?

链接示例:https://mongoplayground.net/p/aNND4EPQhcb

我有一些这样的集合结构

I have some collection structure like this

{ 
    "_id" : ObjectId("5ccbb96706d1d47a4b2ced4b"), 
    "date" : "2019-05-03T10:39:53.108Z",  
    "id" : 166,   
    "update_at" : "2019-05-03T10:45:36.208Z",  
    "type" : "image"
}
{ 
    "_id" : ObjectId("5ccbb96706d1d47a4b2ced4c"), 
    "date" : "2019-05-03T10:39:53.133Z",  
    "id" : 166,   
    "update_at" : "2019-05-03T10:45:36.208Z", 
    "type" : "image"
}
{ 
    "_id" : ObjectId("5ccbb96706d1d47a4b2ced4d"), 
    "date" : "2019-05-03T10:39:53.180Z", 
    "id" : 166,  
    "update_at" : "2019-05-03T10:45:36.208Z", 
    "type" : "image"
}
{ 
    "_id" : ObjectId("5ccbb96706d1d47a4b2ced4e"), 
    "date" : "2019-05-03T10:39:53.218Z",  
    "id" : 166,  
    "update_at" : "2019-05-03T10:45:36.208Z",  
    "type" : "image"
}

我在 mongodb 中有查询以获取收集数据，如何获取总数据的百分比.在下面的示例查询中获取数据:

And I have query in mongodb to get data of collection, how to get percentage of total data. in bellow example query to get data :

db.name_collection.aggregate(
   [
        { "$match": {  
            "update_at": { "$gte": "2019-11-04T00:00:00.0Z", "$lt": "2019-11-06T00:00:00.0Z"},
             "id": { "$in": [166] } 
        } },  
        {
           "$group" : { 
               "_id": {
                       $substr: [ '$update_at', 0, 10 ] 
                },
               "count" : {
                   "$sum" : 1
               } 
           }
        },
        {
            "$project" : {
                "_id" : 0,
                "date" : "$_id",
                "count" : "$count" 
            }
        },
        {
            "$sort" : {
                "date" : 1
            }
        }
   ]
)

和这个回应:

{ 
    "date" : "2019-11-04", 
    "count" : 39
},
{ 
    "date" : "2019-11-05", 
    "count" : 135
}

如何从键计数中获取百分比数据总数?对此的示例响应:

how to get percentage data total from key count ? example response to this :

{ 
    "date" : "2019-11-04", 
    "count" : 39,
    "percentage" : "22%"
},
{ 
    "date" : "2019-11-05", 
    "count" : 135,
    "percentage" : "78%"
}

推荐答案

You have to group by null to get total count and then use $map 来计算百分比.$round 在这种情况下将是一个有用的运算符.最后你可以 $unwind 和 $replaceRoot 取回相同数量的文档:

You have to group by null to get total count and then use $map to calculate the percentage. $round will be a useful operator in such case. Finally you can $unwind and $replaceRoot to get back the same number of documents:

db.collection.aggregate([
    // previous aggregation steps
    {
        $group: {
            _id: null,
            total: { $sum: "$count" },
            docs: { $push: "$$ROOT" }
        }
    },
    {
        $project: {
            docs: {
                $map: {
                    input: "$docs",
                    in: {
                        date: "$$this.date",
                        count: "$$this.count",
                        percentage: { $concat: [ { $toString: { $round: { $multiply: [  { $divide: [ "$$this.count", "$total" ] }, 100 ] } } }, '%' ] }
                    }
                }
            }
        }
    },
    {
        $unwind: "$docs"
    },
    {
        $replaceRoot: { newRoot: "$docs" }
    }
])

蒙戈游乐场

这篇关于如何在MongoDB中按日期分组获取数据的百分比总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！