CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
lino
lino

Reputation: 207

Get difference of values between two dates

I have this documents

{
    "url" : "/url1.php",
    "date" : ISODate("2016-01-02T00:00:00.000Z"),
    "hits" : 104
},
{
    "url" : "/url2.php",
    "date" : ISODate("2016-01-02T00:00:00.000Z"),
    "hits" : 121
},
{
    "url" : "/url3.php",
    "date" : ISODate("2016-01-02T00:00:00.000Z"),
    "hits" : 75
},
{
    "url" : "/url1.php",
    "date" : ISODate("2016-05-11T00:00:00.000Z"),
    "hits" : 100
},
{
    "url" : "/url2.php",
    "date" : ISODate("2016-05-11T00:00:00.000Z"),
    "hits" : 104
}

And I want to get difference between two dates, sort by this difference. If some document not exist in second date then the difference is first value.

{
    "url" : "/url1.php",
    "diff": 4
},
{
    "url" : "/url2.php",
    "diff": 17
},
{
    "url" : "/url3.php",
    "diff": 75
}

The url3.php not exists in second date.

I have tried this 

db.collection.aggregate(
{
   $match : 
      {
       $or:[{"date":{$gte:ISODate("2016-01-02"),$lte:ISODate("2016-01-02")}},{"date":{$gte:ISODate("2016-05-11"),$lte:ISODate("2016-05-11")}}],
       }
   },
   {$group:{_id:{"url":"$url",month:{$month:"$date"}},total:{"$sum":"$visitas"}}},
   {$group:{_id:{"url":"$_id.url",super:{"$subtract":[{$max:"$total"},{$min:"$total"}]}},max:{$first:"$total"},min:{$last:"$total"}}},
    {$group:{_id:{"url":"$_id.url",difference:{"$subtract":["$max","$min"]},"dateHight":"$max","dateLow":"$min","diff2":"$super"}}},
    {$unwind:"$_id.difference"},
    {$match:{"_id.difference":{$ne:0}}},
   {$sort:{"_id.difference":-1}}
)

This show

{
    "url" : "/url1.php",
    "diff": 4
},
{
    "url" : "/url2.php",
    "diff": 17
}

But not url3.php because not exists in second date

Upvotes: 0

Views: 4708

Answers (1)

Sede
Sede

Reputation: 61225

You need to first $sort your document by "date" in ascending order. From there, you then $group them by "url". The preliminary sorting stage allow us to use the $first operator and his partner in crime $last to return the first and last "hits".

But this is not enough we also need to return the number of document per group. This is because we need to perform a logical $condition processing in the $project. Here the condition is $eq which return true when we only have one document in the group and false otherwise. We then set the first hit to 0 as the returned value of expression.

db.collection.aggregate(
   [
        { "$sort": { "date": -1 } }, 
        { "$group": { 
            "_id": "$url", 
            "lastHit": { "$last": "$hits" }, 
            "firstHit": { "$first": "$hits" }, 
            "count": { "$sum": 1 } 
        }}, 
        { "$project": { 
            "diff": { 
                "$subtract": [ 
                    "$lastHit", 
                    { "$cond": [ 
                        { "$eq": [ "$count", 1 ] }, 
                        0, 
                        "$firstHit" 
                    ]} 
                ] 
            } 
        }} 
    ]
)

The query yields the following result.

{ "_id" : "/url2.php", "diff" : 17 }
{ "_id" : "/url3.php", "diff" : 75 }
{ "_id" : "/url1.php", "diff" : 4 }

Upvotes: 3

Related Questions