MongoDB聚合统计certResults数组percentageUtilization平均值

你的数据中startTime、endTime字段当前存储为YYYY/MM/DD HH:mm:ss.SSS格式的字符串，做时间范围筛选时，传入的查询起止时间必须和该格式完全对齐，才能保证字符串大小比较的结果符合时间先后逻辑。长期使用建议将这两个字段入库时转换为MongoDB原生BSON日期类型，查询效率和准确性都会明显提升。

聚合管道逻辑：

1. 前置过滤dataFormatVersion=10的文档，提前筛掉完全不包含目标主机、没有条目落在查询时间范围内的文档，减少后续计算量
2. 过滤certResults数组，仅保留hostname为reuters.com、且扫描时间在指定区间内的条目
3. 拆解过滤后的数组为独立文档
4. 全量聚合计算目标字段的平均值

对应聚合代码：

// 替换为实际查询的起止时间，格式必须和存储格式一致
const queryStart = "2022/06/28 00:00:00.000"
const queryEnd = "2022/07/02 00:00:00.000"
const targetHost = "reuters.com"

db.certCollection.aggregate([
  {
    $match: {
      dataFormatVersion: 10,
      certResults: {
        $elemMatch: {
          hostname: targetHost,
          startTime: { $gte: queryStart },
          endTime: { $lte: queryEnd }
        }
      }
    }
  },
  {
    $project: {
      certResults: {
        $filter: {
          input: "$certResults",
          cond: {
            $and: [
              { $eq: ["$$this.hostname", targetHost] },
              { $gte: ["$$this.startTime", queryStart] },
              { $lte: ["$$this.endTime", queryEnd] }
            ]
          }
        }
      }
    }
  },
  { $unwind: "$certResults" },
  {
    $group: {
      _id: null,
      avgPercentageUtilization: { $avg: "$certResults.percentageUtilization" },
      // 可选：额外统计匹配的扫描总次数
      totalScanCount: { $sum: 1 }
    }
  }
])

聚合管道逻辑：

1. 前置过滤dataFormatVersion=10、且存在扫描条目落在查询时间范围内的文档
2. 过滤certResults数组，仅保留扫描时间在指定区间内的所有主机条目
3. 拆解过滤后的数组为独立文档（每个主机的单次扫描结果对应一条记录）
4. 全量聚合计算所有记录的percentageUtilization平均值

对应聚合代码：

// 替换为实际查询的起止时间
const queryStart = "2022/06/28 00:00:00.000"
const queryEnd = "2022/07/02 00:00:00.000"

db.certCollection.aggregate([
  {
    $match: {
      dataFormatVersion: 10,
      certResults: {
        $elemMatch: {
          startTime: { $gte: queryStart },
          endTime: { $lte: queryEnd }
        }
      }
    }
  },
  {
    $project: {
      certResults: {
        $filter: {
          input: "$certResults",
          cond: {
            $and: [
              { $gte: ["$$this.startTime", queryStart] },
              { $lte: ["$$this.endTime", queryEnd] }
            ]
          }
        }
      }
    }
  },
  { $unwind: "$certResults" },
  {
    $group: {
      _id: null,
      overallAvgPercentageUtilization: { $avg: "$certResults.percentageUtilization" },
      // 可选：额外统计匹配的总扫描条目数、涉及的主机数量
      totalEntryCount: { $sum: 1 },
      distinctHosts: { $addToSet: "$certResults.hostname" }
    }
  },
  // 可选：把distinctHosts从数组转成数量值
  {
    $addFields: {
      distinctHostCount: { $size: "$distinctHosts" },
      distinctHosts: 0
    }
  }
])

补充说明：如果遇到字符串时间格式不统一导致筛选异常的情况，可以在聚合中使用$dateFromString操作符将存储的时间字符串转换为原生日期类型再做比较，转换时需要指定和存储格式匹配的format参数以及数据对应的时区，避免时差导致的筛选偏差。

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