CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Phorce
Phorce

Reputation: 4642

Get average of values over a period of time

I have the following collection:

{
        "_id" : ObjectId("58503934034b512b419a6eab"),
        "website" : "https://www.google.com",
        "name" : "Google",
        "keywords" : [ 
            "Search", 
            "Websites", 
        ],
        "tracking" : [ 
            {
                "_id" : ObjectId("5874aa1df63258286528598d"),
                "position" : 0,
                "created_at" : ISODate("2017-01-1T09:32:13.831Z"),
                "real_url" : "https://www.google.com",
                "keyword" : "Search"
            }, 
            {
                "_id" : ObjectId("5874aa1ff63258286528598e"),
                "keyword" : "Page",
                "real_url" : "https://www.google.com",
                "created_at" : ISODate("2017-01-1T09:32:15.832Z"),
                "found_url" : "https://google.com/",
                "position" : 3
            }, 
            {
                "_id" : ObjectId("5874aa21f63258286528598f"),
                "keyword" : "Search",
                "real_url" : "https://www.foamymedia.com",
                "created_at" : ISODate("2017-01-2T09:32:17.017Z"),
                "found_url" : "https://google.com/",
                "position" : 2
            }, 

            {
                "_id" : ObjectId("5874aa21f63258286528532f"),
                "keyword" : "Search",
                "real_url" : "https://www.google.com",
                "created_at" : ISODate("2017-01-2T09:32:17.017Z"),
                "found_url" : "https://google.com/",
                "position" : 1
            },     
        ]
    }

What I want to do is group all of the keywords together and calculate the average for that particular day, over a certain period.

So let's say for example:

Between: 2017-01-01 to 2017-01-31 the following keywords was tracked:

2017-01-01:
  'Seach' => 1, 
  'Page'  => 3, 
Average = 2 

2017-01-02: 
   'Search' => 4, 
   'Page'  => 6,
 Average = 5

....

So in the end result, I would be finished with (in this case):

{
    "_id" : ObjectId("5874dccb9cd90425e41b7c54"),
    "website" : "www.google.com", 
    "averages" : [
       "2", 
       "5"

    ]
}

Upvotes: 0

Views: 73

Answers (1)

s7vr
s7vr

Reputation: 75914

You can try something like this.

$unwind the tracking array followed by $sort on tracking.keyword and tracking.created_at.$group by day to get average for day across all categories. Final $group to push all the day's average values into array for a website.

db.website.aggregate([{
    $match: {
        "_id": ObjectId("58503934034b512b419a6eab")
    }
}, {
    $lookup: {
        from: "seo_tracking",
        localField: "website",
        foreignField: "real_url",
        as: "tracking"
    }
}, {
    $unwind: "$tracking"
}, {
    $sort: {
        "tracking.keyword": 1,
        "tracking.created_at": -1
    }
}, {
    $group: {
        _id: {
           $dayOfMonth: "$tracking.created_at"
         },
        "website": {
            $first: "$website"
        },
        "website_id": {
            $first: "$_id"
        },
        "averageByDay": {
            $avg: "$tracking.position"
        }
    }
}, {
    $group: {
        "_id": "$website_id",
        "website": {
            $first: "$website"
        },
        "average": {
            $push: "$averageByDay"
        }
    }
}]);

Upvotes: 1

Related Questions