MongoDB聚合-如何获取一周中每天发生一次事件的次数的百分比值 [英] MongoDB aggregation - how to get a percentage value of how many times an event occurred per day of week

问题描述

我是MongoDB新手，所以如果我的问题很愚蠢，请不要判断我:P 我试图从MongoDB中获得一些结果以创建一个表，该表将显示每周某天我玩某款游戏多少的百分比统计信息(每天所有游戏合计= 100％).这是我的数据库JSON导入:

Im a MongoDB noob so please dont judge me if my question is stupid:P Im trying to get some results from MongoDB to create a table that would show percentage statistics of how much do i play a certain game per day of week (all games together per day = 100%). This is my JSON import for the database:

[
{"title":"GTA","date":"2017-11-13"},
{"title":"GTA","date":"2017-11-13"},
{"title":"BattleField","date":"2017-11-13"},
{"title":"BattleField","date":"2017-11-13"},
{"title":"BattleField","date":"2017-11-14"}
]

我编写了一个汇总，将结果按天分组，并计算了每天玩游戏的总次数……:

Ive written an aggregation that grouped the results by days and counted the total amount of times a game has been played per each day…:

db.games.aggregate([
    { $project: { _id: 0, date : { $dayOfWeek: "$date" }, "title":1} },
    { $group: { _id: {title: "$title", date: "$date"}, total: {$sum: 1} } }, 
    { $group: { _id: "$_id.date", types: {$addToSet: {title:"$_id.title", total: "$total"} } } }
])

...这就是我现在从MongoDB获得的信息:

…and this is what i got from MongoDB now:

/* 1 */
{
    "_id" : 3, 
    "types" : [
        {
            "title" : "BattleField",
            "total" : 1.0
        }
    ]
},

/* 2 */
{
    "_id" : 2, 
    "types" : [
        {
            "title" : "GTA",
            "total" : 2.0
        },
        {
            "title" : "BattleField",
            "total" : 2.0
        }
    ]
}

我需要获取的是一个看起来像这样的表:

what i need to get is a table that would look like this:

            Monday      Tuesday
GTA         50,00%      0%
BattleField 50,00%      100%

您能告诉我如何从Mongo获得这样的百分比结果吗?

Could you please advise me how can i get such percentage results from Mongo?

推荐答案

您的尝试非常接近解决方案！以下内容将为您指明正确的方向:

Your attempt was pretty close to a solution! The following should point you in the right direction:

aggregate([
    { $project: { "_id": 0, "date" : { $dayOfWeek: "$date" }, "title": 1 } }, // get the day of the week from the "date" field
    { $group: { "_id": { "title": "$title", "date": "$date" }, "total": { $sum: 1 } } }, // group by title and date to get the total per title and date
    { $group: { "_id": "$_id.date", "types": { $push: { "title": "$_id.title", total: "$total" } }, "grandTotal": { $sum: "$total" } } }, // group by date only to get the grand total
    { $unwind: "$types" }, // flatten grouped items
    { $project: { "_id": 0, "title": "$types.title", "percentage": { $divide: [ "$types.total", "$grandTotal" ] }, "day": { $arrayElemAt: [ [ "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" ], "$_id" ] } } }, // calculate percentage and beautify output for "day"
])

结果:

{
    "title" : "BattleField",
    "percentage" : 0.5,
    "day" : "Tue"
}
{
    "title" : "GTA",
    "percentage" : 0.5,
    "day" : "Tue"
}
{
    "title" : "BattleField",
    "percentage" : 1.0,
    "day" : "Wed"
}

这篇关于MongoDB聚合-如何获取一周中每天发生一次事件的次数的百分比值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！