我有一個像下面這樣的集合。
{
"field1":"value1",
"created_at":"2022-01-01T11:42:01Z"
},
{
"field1":"value2",
"created_at":"2022-01-01T11:22:15Z"
}
我需要按 15 分鐘的時間間隔對結果進行分組，并從這個集合中投影出如下所示的結果。
[{
"from":"2022-01-01T11:15:00Z",
"to":"2022-01-01T11:30:00Z",
"count":1
},
{
"from":"2022-01-01T11:30:00Z",
"to":"2022-01-01T11:45:00Z",
"count":1
}]
我可以使用以下查詢以 15 分鐘的時間間隔獲得計數。但我也想預測日期。
db.collection.aggregate([
{ "$group": {
"_id": {
"year": { "$year": "$created_at" },
"dayOfYear": { "$dayOfYear": "$created_at" },
"hour": { "$hour": "$created_at" },
"interval": {
"$subtract": [
{ "$minute": "$created_at" },
{ "$mod": [{ "$minute": "$created_at"}, 15] }
]
}
}},
"count": { "$sum": 1 }
}}
])
uj5u.com熱心網友回復：
你可以嘗試一種方法，
$dateToParts獲取部分created_at日期$group通過year,month,day,hour, 和interval根據 mod 和減法計算得到總數- 要從間隔獲取和到日期，您可以使用
$dateFromParts運算子，只需在日期中添加 15 分鐘。
db.collection.aggregate([
{
$addFields: {
created_at: { $dateToParts: { date: "$created_at" } }
}
},
{
$group: {
_id: {
year: "$created_at.year",
month: "$created_at.month",
day: "$created_at.day",
hour: "$created_at.hour",
interval: {
$subtract: [
"$created_at.minute",
{ $mod: ["$created_at.minute", 15] }
]
}
},
count: { $sum: 1 }
}
},
{
$project: {
_id: 0,
count: 1,
from: {
$dateFromParts: {
year: "$_id.year",
month: "$_id.month",
day: "$_id.day",
hour: "$_id.hour",
minute: "$_id.interval"
}
},
to: {
$dateFromParts: {
year: "$_id.year",
month: "$_id.month",
day: "$_id.day",
hour: "$_id.hour",
minute: { $add: ["$_id.interval", 15] }
}
}
}
}
])
操場
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/406699.html
標籤：