MongoDB聚合框架 - 如何按日期范围,按天分组,每天返回平均值? [英] MongoDB Aggregation Framework - How to Match by Date Range, Group By Days, and Return Average For Each Day?
问题描述
我正在了解MongoDB的聚合框架,我想知道有人可以帮助我改进这个查询以执行以下操作:
- 按日期分组结果
- 每天返回平均值
这是我的模型,有一些有用的属性可以使这个查询变得更简单,像 day
property ...
//模型
var PriceHourlySchema = new Schema({
created :{
type:Date,
default:Date.now
},
day:{
type:String,
required:true,
trim:true
},
hour:{
type:String,
required:true,
trim:true
},
价格:{
类型:数量,
必需:真
},
日期:{
类型:日期,
必需:true
}
},
{
autoIndex:true
});
这是我的查询,迄今为止,这只返回范围内包含的所有日期的总平均数,并且不按日期分组,并且每天返回平均值,因此您可以通过 问题似乎是与 也就是说,您不需要将日期和时间作为单独的元素存储在文档中。它是冗余数据。您可以从 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: Here is my model, there are some helpful properties to make writing this query this easier, like the 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
The problem seems to be with the 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 这篇关于MongoDB聚合框架 - 如何按日期范围,按天分组,每天返回平均值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!日<$ code> $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ pre>
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 ,价格:{$ avg:'$ price'}}}
],function(err,results){
console.log(results);
}); // PriceHourly();
_id
您正在使用$ group。如果要按日期分组结果,您应该按 $ day
而不是 null
进行分组。尝试这样:
PriceHourly.aggregate([
{$ match:{date:{$ gt:start,$ lt; $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
console.log(results);
});
日期
字段中提取日
和小时
汇总查询使用 日期汇总
运算符。
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
});
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();
_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);
});
day
and hour
from date
field in the aggregation query using the Date Aggregation
operators.