如何统计归属Active办公区的Active与Inactive客户数量？

原本计划按Status分组统计客户状态，但需先筛选出所属办公区为Active的客户，再统计这些客户的Active（Status=1）和Inactive（Status=0）数量。

办公区数据 #

[
  {
    _id: UUID('d0ca792b-6ab3-464c-92ec-241be5551405'),
    Name: 'Office1',
    Status: 'Active'
  },
  {
    _id: UUID('1e49c08b-dfdb-4b29-aa01-63f32086647d'),
    Name: 'Office2',
    Status: 'Inactive'
  },
  {
    _id: UUID('d20d002f-a343-478b-9910-ceb2ee5d9fd3'),
    Name: 'Office3',
    Status: 'Active'
   }
]

客户数据 #

[
  {
    _id: UUID('10734c4f-c7c9-4db0-8bc6-2077cb85c242'),
    Status: 0,
    OfficeId: UUID('d0ca792b-6ab3-464c-92ec-241be5551405')
  },
  {
    _id: UUID('557fe764-c345-4fd7-94fa-086ce9e95415'),
    Status: 0,
    OfficeId: UUID('d20d002f-a343-478b-9910-ceb2ee5d9fd3')
  },
  {
    _id: UUID('b8998295-700b-451e-aff9-8c2a61ee25d3'),
    Status: 0,
    OfficeId: UUID('d0ca792b-6ab3-464c-92ec-241be5551405')
  },
  {
    _id: UUID('f90bce6d-a35d-4733-8d53-3085b15412d7'),
    Status: 1,
    OfficeId: UUID('d20d002f-a343-478b-9910-ceb2ee5d9fd3')
  },
  {
    _id: UUID('3023ad50-6dde-4c1b-b695-c75a147481e1'),
    Status: 0,
    OfficeId: UUID('d0ca792b-6ab3-464c-92ec-241be5551405')
  },
  {
    _id: UUID('74c46c51-6738-4eb8-ba47-a494189db5b4'),
    Status: 1,
    OfficeId: UUID('d20d002f-a343-478b-9910-ceb2ee5d9fd3')
  },
  {
    _id: UUID('447542b4-bf34-4a74-8168-c068248ae703'),
    Status: 1,
    OfficeId: UUID('1e49c08b-dfdb-4b29-aa01-63f32086647d')
  },
  {
    _id: UUID('aa461e7b-5a40-4e74-91f0-82c8df9c6106'),
    Status: 0,
    OfficeId: UUID('d20d002f-a343-478b-9910-ceb2ee5d9fd3')
  },
  {
    _id: UUID('06d224f1-fc7f-4395-934b-41f53bf6439b'),
    Status: 1,
    OfficeId: UUID('1e49c08b-dfdb-4b29-aa01-63f32086647d')
  },
  {
    _id: UUID('6bf2b36d-3470-4d1a-a50e-70b693b02ea5'),
    Status: 0,
    OfficeId: UUID('d20d002f-a343-478b-9910-ceb2ee5d9fd3')
  }
]

解决方案 #

方案1：MongoDB聚合查询 #

适合数据库端直接处理，通过聚合管道关联数据、筛选条件后分组统计：

db.customers.aggregate([
  // 关联办公区集合获取办公区状态
  {
    $lookup: {
      from: "offices",
      localField: "OfficeId",
      foreignField: "_id",
      as: "office_info"
    }
  },
  // 展开关联的办公区数据
  { $unwind: "$office_info" },
  // 筛选办公区状态为Active的客户
  { $match: { "office_info.Status": "Active" } },
  // 按客户Status分组统计数量
  {
    $group: {
      _id: "$Status",
      count: { $sum: 1 }
    }
  },
  // 可选：将数字状态映射为易读名称
  {
    $project: {
      _id: 0,
      customer_status: {
        $cond: { if: { $eq: ["$_id", 1] }, then: "Active", else: "Inactive" }
      },
      count: 1
    }
  }
])

执行结果：

[
  { "customer_status": "Inactive", "count": 6 },
  { "customer_status": "Active", "count": 2 }
]

方案2：Python脚本处理（本地数据场景） #

如果数据是本地列表，用脚本完成筛选和统计：

# 定义办公区和客户数据（省略重复数据，可直接替换为你的数据）
offices = [/* 办公区数据列表 */]
customers = [/* 客户数据列表 */]

# 1. 获取所有Active办公区的ID集合
active_office_ids = {office["_id"] for office in offices if office["Status"] == "Active"}

# 2. 筛选出所属办公区为Active的客户
target_customers = [c for c in customers if c["OfficeId"] in active_office_ids]

# 3. 统计客户状态数量
status_count = {"Inactive": 0, "Active": 0}
for c in target_customers:
    status_count["Active" if c["Status"] == 1 else "Inactive"] += 1

print(status_count)

执行输出：

{'Inactive': 6, 'Active': 2}

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