javascript - MongoDB Aggregation Framework - How to Match by Date Range, Group By Days, and Return Average For Each Day? - Stack

admin管理员组

文章数量:1279144

I'm just learning about MongoDB's Aggregation Framework, and I'm wondering if someone can help me improve this query to do the following:

• Find/Match Records with Dates in between a submitted range
• Group the results by Day
• Return Averages for each Day

Here is my model, there are some helpful properties to make writing this query this easier, like the day property...

// Model
var PriceHourlySchema = new Schema({
    created: {
        type: Date,
        default: Date.now
    },
    day: {
        type: String,
        required: true,
        trim: true
    },
    hour: {
        type: String,
        required: true,
        trim: true
    },
    price: {
        type: Number,
        required: true
    },
    date: {
        type: Date,
        required: true
    }
}, 
{ 
    autoIndex: true 
});

Here is my query so far, this only returns a Total Average for all dates included within the range, and does not group by days and return averages for each day, so you can $group by day...

var start       = moment.utc(req.query.start).startOf('year').toDate();
var end         = moment.utc(req.query.start).add('years',1).startOf('year').add('hours',1).toDate();

PriceHourly.aggregate([
    { $match: { date: { $gt: start, $lt: end } } },
    { $group: { _id: null, price: { $avg: '$price' } } }
], function(err, results){
    console.log(results); 
}); // PriceHourly();

I'm just learning about MongoDB's Aggregation Framework, and I'm wondering if someone can help me improve this query to do the following:

• Find/Match Records with Dates in between a submitted range
• Group the results by Day
• Return Averages for each Day

Here is my model, there are some helpful properties to make writing this query this easier, like the day property...

// Model
var PriceHourlySchema = new Schema({
    created: {
        type: Date,
        default: Date.now
    },
    day: {
        type: String,
        required: true,
        trim: true
    },
    hour: {
        type: String,
        required: true,
        trim: true
    },
    price: {
        type: Number,
        required: true
    },
    date: {
        type: Date,
        required: true
    }
}, 
{ 
    autoIndex: true 
});

Here is my query so far, this only returns a Total Average for all dates included within the range, and does not group by days and return averages for each day, so you can $group by day...

var start       = moment.utc(req.query.start).startOf('year').toDate();
var end         = moment.utc(req.query.start).add('years',1).startOf('year').add('hours',1).toDate();

PriceHourly.aggregate([
    { $match: { date: { $gt: start, $lt: end } } },
    { $group: { _id: null, price: { $avg: '$price' } } }
], function(err, results){
    console.log(results); 
}); // PriceHourly();

• javascript
• node.js
• mongodb
• express
• mongoose

Share Improve this question Follow asked Mar 31, 2014 at 20:31 ac360ac360 7,8351414 gold badges5555 silver badges9595 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 9

The problem seems to be with the _id you are using for $group. You should group by $day rather than null, if you want to group the results by day. Try this:

PriceHourly.aggregate([
    { $match: { date: { $gt: start, $lt: end } } },
    { $group: { _id: "$day", price: { $avg: '$price' } } }
], function(err, results){
    console.log(results); 
});

That said, you do not need to store the day and hour as separate elements in the document. It's redundant data. You can extract the day and hour from date field in the aggregation query using the Date Aggregation operators.

本文标签：