我有以下收藏：
{
_id: ObjectId("6192513e0fd6ec5ad80376a7"),
user: "mark.1239",
connection: ISODate("2021-11-09T23:55:40.342Z"),
disconnection: ISODate("2021-11-10T01:10:40.342Z")
},
{
_id: ObjectId("6192513e0fd6ec5ad80376a9"),
user: "john.9874",
connection: ISODate("2021-11-02T07:15:42.318Z"),
disconnection: ISODate("2021-11-02T08:40:42.318Z")
},
...
我想以 15 分鐘的間隔讓每個連接的用戶。所以在這種情況下，約翰在 7:30 和 8:15 連接。
我可以在 15 分鐘間隔內按連接或斷開連接進行分組，但不會讓用戶連接到 15 分鐘間隔之外的連接和斷開連接欄位。
我實際上有這個，但找不到如何修改它以實作我想要的
collection.aggregate([
{
"$group": {
"_id": {
"$toDate": {
"$subtract": [
{ "$toLong": "$connection" },
{ "$mod": [ { "$toLong": "$connection" }, 1000 * 60 * 15 ] }
]
}
},
"count": { "$sum": 1 },
"user": {"$addToSet":"$user" }
}
},
{ $sort: {_id: 1} }
])
是否可以僅使用 mongo 而無需 javascript 來實作此目的？
uj5u.com熱心網友回復：
你的條件不是很清楚，看看這個大綱：
connection disconnection
————————————|————————————————|——————————————> t
start end
x x | | | NO
x | x | | YES/NO?
| x x | | YES
| x | x | YES/NO?
x | | x | YES/NO?
| | x x | NO
一種方法是生成 15 分鐘的間隔，然后對這些間隔進行過濾。可能是這個：
db.collection.aggregate([
{
$group: {
_id: null,
data: { $push: "$$ROOT" },
// Determine total min. and max. time. Might be replaces by static values
min: { $min: "$connection" },
max: { $max: "$disconnection" }
}
},
{
$set: {
// Round min/max time to 15 Minute interval
min: { $dateTrunc: { date: "$min", unit: "minute", binSize: 15 } },
max: { $dateTrunc: { date: "$max", unit: "minute", binSize: 15 } }
}
},
{
$set: {
// Get number of 15-Minute intervals between min and max
steps: {
$dateDiff: {
startDate: "$min",
endDate: "$max",
unit: "minute"
}
}
}
},
{
$set: {
// Generate the 15-Minute intervals
intervals: {
$map: {
input: { $range: [0, "$steps", 15] },
as: "t",
in: {
start: {
$dateAdd: {
startDate: "$min",
unit: "minute",
amount: "$$t"
}
},
end: {
$dateAdd: {
startDate: "$min",
unit: "minute",
amount: { $add: ["$$t", 15] }
}
}
}
}
}
}
},
// Transpose array to documents
{ $unwind: "$intervals" },
// Just some cosmetic
{ $project: { data: 1, start: "$intervals.start", end: "$intervals.end" } },
{
$set: {
data: {
// Filter matching connections (based on outline above)
$filter: {
input: "$data",
cond: { $and: [{ $lte: ["$$this.connection", "$start"] }, { $gte: ["$$this.disconnection", "$end"] }] }
}
}
}
},
// Skip all 15-Minute intervals without any connection
{ $match: { data: { $ne: [] } } },
// Count users and some cosmetic
{
$project: {
start:1,
end: 1,
count: { $size: "$data" },
user: { $setUnion: "$data.user" }
}
}
])
另一種方法可能$bucket但會很困難，因為它僅適用于數字值，即您必須扭曲Date值。
只是一個提示：創建一個輔助函式
function between(ts, start, end) {
let ret = {};
if (typeof ts == "string") {
ret["$expr"] = { $and: [{ $lte: ["$" start, "$" ts] }, { $gte: ["$" end, "$" ts] }] };
} else {
ret[start] = { $lte: ts };
ret[end] = { $gte: ts };
}
return ret;
}
它對應于SQL BETWEEN Operator，即WHERE ts BETWEEN start AND end
然后你可以撰寫條件，如
{ $match: between("start", "connection", "disconnection") }
{ $match: between(new Date(), "connection", "disconnection") }
它使撰寫查詢變得更加容易。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/357781.html