我想知道是否有人可以帮助我正确实现聚合功能。我正在尝试计算健身的访问量。我有Visits表,其中保存了每次访问(从,到)日期。也可以访问一些保存的服务。
[{
"from": ISODate("2020-01-17T10:23:27.645Z"),
"to": ISODate("2020-01-17T12:23:28.760Z"),
"visits": [
{
"from": ISODate("2020-01-17T10:23:27.646Z"),
"to": ISODate("2020-01-17T10:30:28.760Z"),
"service": ObjectId("5e05f17d6b7f7920d4a62403")
},
{
"from": ISODate("2020-01-17T10:30:29.760Z"),
"to": ISODate("2020-01-17T12:23:28.760Z"),
"service": ObjectId("5d05f17dt57f7920d4a62404")
}
],
...
},
{
"from": ISODate("2020-01-17T10:40:00.000Z"),
"to": ISODate("2020-01-17T11:30:28.760Z"),
"visits": [
{
"from": ISODate("2020-01-17T10:40:00.000Z"),
"to": ISODate("2020-01-17T11:30:28.760Z"),
"service": ObjectId("h505f17s6b2f7920d4a6295y")
}
],
...
}
]
如果当前时间是13:35,我想从今天00:00开始获得今天的结果,然后直到13:00为止:
[{
'date': '2020-01-18T00:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T00:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T00:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T01:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T02:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T03:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T04:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T05:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T06:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T07:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T08:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T09:00:0.000Z'
'visits': 0
},
{
'date': '2020-01-18T010:00:0.000Z'
'visits': 2
},
{
'date': '2020-01-18T11:00:0.000Z'
'visits': 2
},
{
'date': '2020-01-18T12:00:0.000Z'
'visits': 1
},
{
'date': '2020-01-18T13:00:0.000Z'
'visits': 0
}]
尝试了太多示例,但无济于事,请帮助!
最佳答案
说明
$$NOW
,我们将获得当前日期。如果您手动设置currDate
,today
,nextDay
,MongoDB聚合将限制所需的值。 today
(yyyy-MM-dd 00:00:00)-currDate
(yyyy-MM-dd 13:35:00)〜13小时的小时数,并使用$range
运算符创建数组您可以使用以下查询:
db.collection.aggregate([
{
$addFields: {
nextDay: {
$add: [
{
$dateFromString: {
dateString: {
$substr: [
{
$toString: "$$NOW"
},
0,
10
]
},
format: "%Y-%m-%d"
}
},
{
$multiply: [
24,
60,
60,
1000
]
}
]
},
today: {
$dateFromString: {
dateString: {
$substr: [
{
$toString: "$$NOW"
},
0,
10
]
},
format: "%Y-%m-%d"
}
},
currDate: {
$dateFromString: {
dateString: {
$substr: [
{
$toString: "$$NOW"
},
0,
13
]
},
format: "%Y-%m-%dT%H",
timezone: "-01"
}
}
}
},
{
$addFields: {
interval: {
$range: [
0,
{
$add: [
{
$divide: [
{
$subtract: [
"$currDate",
"$today"
]
},
{
$multiply: [
60,
60,
1000
]
}
]
},
1
]
},
1
]
}
}
},
{
$unwind: "$interval"
},
{
$addFields: {
date: {
$add: [
"$today",
{
$multiply: [
"$interval",
60,
60,
1000
]
}
]
},
nextHour: {
$add: [
"$today",
{
$multiply: [
{
$add: [
"$interval",
1
]
},
60,
60,
1000
]
}
]
}
}
},
{
$project: {
_id: 0,
date: 1,
today: 1,
nextDay: 1,
visits: {
$reduce: {
input: "$visits",
initialValue: 0,
in: {
$add: [
"$$value",
{
$cond: [
{
$or: [
{
$and: [
{
$gte: [
"$$this.from",
"$date"
]
},
{
$lte: [
"$$this.from",
"$nextHour"
]
}
]
},
{
$and: [
{
$lte: [
"$date",
"$$this.to"
]
},
{
$gte: [
"$date",
"$$this.from"
]
}
]
}
]
},
1,
0
]
}
]
}
}
}
}
},
{
$match: {
$expr: {
$and: [
{
$gte: [
"$date",
"$today"
]
},
{
$lte: [
"$date",
"$nextDay"
]
},
]
}
}
},
{
$group: {
_id: "$date",
visits: {
$sum: "$visits"
}
}
},
{
$sort: {
_id: 1
}
}
])
MongoPlayground
对于JS设置,请单击here
如果您想要更优雅的方式,可以尝试this解决方案