如何基于OfficeLicensePurchases统计各年新建Office数及当前活跃数？

需求说明 #

• 依据每个Office的最早许可证创建时间，统计每年新建Office的数量
• 统计各年份新建的Office中，当前仍持有活跃（Active）许可证的数量

原始数据集 #

[
  {
    "_id": "514de62f-0a4c-46c8-ba66-5692935f0930",
    "OfficeId": "09ac58f0-8d2a-42ec-ba47-f518dd9d0904",
    "PlanName": "Plan 50",
    "PlanPrice": 777,
    "PaidValue": 330,
    "CreationDate": {
      "$date": "2017-03-08T03:00:00.000Z"
    },
    "Status": "Active"
  },
  {
    "_id": "e41a5923-3a69-43bb-87a4-f1169e172e2e",
    "OfficeId": "9679fdf7-02a1-4a1f-b0f0-17d3505e86e3",
    "PlanName": "Plan 50",
    "PlanPrice": 777,
    "PaidValue": 330,
    "CreationDate": {
      "$date": "2019-03-08T03:00:00.000Z"
    },
    "Status": "Inactive"
  },
  {
    "_id": "d314a8bf-0147-4aee-8357-f36db686c85a",
    "OfficeId": "eab54926-0c11-4c9f-aee2-7fbeabad4135",
    "PlanName": "Plan 50",
    "PlanPrice": 777,
    "PaidValue": 330,
    "CreationDate": {
      "$date": "2021-03-08T03:00:00.000Z"
    },
    "Status": "Active"
  },
  {
    "_id": "d314a8bf-0147-4aee-8357-f36db686c85b",
    "OfficeId": "eab54926-0c11-4c9f-aee2-7fbeabad4135",
    "PlanName": "Plan 100",
    "PlanPrice": 7776,
    "PaidValue": 3308,
    "CreationDate": {
      "$date": "2021-03-08T03:00:00.000Z"
    },
    "Status": "Active"
  },
  {
    "_id": "d314a8bf-0147-4aee-8357-f36db686c856",
    "OfficeId": "eab54926-0c11-4c9f-aee2-7fbeabad4139",
    "PlanName": "Plan 100",
    "PlanPrice": 7776,
    "PaidValue": 3308,
    "CreationDate": {
      "$date": "2021-03-08T09:00:00.000Z"
    },
    "Status": "Active"
  },
  {
    "_id": "d314a8bf-0147-4aee-8357-f36db686c855",
    "OfficeId": "eab54926-0c11-4c9f-aee2-7fbeabad4135",
    "PlanName": "Plan 100",
    "PlanPrice": 7776,
    "PaidValue": 3308,
    "CreationDate": {
      "$date": "2023-03-08T09:00:00.000Z"
    },
    "Status": "Active"
  },
  {
    "_id": "d314a8bf-0147-4aee-8357-f36db686c859",
    "OfficeId": "eab54926-0c11-4c9f-aee2-7fbeabad4135",
    "PlanName": "Plan 100",
    "PlanPrice": 7776,
    "PaidValue": 3308,
    "CreationDate": {
      "$date": "2023-03-08T09:00:00.000Z"
    },
    "Status": "Active"
  }
]

实现代码（MongoDB聚合管道） #

db.OfficeLicensePurchases.aggregate([
  // 按OfficeId分组，提取每个Office的最早创建年份和是否有活跃许可证
  {
    $group: {
      _id: "$OfficeId",
      earliestCreationYear: { $year: { $min: "$CreationDate" } },
      hasActiveLicense: { $max: { $cond: [{ $eq: ["$Status", "Active"] }, 1, 0] } }
    }
  },
  // 按创建年份分组，统计新建数量和活跃数量
  {
    $group: {
      _id: "$earliestCreationYear",
      NewOffices: { $sum: 1 },
      Active: { $sum: "$hasActiveLicense" }
    }
  },
  // 整理输出格式，重命名字段
  {
    $project: {
      _id: 0,
      CreationYear: "$_id",
      NewOffices: 1,
      Active: 1
    }
  },
  // 按创建年份升序排序
  { $sort: { CreationYear: 1 } }
])

代码解释 #

1. 第一阶段（$group）：按OfficeId聚合，用$min获取该Office的最早许可证创建时间，再用$year提取年份；通过$cond和$max判断该Office是否存在活跃许可证（只要有一条记录是Active，hasActiveLicense就为1）。
2. 第二阶段（$group）：按第一步得到的earliestCreationYear聚合，统计每个年份的新建Office总数（$sum:1），以及其中仍有活跃许可证的数量（$sum: "$hasActiveLicense"）。
3. 第三阶段（$project）：移除默认的_id字段，将分组字段重命名为CreationYear，保留统计字段。
4. 第四阶段（$sort）：按创建年份升序排列结果。

预期输出 #

[
  { "CreationYear": 2017, "NewOffices": 1, "Active": 1 },
  { "CreationYear": 2019, "NewOffices": 1, "Active": 1 },
  { "CreationYear": 2021, "NewOffices": 2, "Active": 1 }
]

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