从MongoDB文档按邮箱分组统计客户流媒体服务激活天数

问题说明 #

我存储了客户流媒体服务的激活/停用记录，客户可随时执行激活、停用操作，甚至同一月内多次操作。需要按邮箱分组，统计指定时间段内每个客户每次激活周期的服务天数。

示例原始数据 #

[
  {
    "_id": 1,
    "email": "customer1@email.com",
    "packageid": "movies",
    "command": "activated",
    "tid": "123",
    "createdAt": ISODate("2021-06-08")
  },
  {
    "_id": 2,
    "email": "customer2@email.com",
    "packageid": "movies",
    "command": "activated",
    "tid": "124",
    "createdAt": ISODate("2021-06-20")
  },
  {
    "_id": 3,
    "email": "customer1@email.com",
    "packageid": "movies",
    "command": "deactivated",
    "tid": "1234",
    "createdAt": ISODate("2021-06-10")
  },
  {
    "_id": 4,
    "email": "customer2@email.com",
    "packageid": "movies",
    "command": "deactivated",
    "tid": "1244",
    "createdAt": ISODate("2021-06-22")
  },
  {
    "_id": 5,
    "email": "customer1@email.com",
    "packageid": "movies",
    "command": "activated",
    "tid": "123",
    "createdAt": ISODate("2021-06-11")
  },
  {
    "_id": 6,
    "email": "customer2@email.com",
    "packageid": "movies",
    "command": "activated",
    "tid": "1244",
    "createdAt": ISODate("2021-06-23")
  },
  {
    "_id": 7,
    "email": "customer1@email.com",
    "packageid": "movies",
    "command": "deactivated",
    "tid": "1237",
    "createdAt": ISODate("2021-06-15")
  },
  {
    "_id": 8,
    "email": "customer2@email.com",
    "packageid": "movies",
    "command": "deactivated",
    "tid": "1244",
    "createdAt": ISODate("2021-06-25")
  }
]

预期输出 #

[
  {
    "email":"customer1@email.com",
    "packageid":"movies",
    "days": 3
  },
  {
    "email":"customer1@email.com",
    "packageid":"movies",
    "days": 5
  },
  {
    "email":"customer2@email.com",
    "packageid":"movies",
    "days": 3
  },
  {
    "email":"customer2@email.com",
    "packageid":"movies",
    "days": 3
  }
]

解决方案：MongoDB聚合查询 #

使用以下聚合管道实现需求，每一步作用如下：

• 筛选指定时间段内的记录
• 按邮箱和套餐ID分组，收集每个用户的操作记录并按时间排序
• 将激活、停用操作配对，生成每个服务激活周期的起止时间
• 计算每个周期的天数（包含起止当天）
• 整理成预期输出格式

db.collection.aggregate([
  // 筛选2021年6月的记录，可根据需求修改时间段
  {
    $match: {
      createdAt: {
        $gte: ISODate("2021-06-01"),
        $lte: ISODate("2021-06-30")
      }
    }
  },
  // 按邮箱和套餐分组，收集操作记录
  {
    $group: {
      _id: {
        email: "$email",
        packageid: "$packageid"
      },
      actions: {
        $push: {
          command: "$command",
          createdAt: "$createdAt"
        }
      }
    }
  },
  // 确保操作记录按时间顺序排列
  {
    $addFields: {
      actions: {
        $sortArray: {
          input: "$actions",
          sortBy: { createdAt: 1 }
        }
      }
    }
  },
  // 配对激活与停用操作，生成周期数组
  {
    $addFields: {
      periods: {
        $reduce: {
          input: "$actions",
          initialValue: { currentActivation: null, periods: [] },
          in: {
            $cond: {
              if: { $eq: ["$$this.command", "activated"] },
              then: {
                currentActivation: "$$this.createdAt",
                periods: "$$value.periods"
              },
              else: {
                currentActivation: null,
                periods: {
                  $concatArrays: [
                    "$$value.periods",
                    [{
                      start: "$$value.currentActivation",
                      end: "$$this.createdAt"
                    }]
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  // 提取周期数组，移除临时变量
  {
    $addFields: {
      periods: "$periods.periods"
    }
  },
  // 展开每个周期记录
  {
    $unwind: "$periods"
  },
  // 计算每个周期的天数（日期差+1，包含起止当天）
  {
    $addFields: {
      days: {
        $add: [
          { $dateDiff: { startDate: "$periods.start", endDate: "$periods.end", unit: "day" } },
          1
        ]
      }
    }
  },
  // 整理输出字段
  {
    $project: {
      _id: 0,
      email: "$_id.email",
      packageid: "$_id.packageid",
      days: 1
    }
  }
])

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