i hope someone can help me with my actual problem. I working since 2 month with MongoDB and in past i was only working with SQL databases, this is why thinking in mongoDB is not that easy for me.
I looked already at several examples within the Forum but i am not able to adapt them to my problem.
I have a big collection with gamers and gameresults.
The collection looks like:
> db.gameresults.find({"player": 6805}).pretty()
{
"_id" : ObjectId("c5b037807ab6cc37951a0000"),
"player" : 6805,
"birthdate" : ISODate("1999-01-01T00:00:00Z"),
"gender" : "m",
"race" : "ASIAN",
"playedgames" : [
{
"vGameName" : "GameA",
"highscore" : 36.20000076293945,
"hsDate" : ISODate("2015-07-20T01:01:00Z"),
},
{
"vGameName" : "GameA",
"highscore" : 13,
"hsDate" : ISODate("2015-08-03T01:02:00Z"),
},
{
"vGameName" : "GameB",
"highscore" : 55,
"hsDate" : ISODate("2015-08-03T01:01:00Z"),
},
{
"vGameName" : "GameC",
"highscore" : 104,
"hsDate" : ISODate("2015-11-08T09:30:00Z"),
},
{
"vGameName" : "GameA",
"highscore" : 81,
"hsDate" : ISODate("2015-06-03T11:04:00Z"),
},
{
"vGameName" : "GameC",
"highscore" : 21,
"hsDate" : ISODate("2016-12-03T11:04:00Z"),
},
{
"vGameName" : "GameB",
"highscore" : 7.170000076293945,
"hsDate" : ISODate("2016-09-08T09:30:00Z"),
}
]
}
Now i tried to create a aggregation on my collection to get a result that is showing me the following:
Average highscore within a 120 days time range, starting from smallest date sorted by player.
The result should look like, but within a 120 days intervall:
{ "_id" : { "player" : 6805, "hsDate" : ISODate("2015-06-03T00:00:00Z") }, "avgHighscore" : 51 }
{ "_id" : { "player" : 6805, "hsDate" : ISODate("2015-06-07T00:00:00Z") }, "avgHighscore" : 9.520000457763672 }
{ "_id" : { "player" : 6805, "hsDate" : ISODate("2015-06-08T00:00:00Z") }, "avgHighscore" : 60.50477317381989 }
{ "_id" : { "player" : 6805, "hsDate" : ISODate("2015-06-10T00:00:00Z") }, "avgHighscore" : 5.766666730244954 }
{ "_id" : { "player" : 6805, "hsDate" : ISODate("2016-06-14T00:00:00Z") }, "avgHighscore" : 9.5600004196167 }
{ "_id" : { "player" : 6805, "hsDate" : ISODate("2016-06-15T00:00:00Z") }, "avgHighscore" : 57.574060224220155 }
Results after the 120 days, should start with a new interval. I tried now for more than one week to figure out how to resolve this but the only way i found was a daily interval and i was not able to improve it to 120 days.
Also i was trying to use the suggestions within this forum from similar solutions for minutes, seconds or hours. But i was not able to change them to work with my 120 days interval.
Old MySQL Data looks like:
player highscore hsDate 6805 81 2015-06-03 12:04:00 6805 21 2015-06-03 12:04:00 6805 9.52 2015-06-07 21:00:00 6805 104 2015-06-08 10:30:00 6805 7.17 2015-06-08 10:30:00 6805 2.54 2015-06-08 10:30:00 6805 8.2 2015-06-08 10:30:00 6805 25.5 2015-06-08 10:30:00
My Code for 1 day interval is looking at the moment like:
db.gameresults.aggregate(
{ $unwind : "$playedgames" },
{ "$project" : { "player" : 1, "playedgames" : 1, "date" : '$playedgames.hsDate', "_id" : 0, "day" : { "$dayOfMonth" : '$playedgames.hsDate' }, "h" : { "$hour" : '$playedgames.hsDate' }, "m" : { "$minute" : '$playedgames.hsDate' }, "s" : { "$second" : '$playedgames.hsDate' }, "ml" : { "$millisecond" : '$playedgames.hsDate' } } },
{ "$project" : { "player" : 1, "playedgames" : 1, "date" : { "$subtract" : [ "$date", { "$add" : [ "$ml", { "$multiply" : [ "$s", 1000 ] }, { "$multiply" : [ "$m", 60, 1000 ] }, { "$multiply" : [ "$h", 60, 60, 1000 ] } ] } ] } } },
{ "$group" : { "_id" : { "player" : "$player", "hsDate" : "$playedgames.hsDate" }, "avg" : { "$avg" : "$playedgames.highscore" } } },
{ $sort: { _id : 1 } }
)
Until now i was not able to get it working with 120 Days interval. The year should also be checked because of year changes.
Please can someone help me to solve this problem.
Greetings, Eddi
