按日/月份分组,并在mongo中取得该日/月份的平均评级 [英] group by day/month and take average of rating on that day/month in mongo
问题描述
[
{
rating:4,
ceatedAt:ISODate(08-08T15:32:41.262 + 0000)
},
{
rating:3,
createdAt:ISODate(08-08T15:32:41.262 + 0000)
},
{
rating:3,
ceatedAt:ISODate $ 7 $ $
rating:5,
createdAt:ISODate(2016-07- 01T15:32:41.262 + 0000)
}
]
根据过滤条件取平均值为该特定日/月的评级。
如果日期的过滤器范围小于一个月,那么它将按日分组。如果日期的过滤器范围超过一个月,那么将按月份分组。
这是我期待的一种
[
{
average:3.5,
ceatedAt:2016-08-08,
month:August
},
{
average:4,
ceatedAt:2016-07-01,
月:七月
}
]
如何在mongodb中执行?
要按日/月份分组文档并返回输出中的月份键,您需要先 $ project
使用 Date 操作符,特别是 $ dateToString
和 $ month
运算符。这可以在 中完成 从
管道住宿主要是累加器运算符。 $ group
mongodb.com/manual/reference/operator/aggregation/group/#pipe._S_grouprel =nofollow> $ group
在前面的 $ group
管道,您可以通过格式化的日期键对文档进行分组,使用 $ avg
运算符,并将该月返回为整数从上一个管道使用 $ first
累加器运算符。
运行以下聚合管道应为您提供所需的结果:
db.collection.aggregate([
{
$ project:{
formattedDate :{
$ dateToString:{format:%Y-%m-%d,date:$ ceatedAt}
},
createdAtMonth {$ month:$ ceatedAt},
评级:1
}
},
{
$ group:{
_id:$ formattedDate,
average:{$ avg:$ rating},
month:{$ first:$ createdAtMonth},
}
}
])
I have this data in mongodb:
[
{
"rating": 4,
"ceatedAt": ISODate("2016-08-08T15:32:41.262+0000")
},
{
"rating": 3,
"createdAt": ISODate("2016-08-08T15:32:41.262+0000")
},
{
"rating": 3,
"ceatedAt": ISODate("2016-07-01T15:32:41.262+0000")
},
{
"rating": 5,
"createdAt": ISODate("2016-07-01T15:32:41.262+0000")
}
]
I need to take the average of rating for that particular day/month depending upon the filter criteria. if the filter range for date is less than a month then it will be grouped basis on day. if filter range for date is more than a month, then it will be grouped basis on month.
This is what I am kind of expecting
[
{
"average": 3.5,
"ceatedAt": "2016-08-08",
"month": "August"
},
{
"average": 4,
"ceatedAt": 2016-07-01,
"month": "July"
}
]
How can I do it in mongodb?
To group documents by day/month and return the month key in your output, you need to first $project
the key fields to appropriate formats using the Date operators, in particular the $dateToString
and $month
operators. This is done in the $project
stage prior to the $group
step since the $group
pipeline accomodates mostly the accumulator operators.
In the preceding $group
pipeline, you can the group the documents by the formatted date key, aggregate using the $avg
operator and return the month as an integer from the previous pipeline using $first
accumulator operator.
Running the following aggregation pipeline should give you the desired result:
db.collection.aggregate([
{
"$project": {
"formattedDate": {
"$dateToString": { "format": "%Y-%m-%d", "date": "$ceatedAt" }
},
"createdAtMonth": { "$month": "$ceatedAt" },
"rating": 1
}
},
{
"$group": {
"_id": "$formattedDate",
"average": { "$avg": "$rating" },
"month": { "$first": "$createdAtMonth" },
}
}
])
这篇关于按日/月份分组,并在mongo中取得该日/月份的平均评级的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!