MongoDB分组数据间最小/最大记录数及末次出现记录数查询问题

需求说明 #

在已排序的MongoDB数据中，按winCode字段分组，需计算以下指标：

• 同一winCode连续出现之间的最小间隔记录数
• 同一winCode连续出现之间的最大间隔记录数
• 该winCode末次出现时，从排序起始位置到该条的累计记录数

现有尝试 #

当前已完成排序与分组，但无法获取上述指标，尝试使用$accumulator因阶段限制失败。

当前聚合查询 #

db.collection.aggregate([
  { $sort: { createTime: 1 } },
  {
    $group: {
      _id: "$winCode",
      count: { $sum: 1 },
      occurrences: { $push: "$createTime" }
    }
  }
])

当前输出 #

[
  {
    "_id": "A",
    "count": 3,
    "occurrences": [ISODate("2023-01-01T00:00:00Z"), ISODate("2023-01-03T00:00:00Z"), ISODate("2023-01-06T00:00:00Z")]
  },
  {
    "_id": "B",
    "count": 2,
    "occurrences": [ISODate("2023-01-02T00:00:00Z"), ISODate("2023-01-05T00:00:00Z")]
  }
]

解决方案 #

通过$setWindowFields添加全局序号，再分组收集序号列表，最后计算间隔统计与末次位置：

db.collection.aggregate([
  // 1. 按时间排序（保持原有排序逻辑）
  { $sort: { createTime: 1 } },
  // 2. 为每条记录添加全局排序后的序号（从1开始）
  {
    $setWindowFields: {
      partitionBy: null,
      sortBy: { createTime: 1 },
      output: {
        rowNum: { $rowNumber: {} }
      }
    }
  },
  // 3. 按winCode分组，收集所有出现的序号
  {
    $group: {
      _id: "$winCode",
      positions: { $push: "$rowNum" },
      lastOccurrence: { $last: "$rowNum" }
    }
  },
  // 4. 计算相邻出现的间隔，并统计最小/最大间隔
  {
    $set: {
      intervals: {
        $map: {
          input: { $range: [1, { $size: "$positions" }] },
          as: "i",
          in: {
            $subtract: [
              { $arrayElemAt: ["$positions", "$$i"] },
              { $arrayElemAt: ["$positions", { $subtract: ["$$i", 1] }] }
            ]
          }
        }
      }
    }
  },
  // 5. 提取最小/最大间隔，清理临时字段
  {
    $project: {
      minInterval: { $min: "$intervals" },
      maxInterval: { $max: "$intervals" },
      lastOccurrenceRow: "$lastOccurrence",
      occurrenceCount: { $size: "$positions" }
    }
  },
  // 6. 处理仅出现一次的winCode（间隔设为null或0，按需调整）
  {
    $set: {
      minInterval: { $cond: [{ $gt: ["$occurrenceCount", 1] }, "$minInterval", null] },
      maxInterval: { $cond: [{ $gt: ["$occurrenceCount", 1] }, "$maxInterval", null] }
    }
  }
])

步骤解释 #

1. 排序：保持原有按createTime升序排序的逻辑，确保后续序号的准确性。
2. 添加全局序号：使用$setWindowFields的$rowNumber生成每条记录在全局排序后的位置，这是计算间隔的基础。
3. 分组收集位置：按winCode分组，收集所有出现的序号，并直接获取末次出现的序号。
4. 计算间隔列表：通过$map遍历位置列表，计算相邻两次出现的序号差（即间隔的记录数）。
5. 统计间隔指标：用$min和$max从间隔列表中提取最小、最大间隔，同时保留末次出现的序号和出现次数。
6. 处理单次出现的情况：对于仅出现一次的winCode，间隔指标设为null（可根据需求调整为0或其他值）。

期望输出 #

[
  {
    "_id": "A",
    "occurrenceCount": 3,
    "minInterval": 2,
    "maxInterval": 3,
    "lastOccurrenceRow": 6
  },
  {
    "_id": "B",
    "occurrenceCount": 2,
    "minInterval": 3,
    "maxInterval": 3,
    "lastOccurrenceRow": 5
  }
]

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