如何在MongoDB聚合管道中实现PowerBI报表的统计指标

核心问题分析 #

你遇到的问题本质是：单个作业的三个日期字段（创建、到期、完成）可能分属不同年月，仅按某一个日期分组会导致跨年月的统计维度数据遗漏。比如示例中第二个作业创建在6月，但完成在7月，按创建日期分组会把它归到6月，从而遗漏7月的完成计数。

解决方案分两种场景 #

场景1：统计单个指定年月的报表 #

如果你只需要针对某一个年月（比如2022年7月）生成统计数据，用以下聚合管道：

1. 先定义目标年月的起止时间（MongoDB推荐用UTC时间避免时区问题）
2. 过滤出所有和目标年月相关的文档（三个日期任意一个在该区间）
3. 标记每个文档是否符合当月的三个统计维度
4. 分组累加计数

// 定义目标年月
const targetYear = 2022;
const targetMonth = 7;
// 计算UTC时区的起止日期（左闭右开）
const startDate = new Date(Date.UTC(targetYear, targetMonth - 1, 1));
const endDate = new Date(Date.UTC(targetYear, targetMonth, 1));

db.assignments.aggregate([
  // 过滤出和目标年月相关的文档，减少计算量
  {
    $match: {
      $or: [
        { createdOn: { $gte: startDate, $lt: endDate } },
        { dueOn: { $gte: startDate, $lt: endDate } },
        { completedOn: { $gte: startDate, $lt: endDate } }
      ]
    }
  },
  // 标记每个文档是否属于当月的创建/到期/完成
  {
    $addFields: {
      isCreatedThisMonth: {
        $and: [{ $gte: ["$createdOn", startDate] }, { $lt: ["$createdOn", endDate] }]
      },
      isDueThisMonth: {
        $and: [{ $gte: ["$dueOn", startDate] }, { $lt: ["$dueOn", endDate] }]
      },
      isCompletedThisMonth: {
        $and: [{ $gte: ["$completedOn", startDate] }, { $lt: ["$completedOn", endDate] }]
      }
    }
  },
  // 分组统计各维度总数
  {
    $group: {
      _id: null,
      totalCreated: { $sum: { $cond: ["$isCreatedThisMonth", 1, 0] } },
      totalDue: { $sum: { $cond: ["$isDueThisMonth", 1, 0] } },
      totalCompleted: { $sum: { $cond: ["$isCompletedThisMonth", 1, 0] } }
    }
  },
  // 整理输出格式
  {
    $project: {
      _id: 0,
      year: targetYear,
      month: targetMonth,
      totalCreated: 1,
      totalDue: 1,
      totalCompleted: 1
    }
  }
])

运行后会得到类似结果：

{ "year": 2022, "month": 7, "totalCreated": 1, "totalDue": 0, "totalCompleted": 2 }

场景2：统计所有年月的汇总报表 #

如果需要生成所有年月的统计数据（每个年月一行），需要把每个文档拆分成三个日期维度的条目，再分组统计：

db.assignments.aggregate([
  // 将每个文档的三个日期字段转成数组条目
  {
    $project: {
      dateEntries: [
        { type: "created", date: "$createdOn" },
        { type: "due", date: "$dueOn" },
        { type: "completed", date: "$completedOn" }
      ]
    }
  },
  // 展开数组，每个日期条目成为单独文档
  { $unwind: "$dateEntries" },
  // 过滤空日期（如果存在未设置的日期字段）
  { $match: { "dateEntries.date": { $exists: true } } },
  // 提取日期的年和月
  {
    $addFields: {
      year: { $year: "$dateEntries.date" },
      month: { $month: "$dateEntries.date" },
      type: "$dateEntries.type"
    }
  },
  // 按年、月、类型分组计数
  {
    $group: {
      _id: { year: "$year", month: "$month", type: "$type" },
      count: { $sum: 1 }
    }
  },
  // 合并同一年月的三个维度计数
  {
    $group: {
      _id: { year: "$_id.year", month: "$_id.month" },
      totalCreated: { $sum: { $cond: [{ $eq: ["$_id.type", "created"] }, "$count", 0] } },
      totalDue: { $sum: { $cond: [{ $eq: ["$_id.type", "due"] }, "$count", 0] } },
      totalCompleted: { $sum: { $cond: [{ $eq: ["$_id.type", "completed"] }, "$count", 0] } }
    }
  },
  // 按年月排序
  { $sort: { "_id.year": 1, "_id.month": 1 } },
  // 整理输出格式
  {
    $project: {
      _id: 0,
      year: "$_id.year",
      month: "$_id.month",
      totalCreated: 1,
      totalDue: 1,
      totalCompleted: 1
    }
  }
])

关键说明 #

• 用UTC时间计算起止日期可以避免时区偏移导致的统计错误
• $match阶段先过滤无关文档，能大幅提升聚合效率
• $cond操作符用于将布尔值转换为1/0，实现条件累加计数

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