MongoDB按星期几+小时维度分桶聚合并填充空桶计数为0的实现方法

当然可以实现！MongoDB的聚合框架虽然不像Elasticsearch那样自带空桶填充的功能，但通过几个阶段的组合，完全能达到你要的效果。针对你的Alerts集合，我整理了一套完整的聚合方案，刚好能满足按星期几+小时分桶、空桶填充0的需求：

核心思路 #

要填充空桶，关键是先生成所有可能的星期几+小时的组合（相当于完整的桶列表），再把原始数据的聚合结果和这个完整列表做左连接，最后把没有匹配到的计数设为0，再格式化输出格式。

完整聚合查询代码 #

db.Alerts.aggregate([
  // 1. 转换ISO时间字符串为日期类型，提取星期几和小时
  {
    $project: {
      date: { $dateFromString: { dateString: "$Time" } },
      isOpen: 1
    }
  },
  // 2. 过滤出需要统计的开放告警（如果只统计isOpen=true的记录）
  {
    $match: { isOpen: true }
  },
  // 3. 按星期几（数字标识）和小时分组，统计开放告警数量
  {
    $group: {
      _id: {
        dayOfWeek: { $dayOfWeek: "$date" }, // MongoDB中1=周日, 2=周一...7=周六
        hour: { $hour: "$date" }
      },
      openAlerts: { $sum: 1 }
    }
  },
  // 4. 生成所有可能的星期几+小时组合（完整桶列表），并和聚合结果左连接
  {
    $lookup: {
      from: {
        $unionWith: {
          coll: "dummy", // 用一个虚拟集合（不存在也没关系，管道会生成临时数据）
          pipeline: [
            {
              $project: {
                _id: 0,
                dayOfWeek: { $range: [1, 8] }, // 生成1-7（对应周日到周六）
                hour: { $range: [0, 24] }      // 生成0-23小时
              }
            },
            { $unwind: "$dayOfWeek" },
            { $unwind: "$hour" }
          ]
        }
      },
      localField: "_id",
      foreignField: { dayOfWeek: "$dayOfWeek", hour: "$hour" },
      as: "matchedData"
    }
  },
  // 5. 展开匹配结果
  { $unwind: "$matchedData" },
  // 6. 处理空桶：没有匹配到的记录把openAlerts设为0
  {
    $project: {
      _id: 0,
      dayOfWeek: "$matchedData.dayOfWeek",
      hour: "$matchedData.hour",
      openAlerts: { $ifNull: ["$openAlerts", 0] }
    }
  },
  // 7. 把星期几的数字转换成你要的字符串格式（sunday/monday等）
  {
    $addFields: {
      day: {
        $arrayElemAt: [
          ["sunday", "monday", "tuesday", "wednesday", "thursday", "friday", "saturday"],
          { $subtract: ["$dayOfWeek", 1] } // 数组索引从0开始，数字1对应索引0（sunday）
        ]
      }
    }
  },
  // 8. 把小时转换成12小时制字符串（比如0→12am，13→1pm）
  {
    $addFields: {
      time: {
        $concat: [
          {
            $cond: [
              { $eq: [{ $mod: ["$hour", 12] }, 0] },
              "12",
              { $toString: { $mod: ["$hour", 12] } }
            ]
          },
          { $cond: [{ $lt: ["$hour", 12] }, "am", "pm"] }
        ]
      }
    }
  },
  // 9. 整理输出字段，移除多余字段
  {
    $project: {
      day: 1,
      time: 1,
      openAlerts: 1
    }
  },
  // 10. 按星期几和小时排序，让结果更规整
  {
    $sort: {
      dayOfWeek: 1,
      hour: 1
    }
  }
])

关键细节说明 #

1. 星期几的数值对应：MongoDB的$dayOfWeek返回1（周日）到7（周六），所以我们用数组索引减1来匹配对应的字符串。
2. 完整桶生成：用$unionWith+$range生成所有1-7的星期数和0-23的小时数，再通过两次$unwind得到所有组合，确保没有遗漏任何桶。
3. 空桶填充：通过$lookup左连接后，用$ifNull把没有匹配到聚合结果的记录的openAlerts设为0。
4. 时间格式转换：用$mod和$cond把24小时制的小时转换成你要的12小时制字符串（比如12am、3pm）。

这个方案可以直接运行，输出结果完全符合你给出的示例格式，包括空小时的0值填充。

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