这是查询,
db.getCollection('_ad.insight').aggregate([
{
$match:{
date: {
$lte: ISODate('2018-12-31'),
$gte: ISODate('2017-01-01')
}
}
},
{
$project:{
_id: 0,
year: {$year: "$date"},
month: {$month: "$date"},
day: {$dayOfMonth: "$date"},
date: 1,
clicks: 1,
reach: 1
}
},
{
$group:{
_id: {
month: "$month",
year: "$year"
},
clicks: {$sum: "$clicks"},
reach: {$sum: "$reach"},
date: {$addToSet: "$date"}
}
},
{
$project:{
_id: 0,
month: "$_id.month",
year: "$_id.year",
clicks: 1,
reach: 1,
date: 1
}
}
]);
而我得到的回应,
/* 1 */
{
"clicks" : 1096,
"reach" : 33288,
"date" : [
ISODate("2018-01-01T00:00:00.000+05:00"),
ISODate("2017-12-31T00:00:00.000+05:00"),
ISODate("2017-12-28T00:00:00.000+05:00"),
ISODate("2017-12-26T00:00:00.000+05:00"),
ISODate("2017-12-24T00:00:00.000+05:00"),
ISODate("2017-12-23T00:00:00.000+05:00"),
ISODate("2017-12-25T00:00:00.000+05:00"),
ISODate("2017-12-29T00:00:00.000+05:00"),
ISODate("2017-12-22T00:00:00.000+05:00"),
ISODate("2017-12-21T00:00:00.000+05:00"),
ISODate("2017-12-30T00:00:00.000+05:00"),
ISODate("2017-12-20T00:00:00.000+05:00"),
ISODate("2017-12-27T00:00:00.000+05:00")
],
"month" : 12,
"year" : 2017
},
/* 2 */
{
"clicks" : 1629,
"reach" : 98113,
"date" : [
ISODate("2018-01-05T00:00:00.000+05:00"),
ISODate("2018-01-04T00:00:00.000+05:00"),
ISODate("2018-01-03T00:00:00.000+05:00"),
ISODate("2018-01-07T00:00:00.000+05:00"),
ISODate("2018-01-08T00:00:00.000+05:00"),
ISODate("2018-01-02T00:00:00.000+05:00"),
ISODate("2018-01-06T00:00:00.000+05:00")
],
"month" : 1,
"year" : 2018
}
样品收集:它是一个扁平的结构,包含大约 400 个字段,但我只显示我在查询中使用的字段。
{
"_id" : ObjectId("5akjbrd51f193455adtrf6fc"),
"clicks" : 5,
"reach" : 10
"date" : ISODate("2018-01-06T00:00:00.000+05:00"),
"post_engagement" : 127,
"post_reactions" : 1,
"post_shares" : 0,
"qualificationfailed" : 0,
"qualificationfailed_conversion_value" : 0
}
期望输出:
/* 1 */
{
"clicks" : 1096,
"reach" : 33288,
"date" : [
ISODate("2018-01-01T00:00:00.000+05:00"),//this shouldn't be here
ISODate("2017-12-31T00:00:00.000+05:00"),
ISODate("2017-12-28T00:00:00.000+05:00"),
ISODate("2017-12-26T00:00:00.000+05:00"),
ISODate("2017-12-24T00:00:00.000+05:00"),
ISODate("2017-12-23T00:00:00.000+05:00"),
ISODate("2017-12-25T00:00:00.000+05:00"),
ISODate("2017-12-29T00:00:00.000+05:00"),
ISODate("2017-12-22T00:00:00.000+05:00"),
ISODate("2017-12-21T00:00:00.000+05:00"),
ISODate("2017-12-30T00:00:00.000+05:00"),
ISODate("2017-12-20T00:00:00.000+05:00"),
ISODate("2017-12-27T00:00:00.000+05:00")
],
"month" : 12,
"year" : 2017
},
/* 2 */
{
"clicks" : 1629,
"reach" : 98113,
"date" : [
// ISODate("2018-01-01T00:00:00.000+05:00") this should be in this group
ISODate("2018-01-05T00:00:00.000+05:00"),
ISODate("2018-01-04T00:00:00.000+05:00"),
ISODate("2018-01-03T00:00:00.000+05:00"),
ISODate("2018-01-07T00:00:00.000+05:00"),
ISODate("2018-01-08T00:00:00.000+05:00"),
ISODate("2018-01-02T00:00:00.000+05:00"),
ISODate("2018-01-06T00:00:00.000+05:00")
],
"month" : 1,
"year" : 2018
}
问题是,ISODate("2018-01-01T00:00:00.000+05:00"(正如您在输出文档 1 中看到的,在日期数组中,上述日期位于第一个索引上。它显示了"月":12和"年":2017,因为我按月和年分组。所以我担心的是,ISODate("2018-01-01T00:00:00.000+05:00"(应该属于组号 2,即 2 个输出文档,但它显示在组 1 中。
我不知道我做错了什么,因为它是一个简单的管道。请帮忙!!
请注意,ISODate("2018-01-01T00:00:00.000+05:00")
采用 UTC + 5。这意味着此条目的日期为 UTC 时间 2017-12-31T19:00:00。
Mongo根据UTC对日期进行分组。
您可能需要查看这篇文章以处理不同的时区如何在不同的时区上按年-月-日聚合
正如 Juanín 所提到的,$year默认使用 UTC 时间。现在,您可以在将日期转换为字符串时提及时区。请参阅 https://docs.mongodb.com/manual/reference/operator/aggregation/month/index.html
下面将为您完成工作。
db.getCollection('_ad.insight').aggregate([
{
$match:{
date: {
$lte: ISODate('2018-12-31T00:00:00.000+05:00'),
$gte: ISODate('2017-01-01T00:00:00.000+05:00')
}
}
},
{
$project:{
_id: 0,
year: {$year: {date: "$date",timezone: "+0500"}},
month: {$month: {date: "$date",timezone: "+0500"}},
day: {$dayOfMonth: {date: "$date",timezone: "+0500"}},
date: 1,
clicks: 1,
reach: 1
}
},
{
$group:{
_id: {
month: "$month",
year: "$year"
},
clicks: {$sum: "$clicks"},
reach: {$sum: "$reach"},
date: {$addToSet: "$date"}
}
},
{
$project:{
_id: 0,
month: "$_id.month",
year: "$_id.year",
clicks: 1,
reach: 1,
date: 1
}
}
]);