从mongodb文档获取客户活动天数

Question

我有一个用于客户流激活的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
}

编辑:任何用户都可以在他们想要的任何时候激活然后停用服务，有时用户会在同一个月内多次激活和停用。我想知道客户被激活了多少天。

Answer 1

我们可以用$setWindowFields计算$dateDiff和$group之和。

db.collection.aggregate([
  {
    "$setWindowFields": {
      "partitionBy": {
        email: "$email",
        packageid: "$packageid"
      },
      "sortBy": {
        "createdAt": 1
      },
      "output": {
        "next": {
          $shift: {
            output: "$createdAt",
            by: 1
          }
        }
      }
    }
  },
  {
    $match: {
      "command": "activated"
    }
  },
  {
    $project: {
      email: "$email",
      packageid: "$packageid",
      "days": {
        "$dateDiff": {
          startDate: "$createdAt",
          endDate: {
            $ifNull: [
              "$next",
              "$$NOW"
            ]
          },
          unit: "day"
        }
      }
    }
  }
])

这是供您参考的蒙戈游乐场。