我已经存储了十站stations集合：Station A，Station B，Station C，Station D，Station E，Station F，Station G，Station H，Station I，Station J。
现在，要创建所有可能的站点对之间的所有站点间行驶的计数列表，我在Node.js代码中执行以下操作（使用Mongoose）：
const stationCombinations = []
// get all stations from the stations collection
const stationIds = await Station.find({}, '_id name').lean().exec()
// list of all possible from & to combinations with their names
stationIds.forEach(fromStation => {
stationIds.forEach(toStation => {
stationCombinations.push({ fromStation, toStation })
})
})
const results = []
// loop through all station combinations
for (const stationCombination of stationCombinations) {
// create aggregation query promise
const data = Ride.aggregate([
{
$match: {
test: false,
state: 'completed',
duration: { $gt: 2 },
fromStation: mongoose.Types.ObjectId(stationCombination.fromStation._id),
toStation: mongoose.Types.ObjectId(stationCombination.toStation._id)
}
},
{
$group: {
_id: null,
count: { $sum: 1 }
}
},
{
$addFields: {
fromStation: stationCombination.fromStation.name,
toStation: stationCombination.toStation.name
}
}
])
// push promise to array
results.push(data)
}
// run all aggregation queries
const stationData = await Promise.all(results)
// flatten nested/empty arrays and return
return stationData.flat()
执行此功能可以得到以下格式的结果：
[
{
"fromStation": "Station A",
"toStation": "Station A",
"count": 1196
},
{
"fromStation": "Station A",
"toStation": "Station B",
"count": 1
},
{
"fromStation": "Station A",
"toStation": "Station C",
"count": 173
},
]
And so on for all other combinations...
该查询当前需要花费很多时间来执行，并且由于这些查询，我不断从MongoDB Atlas收到有关数据库服务器上负载过大的警报。当然必须有一种优化的方法来做这样的事情吗？
您需要使用MongoDB本机操作。您需要$group按fromStation和toStation并$lookup加入两个集合。
注意：我假设您的MongoDB> = v3.6，Station._id并且ObjectId
db.ride.aggregate([
{
$match: {
test: false,
state: "completed",
duration: {
$gt: 2
}
}
},
{
$group: {
_id: {
fromStation: "$fromStation",
toStation: "$toStation"
},
count: {
$sum: 1
}
}
},
{
$lookup: {
from: "station",
let: {
fromStation: "$_id.fromStation",
toStation: "$_id.toStation"
},
pipeline: [
{
$match: {
$expr: {
$in: [
"$_id",
[
"$$fromStation",
"$$toStation"
]
]
}
}
}
],
as: "tmp"
}
},
{
$project: {
_id: 0,
fromStation: {
$reduce: {
input: "$tmp",
initialValue: "",
in: {
$cond: [
{
$eq: [
"$_id.fromStation",
"$$this._id"
]
},
"$$this.name",
"$$value"
]
}
}
},
toStation: {
$reduce: {
input: "$tmp",
initialValue: "",
in: {
$cond: [
{
$eq: [
"$_id.toStation",
"$$this._id"
]
},
"$$this.name",
"$$value"
]
}
}
},
count: 1
}
},
{
$sort: {
fromStation: 1,
toStation: 1
}
}
])
未经测试：
const data = Ride.aggregate([
{
$match: {
test: false,
state: 'completed',
duration: { $gt: 2 }
}
},
{
$group: {
_id: {
fromStation: "$fromStation",
toStation: "$toStation"
},
count: { $sum: 1 }
}
},
{
$lookup: {
from: "station",
let: {
fromStation: "$_id.fromStation",
toStation: "$_id.toStation"
},
pipeline: [
{
$match: {
$expr: {
$in: [
"$_id",
[
"$$fromStation",
"$$toStation"
]
]
}
}
}
],
as: "tmp"
}
},
{
$project: {
_id: 0,
fromStation: {
$reduce: {
input: "$tmp",
initialValue: "",
in: {
$cond: [
{
$eq: [
"$_id.fromStation",
"$$this._id"
]
},
"$$this.name",
"$$value"
]
}
}
},
toStation: {
$reduce: {
input: "$tmp",
initialValue: "",
in: {
$cond: [
{
$eq: [
"$_id.toStation",
"$$this._id"
]
},
"$$this.name",
"$$value"
]
}
}
},
count: 1
}
},
{
$sort: {
fromStation: 1,
toStation: 1
}
}
])
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句