在MongoDB上查找与每天的第一个日期相差1小时的日期

Question

下面是我的数据的摘录

{ _id: ObjectId("1"), date: 2021-07-01T08:00.000+00:00, price: 10, id: 1}
{ _id: ObjectId("2"), date: 2021-07-01T08:20.000+00:00, price: 10.5, id: 1}
{ _id: ObjectId("3"), date: 2021-07-01T08:40.000+00:00, price: 9.8, id: 1}
{ _id: ObjectId("4"), date: 2021-07-01T09:00.000+00:00, price: 10.2, id: 1}
{ _id: ObjectId("5"), date: 2021-07-01T09:20.000+00:00, price: 10.9, id: 1}
{ _id: ObjectId("6"), date: 2021-07-01T09:40.000+00:00, price: 13, id: 1}
{ _id: ObjectId("7"), date: 2021-07-02T07:00.000+00:00, price: 10, id: 2}
{ _id: ObjectId("8"), date: 2021-07-02T07:20.000+00:00, price: 10.2, id: 2}
{ _id: ObjectId("9"), date: 2021-07-02T07:40.000+00:00, price: 8, id: 2}
{ _id: ObjectId("10"), date: 2021-07-02T08:00.000+00:00, price: 10.7, id: 2}
{ _id: ObjectId("11"), date: 2021-07-02T08:20.000+00:00, price: 10, id: 2}
{ _id: ObjectId("12"), date: 2021-07-02T08:40.000+00:00, price: 11, id: 2}
{ _id: ObjectId("13"), date: 2021-07-03T08:00.000+00:00, price: 11.5, id: 2}
{ _id: ObjectId("14"), date: 2021-07-03T08:20.000+00:00, price: 10.8, id: 2}

是否有一种方法来SELECT每一天内的每一个id的第一个小时?

基于提取的结果将是

{ _id: ObjectId("1"), date: 2021-07-01T08:00.000+00:00, price: 10, id: 1}
{ _id: ObjectId("2"), date: 2021-07-01T08:20.000+00:00, price: 10.5, id: 1}
{ _id: ObjectId("3"), date: 2021-07-01T08:40.000+00:00, price: 9.8, id: 1}
{ _id: ObjectId("7"), date: 2021-07-02T07:00.000+00:00, price: 10, id: 2}
{ _id: ObjectId("8"), date: 2021-07-02T07:20.000+00:00, price: 10.2, id: 2}
{ _id: ObjectId("9"), date: 2021-07-02T07:40.000+00:00, price: 8, id: 2}
{ _id: ObjectId("13"), date: 2021-07-03T08:00.000+00:00, price: 11.5, id: 2}
{ _id: ObjectId("14"), date: 2021-07-03T08:20.000+00:00, price: 10.8, id: 2}

Answer 1

查询

• 将字符串日期转换为日期对象
• 按年按日分组
  按日收集所有文件
  查找每天的最小小时
• 过滤所有的日信息，只保留在最小小时内发生的信息
• 恢复文件结构

此处测试代码

查询(如果您有日期，而不是字符串，请跳过第一个$set阶段)

aggregate(
[ {
"$set" : {
"date" : {
"$dateFromString" : {
"dateString" : "$date"
}
}
}
}, {
"$group" : {
"_id" : {
"year" : {
"$year" : "$date"
},
"day" : {
"$dayOfYear" : "$date"
}
},
"day-info" : {
"$push" : "$$ROOT"
},
"min-hour" : {
"$min" : {
"$hour" : "$date"
}
}
}
}, {
"$project" : {
"day-info" : {
"$filter" : {
"input" : "$day-info",
"as" : "d",
"cond" : {
"$eq" : [ {
"$hour" : "$$d.date"
}, "$min-hour" ]
}
}
}
}
}, {
"$unwind" : {
"path" : "$day-info"
}
}, {
"$replaceRoot" : {
"newRoot" : "$day-info"
}
} ]
)

---

编辑(以保留日期，他们的差异是<从每天的最小日期起1小时，你可以这样做)>

此处测试代码

查询

db.collection.aggregate([
{
"$set": {
"date": {
"$dateFromString": {
"dateString": "$date"
}
}
}
},
{
"$group": {
"_id": {
"year": {
"$year": "$date"
},
"day": {
"$dayOfYear": "$date"
}
},
"day-info": {
"$push": "$$ROOT"
},
"min-date": {
"$min": "$date"
}
}
},
{
"$set": {
"day-info": {
"$filter": {
"input": "$day-info",
"as": "d",
"cond": {
"$lt": [
{
"$subtract": [
"$$d.date",
"$min-date"
]
},
{
"$multiply": [
60,
60,
1000
]
}
]
}
}
}
}
},
{
"$unwind": {
"path": "$day-info"
}
},
{
"$replaceRoot": {
"newRoot": "$day-info"
}
},
{
"$sort": {
"date": 1
}
}
])