如何用MongoDB聚合管道筛选res字段异常值的文档ID？

完全可以用MongoDB的聚合管道实现，无需将全量数据导出到pandas处理，能大幅提升大数据集下的处理效率。以下是具体方案：

完整聚合管道代码（MongoDB 5.0+） #

db.yourCollectionName.aggregate([
  // 计算*Q1（25分位数）*、*Q3（75分位数）*，同时收集所有res值
  {
    $group: {
      _id: null,
      allRes: { $push: "$res" },
      q1: { $percentile: { input: "$res", p: 0.25 } },
      q3: { $percentile: { input: "$res", p: 0.75 } }
    }
  },
  // 计算*IQR（四分位距）*和异常值的上下边界
  {
    $addFields: {
      iqr: { $subtract: ["$q3", "$q1"] },
      lowerBound: { $subtract: ["$q1", { $multiply: [1.5, { $subtract: ["$q3", "$q1"] }] }] },
      upperBound: { $add: ["$q3", { $multiply: [1.5, { $subtract: ["$q3", "$q1"] }] }] }
    }
  },
  // 展开res数组，关联原集合匹配文档，筛选异常值并提取_id
  { $unwind: "$allRes" },
  {
    $lookup: {
      from: "yourCollectionName",
      localField: "allRes",
      foreignField: "res",
      as: "matchingDocs"
    }
  },
  { $unwind: "$matchingDocs" },
  {
    $match: {
      $expr: {
        $or: [
          { $lt: ["$matchingDocs.res", "$lowerBound"] },
          { $gt: ["$matchingDocs.res", "$upperBound"] }
        ]
      }
    }
  },
  {
    $project: {
      _id: "$matchingDocs._id",
      res: "$matchingDocs.res" // 不需要的话可以删除这一行
    }
  }
])

代码说明 #

• $group阶段：利用MongoDB 5.0新增的$percentile操作符直接计算百分位数，同时将所有res值存入数组，为后续关联做准备。
• $addFields阶段：通过Q1和Q3计算IQR，进而得出异常值的判定边界：小于Q1 - 1.5*IQR或大于Q3 + 1.5*IQR的数值即为异常值。
• 关联与筛选阶段：通过$unwind展开数组，$lookup关联原集合找到对应文档，最后用$match筛选出异常文档，$project保留需要的字段。

兼容MongoDB 5.0以下版本的方案 #

如果你的MongoDB版本不支持$percentile，可以通过排序+数组索引的方式计算百分位数：

db.yourCollectionName.aggregate([
  // 按res字段升序排序
  { $sort: { res: 1 } },
  // 统计总文档数并收集所有文档
  { $group: { _id: null, totalCount: { $sum: 1 }, allDocs: { $push: "$$ROOT" } } },
  // 计算Q1、Q3的索引并提取对应值
  {
    $addFields: {
      q1Index: { $floor: { $multiply: ["$totalCount", 0.25] } },
      q3Index: { $floor: { $multiply: ["$totalCount", 0.75] } },
      q1Value: { $arrayElemAt: ["$allDocs.res", { $floor: { $multiply: ["$totalCount", 0.25] } }] },
      q3Value: { $arrayElemAt: ["$allDocs.res", { $floor: { $multiply: ["$totalCount", 0.75] } }] }
    }
  },
  // 计算异常值边界
  {
    $addFields: {
      iqr: { $subtract: ["$q3Value", "$q1Value"] },
      lowerBound: { $subtract: ["$q1Value", { $multiply: [1.5, { $subtract: ["$q3Value", "$q1Value"] }] }] },
      upperBound: { $add: ["$q3Value", { $multiply: [1.5, { $subtract: ["$q3Value", "$q1Value"] }] }] }
    }
  },
  // 展开文档并筛选异常值
  { $unwind: "$allDocs" },
  {
    $match: {
      $expr: {
        $or: [
          { $lt: ["$allDocs.res", "$lowerBound"] },
          { $gt: ["$allDocs.res", "$upperBound"] }
        ]
      }
    }
  },
  // 提取需要的字段
  { $project: { _id: "$allDocs._id", res: "$allDocs.res" } }
])

优化提示 #

• 给res字段建立单字段索引，能显著提升排序、聚合阶段的处理速度。
• 若仅需异常文档的_id，可在$project阶段只保留_id字段，减少数据传输量。

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