NoSQL聚合查询添加$limit后结果异常，如何获取最高营收产品？

问题 #

需要编写MongoDB聚合查询找出销量（数量）和营收最高的产品。

初始执行的聚合查询：

db.collection.aggregate([
  {$project: {product: '$name_field', quantity: '$quantity_field', revenue:{$multiply:["$quantity_field", "$price_field"]}}},
  {$sort: {revenue: -1}}
]);

得到正确的营收降序结果：

{ _id: ObjectId("633dcf127e6d8005365d4454"),
 product: 'Classic Cars',
 quantity: 47,
 revenue: 9571.08 }
{ _id: ObjectId("633dcf127e6d8005365d447b"),
 product: 'Motorcycles',
 quantity: 49,
 revenue: 9394.28 }
{ _id: ObjectId("633dcf127e6d8005365d41b6"),
 product: 'Classic Cars',
 quantity: 46,
 revenue: 8889.5 }

但添加$limit:1后，查询变为：

db.collection.aggregate([
  {$project: {product: '$name_field', quantity: '$quantity_field', revenue:{$multiply:["$quantity_field", "$price_field"]}}},
  {$sort: {revenue: -1}},
  {$limit: 1}
]);

却得到异常结果：

{ _id: ObjectId("633dcf127e6d8005365d40c0"),
 product: 'Vintage Cars',
 quantity: 30,
 revenue: 4080 }

需求：获取排序后的最高营收单条记录，修正查询问题。

解决方案 #

原因分析 #

异常是因为MongoDB聚合优化器在处理$sort紧跟$limit时，会启用“带限制的排序”优化，该优化在处理计算生成的revenue字段时可能出现逻辑错误，导致未完成完整排序就返回了限制数量的记录。

方法1：使用$setWindowFields获取精准排名 #

通过窗口函数给每条记录按营收降序排名，再筛选排名第一的记录：

db.collection.aggregate([
  // 计算营收并保留需要的字段
  {$addFields: {
    product: '$name_field',
    revenue: {$multiply: ["$quantity_field", "$price_field"]}
  }},
  // 按营收降序排序，生成排名
  {$setWindowFields: {
    sortBy: {revenue: -1},
    output: {rank: {$rank: {}}}
  }},
  // 筛选排名第一的记录
  {$match: {rank: 1}},
  // 整理输出字段
  {$project: {_id: 1, product: 1, quantity: '$quantity_field', revenue: 1}}
]);

方法2：用$facet绕过优化 #

通过$facet先完成完整排序，再取第一条记录：

db.collection.aggregate([
  {$project: {
    product: '$name_field',
    quantity: '$quantity_field',
    revenue: {$multiply: ["$quantity_field", "$price_field"]}
  }},
  // 单独执行排序逻辑
  {$facet: {
    sorted_records: [{$sort: {revenue: -1}}]
  }},
  // 展开排序后的数组
  {$unwind: '$sorted_records'},
  // 取第一条
  {$limit: 1},
  // 将结果作为根文档返回
  {$replaceRoot: {newRoot: '$sorted_records'}}
]);

补充：若需求是按产品分组的总销量/总营收最高 #

如果你的需求是找出所有产品中总销量和总营收最高的那一款，而非单条记录的最高，可使用以下查询：

db.collection.aggregate([
  // 计算单条记录的营收
  {$addFields: {
    revenue: {$multiply: ["$quantity_field", "$price_field"]}
  }},
  // 按产品分组，统计总销量和总营收
  {$group: {
    _id: '$name_field',
    total_quantity: {$sum: '$quantity_field'},
    total_revenue: {$sum: '$revenue'}
  }},
  // 按总营收降序排序
  {$sort: {total_revenue: -1}},
  // 取排名第一的产品
  {$limit: 1},
  // 调整输出字段名
  {$project: {
    product: '$_id',
    total_quantity: 1,
    total_revenue: 1,
    _id: 0
  }}
]);

内容的提问来源于stack exchange，提问作者hshodimu