Mongodb-多阶段查询-计算、比较并返回结果

Question

我有如下mongodb文档：

[
{
_id: '5e839a223552e431fxd3c979',
dateTime: '2020-03-31T19:29:41.259Z',
regionId: 'eg',
transId: 'serviceFee',
revenue: 30,
details: {
serviceId: 'serv_a'
}
},
{
_id: '5e839a223559e431fxd3c979',
dateTime: '2020-03-31T19:29:42.259Z',
regionId: 'eg',
transId: 'serviceFee',
revenue: 10,
details: {
serviceId: 'serv_b'
}
},
{
_id: '5e839a223552ed82fxd3c979',
dateTime: '2020-03-31T19:29:43.259Z',
regionId: 'eg',
transId: 'serviceFee',
revenue: 15,
details: {
serviceId: 'serv_a'
}
},
{
_id: '5e8902223552e431fxd3c979',
dateTime: '2020-03-31T19:29:44.259Z',
regionId: 'eg',
transId: 'serviceFee',
revenue: 55,
details: {
serviceId: 'serv_a'
}
},
{
_id: '5e839a223552e43jjid3c979',
dateTime: '2020-03-31T19:29:45.259Z',
regionId: 'eg',
transId: 'serviceFee',
revenue: 7,
details: {
serviceId: 'serv_b'
}
},
{
_id: '5e839a223552e431fx0c3979',
dateTime: '2020-03-31T19:29:46.259Z',
regionId: 'eg',
transId: 'serviceFee',
revenue: 120,
details: {
serviceId: 'serv_c'
}
},
{
_id: '5e839a22ggt2e431fxd3c979',
dateTime: '2020-03-31T19:29:47.259Z',
regionId: 'us',
transId: 'serviceFee',
revenue: 500,
details: {
serviceId: 'serv_a'
}
},
]

查看文件，我们可以说：

• 根据regionId:[eg,us]存在两个区域
• 根据serviceId:[serv_a, serv_b, serv_c]
• 每个文档显示特定地区特定服务的收入

现在我想要的是：

• 我想知道特定地区(例如(表现最好的服务(总收入最高(
• 我还想知道同一地区表现最差的服务
• 因此，预期结果应该是：
  • 'eg'地区表现最好的服务是：serv_c
  • 该地区表现最差的服务是：serv_b

因此，根据我的基本理解，我认为我需要找到一种方法来计算该地区每项服务的总收入(例如，eg(，然后在总收入之间进行比较，以决定哪个最高，哪个最低！我认为我应该使用mongodbaggregate函数，但我不知道在这种情况下如何使用它。

Answer 1

我想我已经明白了。以下是javascript中的一个示例，展示了我是如何做到的：

var bestPerformingService = 'ph_besterviceId'; // <-- value placeholder
var worstPerformingService = 'ph_worstServiceId' // <-- value placeholder
var trId = 'serviceFee'; // <-- selected transId
var regId = 'eg'; // <-- selected regionId
var resultArray = await records_revenues_model.aggregate([
{
$match: {
regionId: regId,
transId: trId,
}
},
{
$group: {
_id: '$details.serviceId',
totalRevenues: { $sum: '$revenue' },
}
},
{
$sort: { totalRevenues: -1 }
},
]).exec() || [];
try {
bestPerformingService = resultArray[0]._id;
worstPerformingService = resultArray[resultArray.length - 1]._id;
} catch (error) {
methods.log(`safed error : ${error}`);
}
console.log(`best performing service: ${bestPerformingService}`);
console.log(`worst performing service: ${worstPerformingService}`);
// expected outcome:
// best performing service: serv_c
// worst performing service: serv_b

我希望有一天这对某人有用。