基于MongoDB Aggregation的活动参会及新冠疫苗相关数据统计咨询

需求说明 #

• 统计不同活动的总参会人数
• 统计各活动已接种新冠疫苗的参会人数
• 统计各活动希望现场接种新冠疫苗的参会人数

数据集合说明 #

events集合（存储所有活动基础信息） #

[{
  "_id": "87154163-d277-42cc-912c-e316dac5140d",
  "eventType": "A",
  "date": {
    "$date": "2021-09-04T00:00:00Z"
  },
  "location": "Japan",
  "__v": 0
},{
  "_id": "0b168c44-4f38-4f86-8ee6-e077333aca95",
  "eventType": "B",
  "date": {
    "$date": "2021-09-08T00:00:00Z"
  },
  "location": "Korea",
  "__v": 0
},{
  "_id": "91205d34-4480-4e4e-bdf7-fe66e46922b0",
  "eventType": "C",
  "date": {
    "$date": "2021-10-09T00:00:00Z"
  },
  "location": "United States",
  "__v": 0
},{
  "_id": "d4d81da6-b453-4a31-999f-a2ea04848ee9",
  "eventType": "D",
  "date": {
    "$date": "2021-10-17T00:00:00Z"
  },
  "location": "India",
  "__v": 0
},{
  "_id": "b1606383-0382-4985-bbb1-44f2ef35efbe",
  "eventType": "E",
  "date": {
    "$date": "2021-10-17T00:00:00Z"
  },
  "location": "Germany",
  "__v": 0
}]

attendees集合（存储参会者报名和疫苗状态信息） #

[{
  "_id": "4d4649f0-27c7-11ec-a6de-c566600748e5",
  "firstName": "Abigail",
  "lastName": "Ava",
  "zipCode": 12345,
  "COVID19": {
    "WantedCOVIDvaccine": "no",
    "ReceivedVaccine": "no"
  },
  "event": ["A", "B"],
  "__v": 0
},{
  "_id": "8776ae80-27c7-11ec-a6de-c566600748e5",
  "firstName": "Alexandra",
  "lastName": "Claire",
  "zipCode": 45678,
  "COVID19": {
    "WantedCOVIDvaccine": "no",
    "ReceivedVaccine": "yes"
  },
  "event": ["A"],
  "__v": 0
},{
  "_id": "963eb570-27c7-11ec-a6de-c566600748e5",
  "firstName": "Lauren",
  "lastName": "Rachel",
  "zipCode": 54321,
  "COVID19": {
    "WantedCOVIDvaccine": "yes",
    "ReceivedVaccine": "no"
  },
  "event": ["C", "D"],
  "__v": 0
},{
  "_id": "cc8f6ed0-27c7-11ec-a6de-c566600748e5",
  "firstName": "Michelle",
  "lastName": "Tracey",
  "zipCode": 12345,
  "COVID19": {
    "WantedCOVIDvaccine": "no",
    "ReceivedVaccine": "yes"
  },
  "event": ["A", "C", "D"],
  "__v": 0
},{
  "_id": "e9a124f0-27c7-11ec-a6de-c566600748e5",
  "firstName": "Theresa",
  "lastName": "Sue",
  "zipCode": 91586,
  "COVID19": {
    "WantedCOVIDvaccine": "no",
    "ReceivedVaccine": "yes"
  },
  "event": ["B", "C", "D"],
  "__v": 0
},{
  "_id": "09969a10-27c8-11ec-a6de-c566600748e5",
  "firstName": "Christopher",
  "lastName": "Penelope",
  "zipCode": 12362,
  "COVID19": {
    "WantedCOVIDvaccine": "no",
    "ReceivedVaccine": "yes"
  },
  "event": ["A", "D"],
  "__v": 0
},{
  "_id": "610ff160-27c8-11ec-a6de-c566600748e5",
  "firstName": "Peter",
  "lastName": "Diane",
  "zipCode": 45678,
  "COVID19": {
    "WantedCOVIDvaccine": "yes",
    "ReceivedVaccine": "yes"
  },
  "event": ["A", "C"],
  "__v": 0
}]

完整聚合查询语句 #

在attendees集合执行以下查询即可得到目标结果：

db.attendees.aggregate([
  // 拆分参会者的多活动报名数组，每个活动对应一条独立记录
  { $unwind: "$event" },
  // 按活动分组，统计三个核心指标
  {
    $group: {
      _id: "$event",
      totalAttendees: { $count: {} },
      vaccinatedCount: {
        $sum: { $cond: [{ $eq: ["$COVID19.ReceivedVaccine", "yes"] }, 1, 0] }
      },
      wantedVaccineCount: {
        $sum: { $cond: [{ $eq: ["$COVID19.WantedCOVIDvaccine", "yes"] }, 1, 0] }
      }
    }
  },
  // 聚合已有的活动统计数据
  {
    $group: {
      _id: null,
      stats: {
        $push: {
          k: "$_id",
          v: {
            total: "$totalAttendees",
            vaccinated: "$vaccinatedCount",
            wanted: "$wantedVaccineCount"
          }
        }
      }
    }
  },
  // 关联events集合获取全部活动，补全无参会者的活动
  {
    $lookup: {
      from: "events",
      pipeline: [],
      as: "allEvents"
    }
  },
  // 转换统计数据为映射格式，提取所有活动类型
  {
    $addFields: {
      allEventTypes: {
        $map: {
          input: "$allEvents",
          in: "$$this.eventType"
        }
      },
      statsMap: { $arrayToObject: "$stats" }
    }
  },
  // 构造符合要求的最终输出格式
  {
    $project: {
      _id: 0,
      AttendeesEvents: {
        $arrayToObject: {
          $map: {
            input: "$allEventTypes",
            in: {
              k: "$$this",
              v: { $ifNull: ["$statsMap.$$this.total", 0] }
            }
          }
        }
      },
      Vaccinated: {
        $arrayToObject: {
          $map: {
            input: "$allEventTypes",
            in: {
              k: "$$this",
              v: { $ifNull: ["$statsMap.$$this.vaccinated", 0] }
            }
          }
        }
      },
      WantedVaccine: {
        $arrayToObject: {
          $map: {
            input: "$allEventTypes",
            in: {
              k: "$$this",
              v: { $ifNull: ["$statsMap.$$this.wanted", 0] }
            }
          }
        }
      }
    }
  }
])

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