Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mongodb group every 2 weeks

This is how to group data weekly :

 transactions.aggregate({   
    [
        {$match: {status: "committed"}},
        {$group: {
            _id: {
                $year: "$date",                                 
                $week: "$date"                          
            },
            count: {$sum: 1},
            start_date: {$min: "$date"},
            end_date: {$max: "$date"}                               
        }}
    ]
});     

The question is, how about grouping every 2 weeks and get the count?

Thank you.

CORRECT WAY ACCORDING TO ACCEPTED ANSWER

Thanks to Mzzl, this works for grouping every 2 weeks. Below is the complete version.

db.transactions.aggregate([
    {$match: {status: "committed"}},
    {$project: {
        twoweekperiod: {
            $subtract: [
              {$week: "$date"}, {$mod: [{$week: "$date"}, 2] }
            ]
        },
        date:1,
        status:1
   }},
   {$group: {
        _id: {
            year: {$year: "$date"},                                 
            twoweek: "$twoweekperiod"                      
        },
        count: {$sum: 1},
        start_date: {$min: "$date"},
        end_date: {$max: "$date"}                               
    }}
])
like image 894
Yudho Ahmad Diponegoro Avatar asked Jan 17 '14 13:01

Yudho Ahmad Diponegoro


1 Answers

Assuming $week contains the week number, you can create an 'every two weeks' number, and group by that. Try something like this:

{ 
    $project: {
        twoweekperiod: {
            $subtract: [
              '$week', {$mod: ['$week', 2] }
            ]
        }, status:1, date:1, etc...
   }
}

I don't know of a way to do an integer divide in a mongo query, so instead I subtract weeknumber mod 2 from the weeknumber, to get a number that changes every other week instead of every week. You could then try grouping by this number.

like image 55
Mzzl Avatar answered Sep 28 '22 03:09

Mzzl