如何通过单条聚合管道查找MongoDB指定时间范围缺失的小时文档

以下方案完全适配Metabase的限制，不需要创建临时集合、不需要跨集合$lookup，单条聚合管道即可返回结果，前提是集合内有效记录的时间字段均为整点小时级，可根据实际业务替换字段名、时间范围。

核心逻辑 #

不需要预先生成全量小时序列，直接通过已有记录的相邻时间差判断数据缺口，同时自动补全校验查询起止边界的缺失时段，所有计算都在单聚合管道内完成。

可直接复用的聚合代码 #

注意替换代码里的3类自定义值：

• your_collection：实际业务集合名
• ts：实际存储小时级时间戳的字段名（默认是BSON Date类型，数字时间戳适配方案看后续说明）
• 两处ISODate值：分别替换为你要查询的整点起始时间和整点结束时间

db.your_collection.aggregate([
  // 过滤指定时间范围内的文档，减少无效计算
  {
    $match: {
      ts: {
        $gte: ISODate("2024-05-01T00:00:00Z"),
        $lte: ISODate("2024-05-03T00:00:00Z")
      }
    }
  },
  // 按小时去重，每个小时仅保留一条标记记录
  {
    $group: {
      _id: { $dateTrunc: { date: "$ts", unit: "hour" } }
    }
  },
  // 按时间正序排列，取每条记录的上一个小时点
  {
    $setWindowFields: {
      sortBy: { _id: 1 },
      output: {
        prevHour: { $shift: { by: -1, output: "$_id" } }
      }
    }
  },
  // 筛选出相邻时间差大于1小时的缺口段
  {
    $match: {
      $expr: {
        $gt: [{ $dateDiff: { startDate: "$prevHour", endDate: "$_id", unit: "hour" } }, 1]
      }
    }
  },
  // 生成缺口段内所有缺失的整点小时
  {
    $project: {
      missingHours: {
        $map: {
          input: {
            $range: [
              0,
              { $subtract: [
                { $dateDiff: { startDate: "$prevHour", endDate: "$_id", unit: "hour" } },
                1
              ]}
            ]
          },
          as: "offset",
          in: { $dateAdd: { startDate: "$prevHour", unit: "hour", amount: { $add: ["$$offset", 1] } } }
        }
      }
    }
  },
  // 把缺失小时数组拆分为单条记录
  { $unwind: "$missingHours" },
  { $replaceRoot: { newRoot: { missingHour: "$missingHours" } } },
  // 补全校验查询首尾边界的缺失时段
  {
    $unionWith: {
      coll: "your_collection",
      pipeline: [
        {
          $match: {
            ts: {
              $gte: ISODate("2024-05-01T00:00:00Z"),
              $lte: ISODate("2024-05-03T00:00:00Z")
            }
          }
        },
        {
          $group: {
            _id: null,
            firstHour: { $min: { $dateTrunc: { date: "$ts", unit: "hour" } } },
            lastHour: { $max: { $dateTrunc: { date: "$ts", unit: "hour" } } }
          }
        },
        {
          $project: {
            missingHours: {
              $concatArrays: [
                // 计算查询起点到第一条有效记录之间的缺失小时
                {
                  $map: {
                    input: { $range: [0, { $dateDiff: { startDate: ISODate("2024-05-01T00:00:00Z"), endDate: "$firstHour", unit: "hour" } }] },
                    as: "offset",
                    in: { $dateAdd: { startDate: ISODate("2024-05-01T00:00:00Z"), unit: "hour", amount: "$$offset" } }
                  }
                },
                // 计算最后一条有效记录到查询终点之间的缺失小时
                {
                  $map: {
                    input: { $range: [0, { $dateDiff: { startDate: "$lastHour", endDate: ISODate("2024-05-03T00:00:00Z"), unit: "hour" } }] },
                    as: "offset",
                    in: { $dateAdd: { startDate: "$lastHour", unit: "hour", amount: { $add: ["$$offset", 1] } } }
                  }
                }
              ]
            }
          }
        },
        { $unwind: "$missingHours" },
        { $replaceRoot: { newRoot: { missingHour: "$missingHours" } } }
      ]
    }
  },
  // 最终按时间正序返回结果
  { $sort: { missingHour: 1 } }
])

适配说明 #

• 版本兼容：上述代码用到的日期运算符、窗口函数均为MongoDB 5.0及以上版本原生支持，目前绝大多数云MongoDB实例、自建实例版本都满足要求。如果是4.x及以下版本，可将$dateTrunc替换为日期格式化截断的写法，$shift替换为数组滑动窗口的写法，核心逻辑不变。
• 时间戳格式适配：如果你的时间字段存的是毫秒级数字而非Date类型，不需要改整体逻辑，只需把日期运算替换为数字计算即可：1小时对应3600000毫秒，小时截断逻辑为{ $subtract: ["$ts", { $mod: ["$ts", 3600000] }] }，时间差直接做减法即可。
• 结果校验：返回的missingHour字段就是所有缺失记录的整点小时时间，可直接在Metabase中做可视化展示。

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